Important SQL Queries for DBA’s

How to find database size?

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

                              (OR)

SQL>select sum(result) from (

select 'dba_data_files',sum(bytes)/1024/1024/1024 result from dba_data_files

union

select 'dba_temp_files',sum(bytes)/1024/1024/1024 result from dba_temp_files);

(OR)

SELECT ROUND (SUM (used.bytes) / 1024 / 1024 / 1024) || ' GB'

"Database Size",ROUND (SUM (used.bytes) / 1024 / 1024 / 1024)

- ROUND (free.p / 1024 / 1024 / 1024)|| ' GB' "Used space",

ROUND (free.p / 1024 / 1024 / 1024) || ' GB' "Free space"

FROM (SELECT   bytes FROM v$datafile UNION ALL

SELECT bytes FROM v$tempfile UNION ALL SELECT bytes FROM v$log) used,

(SELECT SUM (bytes) AS p FROM dba_free_space)free GROUP BY free.p;

How to find Schema size?

SQL> select sum(bytes)/1024/1024 from dba_segments where OWNER='SCOTT';

How to find tablespace size?

SQL> select tablespace_name,file_name,(bytes/1024/1024) from dba_data_files where tablespace_name='USERS'

How to find free space of tablespace?

SQL> select sum(bytes/1024/1024/1024) from dba_free_space where TABLESPACE_NAME= 'USERS’;

How to find datafile size?

SQL>select file#,status,enabled,checkpoint_change#,bytes,create_bytes,name from v$datafile;

How to find table size?

SQL>select segment_name,segment_type,bytes/1024/1024 MB

 from dba_segments

 where segment_type='TABLE' and segment_name= <’table_name'>;

How to find used size in database?

dba_segments

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

How to find free space in database?

dba_free_space

select sum(bytes)/1024/1024/1024 from dba_free_space;

How to find redo log size?

SELECT sum(bytes)/1024/1024 redo_size FROM sys.v_$log;

How to find temp file size?

SELECT sum(bytes)/1024/1024 temp_size FROM dba_temp_files;

How to find default tablespace in user?

SQL> select default_tablespace from dba_users where username='<User_name>’;

How find default tablespace, user in particular table?

SQL> select OWNER,TABLESPACE_NAME from dba_tables where TABLE_NAME='<Table_name>';