前提知識
ユーザー名やテーブル名などの固有名詞は、大文字・数字・アンダーバーのみで構成すること。
英語の小文字は、OracleDBに対して使用すると勝手に大文字に変換されるので、元から大文字を使用するように心がける。
英語の小文字にアクセスする場合、ダブルクォーテーションで括るなどの制約が出てくる必要がある。
テーブル操作
基本
COLNAME = 'VAL'nullでない
COLNAME <> null前方一致
※WHERE句で使用する場合は = ではなくLIKEで結ぶ
COLNAME = 'VAL%'任意の一文字
※WHERE句で使用する場合は=ではなくLIKEで結ぶ
COLNAME = '_A' // _に任意の一文字が入る複数条件
COLNAME = ‘VAL’ and COLNAME2 = ‘VAL2’
否定
※FROM句とWHERE句で書き方が違う
SELECT *
FROM TABLE_NAME T
JOIN TABLE_NAME2 T2
ON T.COL_NAME = T.COL_NAME2
AND T.COL_NAME <> 'VAL'
WHERE T.COL_NAME != 'VAL2';降順：DEST
ORDER BY DESC NULLS LASTNULLのレコードは最後に表示：NULLS LAST
ORDER BY DESC NULLS LAST重複は除く：DISTINCT
SELECT DISTINCT OWNER FROM DBA_SEGMENTS;DISTINCTは使いやすい反面、フルスキャンのため、インデックスの付与されていない膨大なレコードを持つテーブルでは、パフォーマンスの悪化を引き起こす可能性がある。
その場合は、GROUP BYを使用する。
SELECT col1, col2 FROM table_name GROUP BY col1, col2;重複しているデータを抽出
SELECT col_name FROM table_name GROUP BY col_name HAVING COUNT (col_name) > 1JOIN
SELECT *
FROM TABLE_NAME T
JOIN TABLE_NAME2 T2
ON T.COL_NAME = T.COL_NAME2
WHERE T.COL_NAME = 'VAL';(+)：LEFT OUTER JOINと同義
WHERE TABLE_A = TABLE_B (+);日付
WHERE TIME_COLUMN_NAME >= TO TIMESTAMP('2022-01-01 00:00:00')レコードの検索上限数を指定
WHERE ROWNUM < 10値の更新
UPDATE [TABLE_NAME] SET [COLUMN_NAME] = [VALUE], [COLUMN_NAME2] = [VALUE2] WHERE [CONDITION]変数の使用
--* SetParameter varName 'sample' String // 変数名：varName, 値：'sample'
SELECT [COLUMN_NAME]
FROM [TABLE_NAME]
WHERE [COLUMN_NAME] = : varName; // 変数名で呼び出し副問合せ
SELECT [COLUMN_NAME]
FROM [TABLE_NAME]
WHERE [COLUMN_NAME] = (
SELECT [COLUMN_NAME]
FROM [TABLE_NAME]
WHERE [COLUMN_NAME] = 'val' // ;はつけない
)応用SQL
社員テーブル、社員家族テーブル（社員の家族を登録するテーブル）が、社員IDで紐づいている。
社員が独身である場合、社員家族テーブルにはその社員に関するレコードは存在しない。
社員テーブルから独身社員のレコードを抽出せよ。
■JOINする方法
SELECT 社員.*
FROM 社員
LEFT JOIN 社員家族
ON 社員.社員ID = 社員家族.社員ID
WHERE 社員家族.社員ID IS NULL;■服問い合わせ＆IN句を使う方法
SELECT 社員.*
FROM 社員
WHERE 社員.社員ID NOT IN(
SELECT 社員家族.社員ID
FROM 社員家族
);権限設定
権限の種類
権限は大きく以下の2つがある。
- システム権限
- オブジェクト権限
オブジェクト権限
主に以下の権限。テーブル単位で付与される。
INSERT
SELECT
UPDATE
DELETE
ALTERシステム権限
主に以下のような権限。ユーザー（スキーマ）単位で付与される。
CREATE
DROP
○○ ANYANYについて、例えば INSERT ANY TABLE で、すべてのスキーマのすべてのテーブルへ INSERT できる権限を持つ。
権限の確認
データベースに登録されているロール一覧と、ロールに付与されているシステム権限
ロールはDB単位で付与される。強力なロールはスキーマを横断して操作が可能となる。
SELECT * FROM ROLE_SYS_PRIVS;現在のユーザーが持つロール
SELECT * FROM USER_ROLE_PRIVS;現在のユーザーが持つシステム権限
SELECT * FROM USER_SYS_PRIVS;現在のユーザーが持つオブジェクト権限
ユーザーが作成した（ownerが自身となっている）テーブルに関しては、すべてのオブジェクト権限を持っている。
すなわち、ユーザーが持つオブジェクト権限を確認することは、ユーザーが所有するテーブルの一覧を確認することとニアリーイコールとなる。
SELECT table_name FROM USER_TABLES;『所有する』ではなく、ほかのスキーマも含め『アクセスできる』（何らかのオブジェクト権限を持つ）テーブルを調べる場合は、USER_TABLESではなく、ALL_TABLESを参照する。
他のユーザーから付与されたオブジェクト権限
複数のスキーマを管理できる強力な管理者権限を用意している場合や、1つのスキーマで複数の権限レベルを用意する場合には、自分が作ったものではないテーブルを管理するケースも出てくる。
その場合には、他者から付与された権限を、以下のSQLで確認する。
SELECT * FROM USER_TAB_PRIVS_RECD;アクセスできるスキーマ一覧
以下は現在のユーザーがアクセスできるスキーマの一覧を確認するSQL。
基本的には自身のスキーマしかアクセスできないが、強力な管理者権限を用意している場合には、複数のスキーマにアクセスできる設定になっている可能性がある。
SELECT username
FROM all_users
WHERE username IN (
SELECT DISTINCT owner
FROM all_objects
WHERE object_type IN ('TABLE', 'VIEW', 'PACKAGE', 'FUNCTION', 'PROCEDURE', 'SEQUENCE')
AND owner != 'SYS'
AND owner != 'SYSTEM'
)
ORDER BY username;権限の付与
ロールの作成
以下はIT環境で使用するロールを作成する一般的な例。
CREATE ROLE IT_CONNECT;
GRANT
CREATE SESSION,
CREATE SEQUENCE,
CREATE VIEW,
CREATE TABLE,
CREATE SYNONYM,
CREATE CLUSTER,
CREATE DATABASE LINK,
ALTER SESSION
TO IT_CONNECT;
CREATE ROLE IT_RESOURCE;
GRANT
CREATE INDEXTYPE,
CREATE TYPE,
CREATE TRIGGER,
CREATE TABLE,
CREATE PROCEDURE,
CREATE OPERATOR,
CREATE CLUSTER,
CREATE SEQUENCE
TO IT_RESOURCE;ロールの付与
GRANT [ROLE_NAME] TO [USER_NAME];システム権限の付与
以下はすべてのテーブルにインサートできる権限を付与する例。
GRANT INSERT ANY TABLE TO user1;オブジェクト権限の付与
GRANTを使用して、ユーザー（またはロール）に、オブジェクト権限、システム権限を付与する。
ユーザーの作成したテーブルはすでに基本的なオブジェクト権限が付与されいているので、あまり使用する機会は無いかもしれない。
オブジェクト権限はテーブル単位で指定する必要がある。
GRANT INSERT ON schema1.table1 TO user1;全てのテーブルにオブジェクト権限を付与する
テーブルに関してはユーザー自身が作成しそのまま所有者となることが多く、もとからオブジェクト権限を持っているケースがほとんど。
そのため、オブジェクト権限をわざわざ付与するシーンはあまり無いが、発生した時に大変。
以下は、ユーザーに基本的なオブジェクト権限を、特定スキーマのすべてのテーブルに対して付与する例。
BEGIN
FOR t IN (SELECT table_name FROM all_tables WHERE owner = 'SCHEMA1') LOOP
EXECUTE IMMEDIATE 'GRANT SELECT, INSERT, UPDATE, DELETE ON schema1.' || t.table_name || ' TO multi_schema_user';
END LOOP;
END;解説
SELECT table_name FROM all_tables WHERE owner = 'SCHEMA1';all_tables ではシステムが作成したメタデータに関するテーブルも含まれる。
業務で使用するテーブルは、そのユーザーが作成していることが一般的なので、作成したユーザー名（owner）でフィルタする。
そうすることで、業務で使用するテーブルのテーブル名一覧を抽出できる。
EXECUTE IMMEDIATE 'GRANT SELECT, INSERT, UPDATE, DELETE ON schema1.' || t.table_name || ' TO multi_schema_user';変数を使用する箇所は「’」（シングルクォート）でくくる。t には先ほど抽出した業務で使用するテーブルの一覧が一つずつ代入される。
さらに同じ SQL で、owner の箇所を owner = ‘SCHEMA2’ として再実行すれば、multi_schema_user は SCHEMA1 と SCHEMA2 の 2 つのスキーマのテーブルに対して権限を持つことになる。
DBAによる確認
DBAとはデータベースアドミニストレーター、つまり管理者権限のこと。
DBA権限で良く実行する操作は以下。
ユーザーの一覧
SELECT username FROM dba_users ORDER BY username;Appendix
ユーザーとは？
OracleDBにおけるユーザーはスキーマとも呼ばれる。
ユーザーと聞くと、何人もいて同じ環境をその複数人で使いまわすイメージだが、OracleDBではそうではない。
例えばIT面とST面の2つの開発環境があったとして、この2つはユーザーという単位で区切られる。
各環境には別のユーザーIDとパスワードがあり、それを使用してログインできる。
つまりは、ユーザーとはアカウントのようなもの。
OracleDBのバージョン確認
SELECT * FROM product_component_version; // VERSION_FULLの項目を参照表領域、セグメント、エクステント、ブロックの関係
| 表領域（TABLESPACE） | データを格納する「最上位のストレージ単位」 | USERS や SYSTEM テーブルスペース |
| セグメント（SEGMENT） | テーブル・インデックスなどのデータの集合 | HR.EMPLOYEES といった、テーブルのデータ |
| エクステント（EXTENT） | セグメントの一部を構成する連続したデータ領域 | 1エクステント = 64KB のデータ領域 |
| ブロック（BLOCK） | Oracle のデータの「最小管理単位」 | 1ブロック = 8KB（通常） |
【表領域（TABLESPACE）】
┗【セグメント（TABLE / INDEX / UNDO など）】
┗【エクステント（データのまとまり）】
┗【ブロック（データの最小単位）】
┗【ブロック（データの最小単位）】
┗【エクステント（データのまとまり）】
┗【セグメント（TABLE / INDEX / UNDO など）】表領域とスキーマ
容量について考えるとき、表領域という概念は避けては通れない。
表領域は1つの空間で、Oracleのデフォルトの運用では、表領域とスキーマを1対1で構成する。そのため、表領域とスキーマを同じものとして考えがちだが、これらは別の概念。
表領域の方が親に当たり、1つの表領域に複数のスキーマを構成することもできる。
容量制限をかける際などは、表領域を制限するのか、スキーマを制限するのか区別して考える必要がある。
データディクショナリ
Oracle Databaseには、デフォルトで作成されるメタデータがあり、表領域やユーザー情報などが登録されている。
主に、以下の3つのビューがある。
| ビューの種類 | 説明 | 例 |
| DBA_XXX | 管理者（DBA）向けのデータ | DBA_TABLES, DBA_USERS, DBA_INDEXES |
| ALL_XXX | 現在のユーザーがアクセス可能なデータ | ALL_TABLES, ALL_VIEWS, ALL_INDEXES |
| USER_XXX | 現在のユーザーが所有するデータ | USER_TABLES, USER_VIEWS, USER_INDEXES |
良く参照するビューは以下。
| 種類 | ビュー名 | 用途 |
| テーブル情報 | DBA_TABLES, ALL_TABLES, USER_TABLES | テーブルの一覧と構造 |
| カラム情報 | DBA_TAB_COLUMNS, ALL_TAB_COLUMNS, USER_TAB_COLUMNS | テーブルのカラム情報 |
| インデックス情報 | DBA_INDEXES, ALL_INDEXES, USER_INDEXES | インデックスの一覧 |
| 表領域情報 | DBA_TABLESPACES, DBA_DATA_FILES | 表領域のサイズとデータファイル情報 |
| ユーザー情報 | DBA_USERS | データベースユーザーの一覧 |
| 権限情報 | DBA_ROLE_PRIVS, DBA_TAB_PRIVS | ユーザーの権限やロール |
| セッション情報 | V$SESSION | 現在接続中のセッション情報 |
インシデント
ORA-00907: 右カッコがありません。
副問合せを作成する際、WHERE句の中の問合せにセミコロンを付けたまま貼り付けていた
ORA-01427: 単一行副問合せにより2つ以上の行が戻されます
副問合せの結果が複数レコードを返す場合、 主問合せとの符号はイコールではなく『IN』 で結ぶ必要がある
ORA-00904: “VAL”: 無効な識別子です。
値はダブルクォーテーションではなく、シングルクォーテーションで囲いましょう。
GROUP BYの式ではありません
SELECTでは、GROUP BYの条件句で指定しているカラムしか選択できません。
ORA-01013: ユーザーによって現行の操作の取消しがリクエストされました
タイムアウトしている。DBに再接続すれば解消する。
ORA-01536 テーブル容量オーバー
Oracle XE（無料版）では、全体の限度容量（MACBYTES）は12GB。かつ、各スキーマでも容量の限度が設定されている。
確認方法は、DBAユーザで以下コマンドを実行する。
select * from DBA_TS_QUOTAS; // スキーマ名などの一覧
select * from DBA_DATA_FILES; // データ容量や、データが保存されているパス使用率の高い表領域を調査したい場合、以下のSQLで調べることができる。
SELECT
df.tablespace_name,
ROUND((df.bytes - NVL(f.free_bytes, 0)) / df.bytes * 100, 2) AS 使用率,
ROUND(df.bytes / 1024 / 1024, 2) AS TOTAL容量,
ROUND((df.bytes - NVL(f.free_bytes, 0)) / 1024 / 1024, 2) AS 使用容量,
ROUND(NVL(f.free_bytes, 0) / 1024 / 1024, 2) AS 空き容量
FROM
(SELECT tablespace_name, SUM(bytes) AS bytes FROM dba_data_files GROUP BY tablespace_name) df
LEFT JOIN
(SELECT tablespace_name, SUM(bytes) AS free_bytes FROM dba_free_space GROUP BY tablespace_name) f
ON df.tablespace_name = f.tablespace_name
ORDER BY used_percent DESC;このとき、使用率にまだ多くの空きがあるのに、容量オーバーのエラーが出るケースがある。
表領域に含まれるデータは、dbfという物理ファイルによって管理される。
これは、DBA_DATA_FILESテーブルで確認することができるが、データファイルと表領域は必ずしも1対1になっておらず、1つの表領域がデータファイルにまたがることもある。
そのため、単純に表領域の使用率を見ただけでは、厳密な空き領域を確認することはできない。
参考：Oracle Database 概要_表領域、データファイルおよび制御ファイル
容量オーバーとなっている表領域と、使用率にあまりにも大きな乖離がある場合は、容量の大きいテーブルのtrancateを検討する（deleteではなく、trancateを使用すること）。
それでも解消しない場合は、単純なテーブルの見た目領域に、必要以上のシステム管理データが紐づいている可能性があるため、テーブルのDROP＞再構築を検討する。
データを削除するのがNGで、表領域の限度を変更したい場合は、以下を実施する。
まずは表領域の中で、使用率の高いユーザーを特定
SELECT
s.tablespace_name,
s.owner AS username,
ROUND(SUM(s.bytes) / 1024 / 1024, 2) AS 使用容量
FROM
dba_segments s
GROUP BY
s.tablespace_name, s.owner
ORDER BY
used_size_mb DESC;また、ユーザーに容量制限（quotas）がかかっているかも確認。※以下のSQLでは容量制限がUNLIMITEDに指定されているユーザーについては取得されないため注意する。
SELECT tablespace_name, username, bytes/1024/1024 AS allocated_mb, max_bytes/1024/1024 AS max_alloc_mb
FROM dba_ts_quotas
ORDER BY allocated_mb DESC;ユーザーの容量制限を拡張する。
ALTER USER ユーザー名 QUOTA 20G ON テーブルスペース名;無制限にする場合は、20Gの値をUNLIMITEDに指定する。
コメント