DDL for extracting and storing database objects in Oracle (1)

Common methods for extracting DDL commands from objects such as database tables, indexes, constraints, and triggers include extracting metadata from these objects ), and store the data in the memory. Although many scripts can implement such functions, they are usually incomplete or outdated. Fortunately, Oracle 9.2 provides an API to implement this function: DBMS_METADATA Package.

In many cases, data maintenance in the database requires us to extract DDLData Definition Language (Data Definition Language) of multiple objects, such as database tables, indexes, constraints, and triggers ).

Recently I undertake a task. I need to compile a set of database packages to perform high-performance DELETE operations on a large amount of data. This operation requires that I have technologies related to extracting and storing database object DDL.

The DDL method for extracting and storing database objects is as follows:

· Create a data table with the same structure as the source table, but it does not contain primary keys, backup keys, and external key constraints.

· For example, MyTable_X is used, where MyTable is the target data table to be deleted.

· Insert the data to be saved into the new data table MyTable_X.

· Use the nologging parallel option to create an index on the new data table.

· Create constraints on the new data table.

· Exchange between MyTable and MyTable_X data tables. Change the name of the primary table to MyTable_T and MyTable_X to MyTable.

· Verify the results and delete the MyTable_T table.

Obviously, in order to write the code to achieve the above goal, you must extract the metadata definition of the database object and the selected Attribute) and store it in the memory, in this way, you can use the above operation.

There are a large number of scripts on the Internet. They can extract the metadata of database objects from multiple Oracle data dictionaries, such as user_tables, user_indexes, user_ind_columns, user_constraints, and user_cons_columns, create DDL commands for specific objects. One problem with these scripts is that they are usually SQL * Plus scripts that generate client text files that cannot be accessed by server code. Their main problems are:

· Incomplete: all options cannot be extracted and combined into DDL statements.

· Outdated: these scripts usually do not support the latest Oracle Database feature-partition partitioning), function-based indexes, automatic segment space management ASSM, and so on. These scripts may crash or generate incorrect DDL statements.

Conclusion: although there are a large number of scripts for extracting database object metadata from the Oracle Data Dictionary, most of them are incomplete or expired.

Solution: Use the DBMS_METADATA Package to learn how to execute the preceding transaction in the best, error-free, and easy-to-maintain manner.

Use the local Oracle API: DBMS_METADATA package. The Oracle database provides a wide range of pre-packaged APIS by supplementing the PL/SQL package. The DBMS_METADATA package introduced in Oracle 9.2 may be suitable for your needs. It contains APIs used to retrieve database object definitions.

The main API we will use is the DBMS_METADATA.GET_DDL function. The object returned by this function defines that the SQL string is CLOB. It has the following input parameters:

· object_type VARCHAR2· name VARCHAR2· schema VARCHAR2 DEFAULT NULL· version VARCHAR2 DEFAULT ’COMPATIBLE’· model VARCHAR2 DEFAULT ’ORACLE’,· transform VARCHAR2 DEFAULT ’DDL’

　

An EmpTest data table for testing is created below, with indexes and constraints:

create table EmpTest(empNo integer not null,lastName varchar2(30) not null,firstName varchar2(20) not null,job varchar2(9) ’hireDate date ’isActive number(1)constraint EmpTest_CK1check (isActive in (0,1)) ,salary number(9,2) ,commision number(9,2) ,deptNo number(2) ,constraint EmpTest_PKprimary key (empNo),constraint EmpTest_AK1unique (lastName, firstName));create index EmpTest_HireDate_Salaryon EmpTest(salary,hireDate);

After running the script above, an EmpTest table with three indexes and two unique indexes is created:

select index_name, index_type, uniquenessfrom user_indexeswhere table_name = ’EMPTEST’;