Shulou(Shulou.com)05/31 Report--

Editor to share with you how to obtain table building ddl statements in oracle, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

A large number of partitioned tables were encountered when initializing with DIP tool, so the full synchronization function could not be used normally, so the table structure was extracted first, and then the data was synchronized.

The first method is to use tools, such as:

Pl/sql developer, you can get the table creation script when it appears in [tools]-[Export user object].

The second method is the sql statement.

The DBMS_METADATA.GET_DDL package can get ddl scripts for objects in the database. As follows (executed in SQLPLUS):

1. Get the ddl statement of a table:

SET SERVEROUTPUT ON

SET LINESIZE 1000

SET FEEDBACK OFF

Set long 99999

Set pagesize 4000

-remove redundant parameters such as storage (optional)

SQL > EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false)

-- get ddl # # ('TABLE',' table name', 'user name')

SQL > SELECT DBMS_METADATA.GET_DDL ('TABLE','CBZS_DMCODE_DEP_TYPE','MOBILE_CBZS') FROM DUAL

DBMS_METADATA.GET_DDL ('TABLE','CBZS_DMCODE_DEP_TYPE','MOBILE_CBZS')

CREATE TABLE "MOBILE_CBZS". "CBZS_DMCODE_DEP_TYPE"

("TYPE_ONE" VARCHAR2 (6)

"TYPE_ONE_DESC" VARCHAR2 (30)

"TYPE_TWO" VARCHAR2 (6)

"TYPE_TWO_DESC" VARCHAR2 (30)

"TYPE_ONE_ORD" NUMBER

"TYPE_TWO_ORD" NUMBER

) PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255COMPRESS LOGGING

TABLESPACE "DM_TBS_001"

two。 Get the ddl of all tables, indexes, and stored procedures under a user

SELECT DBMS_METADATA.GET_DDL (U.OBJECT_TYPE, u.object_name) FROM USER_OBJECTS u where U.OBJECT_TYPE IN ('TABLE','INDEX','PROCEDURE')

3. Get ddl statements for all tablespaces

SELECT DBMS_METADATA.GET_DDL ('TABLESPACE', TS.tablespace_name) FROM DBA_TABLESPACES TS

4. Get the ddl of all the created users

SELECT DBMS_METADATA.GET_DDL ('USER',U.username) FROM DBA_USERS U

In addition, if the package cannot be executed, the corresponding package needs to be installed correctly.

Expand:-- complement data through dblink

Insert into MOBILE_CBZS.MSS_BMS_ZHIJU_YUSUAN select * from MOBILE_CBZS.MSS_BMS_ZHIJU_YUSUAN@oldhbdw

The above is all the contents of the article "how to get ddl statements in oracle". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.