here are times when database data manipulation requires extracting DDL (Data Definition Language) commands for various objects, such as database tables, indexes, constraints, triggers, etc.
Recently I was charged with the task of writing a set of database packages to perform a high-performance massive DELETE operation. While my high-performance massive DELETE operation will be covered in my next solution, this month's solution explores the related technique of extracting and storing database objects' DDL.
The method for extracting and storing database objects' DDL is as follows:
- Create a table with identical structure but with no primary key, alternate keys(s), and foreign keys(s) constraints.
- For instance, use MyTable_X, where MyTable is the target table for deletion.
- Insert data that needs to be kept into a newly created table (MyTable_X).
- Create indexes on your new table using the NOLOGGING PARALLEL options.
- Create constraints on your new table.
- Swap the MyTable and MyTable_X tables. Rename your main table to MyTable_T and MyTable_X to MyTable.
- Verify the results and drop the MyTable_T table.
Obviously, in order to write this code, you need to extract the database objects' metadata (definitions and selected attributes) and store it in the memory, so it will be available while executing the above steps.
There are numerous scripts available on the Web that extract database objects' metadata from the various Oracle dictionaries (user_tables, user_indexes, user_ind_columns, user_constraints, user_cons_columns, etc.), and then construct a DDL command for the object specified. One problem with these scripts is that they're usually SQL*Plus scripts that produce client-side text file, which is not accessible for database-side code. The main problem is that they are usually:
- Incomplete: not all of the numerous options are being extracted and incorporated into the DLL statement.
- Out-of-date: scripts usually do not support the latest Oracle database featurespartitioning, function-based indexes, automatic segment space management (ASSM), etc. These scripts either crash or generate incorrect DDL statements.
Though there are plenty of scripts available extract database objects' metadata from the various Oracle dictionaries, most of them are either incomplete or out-of-date.
Learn how to perform the above task in the most optimal, error-free, and maintenance-free way using the DBMS_METADATA package.