Table and Index

Table and index extract

set heading off;
 set echo off;
 Set pages 999;
 set long 90000;
 
spool ddl_list.sql
 select dbms_metadata.get_ddl('TABLE','DEPT','SCOTT') from dual; 
 select dbms_metadata.get_ddl('INDEX','DEPT_IDX','SCOTT') from dual;
 spool off;



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;



For table

SELECT DBMS_METADATA.GET_DDL('TABLE', TABLE_NAME) FROM USER_TABLES;

SELECT DBMS_METADATA.GET_DDL('INDEX', INDEX_NAME) FROM USER_INDEXES WHERE INDEX_TYPE ='NORMAL';

 DBMS_METADATA




________________________________________________________________________________________


ORA-31693: Table data object
"PMFADMIN"."TMP_PRODUCT_DESC_KWD" failed to load/unload and
is being skipped due to error:



ORA-02354: error in exporting/importing data



ORA-39826: Direct path load of view or synonym
(PMFADMIN.TMP_PRODUCT_DESC_KWD) could not be resolved.













select owner, object_name, object_type, last_ddl_time
from   dba_objects
where  owner = 'SDB' and object_name in ('XSDB_ALL_39A_PLANTS_CNTRCT');