How to use dbms_metadata
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!
