Useful Oracle Queries

This whitepaper explores not so popular queries that can help to get some useful information about Oracle database and its components.

Get table sizes. Some tasks require calculating total size of Oracle tables on the disk. For this purpose, use this statement (the list is ordered from large to small):

SELECT segment_name,segment_type, BYTES/power(2,20) Table_Size_MB 
FROM dba_segments WHERE segment_type='TABLE' 
ORDER BY Table_Size_MB desc;

Tables without Primary Key. Sometime it is required to get all tables having no primary key defined for optimization. Here is the statement for this purpose:

SELECT t.owner || '.' || t.table_name AS table_name 
FROM sys.all_tables t LEFT JOIN sys.all_constraints c
    ON t.owner = c.owner AND t.table_name = c.table_name
    AND c.constraint_type = 'P'
WHERE c.constraint_type is null
    -- excluding some Oracle maintained schemas
    AND t.owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS', 
        'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS','OUTLN', 
        'SYS','SYSMAN','TSMSYS','WK_TEST','WKSYS', 'WKPROXY', 'ORDDATA',
        'WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 'AUDSYS', 
        'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
        'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC')  
ORDER by table_name;

Recently created tables. Find all tables created in last 30 days:

SELECT owner || '.' || object_name as table_name,
       created
FROM sys.all_objects
WHERE object_type = 'TABLE'
    -- excluding some Oracle maintained schemas
    AND owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS', 
        'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS','OUTLN', 
        'SYS','SYSMAN','TSMSYS','WK_TEST','WKSYS', 'WKPROXY', 'ORDDATA',
        'WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 'AUDSYS', 
        'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
        'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC')  
    AND created > sysdate - 30
ORDER BY created DESC;

Recently modified tables. Find all tables modified in last 30 days:

SELECT tab.owner || '.' || tab.table_name as table_name,
       obj.last_ddl_time as last_modify
FROM all_tables tab
JOIN all_objects obj on tab.owner = obj.owner
     and tab.table_name = obj.object_name
WHERE tab.owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS',
     'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS','OUTLN',
     'SYS','SYSMAN','TSMSYS','WK_TEST','WKSYS','WKPROXY', 'ORDDATA',
     'WMSYS','XDB','APEX_040000','APEX_PUBLIC_USER','DIP', 'AUDSYS', 
     'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
     'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC')
      and obj.last_ddl_time > (current_date - 30)
ORDER BY last_modify DESC;

Find tables without foreign keys in Oracle database:

SELECT t.owner || '.' || t.table_name as table_name
FROM sys.all_tables t 
LEFT JOIN (
    SELECT distinct owner, table_name
    FROM sys.all_constraints 
    WHERE constraint_type = 'R'
) fks ON t.owner = fks.owner
    AND t.table_name = fks.table_name
WHERE fks.table_name is null
    AND t.owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS',
     'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS','OUTLN',
     'SYS','SYSMAN','TSMSYS','WK_TEST','WKSYS','WKPROXY', 'ORDDATA',
     'WMSYS','XDB','APEX_040000','APEX_PUBLIC_USER','DIP', 'AUDSYS', 
     'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
     'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC') 
ORDER by table_name;

Have questions? Contact us