Oracle Index metadata for given table

Index metadata for given table ....

create index  statement for given table 


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



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