GENERAR DDL TABLE & INDICES X SQL

Listing A:
set heading off;
 set echo off;
 Set pages 999;
 set long 90000;
 
spool ddl_list.sql
 select dbms_metadata.get_ddl('TABLE','EMPLOYEES','HR') from dual; 
 select dbms_metadata.get_ddl('INDEX','EMP_MANAGER_IX','HR') from dual;

 select dbms_metadata.get_ddl('INDEX','DEPT_IDX','SCOTT') from dual; 
 spool off;
The output is shown below.

Listing C:
set pagesize 0

 set long 90000

 set feedback off

 set echo off
 spool scott_schema.sql
 connect scott/tiger;
 SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)
     FROM USER_TABLES u;
 SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name)
     FROM USER_INDEXES u;
 spool off;


SQL> set long 1000000
SQL> select dbms_metadata.get_ddl('INDEX','ADDR_FK1') from dual;


Here is the output:
DBMS_METADATA.GET_DDL('INDEX','ADDR_FK1')
--------------------------------------------------------------------------------

CREATE INDEX "MV_MAINT"."ADDR_FK1" ON

"MV_MAINT"."ADDRESS" ("CUST_ID") PCTFREE 10 INITRANS 2 MAXTRANS 255

COMPUTE STATISTICS STORAGE(INITIAL 1048576

NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0