Oracle 9i easily gets DDL statements for building tables and indexes
- 2020-05-15 02:27:06
- OfStack
The ORACLE tutorial you are looking at is :Oracle 9i easy access to DDL statements for building tables and indexes. We all know that before 9i, it was a hassle to get statements to build tables and indexes. We usually get it by export with rows=no, but its output is not directly usable due to format problems. The other way is to write complex scripts to query the data dictionary, but this is still not available for slightly more complex objects such as IOT and nested tables.
Getting DDL statements from a data dictionary is often used, especially during system upgrades/rebuilds. In Oracle 9i, we can directly investigate DDL statements from the data dictionary by executing dbms_metadata. Using this powerful tool, we can obtain a single object or an DDL statement for SCHEMA as a whole. Best of all, it's easy to use.
1. Methods to obtain single table and index DDL statements:
Here is the output. All we have to do is pull out the build/index statement and put a semicolon after it to run it.
2. Method to obtain the entire SCHEMA DDL statement:
It is important to note that when we have external keys (referential constraints) in the table, we need to check the order between the referential tables to ensure that the reconstruction is carried out in a reasonable order. You can determine the order between the tables by query-dba_constraints and dba_cons_columns.
Getting DDL statements from a data dictionary is often used, especially during system upgrades/rebuilds. In Oracle 9i, we can directly investigate DDL statements from the data dictionary by executing dbms_metadata. Using this powerful tool, we can obtain a single object or an DDL statement for SCHEMA as a whole. Best of all, it's easy to use.
1. Methods to obtain single table and index DDL statements:
Here is the output. All we have to do is pull out the build/index statement and put a semicolon after it to run it.
2. Method to obtain the entire SCHEMA DDL statement:
It is important to note that when we have external keys (referential constraints) in the table, we need to check the order between the referential tables to ensure that the reconstruction is carried out in a reasonable order. You can determine the order between the tables by query-dba_constraints and dba_cons_columns.