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

Editor to share with you how to use dbms_metadata, I hope you will get something after reading this article. Let's discuss it together.

Get_ddl function in dbms_metadata package

1. Get a ddl statement for a table or index

SELECT DBMS_METADATA.GET_DDL ('TABLE','DEPT','SCOTT') FROM DUAL

Select dbms_metadata.get_ddl ('INDEX','PK_DEPT','SCOTT') from dual

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')

Stored procedures: viewing the definition statements of stored procedures

SQL > SET LONG 9999

SQL > select dbms_metadata.get_ddl ('PROCEDURE','ANALYZEDB','NEWCCS') from dual

CREATE OR REPLACE PROCEDURE "NEWCCS". "ANALYZEDB"

IS

CURSOR get_ownertable

IS

SELECT table_name

FROM user_tables

Ownertable get_ownertable%ROWTYPE

BEGIN

OPEN get_ownertable

LOOP

FETCH get_ownertable

INTO ownertable

EXIT WHEN get_ownertable%NOTFOUND

EXECUTE IMMEDIATE 'analyze table'

| | ownertable.table_name |

| | 'compute statistics for table for all indexes for all indexed columns' |

END LOOP

EXCEPTION

WHEN OTHERS

THEN

RAISE

END

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 9i, you can use the DBMS_METADATA.GET_DDL package to get the ddl script for the objects of the database. As follows (executed in SQLPLUS):

a. Get a single syntax for creating tables, views, and indexes

Set pagesize 0

Set long 90000

Set feedback off

Set echo off

Spool DEPT.sql

Select dbms_metadata.get_ddl ('TABLE','TAB_NAME','SCOTT') from dual

Select dbms_metadata.get_ddl ('VIEW','VIEW_NAME','SCOTT') from dual

Select dbms_metadata.get_ddl ('INDEX','IDX_NAME','SCOTT') from dual

Spool off

b. Get all the syntax for creating tables, views, and indexes under a SCHEMA, taking scott as an example:

Set pagesize 0

Set long 90000

Set feedback off

Set echo off

Spool schema.sql

Connect scott/tiger

SELECT DBMS_METADATA.GET_DDL ('TABLE',u.table_name) FROM USER_TABLES u

SELECT DBMS_METADATA.GET_DDL ('VIEW',u.VIEW_name) FROM USER_VIEWS u

SELECT DBMS_METADATA.GET_DDL ('INDEX',u.index_name) FROM USER_INDEXES u

Spool off

c. Get the syntax of a SCHEMA's build all stored procedure

Set pagesize 0

Set long 90000

Set feedback off

Set echo off

Spool procedures.sql

Select DBMS_METADATA.GET_DDL ('PROCEDURE',u.object_name) from user_objects u where object_type =' PROCEDURE'

Spool off

d. Get the syntax for building all functions of a SCHEMA

Set pagesize 0

Set long 90000

Set feedback off

Set echo off

Spool function.sql

Select DBMS_METADATA.GET_DDL ('FUNCTION',u.object_name) from user_objects u where object_type =' FUNCTION'

Spool off

After reading this article, I believe you have a certain understanding of "how to use dbms_metadata". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!

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.