oracle| Object | data | database The operations involved in extracting DDL commands from objects (such as database tables, indexes, constraints, triggers, and so on) involve extracting metadata from these objects (metadata) and storing the data in memory. Although there are a number of scripts that can implement such functionality at the moment, they are often incomplete or outdated. Fortunately, Oracle 9.2 provides a api:dbms_metadata package that implements this functionality.
In many cases, the maintenance of data in a database requires us to extract the DDL (data definition Language, database definition Language) of multiple objects (such as tables, indexes, constraints, triggers, etc.).
Recently I undertook a task, and I need to write a set of database packages to perform a large number of high-performance data deletion (delete) operations. Such operations require that I have the technology to extract and store DDL for database objects.
The methods for extracting and storing DDL for database objects are as follows:
· Creates a datasheet with the same structure as the source table, but it does not have primary keys, alternate keys, and foreign key constraints.
· For example, use mytable_x, where mytable is the destination datasheet to be deleted.
· Insert the data you want to save into the newly created data table (mytable_x).
· Use the nologging parallel option to create an index on the new datasheet.
· Establishes a constraint on a new datasheet.
· MyTable and mytable_x data tables are exchanged. Change the main table to mytable_t and rename 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 (defined and selected attributes) of the database object and store it in memory so that it can be used when performing the above operation.
There are a number of scripts on the web that can be used from a variety of Oracle data dictionaries (user_tables, user_indexes, User_ind_columns, User_constraints, User_cons_ Columns, and so on) extracts the metadata of the database objects, and then constructs the DDL commands for the specific objects. One problem with these scripts is that they are usually sql*plus scripts that generate a client text file that cannot be accessed by server-side code. Their main problems are:
· Incomplete: Cannot extract all the options and combine them into a DDL statement.
· Obsolete: These scripts typically do not support Oracle's newest database features-partitioning (partitioning), function based indexing, automatic segment Space Management (ASSM), and so on. These scripts may crash or generate an incorrect DDL statement.
Summary of issues: Despite the large number of scripts that extract database object metadata from Oracle data dictionaries, most of them are either incomplete or out-of-date.
Solution: Use the Dbms_metadata package to learn how to perform the above transactions in the best, error-free, and maintainable manner.
using Oracle's local Api:dbms_metadata package
Oracle databases provide a rich pre-packaged API in the form of supplemental pl/sql packages. The Dbms_metadata package introduced in Oracle version 9.2 may be just right for your needs. It contains APIs for retrieving database object definitions.
The APIs we will use are mainly dbms_metadata. Get_ddl function. This function returns an object definition SQL string that is CLOB. It has some of 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 '
The following establishes a emptest datasheet for testing, with indexes and constraints:
CREATE TABLE Emptest
(
EmpNo integer NOT NULL,
LastName varchar2 () NOT NULL,
FirstName VARCHAR2 is not NULL,
Job Varchar2 (9) '
HireDate Date '
IsActive Number (1)
Constraint Emptest_ck1
Check (isactive in (0,1)),
Salary number (9,2),
Commision number (9,2),
DeptNo Number (2),
Constraint EMPTEST_PK
Primary KEY (EmpNo),
Constraint EMPTEST_AK1
Unique (LastName, firstName)
);
CREATE INDEX Emptest_hiredate_salary
On Emptest
(
Salary
HireDate
);
after running the above script, a emptest table with three indexes (two unique and one unique index) is established:
Select Index_name, Index_type, uniqueness
From User_indexes
WHERE table_name = ' emptest ';
Index name Index Type Uniqueness EMPTEST_AK1 NORMAL UNIQUE emptest_hiredate_salary NORMAL nonunique EMPTEST_PK NORMAL UNIQUE
The emptest table also includes six constraints:
· A primary key-EMPTEST_PK
· An alternate key-emptest_ak
· A CHECK constraint-emptest_ck1
· System-generated (sys_*) three non-null constraints with the following name:
Constraint name Constraint Type Index name sys_c002144065 C sys_c002144066 C sys_c002144067 C Emptest_ck1 C EMPTEST_PK P EMPTEST_PK EMPTEST_AK1 U EMPTEST_AK1
Dbms_output package can only output strings that are up to 255 characters long, which is a problem because it is too easy to exceed this limit when working with DDL strings for data tables. To solve this problem, we used the local procedure show () (shown in Listing 1).
List 1: Call Dbms_metadata. Pl/sql code block for the GET_DDL () function
Declare
Vclob Clob;
Vlongstring varchar2 (32767);
Voffset Pls_integer: = 0;
Vlength Pls_integer: = 0;
vTable varchar2: = ' emptest ';
Procedure Show (Pvariable varchar2, plinesize pls_integer: = 80)
Is
Begin
Dbms_output.enable (1000000);
if (length (pvariable) > Plinesize)
Then
Dbms_output.put_line (substr (pvariable, 1, plinesize));
Show (substr (pvariable, plinesize + 1), plinesize);
Else
Dbms_output.put_line (pvariable);
End If;
End Show;
Begin
--Get DDL
Vclob: = Dbms_metadata.get_ddl (' TABLE ', Upper (vTable));
--Get CLOB length
Vlength: = Dbms_lob. GetLength (VCLOB);
Dbms_output.put_line (' DDL length: ' | | to_char (vlength));
Voffset: = 1;
Dbms_lob.read (Vclob, Vlength, Voffset, vlongstring);
--Close CLOB
if (Dbms_lob.isopen (Vclob) > 0)
Then
Dbms_lob.close (VCLOB);
End If;
Show (vlongstring, 80);
End;
Listing 1 generates the following output information:
DDL length:461
CREATE TABLE "BORIS". Emptest "
("EMPNO" number (*,0) not NULL ENABLE,
"LASTNAME" VARCHAR2 not NULL ENABLE,
' FIRSTNAME ' VARCHAR2 not NULL ENABLE,
"JOB" VARCHAR2 (9),
"HireDate" DATE,
"IsActive" number (1,0),
"SALARY" number (9,2),
"Commision" number (9,2),
"DEPTNO" number (2,0),
CONSTRAINT "Emptest_ck1" CHECK (IsActive in (0,1)) ENABLE,
CONSTRAINT "EMPTEST_PK" PRIMARY KEY ("EMPNO")
USING INDEX PCTFREE Initrans 2 Maxtrans 255
STORAGE (INITIAL 65536 NEXT 1048576 minextents 1 maxextents 2147483645
Pctincrease 0 freelists 1 freelist GROUPS 1 buffer_pool DEFAULT)
Tablespace "TOOLS" ENABLE, CONSTRAINT "Emptest_ak1" UNIQUE ("LASTNAME", "FIRSTNAME")
USING INDEX PCTFREE Initrans 2 Maxtrans 255
STORAGE (INITIAL 65536 NEXT 1048576 minextents 1 maxextents 2147483645
Pctincrease 0 freelists 1 freelist GROUPS 1 buffer_pool DEFAULT)
Tablespace "TOOLS" ENABLE) PCTFREE pctused Initrans 1
Maxtrans 255 nocompress LOGGING
STORAGE (INITIAL 65536 NEXT 1048576 minextents 1 maxextents 2147483645
Pctincrease 0 freelists 1 freelist GROUPS 1 buffer_pool DEFAULT)
Tablespace "TOOLS"
It's running so well that the DDL string for the returned datasheet has a primary key EMPTEST_PK, an alternate key emptest_ak1, and a check constraint emptest_ck1. It establishes two unique indexes to support primary and secondary key constraints. This is not the result you need: you need a table, but in order to speed up data loading, it does not include constraints and indexes. You only build indexes and constraints after the data load is completed.
Another reason to ensure that objects are defined independently is flexibility: You may need to change the order in which objects are created.
You can now design a data structure to store the object's metadata.
[1] [2] [3] Next page
