Oracle Index metadata for given table

Index metadata for given table ....

create index  statement for given table 

=============================================

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='XXXXXXX'

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

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




*******************************************************************************************************************


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

-- Target Schema & Pattern Configuration
DEFINE v_owner = 'ANUJ'
DEFINE v_pattern = '%\_EXT' -- Matches table names ending in _EXT

DECLARE
  v_target_owner VARCHAR2(128) := UPPER('&v_owner');
  v_ddl          CLOB;
BEGIN
  -- Configure DDL Formatting
  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE);
  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'PRETTY', TRUE);
  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', TRUE);
  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', FALSE);

  -- 1. Loop through all matching tables
  FOR t IN (
    SELECT owner, table_name
    FROM all_tables
    WHERE owner = v_target_owner
      AND table_name LIKE '&v_pattern' ESCAPE '\'
    ORDER BY table_name
  ) LOOP
    
    DBMS_OUTPUT.PUT_LINE('-- =================================================');
    DBMS_OUTPUT.PUT_LINE('-- TABLE DDL: ' || t.owner || '.' || t.table_name);
    DBMS_OUTPUT.PUT_LINE('-- =================================================');
    
    -- Extract Table DDL
    BEGIN
      v_ddl := DBMS_METADATA.GET_DDL('TABLE', t.table_name, t.owner);
      DBMS_OUTPUT.PUT_LINE(v_ddl);
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('-- Error retrieving table DDL: ' || SQLERRM);
    END;

    -- 2. Extract DDL for all indexes on this specific table
    FOR i IN (
      SELECT owner, index_name
      FROM all_indexes
      WHERE table_owner = t.owner
        AND table_name = t.table_name
        AND index_type NOT IN ('LOB', 'DOMAIN') -- Skips system-generated LOB indexes
      ORDER BY index_name
    ) LOOP
      
      DBMS_OUTPUT.PUT_LINE('-- INDEX DDL: ' || i.owner || '.' || i.index_name);
      BEGIN
        v_ddl := DBMS_METADATA.GET_DDL('INDEX', i.index_name, i.owner);
        DBMS_OUTPUT.PUT_LINE(v_ddl);
      EXCEPTION
        WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE('-- Error retrieving index DDL: ' || SQLERRM);
      END;
      
    END LOOP;
    
  END LOOP;
END;
/








============================================================


ORCL\sys> !cat indx_meta.sql

set heading off 
set feedback off
set verify off
prompt set linesize 200
prompt set long 2000
select 'select dbms_metadata.get_ddl(' || '''TABLE'',' || '''' ||table_name||''',' || '''' || owner||''') from dual ;' 
from dba_tables  where table_name ='&Table_name' ;

set verify on
set heading on
set feedback on

----- out put

ORCL\sys> @indx_meta
set linesize 200
set long 2000
Enter value for table_name: USR_SITES

select dbms_metadata.get_ddl('TABLE','USR_SITES','PROD1') from dual ;






out put of the script 

select dbms_metadata.get_ddl('TABLE','USR_SITES','CPROD1') from dual ;


DBMS_METADATA.GET_DDL('TABLE','USER_SITES','CCCPROD1')
--------------------------------------------------------------------------------

  CREATE TABLE "PROD1"."USR_SITES"
   (    "USER_OBJECT_ID" VARCHAR2(16) NOT NULL ENABLE,
        "SITE_NO" VARCHAR2(12) NOT NULL ENABLE,
        "SITE_DATE_ADDED" DATE NOT NULL ENABLE,
        "TANDC_DATE_ACCEPTED" DATE,
         CONSTRAINT "USR_SITES_USPK1" PRIMARY KEY ("USER_OBJECT_ID", "SITE_NO")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "DM_CCCPROD1_DOCBASE"  ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "PROD1"



1 row selected.


===

or

SELECT  DBMS_METADATA.GET_DDL('INDEX',INDEX_NAME,OWNER) FROM    DBA_indexes
WHERE   TABLE_NAME='USR_SITES'    
 /

DBMS_METADATA.GET_DDL('INDEX',INDEX_NAME,OWNER)
--------------------------------------------------------------------------------

  CREATE UNIQUE INDEX "PROD1"."USR_SITES_USPK1" ON "PROD1"."USER_SITES" (
"USER_OBJECT_ID", "SITE_NO")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "PROD1"



1 row selected.





=========

metadata for other objects !!!!!!!!!!!!!!!!!!




Explanation of the script 



if you want to print ' in sqlplus then

ORCL\sys>; select ' '' ' from dual;

'''
---
 '

why four ' for one '  ???




if you want to print x the wt will be sqlplus

apt-lnxtst-01:ORCL\sys> select 'x' from dual;

'
-
x



replace x with '  



ORCL\sys> select ''' from dual;  


SELECT 'select dbms_metadata.get_ddl('  || ',' ||  ' ) FROM dual ;'  FROM dba_indexes where rownum SELECT 'select dbms_metadata.get_ddl('  || ',' ||  ' ) FROM dual ;'  
FROM dba_indexes where rownum 
SELECT 'select dbms_metadata.get_ddl('  ||  '''index'''||',' ||index_name|| ' ) FROM dual ;'  FROM dba_indexes where rownum 
SELECT 'select dbms_metadata.get_ddl('  ||  '''index'''||',' || ' ) FROM dual ;'  FROM dba_indexes where rownum select ''' ) FROM dual ;' from dual;

     
                                                                                                        
                                                                                                                        
                                                                                                                        
 ==================================================================================================
		

Oracle Metadata !!!!

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 vowner='SCOTT';  ----<<<<<<<<<< Change owner


col view1 for a100
 select 'select dbms_metadata.get_ddl(''INDEX'',''' || OBJECT_name|| ''',''' || owner|| ''') from dual;'  view1  from dba_OBJECTS 
where object_type='INDEX' 
and owner='&vowner'
;

for Index

set pagesize 300 linesize 200 long 5000

col view1 for a100

 select 'select dbms_metadata.get_ddl(''INDEX'',''' || INDEX_NAME|| ''',''' || owner|| ''') from dual;'  view1  from dba_indexes
where 1=1
-- object_type='INDEX' 
and TABLE_NAME='xxx'
;


define vowner='REP'
define TNAME='REP'

 select 'select dbms_metadata.get_ddl(''TABLE'',''' || table_NAME|| ''',''' || owner|| ''') from dual;'  view1  from dba_tables
where 1=1
and TABLE_NAME='&TNAME'
;

define vowner='REP'

col TABLE1 for a100
 select 'select dbms_metadata.get_ddl(''TABLE'',''' || OBJECT_name|| ''',''' || owner|| ''') from dual;' TABLE1  from dba_OBJECTS 
where object_type='TABLE' 
and owner='&vowner'
;



col PROCEDURE1 for a100
 select 'select dbms_metadata.get_ddl(''PROCEDURE'',''' || OBJECT_name|| ''',''' || owner|| ''') from dual;' PROCEDURE1  from dba_OBJECTS 
 where object_type='PROCEDURE' 
and owner='&vowner'
;



col FUNCTION1 for a100
 select 'select dbms_metadata.get_ddl(''FUNCTION'',''' || OBJECT_name|| ''',''' || owner|| ''') from dual;' FUNCTION1  from dba_OBJECTS 
where object_type='FUNCTION' 
and owner='&vowner'
;

col TRIGGER1 for a100
 select 'select dbms_metadata.get_ddl(''TRIGGER'',''' || OBJECT_name|| ''',''' || owner|| ''') from dual;' TRIGGER1  from dba_OBJECTS 
where object_type='TRIGGER' 
and owner='&vowner'
;



col VIEW1 for a100
 select 'select dbms_metadata.get_ddl(''VIEW'',''' || OBJECT_name|| ''',''' || owner|| ''') from dual;' VIEW1   from dba_OBJECTS 
 where object_type='VIEW' 
and owner='&vowner'
;



col CONSTRAINT1 for a100
select 'select dbms_metadata.get_ddl(''CONSTRAINT'',''' || CONSTRAINT_NAME || ''',''' || owner|| ''') from dual;'  CONSTRAINT1 from dba_constraints 
where 1=1
and owner='&vowner'
;

                                                                                                                        
=========================================================================================     


With created date !!

		 

alter session set nls_date_format='dd-Mon-yyyy hh24:mi:ss'; 
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;
/			 
			 
select '/*' || created || '*/' || dbms_metadata.get_ddl('INDEX',object_name, owner) 
from dba_objects 
where object_type = 'INDEX' 
and object_name='XXXX'    --- <<< Index name 
order by created, object_name;

=========

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;
/

SELECT DBMS_METADATA.get_ddl ('INDEX', index_name, owner)
FROM   all_indexes
WHERE 1=1
--and owner      = UPPER('&1')
AND    table_name = 'XXXX';


                                                                                                                        
    

View Metadata

  set long 200000 pages 0 lines 150 select dbms_metadata.GET_DDL('VIEW',u.view_name,DECODE(u.owner,'SYS','',owner)) from all_views u where 1=1 and owner IN ('SYS') --and owner IN ('ANUJ') and view_name='ALL_IND_STATISTICS' order by owner,view_name ; Or select dbms_metadata.get_ddl('VIEW', 'ALL_IND_STATISTICS') stmt from dual; 

                                                                                                                    
                                                                                                                        

======================	
Set pagesize 0  Long 90000
Set Feedback off
Set echo off
-- Spool Schema_ddl.sql
SELECT Dbms_metadata.GET_DDL ('TABLE', u.table_name,u.owner) from Dba_tables u;
SELECT Dbms_metadata.GET_DDL ('VIEW', u.view_name,u.owner) from Dba_views u;
SELECT Dbms_metadata.GET_DDL ('INDEX', u.index_name,u.owner) from Dba_indexes u;
Select Dbms_metadata.get_ddl ('PROCEDURE', U.object_name, U.owner) from dba_objects u where U.object_type = 'PROCEDURE';
Select Dbms_metadata.get_ddl ('FUNCTION', U.object_name, U.owner) from dba_objects u where U.object_type = 'FUNCTION';
  
=====
set pagesize 500  linesize 500
define O='ANUJ' ---owner 
define T= 'XXXX'   -- Table 
col COLUMNS for a30
col TABLE for a25
col INDEX_NAME  for a25
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  in ('&O')
    AND ind.table_name='&T'
group by ind.table_owner,
         ind.table_name,
         ind.index_name,
         ind.index_type,
         ind.uniqueness 
order by ind.table_owner, ind.table_name;