![[PukiWiki]](image/pukiwiki.png "[PukiWiki]")
DBMS_METADATA.GET_DDL を使うと、DDL 文が生成できます。
#pre{{
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 さんの記事 にあるやり方が便利です。 ちょっと加工してあります。
#pre{{ 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 }}
#adsense(160x600)
