Space Management

Space In Temp Tablespace

SELECT A.TABLESPACE_NAME TABLESPACE, D.MB_TOTAL,SUM(A.USED_BLOCKS * D.BLOCK_SIZE) / 1024 / 1024 MB_USED, D.MB_TOTAL - SUM (A.USED_BLOCKS * D.BLOCK_SIZE) / 1024 / 1024 MB_FREE FROM V$SORT_SEGMENT A, (SELECT B.NAME, C.BLOCK_SIZE, SUM (C.BYTES) / 1024 / 1024 MB_TOTAL FROM V$TABLESPACE B, V$TEMPFILE C WHERE B.TS#= C.TS# GROUP BY B.NAME, C.BLOCK_SIZE) D WHERE A.TABLESPACE_NAME = D.NAME GROUP BY A.TABLESPACE_NAME, D.MB_TOTAL;

Tablespace Free Space

COLUMN "TABLESPACE" FORMAT A13

COLUMN "USED MB"    FORMAT 99,999,999

COLUMN "FREE MB"    FORMAT 99,999,999

COLUMN "TOTAL MB"   FORMAT 99,999,999

SELECT F.TABLESPACE_NAME,

A.BYTES/1024/1024 " TOTAL MB" ,

ROUND(U.BYTES/1024/1024) "USED MB",

ROUND(F.BYTES/1024/1024) "FREE MB",

ROUND((100 * (F.BYTES/1024/1024))/(A.BYTES/1024/1024)) "PCT. FREE"

FROM SM$TS_AVAIL A,SM$TS_USED U, SM$TS_FREE F

WHERE A.TABLESPACE_NAME=U.TABLESPACE_NAME

AND U.TABLESPACE_NAME=F.TABLESPACE_NAME

AND A.TABLESPACE_NAME=F.TABLESPACE_NAME;

SELECT TABLESPACE_NAME,BYTES/1024/1024 "MB" FROM SM$TS_FREE;

SELECT TABLESPACE_NAME,BYTES/1024/1024 "MB" FROM SM$TS_USED;

SELECT TABLESPACE_NAME,BYTES/1024/1024 "MB" FROM SM$TS_AVAIL;

COLUMN "TABLESPACE" FORMAT A13

COLUMN "USED MB"    FORMAT 99,999,999

COLUMN "FREE MB"    FORMAT 99,999,999

COLUMN "TOTAL MB"   FORMAT 99,999,999

SELECT

   FS.TABLESPACE_NAME                          "TABLESPACE",

   (DF.TOTALSPACE - FS.FREESPACE)              "USED MB",

   FS.FREESPACE                                "FREE MB",

   DF.TOTALSPACE                               "TOTAL MB",

   ROUND(100 * (FS.FREESPACE / DF.TOTALSPACE)) "PCT. FREE"

FROM

   (SELECT

      TABLESPACE_NAME,

      ROUND(SUM(BYTES) / 1048576) TOTALSPACE

   FROM

      DBA_DATA_FILES

   GROUP BY

      TABLESPACE_NAME

   ) DF,

   (SELECT

      TABLESPACE_NAME,

      ROUND(SUM(BYTES) / 1048576) FREESPACE

   FROM

      DBA_FREE_SPACE

   GROUP BY

      TABLESPACE_NAME

   ) FS

WHERE

   DF.TABLESPACE_NAME = FS.TABLESPACE_NAME(+);

Add Datafile

ALTER tablespace USERS add DATAFILE '/testdb1/tables/users01_04.dbf' size 10240M;

Resize Datafile

ALTER database DATAFILE '/u03/oradata/DWHP1/rental03.dbf' resize 40000M;

Add Datafile in ASM

ALTER TABLESPACE DATA1 add datafile '+DATA_R1' size 10G

Resize Datafile In ASM

ALTER DATABASE DATAFILE '+DATA_R1/testdb1/datafile/system.370.745406019' RESIZE 2000M;

Add a autoextendable Datafile

ALTER TABLESPACE DATA1 add datafile '+DATA_R1' size 10G AUTOEXTEND on maxsize 32767M;

Resize autoextendable Datafile

ALTER DATABASE DATAFILE '+DATA_R1/testdb1/datafile/system.370.745406019'  autoextend on maxsize 32767M;

Check Size Of All Tablespace Based On Max Size

SET LINESIZE 140

SET PAGESIZE 100

COL FILE_NAME FOR A60

SELECT TABLESPACE_NAME,SUM(BYTES/1024/1024) "SIZEMB", SUM(MAXBYTES/1024/1024) "MAXMB", ROUND((100 * (SUM(MAXBYTES)-SUM(BYTES)))/NULLIF(SUM(MAXBYTES),0)) "PCT. FREE" FROM DBA_DATA_FILES  GROUP BY TABLESPACE_NAME;

Check Size Of All Datafiles Based On Max Size

SET LINESIZE 140

SET PAGESIZE 100

COL FILE_NAME FOR A60

SELECT TABLESPACE_NAME,FILE_NAME,BYTES/1024/1024 "SIZEMB", MAXBYTES/1024/1024 "MAXMB", AUTOEXTENSIBLE FROM DBA_DATA_FILES;

Check When Datafile Is Added To Tablespace

COLUMN YEAR FORMAT A5

COLUMN MONTH FORMAT A5

COLUMN NAME  FORMAT A55

SELECT TO_CHAR(CREATION_TIME,'RRRR') YEAR, TO_CHAR(CREATION_TIME,'MM') MONTH,NAME, ROUND(SUM(BYTES)/1024/1024/1024) SIZE_GBS FROM  V$DATAFILE GROUP BY  NAME, TO_CHAR(CREATION_TIME,'RRRR'), TO_CHAR(CREATION_TIME,'MM') ORDER BY 1,2;

Check size of database

SET LINESIZE  100

SET PAGESIZE  9999

SET VERIFY    off

SELECT DATAFILESIZE.DATA_SIZE + TEMPFILESIZE.TEMP_SIZE + REDOLOGSIZE.REDO_SIZE

       "Total_size in MB"

FROM   (SELECT SUM(BYTES) / 1024 / 1024 DATA_SIZE

        FROM   DBA_DATA_FILES) DATAFILESIZE,

       (SELECT NVL(SUM(BYTES) / 1024 / 1024, 0) TEMP_SIZE

        FROM   DBA_TEMP_FILES) TEMPFILESIZE,

       (SELECT SUM(BYTES) / 1024 / 1024 REDO_SIZE

        FROM   SYS.V_$LOG) REDOLOGSIZE;