Using EXTRACT Function In Oracle Database

EXTRACT:- This is oracle datetime function. Using this function extracts and returns the value of a specified datetime field from a datetime or interval expression.

EXAMPLE:-

SQL> select sysdate from dual;

SYSDATE
---------
13-MAY-14

SQL>
SQL> select extract(month from trunc(sysdate)) from dual;

EXTRACT(MONTHFROMTRUNC(SYSDATE))
--------------------------------
                               5

SQL> select extract(day from trunc(sysdate)) from dual;

EXTRACT(DAYFROMTRUNC(SYSDATE))
------------------------------
                            13

SQL> select extract(year from trunc(sysdate)) from dual;

EXTRACT(YEARFROMTRUNC(SYSDATE))
-------------------------------
                           2014
SQL> SELECT EXTRACT(YEAR FROM DATE '1998-03-07') FROM DUAL;

EXTRACT(YEARFROMDATE'1998-03-07')
---------------------------------
                             1998

SQL> SELECT last_name, employee_id, hire_date, department_id
  2    FROM employees
  3    WHERE EXTRACT(YEAR FROM TO_DATE(hire_date, 'DD-MON-RR')) > 2007
  4    AND department_id=80
  5    ORDER BY hire_date;

LAST_NAME                 EMPLOYEE_ID HIRE_DATE DEPARTMENT_ID
------------------------- ----------- --------- -------------
Johnson                           179 04-JAN-08            80
Marvins                           164 24-JAN-08            80
Zlotkey                           149 29-JAN-08            80
Lee                               165 23-FEB-08            80
Ande                              166 24-MAR-08            80
Kumar                             173 21-APR-08            80
Banda                             167 21-APR-08            80

7 rows selected.

How to Reclaim / Resize Table space.

Using this statement you can find how to resize you datafile.

SQL> ALTER DATABASE DATAFILE 'D:\APP\ORADATA\STLBAS\ORBSYS01.DBF' RESIZE 1122M;

Database altered.

Scripts:
-----------

SELECT    'ALTER DATABASE DATAFILE '''
       || FILE_NAME
       || ''' RESIZE '
       || US
       || 'M;'
  FROM (  SELECT DF.TABLESPACE_NAME,
                 DF.FILE_NAME,
                 SUM (DF.BYTES) / 1024 / 1024/1024 "Total Size (MB)",
                 SUM (FS.BYTES) / 1024 / 1024/1024 "Total Free (MB)",
                 ROUND (
                    ( (SUM (DF.BYTES) / 1024 / 1024/1024)
                     - (SUM (FS.BYTES) / 1024 / 1024/1024))
                    + 50)
                    US,
                 TRUNC ( ( (SUM (FS.BYTES) / (SUM (DF.BYTES)) * 100)), 1)
                 || '%'
                    "Free Percent"
            FROM DBA_DATA_FILES DF, DBA_FREE_SPACE FS
           WHERE FS.TABLESPACE_NAME = DF.TABLESPACE_NAME
                 AND DF.TABLESPACE_NAME NOT IN
                        ('USERS', 'UNDOTBS1', 'SYSAUX', 'SYSTEM')
        GROUP BY DF.TABLESPACE_NAME, DF.FILE_NAME
        ORDER BY 1)


Finding Data file Size :
----------------------------

  SELECT DF.TABLESPACE_NAME,
         DF.FILE_NAME,
         ROUND (SUM (DF.BYTES) / 1024 / 1024 / 1024, 2) "Total Size (MB)",
         ROUND (SUM (FS.BYTES) / 1024 / 1024 / 1024, 2) "Total Free (MB)",
         ROUND (
            ( (SUM (DF.BYTES) / 1024 / 1024 / 1024)
             - (SUM (FS.BYTES) / 1024 / 1024 / 1024))
            + 50)
            US,
         TRUNC ( ( (SUM (FS.BYTES) / (SUM (DF.BYTES)) * 100)), 1) || '%'
            "Free Percent"
    FROM DBA_DATA_FILES DF, DBA_FREE_SPACE FS
   WHERE FS.TABLESPACE_NAME = DF.TABLESPACE_NAME
         AND DF.TABLESPACE_NAME NOT IN
                ('USERS', 'UNDOTBS1', 'SYSAUX', 'SYSTEM')
GROUP BY DF.TABLESPACE_NAME, DF.FILE_NAME
ORDER BY 3 DESC

How to find tablespace size in oralc database.

Using this query you can find out you tablespace size and used percentage.

SQL> SELECT DF.TABLESPACE_NAME, SUM(DF.BYTES)/1024/1024 "Total Size (MB)", SUM(FS.BYTES)/1024/1024 "Total Free (MB)",
  2  TRUNC(((SUM(FS.BYTES)/(SUM(DF.BYTES))*100)),1)||'%' "Free Percent"
  3  FROM DBA_DATA_FILES DF, DBA_FREE_SPACE FS
  4  WHERE FS.TABLESPACE_NAME=DF.TABLESPACE_NAME
  5  AND DF.TABLESPACE_NAME NOT IN ('USERS','UNDOTBS1','SYSAUX','SYSTEM')
  6  GROUP BY DF.TABLESPACE_NAME;

TABLESPACE_NAME                Total Size (MB) Total Free (MB) Free Percent
------------------------------ --------------- --------------- -----------------------------------------
DATABAK                                    512             511 99.8%
MIG_BCBL                                  1024            1023 99.9%
ORBLOG                                    1024         801.375 78.2%
ORBIND                                 1072104         1163.25 .1%
RECLAIM_TS                                  52          1.5625 3%
DATA                                      9920        758.4375 7.6%

TABLESPACE_NAME                Total Size (MB) Total Free (MB) Free Percent
------------------------------ --------------- --------------- -----------------------------------------
ORBSYS                                    2048         1012.75 49.4%
EXAMPLE                                    300         21.5625 7.1%
ORBDT2                                    1024             626 61.1%
ORBMAV                                    2048         987.625 48.2%
ORBIMG                                    3324             181 5.4%
SHRINK_TEST                                  5               4 80%
ORBMAS                                    1024        788.8125 77%
ORBDT3                                    2248          58.875 2.6%
TBSTRAN                                   3072         1022.25 33.2%
ORBITS                                    9216        262.6875 2.8%

24 rows selected.