D.Z.Memo

Oracle 9iで試した結果、
SELECT 3 - NULL FROM DUAL
の戻り値はNULLになります。

そのため、
SELECT SUM(A-B) FROM ...
と
SELECT SUM(A)-SUM(B) FROM ...
の結果は異なる可能性があります。

Oracleで、日付型のデータから特定の日付要素（年、月、日など）を取得しようとする場合、
EXTRACT関数を使うと便利になります。

最近はジョブでデータベースのパフォーマンスチューニングをやってる。
そのため、データベースの仕組みを学習している。
とりあえず、OracleでSQL*Plusの中で実行計画を表示するコメントをメモする。
SQL> set autotrace traceonly explain;
オフにする場合は：
SQL> set autotrace off;

１．SYNONYMの作成
CREATE SYNONYM [synonym name]
FOR [object name];

CREATE PUBLIC SYNONYM [synonym name]
FOR [object name];

対象がなくても、シノニムを作成することができます。

２．SYNONYMの削除
DROP SYNONYM [synonym name];

DROP PUBLIC SYNONYM [synonym name];

３．SYNONYMの確認
Data Dictionary View (
USER_SYNONYMS
ALL_SYNONYMS
)を使って、シノニムの情報の取得が出来る。

１．作成
CREATE SEQUENCE [sequence name]
START WITH [initial value]
INCREMENT BY [increment value]
MAXVALUE [max value]
MINVALUE [min value]
CYCLE/NOCYCLE
NOCACHE/CACHE [cache value];

Rollbackの操作はSequenceに影響しないため、Rollback後、値が連続で無い場合があります。
Cacheを使うと、Crash時に、値がなくすことがあります。

２．削除
DROP SEQUENCE [squence name];

３．確認
Data Dictionary View (
USER_SEQUENCES
)を使って、Sequenceの情報を確認できる。

１．INDEXの作成
CREATE INDEX [index name]
ON [table name] ( [column name], ... , [column name] )
TABLESPACE [tablespace name]
......;

CREATE UNIQUE INDEX [index name]
ON [table name] ( [column name], ... , [column name] )
TABLESPACE [tablespace name]
......;
UNIQUEを使って、unique indexを作成することができる。

PRIMARY KEY, UNIQUE constraints制約は自動的にunique indexを作成する。

２．INDEXの削除
DROP INDEX [index name];

３．INDEXの確認
Data Dictionary View (
USER_INDEXES
USER_IND_COLUMNS
)を使ってINDEXの情報を確認することができる。

1. Create View
CREATE VIEW [view name] AS
SELECT ... ... ;

CREATE VIEW [view name] ( [column alias name], ... , [column alias name]) AS
SELECT ... ... ;

2. Modify View
CREATE OR REPLACE VIEW [view name] AS
SELECT ... ... ;

元表がないと、新規Viewを作成する場合、
CREATE OR REPLACE FORCE VIEW [view name] AS
SELECT ... ... ;

3. Constraint
Viewを作成時のWhere文の制約を、Viewを通じてデータ挿入／更新時にも有効にする場合は、
CREATE VIEW [view name] AS
SELECT ... FROM ...
WHERE ...
WITH CHECK OPTION CONSTRAINT [constraint name];

挿入／更新／削除できないように、ReadOnly制約をかけることもできる
CREATE VIEW [view name] AS
SELECT ... ...
WITH READ ONLY CONSTRAINT [constraint name];

4. Delete View
DROP VIEW [view name];

5. View View
The information of View can be obtained from Data Dictionary View:
USER_VIEWS

The construction of view can be gotten by
DESC [view name]

１．列制約構文
CREATE TABLE [table name]
(
[column name] [data type] CONSTRAINT [constraint name] [constraint rule],
... ,
[column name] [data type]
);

example: ... CONSTRAINT con_pk PRIMARY KEY,

２．表制約構文
CREATE TABLE [table name]
(
[column name] [data type],
... ,
[column name] [data type],
CONSTRAINT [constraint name] [constraint rule]);

example: ... CONSTRAINT con_ck CHECK( field1 > 100 AND field1 <=200 ) ...

３．制約の追加 (NOT NULL)以外
ALTER TABLE [table name]
ADD CONSTRAINT [constraint name] [constraint rule];

４．制約の変更 (include NOT NULL)
ALTER TABLE [table name]
MODIFY (
[column name] [data type] CONSTRAINT [constraint name] [constraint rule]
);

５．制約の削除
ALTER TABLE [table name]
DROP CONSTRAINT [constraint name];

ALTER TABLE [table name]
DROP CONSTRAINT [constraint name]
CASCADE;
CASCADEが付くと、外部キー制約などの外部参照制約が掛かれても、削除できる。

６．制約の有効・無効
ALTER TABLE [table name]
DISABLE CONSTRAINT [constraint name];

ALTER TABLE [table name]
DISABLE CONSTRAINT [constraint name]
CASCADE;
CASCADEを付くと、外部に参照されている制約も一緒に無効になる。

ALTER TABLE [table name]
ENABLE CONSTRAINT [constraint name];

７．制約の確認
Data Dictionary View(
USER_CONSTRAINTS
USER_CONS_COLUMNS
)で確認にすることができる。

１．表の作成
CREATE TABLE [table name]
(
[column name] [data type],
[column name] [data type],
...,
[column name] [data type]
)
TABLESPACE [table space name]
....
;

別の表を利用して、新しい表を作成
CREATE TABLE [table name]( [column name], ..., [column name]) AS
SELECT .....;

２．表名変更
RENAME [old table name]
TO [new table name];

３．列定義の追加
ALTER TABLE [table name] ADD
(
[column name] [data type]
);

４．列定義の変更
ALTER TABLE [table name] MODIFY
(
[column name] [data type]
);

５．列定義の削除
ALTER TABLE [table name]
DROP COLUMN [column name];

ALTER TABLE [table name]
DROP ([column name], ..., [column name]);

ALTER TABLE [table name]
DROP COLUMN [column name]
CASCADE CONSTRAINTS;

CASCADE CONSTRAINTSにより、制約付き列の削除も可能です。

４．未使用列にする。（アクセスできなくなる）
ALTER TABLE [table name]
SET UNUSED COLUMN [column name];

５．未使用列を削除する。（リソースをリリースする）
ALTER TABLE [table name]
DROP UNUSED COLUMNS;

６．表のすべての行を削除、記憶領域の解放。（Roll Backできない、行の削除トリガーを起動しない、速い）
TRUNCATE TABLE [table name];

７．表にコメントを追加
COMMENT ON TABLE [table name]
IS '[comment info]';
コメントは2MBまで

８．表の削除
DROP TABLE [table name]
DROP TABLE [table name]
CASCADE CONSTRAINTS;
CASCADE CONSTRAINTSにより、外部キー制約が掛かれている表も削除できる。

９．表の確認
Data Dictionary View(
USER_TABLES
USER_TAB_COMMENTS
USER_COL_COMMENTS
USER_UNUSED_COL_TABS
)により確認可能

１０．表の構造の確認
DESC [table name];

１．システム権限の付与
GRANT [system privilege name], ... ,[system privilege name] TO [username], ... , [username];

すべてのユーザー（今後追加されるユーザーも含む）に付与する場合、[username]をPUBLICと指定する事ができます。

GRANT [system privilege name] TO [username]
WITH ADMIN OPTION;
を使って、WITH ADMIN OPTIONを追加することで、付与されたシステム権限を他のユーザーにも与えることが可能になります。

２．システム権限の取消
REVOKE [system privilege name], ..., [system privilege name] FROM [username], ...,[username];

３．システム権限の確認
Data Dictionary View (SESSION_PRIVS, USER_SYS_PRIVS)を使って、Select文で確認することができます。

４．オブジェクト権限の付与
GRANT [object privilege name], ... , [object privilege name]
ON [object name]
TO [username];

PUBLICを使うと、すべてのユーザーへ付与する事ができます。
[object privilege name]にALLを使うと、すべてのオブジェクト権限を与える事ができます。
INSERT, UPDATE, REFERENCES権限は列単位で指定する事ができます。
GRANT update(fieldname1, fieldname2,...,fieldnameX) ON [object name] TO [username];

GRANT [object privilege name], ... , [object privilege name]
ON [object name]
TO [username]
WITH GRANT OPTION;で、WITH GRANT OPTIONを付けると、他のユーザーへ付与された権限を与える事が可能です。

５．オブジェクト権限の取消
REVOKE [object privilege name], ..., [object privilegename]
ON [object name]
FROM [username], ..., [username];

６．オブジェクト権限の確認
Data Dictionary View(
USER_TAB_PRIVS_MADE
USER_TAB_PRIVS_RECD
USER_COL_PRIVS_MADE
USER_COL_PRIVS_RECD
)を使って、Select文で確認できます。

７．ロールの作成
CREATE ROLE [role name];

８．ロールへの権限の付与
GRANT [system privilege name] TO [role name];
GRANT [object privilege name] ON [object] TO [role name];
GRANT [role name] TO [username];
GRANT [role name]
TO [username]
WITH ADMIN OPTION;
WITH ADMIN OPTIONを使うと、ロールを他のユーザーへ付与する権利も与えることができる。

９．ロールの取消
REVOKE [role name] FROM [username];

１０．ロールの確認
Data Dictionary View(
SESSION_ROLES
USER_ROLE_PRIVS
ROLE_SYS_PRIVS
ROLE_TAB_PRIVS
)を使って、Selectで確認することができます。

１．ユーザーの作成
CREATE USER [username]
INDENTIFIED BY [password]
DEFAULT TABLESPACE [default tablespace name]
TEMPORARY TABLESPACE [temporary tablespace name]
QUOTA [size] ON [tablespace name]
...
QUOTA [size] ON [tablespace name];

example of [size]:
UNLIMITED, 1M, 0, ...

２．ユーザーの変更
ALTER USER [username]
INDENTIFIED BY [password]
DEFAULT TABLESPACE [default tablespace name]
TEMPORARY TABLESPACE [temporary tablespace name]
QUOTA [size] ON [tablespace name]
...
QUOTA [size] ON [tablespace name];

[size]を0に設定しても、既存オブジェクトは削除されません。新規作成は出来なくなります。
ユーザーは自分のパスワードをALTERするには、ALTER USER権限は必要としない。

３．ユーザーの削除
DROP USER [username];
オブジェクトを所有しているユーザーは削除できません。

ユーザーとオブジェクトを一緒に削除する場合：
DROP USER [username]
CASCADE;

４．ユーザー情報の確認
Data Dictionary View(USER_USERS, USER_TS_QUOTAS)を使ってSelect文で確認します。

Oracleの表領域は2種類あり；
１．SYSTEM表領域；
２．非SYSTEM表領域；

SYSTEM表領域にはSYSユーザーが所有するデータ・ディクショナリが格納されています。
そのため、ユーザー・データは非SYSTEM表領域に格納した方が良いです。

データ・ディクショナリについて、
ユーザーがSELECT文を使って、データ・ディクショナリ・ビューを検索できます。
データ・ディクショナリ・ビューは主に以下の4種類あり：
１．USER_xxx
２．ALL_xxx
３．DBA_xxx
４．V$xxx

表領域を使い分けると、安定性やパフォーマンスにさまざまなメリットがあります。
１．表セグメントと索引セグメントの表領域を分けて異なるデバイスに配置すると、I/O 衝突が減少し、パフォーマンスが向上します。
２．UNDO セグメントとデータ・セグメントの表領域を分けて異なるデバイスに配置すると、媒体障害による永続的なデータ損失が避けられます。
３．一時セグメントは、永続表領域に作成することもできます。ただし、一時セグメントは、取得／解放が頻繁に行われるため、空き領域が大きく断片化することがあります。したがって、専用の表領域を用意すべきです。
４．ユーザーを作成するときに、テンポラリ表領域 に一時表領域を指定すると、テンポラリ表領域内に 1 つのソート・セグメントが作成され、ソート処理時に使用されます。ソート・セグメントは、Oracle 起動後、ソートに一時表領域を使う文が最初に実行されるときに作成され、Oracle 停止時に解放されます。したがって、ソート処理を必要とするエクステントの取得／解放の頻度が減少し、パフォーマンスが向上します。

表領域の作成法：
CREATE TABLESPACE [表領域名] DATAFILE
'[データ・ファイル名　フルパスで記述]' SIZE [サイズ],
... , (複数個可）
'[データ・ファイル名　フルパスで記述]' SIZE [サイズ]
デフォルト記憶領域パラメータ（TEMPORARY/AUTOEXTEND ON NEXT 1M MAXSIZE 50M(UNLIMITED)/...）;

表領域データ・ファイルの追加
ALTER TABLESPACE 表領域名 ADD DATAFILE
データ・ファイル名 SIZE サイズ;

表領域オンライン／オフライン
ALTER TABLESPACE 表領域名 ONLINE;
ALTER TABLESPACE 表領域名 OFFLINE;

データファイルの再配置
オフライン、物理ファイルのコピー後に
ALTER TABLESPACE 表領域名 RENAME DATAFILE
旧データ・ファイル名 TO 新データ・ファイル名;

表領域の削除
オフラインした後、
DROP TABLESPACE 表領域名;
注意：物理データファイルは手動で削除しなければなりません。

表領域内オブジェクトも一緒に削除する場合、
DROP TABLESPACE 表領域名
INCLUDING CONTENTS;

FOREIGN KEY制約あり、一緒に削除する場合、
DROP TABLESPACE 表領域名
INCLUDING CONTENTS
CASCADE CONSTRAINTS;

データ・ディクショナリ・ビューより、表領域情報の確認
表領域に関連するデータ・ディクショナリ・ビューは以下の２種類あります。
１．DBA_TABLESPACES；
２．DBA_DATA_FILES