Oracle SQL*Plus でテーブルとビューの定義(CREATE TABLE と CREATE VIEW)を取得する方法
Oracle データベースにおいて、コマンドライン (SQL*Plus) でテーブルの CREATE TABLE 文や、ビューの CREATE VIEW 文を取得する方法です。CREATE TABLE のような SQL は DDL (Data Definition Language : データ定義言語) と呼ばれています。Oracle SQL Developer 等のツールを使えば簡単にテーブルの CREATE TABLE 文や、ビューの CREATE VIEW 文を取得することは可能ですが、SQL*Plus で取得出来ればテーブル定義を自動取得するスクリプトを作ったりしてバージョン管理を容易にすることが可能です。
Oracle には DDL を取得するための便利なパッケージがあります。dbms_metadata.get_ddl というパッケージを使うと簡単に定義を取得することが出来ます。
では早速 SQL*Plus でテーブルとビューを作成して実際に定義を取得してみます。
テーブルとビューの作成
まず foo という名前のテーブルと view_foo というビューを作成します。また foo テーブルに５件のレコードを登録し、ビューは id が 3 以上のレコードのみを取得します。CREATE TABLE foo ( id NUMBER(5) PRIMARY KEY, name VARCHAR2(50) NOT NULL ); CREATE OR REPLACE VIEW view_foo AS SELECT id, name FROM foo WHERE id > 3; INSERT INTO foo (id, name) VALUES (1, 'foo'); INSERT INTO foo (id, name) VALUES (2, 'bar'); INSERT INTO foo (id, name) VALUES (3, 'hoge'); INSERT INTO foo (id, name) VALUES (4, 'pikachu'); INSERT INTO foo (id, name) VALUES (5, 'xyz');
テーブルの定義を取得する
先ほど作成した foo テーブルの定義を以下のように取得します。単純な CREATE TABLE でも、次のように様々なパラメータが Oracle によって設定されているのが分かります。SQL> SET PAGES 1000
SQL> SET LONG 2000000000
SQL> SELECT dbms_metadata.get_ddl('TABLE', 'FOO') AS DEFINITION FROM dual;
DEFINITION
--------------------------------------------------------------------------------
CREATE TABLE "TEST"."FOO"
( "ID" NUMBER(5,0),
"NAME" VARCHAR2(50) NOT NULL ENABLE,
PRIMARY KEY ("ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 214748
3645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "HOGE" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOC
OMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2
147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "HOGE"
ビューの定義を取得する
次に先に作成した view_foo ビューの定義を取得してみます。SQL> SET PAGES 1000
SQL> SET LONG 2000000000
SQL> SELECT dbms_metadata.get_ddl('VIEW', 'VIEW_FOO') AS DEFINITION FROM dual;
DEFINITION
--------------------------------------------------------------------------------
CREATE OR REPLACE FORCE VIEW "TEST"."VIEW_FOO" ("ID", "NAME") AS
SELECT id, name FROM foo
WHERE id > 3
CREATE TABLE 文の STORAGE 句を除外する方法
先の CREATE TABLE 文では非常に長い STORAGE 句が含まれていました。もし STORAGE 句が不要であれば、以下のようにして STORAGE 句を除外した CREATE TABLE 文を取得することも可能です。SET PAGES 1000
SET LONG 2000000000
EXECUTE dbms_metadata.set_transform_param(DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', false);
SELECT dbms_metadata.get_ddl('TABLE', 'FOO') AS DEFINITION FROM dual;
DEFINITION
--------------------------------------------------------------------------------
CREATE TABLE "TEST"."FOO"
( "ID" NUMBER(5,0),
"NAME" VARCHAR2(50) NOT NULL ENABLE,
PRIMARY KEY ("ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
TABLESPACE "HOGE" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS L
OGGING
TABLESPACE "HOGE"
