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.sql
SELECT DBMS_METADATA.GET_DDL ('Table', 'SZT_PQSO2', 'Shqsys') from Dual;
SELECT DBMS_METADATA.GET_DDL ('Index', 'Indox_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" varchar2 (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 mineltents 1 maxextents 2147483645
Pctincrease 0 freeelists 1 FreeElist Groups 1 Buffer_Pool Default)
TableSpace "DATA1"
SQL> SELECT DBMS_METADATA.GET_DDL ('INDEX', 'Indox_pqzjyw', 'Shqsys') from DUAL
CREATE INDEX "shqsys". "Index_pqzjyw" on "shqsys". "SZT_PQSO2" ("zjyw")
PCTFree 10 initrans 2 MaxTrans 255
Storage (Initial 1048576 Next 1048576 mineltents 1 maxextents 2147483645
Pctincrease 0 FreeElists 1 FreeList Groups 1 Buffer_Pool Default) TableSpace "Data1"
SQL>
SQL> Spool OFF;
2. Get the entire SCHEMA DDL statement:
SET PAGESIZE 0
Set long 90000
Set feedback off
Set 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.
