We all know that before 9i, we want to get a merits and index statements. Our usual practice is to get through Export with Rows = NO, but its output is not directly used because the problem in format cannot be used directly. Another way is to write complex scripts to query the data dictionary, but this is still not possible for a slightly complex object, such as IoT and nested tables, or not. The DDL statement from the data dictionary is often used, especially when the system is upgraded / rebuild. In Oracle 9i, we can directly investigate DDL statements from the DBMS_Metadata from the data dictionary. With this powerful tool, we can get a single object or the DDL statement of the entire Schema. It is best because it is very simple. 1. Get a single table and an index DDL statement:
set heading off; set echo off; Set pages 999; set long 90000; spool get_single.sqlselect dbms_metadata.get_ddl ( 'TABLE', 'SZT_PQSO2', 'SHQSYS') from dual; select dbms_metadata.get_ddl ( 'INDEX', 'INDXX_PQZJYW ',' Shqsys') from dual; spool off;
Here is the output. As long as we take the table / index statement, we can run directly after adding a semicolon.
SQL> SELECT DBMS_METADATA.GET_DDL ('Table', 'SZT_PQSO2', 'Shqsys') from Dual; Create Table "Shqsys". "SZT_PQSO2" ("PQBH" varchar2 (32) Not null enable, "zjyw" Number (10, 0), "CGSO" Number (10, 0) Not Null Enable, "SOLS" varchar2 (17), "Sorq" varcha2 (8), "SOWR" varchar2 (8), "SoCl" varchar2 (6), "ywhm "VARCHAR2 (10)," YWLX "VARCHAR2 (6)) 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" DATA1 "SQL> select dbms_metadata.get_ddl ( 'INDEX', 'INDXX_PQZJYW', 'SHQSYS') from dual;.. CREATE INDEX "SHQSYS" "INDXX_PQZJYW" ON "SHQSYS" "SZT_PQSO2" ( "ZJYW") PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( Initial 1048576 Next 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 free_pool default) TableSpace "Data1" SQL> SQL> spool off; 2, get the entire Schema DDL statement method:
set pagesize 0set long 90000set feedback offset echo off spool get_schema.sql connect shqsys / shqsys @ hawk1; 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;
It should be noted that when there are external health (referring to constraints) in our table, we need to discriminate the order between the reference tables to ensure that the reconstruction is performed in a reasonable order. You can determine the order between the tables by querying DBA_CONSTRAINTS AND DBA_CONS_CONSTRAINTS AND DBA_CONS_COLUMNS, which is no longer detailed.
