exporting db objects for version control

Question

We are implementing version control for our project. As part of this we need to check in all DB objects. We have tables, procedures, functions, packages, view and materialized view. Problem is there are many objects and we need to put source code file wise. e.g. There are tables T1, T2, T3 and we need files Table_T1.txt which will have T1 definition ( columns definition, indexes for the table and grants) and so on for all objects.

I m aware of metadata tables such as DBA_VIEWS, dba_source and DBMS_METADATA.GET_DDL etc where I can find required information but how to pull that information object wise. Currently we are working where we are taking entire information for specific object and then segregating (CUT - PASTE) it into the different files. Is there any smart way to tackle this?

Database - Oracle 10g

Answer 1

but how to pull that information object wise.

Pass the parameters properly. You could then customize your output.

DBMS_METADATA.GET_DDL (object_type, object_name, object_owner)

For example, to get the METADATA for all the tables in the user SCOTT.

SQL> conn scott/tiger@pdborcl;
Connected.
SQL> set long 200000
SQL> select dbms_metadata.get_ddl('TABLE',t.table_name, 'SCOTT') from US

DBMS_METADATA.GET_DDL('TABLE',T.TABLE_NAME,'SCOTT')
------------------------------------------------------------------------

  CREATE TABLE "SCOTT"."DEPT"
   (    "DEPTNO" NUMBER(2,0),
        "DNAME" VARCHAR2(14),
        "LOC" VARCHAR2(13),
         CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")
  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 FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE
   ) SEGMENT CREATION IMMEDIATE
  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 FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"


  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 FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE,
         CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
          REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
   ) SEGMENT CREATION IMMEDIATE
  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 FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"


  CREATE TABLE "SCOTT"."BONUS"
   (    "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "SAL" NUMBER,
        "COMM" NUMBER
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  TABLESPACE "USERS"


  CREATE TABLE "SCOTT"."SALGRADE"
   (    "GRADE" NUMBER,
        "LOSAL" NUMBER,
        "HISAL" NUMBER
   ) SEGMENT CREATION IMMEDIATE
  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 FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"

So, this gives me the DDL for all the tables in the SCOTT schema.

Similarly, you could do the same for all other objects like INDEXES, ROLES etc.

To get the DDL in a text file, simply use SPOOL. So, you just need individual scripts for different object types to spool in respective text files.