Some Important Queries for an Apps DBA/DBA

Some Important Queries for an Apps DBA/DBA

1. Query to find Database Size

The database mainly comprises of datafiles, temp files and redo log files.

The biggest portion of a database’s size comes from the datafiles.

To find out how many megabytes are allocated to all datafiles:

SELECT sum(bytes)/1024/1024 data_size FROM dba_data_files;

2. Query to get the size of all TEMP files:

SELECT nvl(sum(bytes),0)/1024/1024 temp_size FROM dba_temp_files;

3. Query to get the size of the on-line redo-logs:

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

Finally, summing up the three above, total database size can be found:

SELECT (dsize.data_size + tsize.temp_size + rsize.redo_size)/1024/1024 "total_size"

FROM (SELECT sum(bytes) data_sizeFROM dba_data_files ) dsize,

(SELECT nvl(sum(bytes),0) temp_size FROM dba_temp_files ) tsize,

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

4. Query to find space used by a database user.

Following query can be used to know the space used by the logged in user in MBs:

SELECT sum(bytes)/1024/1024 user_size FROM user_segments;

5.Query to find the space occupied by all the users in a database.

This requires access to dba_segments table:

SELECT owner, sum(bytes)/1024/1024 total_size FROM dba_segments

GROUP BY owner ORDER BY total_size DESC;

Total space occupied by all users:

SELECT sum(bytes)/1024/1024 total_size FROM dba_segments;

6. Query to find free space in temporary tablesapce:

SELECT tablespace_name,SUM(bytes_used),SUM(bytes_free) FROM

V$temp_space_header GROUP BY tablespace_name;

7. Script to find Table size in a database.

select sum(BYTES/1024/1024) as TOTAL_GIG from user_segments where

SEGMENT_NAME = 'TABLE_NAME';

Please Note: Need to execute as owner of the table.