Database Storage Details
Customer can view database storage details which include database storage size and high volume tables using Oracle Database Actions. This section provides SQL statements that can be used to view these details.
Note that the details generated via these SQL statements represent approximate database usage at a given point in time.
Database Storage - Total Size (in Terabytes)
Use the following SQL to view total database storage in terabytes (TB):
WITH my_nested_tables AS (
SELECT /*+ materialize */ owner, parent_table_name, table_name FROM dba_nested_tables
WHERE owner IN (SELECT DISTINCT (owner) FROM dba_segments WHERE tablespace_name NOT LIKE 'UNDO%'
AND tablespace_name NOT IN ('SYSTEM', 'SYSAUX') AND owner IN (
SELECT username FROM dba_users WHERE common = 'NO' AND oracle_maintained = 'N'))),
top_nested_tables AS (
SELECT /*+ materialize */ owner, parent_table parent_table_name, table_name FROM
(SELECT owner, level, CONNECT_BY_ROOT parent_table_name parent_table, table_name FROM my_nested_tables
CONNECT BY parent_table_name = PRIOR table_name) WHERE (owner, parent_table) NOT IN (
SELECT owner, table_name FROM my_nested_tables))
SELECT SUM(bytes) / 1024 / 1024 / 1024 /1024 total_bytes_tb
FROM (
SELECT tab.owner, CAST('TABLE' AS VARCHAR2(11)) object_type,
CASE WHEN tab.iot_type = 'IOT_OVERFLOW' THEN tab.iot_name ELSE tab.table_name END object_name,
CASE WHEN tab.iot_type = 'IOT_OVERFLOW' THEN tab.table_name END auxiliary_name, nvl(seg_tab.bytes, 0) bytes,
nvl(tab.num_rows, 0) AS row_count FROM dba_tables tab
LEFT OUTER JOIN dba_segments seg_tab ON seg_tab.owner = tab.owner AND seg_tab.segment_name = tab.table_name
WHERE tab.owner IN (SELECT DISTINCT (owner) FROM dba_segments WHERE tablespace_name NOT LIKE 'UNDO%'
AND tablespace_name NOT IN ('SYSTEM', 'SYSAUX') AND owner IN (SELECT username FROM dba_users WHERE common = 'NO'
AND oracle_maintained = 'N')) AND nvl(tab.iot_type, 'NORMAL')!= 'IOT'
UNION ALL
SELECT ind.table_owner owner, CAST('INDEX' AS VARCHAR2(11)) object_type, nvl(nst.parent_table_name, ind.table_name) table_name,
index_name auxiliary_name, nvl(seg_ind.bytes, 0) bytes, NULL AS row_count FROM dba_indexes ind
LEFT OUTER JOIN top_nested_tables nst ON nst.owner = ind.table_owner AND nst.table_name = ind.table_name
LEFT OUTER JOIN dba_segments seg_ind ON seg_ind.owner = ind.owner AND seg_ind.segment_name = ind.index_name
WHERE ind.table_owner IN (SELECT DISTINCT (owner) FROM dba_segments WHERE tablespace_name NOT LIKE 'UNDO%'
AND tablespace_name NOT IN ('SYSTEM', 'SYSAUX') AND owner IN ( SELECT username FROM dba_users WHERE common = 'NO' AND oracle_maintained = 'N'))
UNION ALL
SELECT lob.owner, CAST('LOB' AS VARCHAR2(11)) object_type, lob.table_name, lob.column_name auxiliary_name, nvl(seg_lob.bytes, 0) bytes, NULL AS row_count
FROM dba_lobs lob LEFT OUTER JOIN dba_segments seg_lob ON seg_lob.owner = lob.owner AND seg_lob.segment_name = lob.segment_name
WHERE lob.owner IN (SELECT DISTINCT (owner) FROM dba_segments WHERE tablespace_name NOT LIKE 'UNDO%' AND tablespace_name NOT IN ('SYSTEM', 'SYSAUX')
AND owner IN (SELECT username FROM dba_users WHERE common = 'NO' AND oracle_maintained = 'N'))
UNION ALL
SELECT nst.owner, CAST('NESTED' AS VARCHAR2(11)) object_type, nst.parent_table_name object_name, table_name auxiliary_name, nvl(seg_nst.bytes, 0) bytes,
NULL AS row_count FROM top_nested_tables nst LEFT OUTER JOIN dba_segments seg_nst ON seg_nst.owner = nst.owner AND seg_nst.segment_name = nst.table_name
WHERE nst.owner IN (SELECT DISTINCT (owner) FROM dba_segments WHERE tablespace_name NOT LIKE 'UNDO%' AND tablespace_name NOT IN ('SYSTEM', 'SYSAUX')
AND owner IN (SELECT username FROM dba_users WHERE common = 'NO' AND oracle_maintained = 'N'))
)
WHERE owner IN (SELECT DISTINCT (owner) FROM dba_segments WHERE tablespace_name NOT LIKE 'UNDO%' AND tablespace_name NOT IN ('SYSTEM', 'SYSAUX')
AND owner IN (SELECT username FROM dba_users WHERE common = 'NO' AND oracle_maintained = 'N'));
Top 100 Tables List by Size (in Gigabytes)
Use the following SQL to view the top 100 tables, listed by size in gigabytes (GB):
SELECT owner, object_name AS table_name, total_bytes_gb, row_count
FROM
(
WITH my_nested_tables AS (
SELECT /*+ materialize */ owner, parent_table_name, table_name FROM dba_nested_tables
WHERE owner IN (SELECT DISTINCT (owner) FROM dba_segments WHERE tablespace_name NOT LIKE 'UNDO%'
AND tablespace_name NOT IN ('SYSTEM', 'SYSAUX') AND owner IN (
SELECT username FROM dba_users WHERE common = 'NO' AND oracle_maintained = 'N'))),
top_nested_tables AS (
SELECT /*+ materialize */ owner, parent_table parent_table_name, table_name FROM
(SELECT owner, level, CONNECT_BY_ROOT parent_table_name parent_table, table_name FROM my_nested_tables
CONNECT BY parent_table_name = PRIOR table_name) WHERE (owner, parent_table) NOT IN (
SELECT owner, table_name FROM my_nested_tables))
SELECT owner, object_name, SUM(bytes) / 1024 / 1024 / 1024 total_bytes_gb, nvl(MAX(row_count), 0) AS row_count
FROM (
SELECT tab.owner, CAST('TABLE' AS VARCHAR2(11)) object_type,
CASE WHEN tab.iot_type = 'IOT_OVERFLOW' THEN tab.iot_name ELSE tab.table_name END object_name,
CASE WHEN tab.iot_type = 'IOT_OVERFLOW' THEN tab.table_name END auxiliary_name, nvl(seg_tab.bytes, 0) bytes,
nvl(tab.num_rows, 0) AS row_count FROM dba_tables tab
LEFT OUTER JOIN dba_segments seg_tab ON seg_tab.owner = tab.owner AND seg_tab.segment_name = tab.table_name
WHERE tab.owner IN (SELECT DISTINCT (owner) FROM dba_segments WHERE tablespace_name NOT LIKE 'UNDO%'
AND tablespace_name NOT IN ('SYSTEM', 'SYSAUX') AND owner IN (SELECT username FROM dba_users WHERE common = 'NO'
AND oracle_maintained = 'N')) AND nvl(tab.iot_type, 'NORMAL') != 'IOT'
UNION ALL
SELECT ind.table_owner owner, CAST('INDEX' AS VARCHAR2(11)) object_type, nvl(nst.parent_table_name, ind.table_name) table_name,
index_name auxiliary_name, nvl(seg_ind.bytes, 0) bytes, NULL AS row_count FROM dba_indexes ind
LEFT OUTER JOIN top_nested_tables nst ON nst.owner = ind.table_owner AND nst.table_name = ind.table_name
LEFT OUTER JOIN dba_segments seg_ind ON seg_ind.owner = ind.owner AND seg_ind.segment_name = ind.index_name
WHERE ind.table_owner IN (SELECT DISTINCT (owner) FROM dba_segments WHERE tablespace_name NOT LIKE 'UNDO%'
AND tablespace_name NOT IN ('SYSTEM', 'SYSAUX') AND owner IN ( SELECT username FROM dba_users WHERE common = 'NO' AND oracle_maintained = 'N'))
UNION ALL
SELECT lob.owner, CAST('LOB' AS VARCHAR2(11)) object_type, lob.table_name, lob.column_name auxiliary_name, nvl(seg_lob.bytes, 0) bytes, NULL AS row_count
FROM dba_lobs lob LEFT OUTER JOIN dba_segments seg_lob ON seg_lob.owner = lob.owner AND seg_lob.segment_name = lob.segment_name
WHERE lob.owner IN (SELECT DISTINCT (owner) FROM dba_segments WHERE tablespace_name NOT LIKE 'UNDO%' AND tablespace_name NOT IN ('SYSTEM', 'SYSAUX')
AND owner IN (SELECT username FROM dba_users WHERE common = 'NO' AND oracle_maintained = 'N'))
UNION ALL
SELECT nst.owner, CAST('NESTED' AS VARCHAR2(11)) object_type, nst.parent_table_name object_name, table_name auxiliary_name, nvl(seg_nst.bytes, 0) bytes,
NULL AS row_count FROM top_nested_tables nst LEFT OUTER JOIN dba_segments seg_nst ON seg_nst.owner = nst.owner AND seg_nst.segment_name = nst.table_name
WHERE nst.owner IN (SELECT DISTINCT (owner) FROM dba_segments WHERE tablespace_name NOT LIKE 'UNDO%' AND tablespace_name NOT IN ('SYSTEM', 'SYSAUX')
AND owner IN (SELECT username FROM dba_users WHERE common = 'NO' AND oracle_maintained = 'N')))
WHERE owner IN (SELECT DISTINCT (owner) FROM dba_segments WHERE tablespace_name NOT LIKE 'UNDO%' AND tablespace_name NOT IN ('SYSTEM', 'SYSAUX')
AND owner IN (SELECT username FROM dba_users WHERE common = 'NO' AND oracle_maintained = 'N'))
GROUP BY owner, object_name ORDER BY SUM(bytes) DESC ) a
ORDER BY 3 DESC FETCH FIRST 100 ROWS ONLY;