export した dump から DDL を作成する方法 with Perl ワンライナー

show=y をつけて import を実行し、DDL を生成する。

$ imp system/manager file=scott.dmp fromuser=scott touser=scott show=y log=impshow_scott.log

以下のような DDL が出力されるがこのままでは実行できない。

$ cat impshow_scott.log
Import: Release 10.2.0.4.0 - Production on Wed Jan 13 14:13:01 2010

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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V10.02.01 via conventional path
import done in JA16EUC character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into SCOTT
 "BEGIN  "
 "sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','"
 "CURRENT_SCHEMA'), export_db_name=>'ORCL', inst_scn=>'261305');"
 "COMMIT; END;"
 "ALTER SESSION SET CURRENT_SCHEMA= "SCOTT""
 "CREATE SEQUENCE "SEQ1" MINVALUE 1 MAXVALUE 500 INCREMENT BY 1 START WITH 20"
 "1 CACHE 20 NOORDER CYCLE"
 "CREATE TABLE "T1" ("ID" NUMBER(4, 0))  PCTFREE 10 PCTUSED 40 INITRANS 1 MAX"
 "TRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL D"
 "EFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS"
. . skipping table "T1"                               

 "ALTER SESSION SET CURRENT_SCHEMA= "SCOTT""
 "CREATE TABLE "T2" ("ID" NUMBER(4, 0))  PCTFREE 10 PCTUSED 40 INITRANS 1 MAX"
 "TRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL D"
 "EFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS"
. . skipping table "T2"                               

 "ALTER SESSION SET CURRENT_SCHEMA= "SCOTT""
 "CREATE FORCE VIEW "SCOTT"."V1"                           ("TABLE_NAME","ROW"
 "_COUNT") AS "
 "select 't1' table_name, count(1) row_count from t1"
 "      union"
 "      select 't2' table_name, count(1) row_count from t2"
Import terminated successfully without warnings.

Perl ワンライナーを使って実行可能な DDL に変換する。

$ perl -ple '/^ "/ or s/.*//;s/^ "(.+)"$/$1/;s/^(CREATE|ALTER|ANALYZE|GRANT|COMMENT|AUDIT)/\/\n\n$1/i;END{print "\/"}' impshow_scott.log > ddl_scott.sql

変換後の DDL は以下の通り。

$ cat ddl_scott.sql 
BEGIN  
sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','
CURRENT_SCHEMA'), export_db_name=>'ORCL', inst_scn=>'261305');
COMMIT; END;
/

ALTER SESSION SET CURRENT_SCHEMA= "SCOTT"
/

CREATE SEQUENCE "SEQ1" MINVALUE 1 MAXVALUE 500 INCREMENT BY 1 START WITH 20
1 CACHE 20 NOORDER CYCLE
/

CREATE TABLE "T1" ("ID" NUMBER(4, 0))  PCTFREE 10 PCTUSED 40 INITRANS 1 MAX
TRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL D
EFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS


/

ALTER SESSION SET CURRENT_SCHEMA= "SCOTT"
/

CREATE TABLE "T2" ("ID" NUMBER(4, 0))  PCTFREE 10 PCTUSED 40 INITRANS 1 MAX
TRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL D
EFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS


/

ALTER SESSION SET CURRENT_SCHEMA= "SCOTT"
/

CREATE FORCE VIEW "SCOTT"."V1"                           ("TABLE_NAME","ROW
_COUNT") AS 
select 't1' table_name, count(1) row_count from t1
      union
      select 't2' table_name, count(1) row_count from t2


/