Thursday 31 July 2014

command to check Database Size

command to check DB size:
SQL> SELECT a.data_size + b.temp_size + c.redo_size + d.controlfile_size
   "total_size in GB"
    FROM (SELECT SUM (bytes) / 1024 / 1024/1024 data_size FROM dba_data_files) a,
    (SELECT NVL (SUM (bytes), 0) / 1024 / 1024/1024 temp_size
    FROM dba_temp_files) b,
    (SELECT SUM (bytes) / 1024 / 1024/1024 redo_size FROM sys.v_$log) c,
    (SELECT SUM (BLOCK_SIZE * FILE_SIZE_BLKS) / 1024 / 1024/1024
    controlfile_size
    FROM v$controlfile) d;

total_size in GB
----------------
       14.192627
Alternative,
select 
( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) +
( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) +
( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) +
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) "Size in GB"
from
dual
Size in GB
----------
 14.192627


SQL>

=============================

SQL> SELECT SUBSTR (a.file_id, 1, 3) FILE#, SUBSTR (file_name, 1, 80) file_name,
    a.BYTES / 1024 / 1024 tot_bytes, b.free_bytes / 1024 / 1024 free
    FROM dba_data_files a,
    (SELECT file_id, SUM (BYTES) free_bytes
    FROM dba_free_space
    GROUP BY file_id) b
    WHERE a.file_id = b.file_id(+) AND a.tablespace_name = '&tablespace_name'
    ORDER BY 2;
Enter value for tablespace_name: DEV_OCS
old   7: WHERE a.file_id = b.file_id(+) AND a.tablespace_name = '&tablespace_name'
new   7: WHERE a.file_id = b.file_id(+) AND a.tablespace_name = 'DEV_OCS'

FILE#
------------
FILE_NAME
--------------------------------------------------------------------------------
 TOT_BYTES       FREE
---------- ----------
11
/u01/app/oracle/oradata/gmkp11/DEV_ocs.dbf
      1700    80.5625

12
/u01/app/oracle/oradata/gmkp11/DEV_ocs01.dbf
      5120    1766.25

FILE#
------------
FILE_NAME
--------------------------------------------------------------------------------
 TOT_BYTES       FREE
---------- ----------
====================================
To check used space in database:

SQL> select sum(bytes)/1024/1024/1024 from dba_Segments;

SUM(BYTES)/1024/1024/1024
-------------------------
               9.09033203

No comments:

Post a Comment