Oracle DB勉強メモ

MacでOracle DBを触る方法

用意されている環境の最新版が19cまでだったので、こちらで用意を行った

詰まりポイント
- Docker の Resources が足りずに設定変更が必要だった
  - Memory を4.00GBに変更
  - Disk Image sizeを24GBに変更

パスワードがどこにあるかわからなかったけど、以下のコマンドで変更可能

docker exec <container name> /u01/app/oracle/setPassword.sh <your password>

guri3

ユーザーの作成

ユーザーの作成コマンドを実行すると以下のエラーが発生した

SQL> create user test identified by password;
create user test identified by password
            *
ERROR at line 1:
ORA-65096: invalid common user or role name

エラーの原因は以下のサイトを参照した

Docker によるセットアップのデフォルト設定では、ORCLPDB1 という名前でPDBが作成されている
なので、こちらのPDBに接続後ユーザーを作成するコマンドを実行することでユーザーの作成を行える

[oracle@7485f28f242b ~]$ sqlplus system@ORCLPDB1

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jun 20 07:55:30 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Enter password:
Last Successful login time: Sun Jun 20 2021 07:46:52 +00:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> create user test identified by password;

User created.

guri3

SQL*Plus のコマンドについて

guri3

SQL文メモ

TRUNCATE TABLE文

全てのデータを高速に削除する
TRUNCATE TABLE文はトランザクションが無効なので、失敗した場合に取り消すことができない

SQL> truncate table member;

Table truncated.

文字列結合

文字列の結合には || を利用する

WHERE句とHAVING句の違い

WHERE句はGROUP BY句による集計の前に実行され、HAVING句は集計後に実行される
なので、HAVING句は集計関数で得た値に対して条件指定を行うことができる

guri3

Oracle SQL Developer からのPDBへの接続

接続設定で、SID ではなくサービス名にPDBの名前を設定する必要があった

SIDとサービス名の違いがいまいちわかってないので、この辺を読んでみる

guri3

SQL*Plus とか Oracle SQL Developer から登録した日本語が文字化けする

Docker 内部で以下の環境変数を設定したのちにデータを作り直したら直った

$ export NLS_LANG=Japanese_Japan.AL32UTF8

NSL_LANG に関するQ&Aリンク

guri3

出力結果を整形するコマンド

行の文字列やページの表示行数を変更する

set linesize <1行の文字数>
set lines <1行の文字数>

set pagesize <1ページあたりに表示する行数>
set pages <1ページあたりに表示する行数>

column <列名> format a<バイト数>
col <列名> format a<バイト数>

日時表示のフォーマットを調整する

alter session set NLS_DATE_FORMAT='<日時フォーマット文字列>';

TIMESTAMP 型は NLS_TIMESTAMP_FORMAT に日時フォーマット文字列を指定する

guri3

権限

権限の付与、削除は以下のように行う
ロールの割り当ても下の構文の<権限>を<ロール>にすればできる

grant <権限> to <ユーザー>;
revoke <権限> from <ユーザー>;

クォータ

ユーザーがオブジェクトを作成してデータを格納する場合、CREATE xxx システム権限だけでなく、クォータ（表領域の割当て制限）を割当てる必要がある
割当てサイズに unlimited を指定することで無制限に利用できる

alter user <ユーザー> quota <割り当てサイズ> on <表領域>;

guri3

Oracle のストレージ割当て

表領域
- テーブルの格納先
セグメント
- オブジェクト1つ1つに対応する格納用領域
ストレージ
- ディスクなどの記憶領域
データファイル
- 表領域の実態（ファイル）

表領域の作成SQL例

create tablespace <表領域名> datafile '<データファイルのパス>' size <サイズ> [REUSE] [AUTOEXTEND ON];

テーブルの作成時などに表領域を指定することができる
tablespace 句を指定しない場合はデフォルト表領域にテーブルが格納される

create table <テーブル名> (<列名> <データ型>) tablespace <表領域名>;

guri3

データディクショナリビュー

DBA_USERS や V$PARAMETER などのデータベースの管理情報や設定情報などを確認できる特殊なオブジェクト
Oracleでは2000以上のデータディクショナリビューが存在するらしい

guri3

テーブルのデータの見積もり

PL/SQL のプロシージャを用いて、テーブルやインデックスのサイズを見積もることができる

DBMS_SPACE.CREATE_TABLE_COST
DBMS_SPACE.CREATE_INDEX_COST

guri3

バックアップ

アーカイブログモードで運用を行うことで、オンラインでのバックアップやREDOログファイルの保管を行うことができる

REDOログファイルは、障害からのデータベース復旧時にバックアップから障害発生時点のデータベースの状態まで戻すために使用される

Oracle のバックアップ用ツールとして RMAN というものが用意されている

guri3

メンテナンス

Oracle が SQL を実行する仕組み

オプティマイザ統計情報の取得
Oracle 内部の DBO (Cost Based Optimizer) によって実行計画が作られる
実行計画に基づいた SQL の実行

オプティマイザ統計情報

オプティマイザ統計情報はデータの変化によって適切なものが変わるので、定期的に更新する必要がある
更新方法には自動更新と手動更新があり、自動更新することがおすすめ

オプティマイザ統計情報の取得では、大量の I/O が発生するので、システムのパフォーマンスが悪化する場合がある

バッチ等でデータの大量更新を行った場合は、オプティマイザ統計情報の手動取得が必要になることがある

テーブルの断片化

データの insert 後に大量の delete や update などでの更新が入ると、ブロックに未使用領域や、細かくて使えないデータの空きが存在することになる
これらはディスク使用の効率やデータの探索に悪影響を及ぼすため、定期的に解消してあげる必要がある

テーブルの再編成方法

ALTER TABLE MOVE
ALTER TABLE SHRINK
オンライン再定義

guri3

パフォーマンス情報の取得

Statspack からある期間のシステムの負荷などを確認することができる

Statspack から確認するべき最初のセクション

Load Profile
- システム/アプリケーションの負荷特性
Instance Efficiency
- 典型的な指標におけるデータベースの処理効率
Top 5 Timed Events
- 最も待機時間を要した上位5種類の待機イベントに関する情報
SQL Ordered by xxx
- 各種統計項目においてしきい値を超えた高負荷SQLに関する情報