Oracle Database SQL備忘録

SQL基礎

基本

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 LAST

NULLのレコードは最後に表示：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) > 1

JOIN

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 社員家族
);

Appendix

OracleDBのバージョン確認

SELECT * FROM product_component_version;  // VERSION_FULLの項目を参照

表領域、セグメント、エクステント、ブロックの関係

【表領域（TABLESPACE）】
　┗【セグメント（TABLE / INDEX / UNDO など）】
　　　┗【エクステント（データのまとまり）】
　　　　　┗【ブロック（データの最小単位）】
　　　　　┗【ブロック（データの最小単位）】
　　　┗【エクステント（データのまとまり）】
　┗【セグメント（TABLE / INDEX / UNDO など）】

データディクショナリ

Oracle Databaseには、デフォルトで作成されるメタデータがあり、表領域やユーザー情報などが登録されている。

主に、以下の3つのビューがある。

良く参照するビューは以下。

インシデント

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に指定する。