All Administrative SQL Scripts for Monitoring and Managing Oracle Database Space Management

This article consists of all the Ready-To-Use SQL Scripts that can be used to monitor and manage the Oracle Database Space Usage.

-- To find the Total Allocated Datafile Size for the Database / To find the Total DB Size:

select sum(bytes)/(1024*1024) TOTAL_ALLOCATED_SIZE_IN_MB, sum(bytes)/(1024*1024*1024) TOTAL_ALLOCATED_SIZE_IN_GB from v$datafile;

-- To find how much size is currently used by Database Objects:

select sum(bytes)/(1024*1024) TOTAL_USED_SIZE_IN_MB, sum(bytes)/(1024*1024*1024) TOTAL_USED_SIZE_IN_GB from dba_segments;
select sum(bytes)/(1024*1024) TOTAL_USED_SIZE_IN_MB, sum(bytes)/(1024*1024*1024) TOTAL_USED_SIZE_IN_GB from dba_extents;

-- To find the Total Allocated Tempfile Size for the Database:

select sum(bytes)/(1024*1024) TOTAL_ALLOCATED_SIZE_IN_MB, sum(bytes)/(1024*1024*1024) TOTAL_ALLOCATED_SIZE_IN_GB from v$tempfile;

-- To find how much space is currently used by given Tablespace:

select sum(bytes)/(1024*1024) TOTAL_USED_SIZE_IN_MB, sum(bytes)/(1024*1024*1024) TOTAL_USED_SIZE_IN_GB from dba_segments where tablespace_name='&TABLESPACE_NAME';

-- To find the Total Allocated Space for Each Datafile of the given Tablespace:

set lines 250 pages 50000 long 2000000000 longchunksize 2000000
col tablespace_name format a20
col file_name format a50
select tablespace_name, file_name, sum(bytes)/(1024*1024) TOTAL_ALLOCATED_SPACE_IN_MB, sum(bytes)/(1024*1024*1024) TOTAL_ALLOCATED_SPACE_IN_GB
from dba_data_files
where tablespace_name = '&TBS_NAME'
group by tablespace_name, file_name
/

-- To find how much Free Space is available in the given Tablespace:

select tablespace_name, sum(bytes)/(1024*1024) FREE_SPACE_IN_MB, sum(bytes)/(1024*1024*1024) FREE_SPACE_IN_GB
from dba_free_space
where tablespace_name = '&TBS_NAME'
group by tablespace_name
/

-- To find how much is the Allocated Size and Free Size for each Datafile from all the corresponding Tablespaces:

set lines 250 pages 50000 long 2000000000 longchunksize 2000000
col tablespace_name format a20
col file_name format a85
SELECT a.tablespace_name, a.file_name, sum(a.bytes)/(1024*1024*1024) ALLOCATED_GB, b.FREE_GB
FROM dba_data_files a,
     (SELECT file_id, sum(bytes)/(1024*1024*1024) FREE_GB FROM dba_free_space b GROUP BY file_id) b
GROUP BY a.tablespace_name, a.file_name, b.free_gb
ORDER BY a.tablespace_name;

-- Show the Objects that are more than nnn GB from the given Schema

set lines 250 pages 50000
col owner format a15
col segment_name format a38
col partition_name format a38

SELECT owner, segment_name, segment_type, tablespace_name, sum(bytes)/(1024*1024*1024) SIZE_IN_GB 
FROM dba_segments 
WHERE owner='&SCHEMA_NAME'
GROUP BY owner, segment_name, segment_type, tablespace_name 
HAVING sum(bytes)/(1024*1024*1024) > &MORE_THAN_WHAT_OBJECT_SIZE_TO_SHOW -- show only the objects that is more than this passed value in GB
ORDER BY sum(bytes)/(1024*1024*1024) desc;

-- Show the Objects that are more than nnn MB from the given Schema

set lines 250 pages 50000
col owner format a15
col segment_name format a38
col partition_name format a38

SELECT owner, segment_name, segment_type, tablespace_name, sum(bytes)/(1024*1024) SIZE_IN_MB 
FROM dba_segments 
WHERE owner='&SCHEMA_NAME'
GROUP BY owner, segment_name, segment_type, tablespace_name 
HAVING sum(bytes)/(1024*1024) > &MORE_THAN_WHAT_OBJECT_SIZE_TO_SHOW -- show only the objects that is more than this passed value in MB
ORDER BY sum(bytes)/(1024*1024) desc;

-- Show the Top 50 Big Objects from the Database

set lines 250 pages 50000
col owner format a25
col segment_name format a38
col partition_name format a38
select * from 
(
select owner, segment_name, segment_type, tablespace_name, sum(bytes)/(1024*1024*1024) SIZE_IN_GB
from dba_segments
group by owner, segment_name, segment_type, tablespace_name 
order by sum(bytes)/(1024*1024*1024) desc
)
where rownum < 51;

-- Show the Top 50 Big Objects from the Database Excluding the SYS related Schemas like SYS, SYSTEM, AUDSYS, DBSNMP, etc

set lines 250 pages 50000
col owner format a25
col segment_name format a38
col partition_name format a38
select * from 
(
select owner, segment_name, segment_type, tablespace_name, sum(bytes)/(1024*1024*1024) SIZE_IN_GB
from dba_segments
where owner not in ('SYS','SYSTEM','AUDSYS','DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC')
group by owner, segment_name, segment_type, tablespace_name 
order by sum(bytes)/(1024*1024*1024) desc
)
where rownum < 51;

-- To find the default tablespace for a given user:

-- define owner=HR
-- define username=HR
-- set verify off
select default_tablespace from dba_users where username='&USERNAME';

-- Show all the Schemas ordered by their Size

set lines 250 pages 50000
col owner format a25
select owner, sum(bytes)/(1024*1024) SIZE_IN_MB, sum(bytes)/(1024*1024*1024) SIZE_IN_GB from dba_segments group by owner order by 2 desc, 1;
select owner, sum(bytes)/(1024*1024) SIZE_IN_MB, sum(bytes)/(1024*1024*1024) SIZE_IN_GB from dba_segments group by owner order by 2, 1;

-- To find the Total Number of Tables from the given Schema

select count(segment_name) NO_OF_TABLES from dba_segments where owner='&OWNER' and segment_type like'TABLE%';
## -- NOTE: DBA_SEGMENTS will not show empty Tables/Objects

select count(table_name) NO_OF_TABLES from dba_tables where owner='&OWNER';
## -- NOTE: DBA_TABLES will show ALL the Tables even if it is empty

-- To find the list of objects for a given user ordered by their Size:

set lines 250 pages 50000
col owner format a25
col segment_name format a38
col tablespace_name format a36
select owner, segment_name, tablespace_name, bytes/(1024*1024*1024) SIZE_IN_GB from dba_segments where owner='&OWNER' order by owner, bytes/(1024*1024*1024) desc, segment_name, tablespace_name;
select owner, segment_name, tablespace_name, bytes/(1024*1024*1024) SIZE_IN_GB from dba_segments where owner='&OWNER' order by owner, bytes/(1024*1024*1024), segment_name, tablespace_name;
select owner, segment_name, tablespace_name, bytes/(1024*1024) SIZE_IN_MB from dba_segments where owner='&OWNER' order by owner, bytes/(1024*1024) desc, segment_name, tablespace_name;
select owner, segment_name, tablespace_name, bytes/(1024*1024) SIZE_IN_MB from dba_segments where owner='&OWNER' order by owner, bytes/(1024*1024), segment_name, tablespace_name;

-- To find the list of objects from all schemas ordered by their Size:

set lines 250 pages 50000
col owner format a25
col segment_name format a38
col tablespace_name format a36
select owner, segment_name, tablespace_name, bytes/(1024*1024*1024) SIZE_IN_GB from dba_segments order by owner, bytes/(1024*1024*1024) desc, segment_name, tablespace_name;
select owner, segment_name, tablespace_name, bytes/(1024*1024*1024) SIZE_IN_GB from dba_segments order by owner, bytes/(1024*1024*1024), segment_name, tablespace_name;
select owner, segment_name, tablespace_name, bytes/(1024*1024) SIZE_IN_MB from dba_segments order by owner, bytes/(1024*1024) desc, segment_name, tablespace_name;
select owner, segment_name, tablespace_name, bytes/(1024*1024) SIZE_IN_MB from dba_segments order by owner, bytes/(1024*1024), segment_name, tablespace_name;

-- To find the list of tablespaces for a given user
select distinct(tablespace_name) from dba_tables where owner='&OWNER';
select distinct(def_tablespace_name) from dba_part_tables where owner='&OWNER';
select distinct(tablespace_name) from dba_segments where owner='&OWNER';

-- To find the tablespace for a given object:

set lines 250 pages 5000
col owner format a10
col segment_name format a39
col partition_name format a38
col segment_type format a30
col segment_subtype format a38
col tablespace_name format a30
select owner, segment_name, segment_type, tablespace_name, bytes/(1024*1024) from dba_segments where segment_name='&SEGMENT_NAME';

-- To find which objects are stored in a given tablespace:

set lines 250 pages 50000
col owner format a20
col segment_name format a38
col partition_name format a38
col segment_type format a38
col tablespace_name format a20
select owner, segment_name, partition_name, segment_type, tablespace_name, bytes/(1024*1024) from dba_segments where tablespace_name='&TABLESPACE_NAME';
select owner, segment_name, segment_type, tablespace_name, bytes/(1024*1024) from dba_segments where tablespace_name='&TABLESPACE_NAME';

-- To find whether the Table is having any Parent Table
-- The PK_TABLE_NAME in the last query is the parent table name

set lines 250 pages 5000
col owner format a20
col index_owner format a20
col fk_constraint_name format a30
col fk_table_name format a30
col pk_constraint_name format a30
col pk_table_name format a30
col index_name format a30
col r_owner format a20
col r_constraint_name format a30
define v_constraint_name='&Type_FK_Constraint_Name_in_CAPS';
select owner, constraint_name FK_CONSTRAINT_NAME, table_name FK_TABLE_NAME, r_owner, r_constraint_name from user_constraints where constraint_name='&v_constraint_name';
select owner, constraint_name PK_CONSTRAINT_NAME, table_name PK_TABLE_NAME, index_name, index_owner, status from user_constraints where constraint_name in (select r_constraint_name from user_constraints where constraint_name='&v_constraint_name') -- This PK_TABLE_NAME is the parent table of the FK Constraint;

-- To check the Constraints and Related Indexes Information

set lines 250 pages 5000
col column_name format a30
col owner format a20
col index_owner format a20
col table_owner format a20
col tablespace_name format a18
col index_type format a23
col constraint_name format a30
col table_name format a30
col index_name format a30
select owner, constraint_name, constraint_type, table_name, index_name, index_owner, status from user_constraints;
select table_name, index_name, table_owner, table_type, index_type, partitioned, compression, logging, tablespace_name, status, visibility, segment_created from user_indexes order by table_name, index_name -- use this for 11.2.0.2 onwards;
select table_name, index_name, table_owner, table_type, index_type, partitioned, compression, logging, tablespace_name, status, visibility from user_indexes -- use this for 10g and 11.2.0.1 version as there is no segment_created column;
select index_name, table_name, column_name, column_position, descend from user_ind_columns order by index_name, table_name, column_position -- to check in which column the index is created;

col search_condition format a36
col r_owner format a20
col r_constraint_name format a30
select owner, constraint_name, deferrable, deferred, status, search_condition, validated, invalid from user_constraints -- search_condition will show the column name on which constraints are defined;
select owner, constraint_name, table_name, r_owner, r_constraint_name, delete_rule from user_constraints;
select owner, constraint_name, last_change, view_related, generated, bad, rely from user_constraints;

-- To identify what Objects require Statistics Gathering

select table_name, owner, last_analyzed, stale_stats from dba_tab_statistics where table_name='&TABLE_NAME' and owner='&TABLE_OWNER' -- If stale_stats is YES and last_analyzed date is NOT recent, then gather the statistics;