Obtain the DDL statement of the database or SHEME.

Obtain the DDL statement of the database or SHEME. obtain the syntax 1setpagesize02setlong900003setfeedbackoff4setechooff5spoolDEPT for creating a single table, view, and index. sql6 -- the following parameters can be turned on or off according to the actual situation. 7 -- the output information is formatted using a thumbnail or line feed. 8execdbms_metad

Obtain the DDL statement of the database or SHEME. syntax 1 set pagesize 0 2 set long 90000 3 set feedback off 4 set echo off 5 spool DEPT. SQL 6 -- the following parameters can be turned on or off according to the actual situation. 7 -- the output information is formatted using a thumbnail or line feed. 8 exec dbms_metad

Obtain the DDL statement of the database or SHEME.

1. Obtain the syntax for creating a single table, view, and index

1 set pagesize 0

2 set long 90000

3 set feedback off

4 set echo off

5 spool DEPT. SQL

6 -- the following parameters can be enabled or disabled based on actual conditions.

7 -- the output information is formatted by means of contraction or line feed.

8 exec dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'pretty ', true );

9 -- make sure that each statement has a semicolon

10 exec dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'sqlterminator', true );

11 -- disable association such as table index and foreign key (generated separately later)

12 exec dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'straints', false );

13 exec dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'ref _ constraints ', false );

14 exec dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'constraints _ as_alter ', false );

15 -- disable storage and tablespace attributes

16 exec dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'store', false );

17 exec dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'tablespace', false );

18 -- disable attributes such as PCTFREE and NOCOMPRESS for creating a table

19 exec dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'segment _ bubutes ', false );

20

21 -- table

22 SELECT DBMS_METADATA.GET_DDL ('table', 'tab _ name', 'hr ') from dual; -- table name (TAB_NAME) SECHEMA (HR)

23 -- View

24 SELECT DBMS_METADATA.GET_DDL ('view', 'view _ name', 'hr') from dual;

25 -- Index

26 SELECT DBMS_METADATA.GET_DDL ('index', 'idx _ name', 'hr') from dual;

27 spool off;

2. Obtain the syntax for creating tables, views, and indexes in a SCHEMA. Take HR as an example:

1 2. Obtain the syntax for creating tables, views, and indexes in a SCHEMA. Take HR as an example:

2 set pagesize 0

3 set long 90000

4 set feedback off

5 set echo off

6 spool schema. SQL

7 connect HR/HR12345; -- current connected user

8

9 -- the output information is formatted by means of contraction or line feed.

10 exec dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'pretty ', true );

11 -- make sure that each statement has a semicolon

12 exec dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'sqlterminator', true );

13 -- disable association such as table index and foreign key (generated separately later)

14 exec dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'straints', false );

15 exec dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'ref _ constraints ', false );

16 exec dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'constraints _ as_alter ', false );

17 -- disable storage and tablespace attributes

18 exec dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'store', false );

19 exec dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'tablespace', false );

20 -- disable the attributes of the created table, such as PCTFREE and NOCOMPRESS.

21 exec dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'segment _ bubutes ', false );

22

23 -- table

24 SELECT DBMS_METADATA.GET_DDL ('table', u. table_name) FROM USER_TABLES u; -- get the DDL statement of the current user

25 -- View

26 SELECT DBMS_METADATA.GET_DDL ('view', u. VIEW_name) FROM USER_VIEWS u;

27 -- Index

28 SELECT DBMS_METADATA.GET_DDL ('index', u. index_name) FROM USER_INDEXES u;

29 spool off;

3. Obtain the syntax for all stored procedures under HR

1 set pagesize 0

2 set long 90000

3 set feedback off

4 set echo off

5 spool procedures. SQL

6 connect HR/HR12345;

7

8

9 select DBMS_METADATA.GET_DDL ('processed', u. object_name)

10 from user_objects u

11 where object_type = 'processed ';

12 spool off;

4. Obtain the syntax of all functions in HR

1 set pagesize 0

2 set long 90000

3 set feedback off

4 set echo off

5 spool function. SQL

6 connect HR/HR12345;

7 select DBMS_METADATA.GET_DDL ('function', u. object_name)

8 from user_objects u

9 where object_type = 'function ';

10 spool off;

5. Obtain the ddl statements for all tablespaces.

1 set pagesize 0

2 set long 90000

3 set feedback off

4 set echo off

5 spool tablespace. SQL

6

7 SELECT DBMS_METADATA.GET_DDL ('tablespace', TS. tablespace_name)

8 FROM DBA_TABLESPACES TS;

9

10 spool off;

6. Obtain the ddl statements of all created users.

1 set pagesize 0

2 set long 90000

3 set feedback off

4 set echo off

5 spool user. SQL

6

7 SELECT DBMS_METADATA.GET_DDL ('user', U. username)

8 FROM DBA_USERS U;

9

10 spool off;

By combining the preceding SQL statements, you can obtain the logical structure of a database or SCHEMA.