DBMS

xiaoxiao2021-03-06 23

Direct by Eygle. From Oracle9i, Oracle provides a new system package DBMS_METADATA, which can be used to extract DDL statements created by objects.

This package function is extremely powerful, let's take a look at its use.

1. Get the creation statement of the table.

SQL> DESC DBMS_METADATAFUNCTION ADD_TRANSFORM RETURns Number ... function get_ddl returns clob argument name type in / out default? ---------------------------------------------------------------------------------------------------- - ----------------------- ------ -------- Object_type varchar2 in name varchar2 in schema varcha2 in default version VARCHAR2 in Default Model Varchar2 in Default Transform Varchar2 in Default ...

SQL> Set long 2000sql> select dbms_metadata.get_ddl ('Table', 'Test') from DUAL

DBMS_METADATA.GET_DDL ('Table', 'Test') -------------------------------------- ------------------------------------------

Create Table "Sys". "Test" ("Owner" VARCHAR2 (30), "Object_name" varcha2 (128), "subject_name" varcha2 (30), "object_id" Number, "data_Object_id" Number, "Object_Type" varchar2 (18 "Created" DATE, "Last_DDL_TIME" DATE, "TimeStamp" varcha2 (19), "status" varchar2 (7), "temporary" varchar2 (1), "generated" varchar2 (1), "second" varchar2 (1 )) PCTFREE 10 PCTUSED 40 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 "SYSTEM" SQL>

2. Further, you can get a more comprehensive DDL statement via DBMS_METADATA

Oracle provides a comprehensive example: $ oracle_home / rdbms / demo / mddemo.sql includes indexing, authorization, all DDL statements, and more, all DDL statements, and more can be extracted.

SQL> CREATE User A Identified by A;

User has created

SQL> SELECT DBMS_METADATA.GET_DDL ('User', 'A') from DUAL;

DBMS_METADATA.GET_DDL ('User', 'A') -------------------------------------- ----------------------------

Create User "a" Identified by VALUES 'AFCC9478DFBF9029' Temporary TableSpace "Temp"

Interested in referring to the file, tested in accordance with DEMO.

SQL> select dbms_metadata.get_ddl ( 'TABLE', 'IUFO.IUFO_USERINFO') FROM DUAL; ERROR: ORA-31603: object "IUFO.IUFO_USERINFO" of type TABLE not found in schema "SYS" ORA-06512: at "SYS. DBMS_SYS_ERROR ", line 105ORA-06512: at" SYS.DBMS_METADATA_INT ", line 2917ORA-06512: at" SYS.DBMS_METADATA_INT ", line 3302ORA-06512: at" SYS.DBMS_METADATA_INT ", line 4189ORA-06512: at" SYS.DBMS_METADATA " , Line 326RA-06512: AT "sys.dbms_metata", Line 410RA-06512: AT "sys.dbms_metata", line 449ora-06512: AT "sys.dbms_metadata", line 615ora-06512: AT "sys.dbms_metadata", line 1221RA-06512: At line 1no rows selected

SQL> CONN IUFO / UFSoft4460895ERROR: ORA-01017: Invalid UserName / Password; Logon Denied

Warning: You are no longer connected to ORACLE.SQL> CONN IUFO / ufsoft2157923SP2-0734: unknown command beginning "CONN ..." - rest of line ignored.SQL> CONNECT IUFO / ufsoft2157923Connected.SQL> SELECT DBMS_METADATA.GET_DDL ( 'TABLE ',' IUFO_USERINFO ') FROM DUAL; SQL> SELECT DBMS_METADATA.GET_DDL (' TA "- rest of line ignored DUAL; SQL> 734:. unknown command beginning". SQL> SELECT DBMS_METADATA.GET_DDL ( 'TABLE', 'IUFO_USERINFO' .) From Dual;

DBMS_METADATA.GET_DDL ('Table', ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Create Table "Iufo". "IUFO_USERINFO" ("User_ID" varchar2 (20) Not null ENA

SQL> COL DBMS_METADATA.GET_DDL ( 'TABLE', 'IUFO_USERINFO') FROMAT A100; SP2-0158: unknown COLUMN option "FROMAT" SQL> COLUMN DBMS_METADATA.GET_DDL FORMAT A100; SQL> COL DBMS_METADATA.GET_DDL ( 'TABLE', 'IUFO_USERINFO ') Format A100; SQL> SELECT DBMS_METADATA.GET_DDL (' Table ',' Iufo_USERINFO ') from Dual; DBMS_METADATA.GET_DDL (' Table ', ----------------- -------------------------------------------------- -----------

Create Table "Iufo". "IUFO_USERINFO" ("User_ID" varchar2 (20) Not null ENA

SQL> SET Long 3000Sql> Set Pagesize 100Sql> SELECT DBMS_METADATA.GET_DDL ('Table', 'Iufo_USERINFO') from Dual;

Create Table "Iufo" ("User_ID" VARCHAR2 (20) Not Null Enable, "User_code" VARCHAR2 (64) Not Null Enable, "User_Name" VARCHAR2 (64) Not Null Enable, "Password" varchar2 (128) , "Role" Number (4, 0), "Unit_ID" varchar2 (64), "email" varchar2 (128), consTRAINT "PK_IUFO_USERINFO" Primary Key ("user_id" Using Index Pctfree 10 initrans 2 MaxTrans 255 Storage (Initial 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "IUFO" ENABLE, CONSTRAINT "FK_USER_INFO" FOREIGN KEY ( "UNIT_ID") REFERENCES "IUFO". "IUFO_UNIT_INFO" ( "UNIT_ID") ENABLE) PCTFREE 10 PCTUSED 40 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 "IUFO" SQL> CONNECT / AS SYSDBAConnected.SQL> SHOW USERUSER is "SYS" SQL> SELECT DBMS_M ETADATA.GET_DDL ('Table', 'Iufo_UserInfo', 'Iufo') from DUAL

转载请注明原文地址:https://www.9cbs.com/read-40153.html

9cbs

New Post(0)