Front page   Edit Freeze Diff Upload Copy Rename Reload   New List of pages Search Recent changes   Help   RSS of recent changes

技術文書/Oracle/DDLの生成

Last-modified: Sat, 29 Jan 2011 15:27:49 JST (4322d)
Top > 技術文書 > Oracle > DDLの生成

DBMS_METADATA.GET_DDL を使うと、DDL 文が生成できます。

-bash-3.00$ sqlplus scott/tiger

SQL*Plus: Release 10.2.0.1.0 - Production on 土 7月 17 19:48:50 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.



Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
に接続されました。
SQL> set pagesize 0
SQL> set long 10000
SQL> select dbms_metadata.get_ddl('TABLE', 'DEPT') from dual;

  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
  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"



SQL> 

全部欲しい場合は、yohei-a さんの記事 にあるやり方が便利です。 ちょっと加工してあります。

conn scott/tiger

set echo off
set termout off
set heading off
set pagesize 0
set linesize 10000
set trimout on
set trimspool on
set feedback off

spool create_table_scott.sql

select
  to_char(dbms_metadata.get_ddl('TABLE', table_name))||'/'
  from user_tables;

spool off

spool create_index_scott.sql

select
  to_char(dbms_metadata.get_ddl('INDEX', index_name))||'/'
  from user_indexes;

spool off

quit


URL B I U SIZE Black Maroon Green Olive Navy Purple Teal Gray Silver Red Lime Yellow Blue Fuchsia Aqua White