Oracle get ddl of objects

Sometimes it is very useful to get the DDL of an object of particular database.base_datos_objetos

We can recreate such a user from one environment to another without even knowing the password.

DDL statements can be obtained by calling the function:

Source   
DBMS_METADATA.GET_DDL (
object_type IN VARCHAR2,
name IN VARCHAR2,
schema IN VARCHAR2 DEFAULT NULL,
version IN VARCHAR2 DEFAULT 'COMPATIBLE',
model IN VARCHAR2 DEFAULT 'ORACLE',
transform IN VARCHAR2 DEFAULT 'DDL')
RETURN CLOB;

In this package there are other features that allow information about dependencies and permissions.

If for example we want to obtain the DDL user SCOTT instruction, we make the call

Source   
SQL> SET long 30000
SQL> SELECT dbms_metadata.get_ddl('USER', 'SCOTT') || ';' FROM dual;
CREATE USER "SCOTT" IDENTIFIED BY VALUES 'S:BB7E0CC7897451C23DE3A88156F21D7F8BCA878F42A2320124C79CB6EAFE;1CC0B1F327DFA5BC'
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP";

This can be done with any type of object:

Source   
SELECT dbms_metadata.get_ddl('TABLE', 'NOMBRE_TABLA', 'ESQUEMA') FROM dual;
SELECT dbms_metadata.get_ddl('INDEX','NOMBRE_INDICE', 'ESQUEMA') FROM dual;
SELECT dbms_metadata.get_ddl('MATERIALIZED_VIEW', 'NOMBRE_VISTA_MATERIALIZADA','ESQUEMA') FROM dual;
SELECT dbms_metadata.get_ddl('DB_LINK', 'NOMBRE_DB_LINK','ESQUEMA') FROM dual;
...

By simply changing the “object_type” we can get the DDL. There are many types of objects, I leave a link to documentation which describes:

http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_metada.htm#i1018906

Leave a Reply