Get DDL of objects in Oracle

Hi,

To get DDL of oracle database objects you can use dbms_metadata.getddl package.

Syntax:

select dbms_metadata.get_ddl('OBJECT_TYPE','OBJECT_NAME','SCHEMA') from dual;


Below is an example how this works.


set heading off;
set echo off;
Set pages 999;
set long 90000;
set lines 180

spool ddl_EMP.sql

select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual;

spool off;

Here is the output of spool file:

SQL>
SQL> select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual;

CREATE TABLE "SCOTT"."EMP"   ("EMPNO" NUMBER(4,0),  "ENAME" VARCHAR2(10), 
 "JOB" VARCHAR2(9),        "MGR" NUMBER(4,0), "HIREDATE" DATE,  "SAL" NUMBER(7,2), 
 "COMM" NUMBER(7,2),  "DEPTNO" NUMBER(2,0), 
CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO") USING INDEX
PCTFREE 10 
INITRANS 2
 MAXTRANS 255 
COMPUTE STATISTICS  STORAGE(INITIAL 65536 NEXT 1048576 
MINEXTENTS 1 
MAXEXTENTS 2147483645
PCTINCREASE 0 
FREELISTS 1
FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"  ENABLE,
CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE) 
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)   TABLESPACE "USERS"                                                                                                                                                                          
                                                                                                                                                                
SQL> 
SQL> spool off;

Similarly, you can get the DDL for Functions, pacakages, stored procedure etc.

select dbms_metadata.get_ddl('PACKAGE','PACKAGE_NAME','SCHEMA_NAME') from dual;

select dbms_metadata.get_ddl('PACKAGE BODY','PACKAGEBODY_NAME','SCHEMA_NAME') from dual;

select dbms_metadata.get_ddl('TRIGGER','TRIGGER_NAME','SCHEMA_NAME') from dual;

Logical backup of sql query using EXP/IMP utility

Hi Folks,

Today I am writing about how to take exp backup of an sql statement. Many times we have a requirements to backup the modified rows. Taking full table backup may not be feasible if table sizes is GB's.

Better approach is to backup on the rows getting modified.

Consider below sql as an example.

select * from EMP where emp_job in ('DBA','DEVELOPER','ANALYST');


vi exp_example.par  (add below content)

file=EMP_EXP.dmp
log=EMP_EXP.log
query="where emp_job in ('DBA','DEVELOPER','ANALYST')"
tables=(EMP)
userid=system/manager

:wq  ( To save the file)

exp parfile=exp_example.par


That's all. Best of luck.