Contents
How to get DDL from DBMS metadata script?
You can get any package’s get ddl ( create script ) as follows. SQL> SELECT DBMS_METADATA.GET_DDL (‘PACKAGE’,’OBJECT_NAME’,’SCHEMA_NAME’) FROM DUAL; You can get any package’s body get ddl ( create script ) as follows. SQL> SELECT DBMS_METADATA.GET_DDL (‘PACKAGE_BODY’,’OBJECT_NAME’,’SCHEMA_NAME’) FROM DUAL; You can get any constraint’s get ddl (
How to get DBMS metadata from Oracle Database?
Oracle database has DBMS_METADATA package that provide a way for you to retrieve metadata from the database dictionary. You can retrieve metadata of any object using Get DDL (Create Script) DBMS_METADATA.GET_DDL Get DDL (Create Script) DBMS_METADATA.GET_DDL Syntax of DBMS_METADATA.GET_DDL is as follows.
How to extract the DDL for a schema?
I am having troubles to extract the DDL for a given schema with DBMS_METADATA, probably because my understanding of it is wrong. Here’s what I basically do:
How to get the DDL of a table?
You can get all tables get ddl (create script) or any schema’s tables create script as follows. SELECT DBMS_METADATA.get_ddl (‘TABLE’, table_name, owner) FROM all_tables WHERE owner = UPPER (‘&1’);
How to get the DDL of a package?
You can get any package’s get ddl (create script) as follows. SQL> SELECT DBMS_METADATA.GET_DDL (‘PACKAGE’,’OBJECT_NAME’,’SCHEMA_NAME’) FROM DUAL; You can get any package’s body get ddl (create script) as follows. SQL> SELECT DBMS_METADATA.GET_DDL (‘PACKAGE_BODY’,’OBJECT_NAME’,’SCHEMA_NAME’) FROM DUAL;
How to get DDL of any object in Oracle?
You can get all functions, procedures get ddl ( create script ) as follows. You can get any package’s get ddl ( create script ) as follows. You can get any package’s body get ddl ( create script ) as follows. You can get any constraint’s get ddl ( create script ) as follows. You can get any user’s system grant ( create script ) as follows.
Why does DBMS _ metadata produce unusable output?
DBMS_METADATA can produce unusable output in that keywords and values may be split across lines such that the output has to be editted before it can be executed as SQL. they were getting “unusable output” because sqlplus was wrapping at 80 characters.
Is the hashed password missing in DBMS _ metadata?
Information in this document applies to any platform. If you are using DBMS_METADATA and when you try to generate the DDL for a user using GET_DDL, you will notice the hashed password value is missing, for example, even when user SCOTT has been granted the SELECT_CATALOG role: Connected.