Oracle db useful queries

oracle-useful-queries.sql

-- Gets tables with their auto generated sequences
select table_name,data_default from user_tab_cols where identity_column='YES';

-- Gets table and its ID column details, like sequence name if its auto generated sequence
COLUMN table_name FORMAT A50
COLUMN column_name FORMAT A15
COLUMN sequence_name format A15          
COLUMN generation_type FORMAT A10          
COLUMN identity_options FORMAT A75         
                                           
SELECT table_name, 
       column_name,    
       generation_type,    
       sequence_name,  
       identity_options
FROM   user_tab_identity_cols                         
where table_name = '&TABLE_NAME' -- When it runs, it will ask for input, enter the table name here
ORDER BY 1, 2;

-- Gets a sequence's last value
select last_number from user_sequences where sequence_name = '&TABLE_NAME_ID_SEQ'; -- When it runs, it will ask for input, enter the sequence name here

-- Gets owner, contraint name, table name, column name and constraint type for a table
column owner format a20
column constraint_name format a45
column table_name format a45
column column_name format a45
SELECT A.owner,
    A.constraint_name,
    A.table_name,
    A.column_name,
    b.constraint_type
  FROM user_cons_columns A,
    user_constraints b
  WHERE A.owner = b.owner
  AND A.constraint_name = b.constraint_name
  AND A.table_name = b.table_name
  AND A.table_name = '&TABLE_NAME'; -- When it runs, it will ask for input, enter the table name here

-- Gets owner, contraint name, table name, column name and constraint type for a constraint
column owner format a20
column constraint_name format a45
column table_name format a45
column column_name format a45
SELECT A.owner,
    A.constraint_name,
    A.table_name,
    A.column_name,
    b.constraint_type
  FROM user_cons_columns A,
    user_constraints b
  WHERE A.owner = b.owner
  AND A.constraint_name = b.constraint_name
  AND A.table_name = b.table_name
  AND A.constraint_name = '&CONSTRAINT_NAME';  -- When it runs, it will ask for input, enter the contraint name here

-- same as above but for all existing constraints
column owner format a20
column constraint_name format a45
column table_name format a45
column column_name format a45
SELECT A.owner,
    A.constraint_name,
    A.table_name,
    A.column_name,
    b.constraint_type
  FROM user_cons_columns A,
    user_constraints b
  WHERE A.owner = b.owner
  AND A.constraint_name = b.constraint_name
  AND A.table_name = b.table_name;
  

-- Gets table indexes with details
column table format a45
column index_name format a45
column columns format a45
column index_type format a15
column uniquenes format a15
select ind.table_owner || '.' || ind.table_name as "TABLE",
       ind.index_name,
       LISTAGG(ind_col.column_name, ',')
            WITHIN GROUP(order by ind_col.column_position) as columns,
       ind.index_type,
       ind.uniqueness
from sys.all_indexes ind
join sys.all_ind_columns ind_col
           on ind.owner = ind_col.index_owner
           and ind.index_name = ind_col.index_name
where ind.table_owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS',
       'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS',
       'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST',
       'WKPROXY','WMSYS','XDB','APEX_040000','APEX_040200',
       'DIP', 'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
       'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC',
       'LBACSYS', 'OUTLN', 'WKSYS', 'APEX_PUBLIC_USER') 
        AND ind.index_name='&INDEX_NAME' -- or ind.table_name='&TABLE_NAME'
group by ind.table_owner,
         ind.table_name,
         ind.index_name,
         ind.index_type,
         ind.uniqueness 
order by ind.table_owner,
         ind.table_name;