[求助] Oracle下如何获取一个表的完整建表信息

ivhb

和informix类似的
dbschema 的东西，
比如我可以
dbschema -d database -t table -ss table.sql
就能获取指定的table的完整的一个建表语句。
oracle怎么做？

天涯明月刀

针对单个表：
SQL> set long 2000
SQL> set pagesize 0
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"

zzzcrt

请使用PL/SQL DEVELOPER

ivhb

不行，我需要程序里面实现的。

goodhawk

本质其实是只有一句话： DBMS_METADATA.GET_DDL( 'TABLE', u.table_name )

下面是我丰富了的功能。


读取object并解析出DDL写入文件存到UNIX上
[Typeset page]

(1) 准备工作
unix 最低权限:
chmod 010 test_xbrl_dir
d-----x---   2 dev5ap     dba           8192 Jul  6 16:37 test_xbrl_dir

建立一个directory XMLDIR

(2)实现思路: 读取object 并解析出DDL ,结果为CLOB类型. 写入文件. 存到UNIX上.
/*------------------------------------------------------------------------------
  Current Version : 0.01
  Create          : Joodhawk Lin 06/07/2006
  Purpose         : out put dll
  Description     :
  Special Input   :
  Output          :
  Demo            :
  Update          : (1) Joodhawk, 06/07/2006
                        created.
------------------------------------------------------------------------------*/
    PROCEDURE output_ddl
    IS
        v_clob         CLOB;
    BEGIN
        SELECT DBMS_METADATA.GET_DDL( 'TABLE', u.table_name )
          INTO v_clob
          FROM USER_TABLES u
         WHERE u.table_name = 'xx_JoodHawk_1511';

        PRINTCLOBOUT(
            p_filename => 'ABC.SQL',
            p_result => v_clob);

    EXCEPTION
        WHEN OTHERS THEN
            DBMS_OUTPUT.put_line(
                   'Error'
                || SQLERRM
                || SQLCODE );
    END;

    PROCEDURE PRINTCLOBOUT(
        p_filename   IN            VARCHAR2,
        p_result     IN OUT NOCOPY CLOB )
    IS
        v_line         VARCHAR2( 32767 );
        v_outputfile   UTL_FILE.FILE_TYPE;
        v_path_name    all_directories.directory_name%TYPE;
        v_amount       BINARY_INTEGER                        := 1;
        v_position     INTEGER                               := 1;
        v_pattern        VARCHAR2( 1 );
    BEGIN
        v_path_name :='XMLDIR';
        DBMS_OUTPUT.put_line( v_path_name || p_filename);
        v_outputfile := UTL_FILE.FOPEN( v_path_name,
                                      p_filename,
                                      'w' );
        v_pattern := CHR( 10 );
        v_amount := DBMS_LOB.INSTR( p_result,
                                  v_pattern,
                                  v_position,
                                  1 ) - v_position + 1;

        WHILE v_amount > 0
        LOOP
            DBMS_LOB.READ( p_result,
                           v_amount,
                           v_position,
                           v_line );
            UTL_FILE.put_line( v_outputfile, v_line );
            v_position := DBMS_LOB.INSTR( p_result,
                                        v_pattern,
                                        v_position,
                                        1 ) + 1;
            v_amount := DBMS_LOB.INSTR( p_result,
                                      v_pattern,
                                      v_position,
                                      1 ) - v_position + 1;
            v_line := NULL;
        END LOOP;

        UTL_FILE.fclose( v_outputfile );
    EXCEPTION
        WHEN OTHERS THEN
            DBMS_OUTPUT.put_line( 'error: ' || SQLCODE || SQLERRM );
    END;

(3)有用的点3个.

1,写文件要什么权限呢?

啥也不用,以前还搞个什么 grant权限,其实是没用的. 只要unix 上面的那个目录对Group有EXECUTE权限就ok.

2,用的oracle的package的来实现抓取 object的dll的.

3,写文件.

ivhb

谢谢你的回复。
我试验了一下

SELECT DBMS_METADATA.GET_DDL( 'TABLE', u.table_name )
          INTO v_clob
          FROM USER_TABLES u
         WHERE u.table_name = 'xx';

这句话拿出来执行，结果是“no rows selected”
实际上，肯定有“xx”这个表。
因为我

desc  xx

可以看到表结构。

不知道是我什么地方出了差错

天涯明月刀

scott用户所有的表：

SQL> show user
USER 为 "SCOTT"
SQL> set long 2000
SQL> set pagesize 0
SQL> select dbms_metadata.get_ddl('TABLE',u.table_name)
  2  from user_tables u;

  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)
  TABLESPACE "USERS"  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"



  CREATE TABLE "SCOTT"."EMP"
   (    "EMPNO" NUMBER(4,0),

天涯明月刀

找到跟scott.emp关联的index的定义

SQL> select dbms_metadata.get_dependent_ddl('INDEX','EMP','SCOTT') from dual;

  CREATE INDEX "SCOTT"."INDEX_EMP_ENAME" ON "SCOTT"."EMP" ("ENAME")
  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"

  CREATE INDEX "SCOTT"."INDEX_EMP_SAL_JOB" ON "SCOTT"."EMP" ("SAL", "JOB")
  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"

  CREATE UNIQUE INDEX "SCOTT"."PK_EMP" ON "SCOTT"."EMP" ("EMPNO")
  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"

天涯明月刀

回悬赏贴从来都是到最后看不到楼主的回信。。。。。



这个冤枉楼主了

[ 本帖最后由 天涯明月刀 于 2007-10-8 10:21 编辑 ]

ivhb

呵呵，我1000多分都散了出去，不会赖你10分的。看看我的信誉积分？呵呵