Achieved in the production Oracle 9i table and index DDL statement

Apr 13 posted in Oracle 9i (RSS), 68 views.

We all know that before in 9i, in order to get to build the statement table and index is a very troublesome thing. We usually through the practice of export with rows = no to get, but it is because the output format and can not be directly used to use. The other way is to write a complex script to query the data dictionary, but for a slightly complicated object, such as IOT and nested tables, etc., or can not be found.

Obtained from the data dictionary is the frequent use DDL statements, especially in the system upgrade / redevelopment. In Oracle 9i, we can directly through the implementation of dbms_metadata investigated from the data dictionary DDL statement. Use this powerful tool, we can get a single object or the entire SCHEMA statement in DDL. But the best is because it is very simple to use.

1, access to a single table and index DDL statement of methods:

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","INDXX_PQZJYW","SHQSYS") from dual;

spool off;

The following is the output. As long as we build table / index statements out at the back plus a semi-colon can be directly run.

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 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

TABLESPACE "DATA1"

SQL> select dbms_metadata.get_ddl("INDEX","INDXX_PQZJYW","SHQSYS") from dual;

CREATE INDEX "SHQSYS"."INDXX_PQZJYW" ON "SHQSYS"."SZT_PQSO2" ("ZJYW")

PCTFREE 10 INITRANS 2 MAXTRANS 255

STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645　

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)　

TABLESPACE "DATA1"

SQL>　

SQL> spool off;

2, the whole way 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;

Note that the table when we are outside the health (see constraints), we need to determine the order between the light table to ensure that the redevelopment in accordance with a reasonable order. You can query dba_constraints and dba_cons_columns to determine the order between the tables, shall not repeat.