导出数据库对象以进行版本控制 [英] exporting db objects for version control

问题描述

我们正在为我们的项目实施版本控制.作为此过程的一部分，我们需要检入所有数据库对象.我们有表，过程，函数，包，视图和实例化视图.问题是有很多对象，我们需要将源代码文件放在明智的位置.例如有表T1，T2，T3，我们需要文件Table_T1.txt，该文件将具有T1定义(列定义，表的索引和授权)，以及所有对象的依此类推.

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.

我知道诸如DBA_VIEWS，dba_source和DBMS_METADATA.GET_DDL等元数据表，可以在其中找到所需的信息，但是如何明智地获取该信息对象.目前，我们在哪里工作我们将获取特定对象的全部信息，然后将其分离(剪切-粘贴)到不同的文件中.有什么聪明的方法可以解决这个问题吗?

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?

数据库-Oracle 10g

Database - Oracle 10g

推荐答案

但是如何明智地获取该信息对象.

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)

例如，要获取用户 SCOTT 中所有表的 METADATA .

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"

因此，这为 SCOTT 模式中的所有表提供了 DDL .

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

类似地，您可以对所有其他对象(如INDEXES，ROLES等)执行相同的操作.

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

要在文本文件中获取DDL，只需使用 SPOOL .因此，您只需要针对不同对象类型的单独脚本来后台处理各自的文本文件即可.

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.

这篇关于导出数据库对象以进行版本控制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！