Index Info ...

define T='EMP'

-- Reset previous settings
CLEAR COLUMNS

SET LINESIZE 200  PAGESIZE 100

-- Format specific columns to fit output on screen
COLUMN index_owner FORMAT A15       HEADING 'Owner'
COLUMN table_name  FORMAT A20       HEADING 'Table Name'
COLUMN index_name  FORMAT A25       HEADING 'Index Name'
COLUMN idxtype     FORMAT A12       HEADING 'Index Type' TRUNC
COLUMN uniq        FORMAT A10       HEADING 'Uniqueness'
COLUMN status      FORMAT A8        HEADING 'Status'
COLUMN part        FORMAT A4        HEADING 'Part'
COLUMN temp        FORMAT A4        HEADING 'Temp'
COLUMN h           FORMAT 99        HEADING 'H'
COLUMN lfblks      FORMAT 999,999   HEADING 'Leaf Blks'
COLUMN ndk         FORMAT 999,999   HEADING 'NDK'
COLUMN num_rows    FORMAT 999,999   HEADING 'Num Rows'
COLUMN cluf        FORMAT 999,999   HEADING 'Clust Fact'
COLUMN degree      FORMAT A6        HEADING 'Degree'
COLUMN visibility  FORMAT A10       HEADING 'Visibility'
COLUMN last_analyzed FORMAT A11     HEADING 'Last Analyzed'

-- Main Query
SELECT 
    owner AS index_owner,
    table_name,
    index_name,
    index_type AS idxtype,
    uniqueness AS uniq,
    status,
    partitioned AS part,
    temporary AS temp,
    blevel AS h,
    leaf_blocks AS lfblks,
    distinct_keys AS ndk,
    num_rows,
    clustering_factor AS cluf,
    TO_CHAR(last_analyzed, 'YYYY-MM-DD') AS last_analyzed,
    degree,
    visibility
FROM all_indexes
 WHERE 1=1
-- and owner = 'YOUR_SCHEMA' 
AND table_name = '&T'
ORDER BY index_owner, table_name, index_name;





CLEAR COLUMNS

SET LINESIZE 200
SET PAGESIZE 100

-- Format index and table info
COLUMN index_owner FORMAT A15       HEADING 'Owner'
COLUMN table_name  FORMAT A20       HEADING 'Table Name'
COLUMN index_name  FORMAT A25       HEADING 'Index Name'
COLUMN idxtype     FORMAT A12       HEADING 'Index Type' TRUNC
COLUMN uniq        FORMAT A10       HEADING 'Uniqueness'
COLUMN status      FORMAT A8        HEADING 'Status'

-- Format column details (ALL_IND_COLUMNS)
COLUMN column_position FORMAT 999   HEADING 'Pos'
COLUMN column_name     FORMAT A25   HEADING 'Column Name'
COLUMN column_length   FORMAT 999   HEADING 'Len'
COLUMN descend         FORMAT A4    HEADING 'DESC'

-- Format query SELECT statement
SELECT 
    i.owner AS index_owner,
    i.table_name,
    i.index_name,
    i.index_type AS idxtype,
    i.uniqueness AS uniq,
    ic.column_position,
    ic.column_name,
    ic.column_length,
    ic.descend
FROM all_indexes i
JOIN all_ind_columns ic 
  ON i.owner = ic.index_owner
 AND i.index_name = ic.index_name
 AND i.table_owner = ic.table_owner
 AND i.table_name = ic.table_name
 WHERE 1=1
-- and i.owner = 'YOUR_SCHEMA' 
AND i.table_name = '&&T'
ORDER BY 
    i.owner, 
    i.table_name, 
    i.index_name, 
    ic.column_position;
  
  
  define 1='' --- owner

SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON

BEGIN
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
  END;
/

--define tab='T'
SELECT DBMS_METADATA.get_ddl ('TABLE', TABLE_name, owner)
FROM   all_tables
WHERE 1=1
--and owner      = UPPER('&1')
AND    table_name = '&T'
;
SELECT DBMS_METADATA.get_ddl ('INDEX', index_name, owner)
FROM   all_indexes
WHERE 1=1
--and owner      = UPPER('&1')
AND    table_name = '&&T'
;