DDLの取得
DBMS_METADATA.GET_DDL
GET_DDLを使うとDDLを取得できる。
DDLの出力形式を変更する
https://siguniang.wordpress.com/2013/09/04/oracle-dbms_metadata-get_ddl-fetch-ddl-for-tables/
https://www.toadworld.com/platforms/oracle/b/weblog/archive/2015/11/25/get-ddl-and-set-transform-param-in-dbms-metadata
https://stackoverflow.com/questions/6782278/generating-ddl-script-for-object-without-schema-name-baked-in-using-dbms-metadat
スキーマ名を省略する
execute DBMS_METADATA.SET_TRANSFORM_PARAM(dbms_metadata.SESSION_TRANSFORM, 'EMIT_SCHEMA', false);
storageセクションを省略する
execute dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', false );
外部キーをalter tableにする
execute dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS_AS_ALTER', true );
セグメント属性を省く
execute dbms_metadata.set_transform_param(DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', false );
DDL 単位でセミコロンをつける
execute DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);
各オブジェクトを出力するバッチ
getDDL.sh
DBCONNECT=user/pass@host:port/service
OWNER=取得スキーマ
OUT_PATH=出力先
OBJECTS="
TABLE
VIEW
SEQUENCE
FUNCTION
PROCEDURE
INDEX
DATABASE LINK
"
WORKFILE=temp.tmp #拡張子は必ず付けること
source setenvora.sh
LINE_SEP='
'
OLD_IFS=$IFS
IFS=$LINE_SEP
# 各オブジェクト取得
for TYPE in ${OBJECTS}
do
sqlplus64 ${DBCONNECT} @getName.sql "${TYPE}" ${OWNER} ${WORKFILE}
mkdir -p "${OUT_PATH}/${TYPE/ /_}"
rm -rf "${OUT_PATH}/${TYPE/ /_}"/*
cat ${WORKFILE} | while read line
do
sqlplus64 ${DBCONNECT} @getObject.sql "${TYPE}" ${OWNER} "${line}" "${OUT_PATH}/${TYPE/ /_}/${line/ /_}.sql"
done
done
# 表領域取得
sqlplus64 ${DBCONNECT} @getTablespace.sql "${OUT_PATH}/TABLESPACE.sql"
# ユーザ
mkdir -p "${OUT_PATH}/USERS"
rm -rf "${OUT_PATH}/USERS/*"
sqlplus64 ${DBCONNECT} @getUsersName.sql ${WORKFILE}
cat ${WORKFILE} | while read line
do
sqlplus64 ${DBCONNECT} @getUser.sql "${line}" "${OUT_PATH}/USERS/${line/ /_}.sql"
done
IFS=$OLD_IFS
# find ${OUT_PATH} -name "*.sql" -exec sed -i 's/ *$//g' {} \;
rm -f ${WORKFILE}
getName.sql
--1ページの行数 0の場合はページヘッダ等を全て非表示にする
set pagesize 0
--1行のバイト数。これを超える場合は SET WRAPの設定に従う
set linesize 32767
--1行の幅を超えた場合に折り返す
set wrap on
--問合せ結果の件数を表示しない
set feedback off
--LONG、CLOB、NCLOBのチャンクサイズ
set LONGCHUNKSIZE 500
--LONG、CLOB、NCLOB の表示
set long 2000000
-- 行末のスペースを削除
SET TRIMS ON
VAR objecttype VARCHAR2(30)
exec :objecttype := '&1'
VAR owner VARCHAR2(30)
exec :owner := '&2'
spool &3
SELECT
OBJECT_NAME
FROM
DBA_OBJECTS
WHERE
OWNER = :owner
AND OBJECT_TYPE = :objecttype
ORDER BY OBJECT_NAME;
spool off;
exit;
getObject.sql
--1ページの行数 0の場合はページヘッダ等を全て非表示にする
set pagesize 0
--1行のバイト数。これを超える場合は SET WRAPの設定に従う
set linesize 32767
--1行の幅を超えた場合に折り返す
set wrap on
--問合せ結果の件数を表示しない
set feedback off
--LONG、CLOB、NCLOBのチャンクサイズ
set LONGCHUNKSIZE 2048
--LONG、CLOB、NCLOB の表示
set long 20000000
-- 行末のスペースを削除
SET TRIMS ON
VAR objecttype VARCHAR2(30)
exec :objecttype := '&1'
VAR owner VARCHAR2(30)
exec :owner := '&2'
VAR objectname VARCHAR2(50)
exec :objectname := '&3'
-- 最後にセミコロンを付ける
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE);
-- STORAGE属性を省略
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', FALSE );
-- セグメント属性を省略
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', FALSE );
-- キー情報は出力しない
-- EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS', FALSE );
-- キー情報はalter tableにする
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS_AS_ALTER', TRUE );
-- スキーマ名を省略
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'EMIT_SCHEMA', FALSE);
spool &4
SELECT
DBMS_METADATA.GET_DDL(
CASE
WHEN OBJECT_TYPE = 'DATABASE LINK' THEN 'DB_LINK'
ELSE OBJECT_TYPE
END ,
OBJECT_NAME, OWNER) DDL
FROM
DBA_OBJECTS
WHERE
OWNER = :owner
AND OBJECT_TYPE = :objecttype
AND OBJECT_NAME = :objectname;
spool off;
exit;
getTablespace.sql
--1ページの行数 0の場合はページヘッダ等を全て非表示にする
set pagesize 0
--1行のバイト数。これを超える場合は SET WRAPの設定に従う
set linesize 32767
--1行の幅を超えた場合に折り返す
set wrap on
--問合せ結果の件数を表示しない
set feedback off
--LONG、CLOB、NCLOBのチャンクサイズ
set LONGCHUNKSIZE 2048
--LONG、CLOB、NCLOB の表示
set long 20000
-- 行末のスペースを削除
SET TRIMS ON
spool &1
SELECT DBMS_METADATA.GET_DDL('TABLESPACE', TABLESPACE_NAME) AS DDL FROM DBA_TABLESPACES
ORDER BY TABLESPACE_NAME;
spool off;
exit;
getUser.sql
--1ページの行数 0の場合はページヘッダ等を全て非表示にする
set pagesize 0
--1行のバイト数。これを超える場合は SET WRAPの設定に従う
set linesize 32767
--1行の幅を超えた場合に折り返す
set wrap on
--問合せ結果の件数を表示しない
set feedback off
--LONG、CLOB、NCLOBのチャンクサイズ
set LONGCHUNKSIZE 2048
--LONG、CLOB、NCLOB の表示
set long 20000
-- 行末のスペースを削除
SET TRIMS ON
VAR objectname VARCHAR2(50)
exec :objectname := '&1'
spool &2
SELECT DBMS_METADATA.GET_DDL('USER', USERNAME) AS DDL FROM dba_users
where USERNAME = :objectname
ORDER BY USERNAME;
select 'GRANT ' || PRIVILEGE || ' TO ' || GRANTEE || case when ADMIN_OPTION = 'YES' then ' WITH ADMIN OPTION' end case
from dba_sys_privs
where GRANTEE = :objectname
order by GRANTEE;
select 'GRANT ' || GRANTED_ROLE || ' TO ' || GRANTEE || case when ADMIN_OPTION = 'YES' then ' WITH ADMIN OPTION' end case
from dba_role_privs
where GRANTEE = :objectname
order by GRANTEE;
spool off;
exit;
getUsersName.sql
--1ページの行数 0の場合はページヘッダ等を全て非表示にする set pagesize 0 --1行のバイト数。これを超える場合は SET WRAPの設定に従う set linesize 32767 --1行の幅を超えた場合に折り返す set wrap on --問合せ結果の件数を表示しない set feedback off --LONG、CLOB、NCLOBのチャンクサイズ set LONGCHUNKSIZE 2048 --LONG、CLOB、NCLOB の表示 set long 20000 -- 行末のスペースを削除 SET TRIMS ON spool &1 SELECT USERNAME FROM dba_users ORDER BY USERNAME; spool off; exit;
setenvora.sh
export LD_LIBRARY_PATH=/usr/lib/oracle/12.1/client64/lib/:${LD_LIBRARY_PATH}
#export NLS_LANG=JAPANESE_JAPAN.JA16SJIS
export NLS_LANG=Japanese_Japan.UTF8
export ORACLE_SID=XE
以下参考
各オブジェクト
set pagesize 0
set linesize 200
set feedback off
VAR objecttype VARCHAR2(30)
exec :objecttype := 'VIEW' --TABLEとか取得したいオブジェクトのタイプを指定する
SELECT
DBMS_METADATA.GET_DDL(OBJECT_TYPE, OBJECT_NAME, OWNER) DDL
FROM
ALL_OBJECTS
WHERE
OWNER = :owner
AND OBJECT_TYPE = :objecttype
--AND OBJECT_NAME = :objectname;
表領域
SELECT DBMS_METADATA.GET_DDL('TABLESPACE', TABLESPACE_NAME) AS DDL FROM DBA_TABLESPACES
ORDER BY TABLESPACE_NAME;
ユーザ情報
SELECT DBMS_METADATA.GET_DDL('USER', USERNAME) AS DDL FROM dba_users
ORDER BY USERNAME;
ユーザの権限
select * from dba_sys_privs select * from dba_role_privs
以下のような感じで権限付与のSQLを作成できる
select 'GRANT ' || PRIVILEGE || ' TO ' || GRANTEE || case when ADMIN_OPTION = 'YES' then ' WITH ADMIN OPTION' end case from dba_sys_privs where grantee = 'hoge' select 'GRANT ' || GRANTED_ROLE || ' TO ' || GRANTEE || case when ADMIN_OPTION = 'YES' then ' WITH ADMIN OPTION' end case from dba_role_privs where grantee = 'hoge'
以下、古い情報
テーブル
set pagesize 0
set linesize 200
set feedback off
VAR owner VARCHAR2(10)
exec :owner := 'USER'
-- テーブル
spool src/TABLE.sql
SELECT
TO_CHAR(DBMS_METADATA.GET_DDL('TABLE', TABLE_NAME, OWNER)) DDL
FROM
ALL_TABLES
WHERE
OWNER = :owner
ORDER BY TABLE_NAME;
spool off;
exit;
インデックス
set pagesize 0
set linesize 200
set feedback off
VAR owner VARCHAR2(10)
exec :owner := 'USER'
-- インデックス
spool src/INDEX.sql
SELECT
TO_CHAR(DBMS_METADATA.GET_DDL('INDEX', INDEX_NAME, OWNER)) DDL
FROM
ALL_INDEXES
WHERE
OWNER = :owner
ORDER BY INDEX_NAME;
spool off;
exit;
ビュー
set pagesize 0
set linesize 200
set feedback off
VAR owner VARCHAR2(10)
exec :owner := 'USER'
-- ビュー
spool src/VIEW.sql
SELECT
TO_CHAR(DBMS_METADATA.GET_DDL('VIEW', VIEW_NAME, OWNER)) DDL
FROM
ALL_VIEWS
WHERE
OWNER = :owner
ORDER BY VIEW_NAME;
spool off;
exit;
シーケンス
set pagesize 0
set linesize 200
set feedback off
VAR owner VARCHAR2(10)
exec :owner := 'USER'
-- シーケンス
spool src/SEQUENCE.sql
SELECT
TO_CHAR(DBMS_METADATA.GET_DDL('SEQUENCE', SEQUENCE_NAME, SEQUENCE_OWNER)) DDL
FROM
ALL_SEQUENCES
WHERE
SEQUENCE_OWNER = :owner
ORDER BY SEQUENCE_NAME;
spool off;
exit;
DB LINK
set pagesize 0
set linesize 200
set feedback off
VAR owner VARCHAR2(10)
exec :owner := 'USER'
-- DB LINK
spool src/DB_LINK.sql
SELECT
TO_CHAR(DBMS_METADATA.GET_DDL('DB_LINK', DB_LINK, OWNER)) DDL
FROM
ALL_DB_LINKS
WHERE
OWNER = :owner
ORDER BY DB_LINK;
spool off;
exit;
PROCEDURE
set pagesize 0
set linesize 200
set feedback off
set LONGCHUNKSIZE 200
set long 20000
VAR owner VARCHAR2(10)
exec :owner := 'USER'
-- Procedure
spool src/PROCEDURE.sql
SELECT
DBMS_METADATA.GET_DDL('PROCEDURE', OBJECT_NAME, OWNER) DDL
FROM
ALL_PROCEDURES
WHERE
OWNER = :owner
and OBJECT_TYPE='PROCEDURE'
ORDER BY PROCEDURE_NAME;
spool off;
exit;
FUNCTION
set pagesize 0
set linesize 200
set feedback off
set LONGCHUNKSIZE 200
set long 20000
VAR owner VARCHAR2(10)
exec :owner := 'USER'
-- Function
spool src/FUNCTION.sql
SELECT
DBMS_METADATA.GET_DDL('FUNCTION', OBJECT_NAME, OWNER) DDL
FROM
ALL_PROCEDURES
WHERE
OWNER = :owner
and OBJECT_TYPE='FUNCTION'
ORDER BY PROCEDURE_NAME;
spool off;
exit;
参考
http://oracle.se-free.com/ddl/A1_get_ddl.html
DBMS_METADATA.GET_DDL( オブジェクトタイプ , オブジェクト名 , [ スキーマ ] )
SQL> set long 2000
SQL> set heading off
SQL>
SQL> select
2 dbms_metadata.get_ddl('TABLE','EMP')
3 from dual;
CREATE TABLE "TEST"."EMP"
( "EMP_ID" CHAR(5),
"DEPT_ID" CHAR(5),
"EMP_NAME" VARCHAR2(10),
"EMP_DATE" DATE,
"SALARY" NUMBER(5,0),
PRIMARY KEY ("EMP_ID")
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"
[通知用URL]
Tweet
最終更新時間：2017年05月24日 23時49分28秒