「プロとしてのOracle入門」読了

プロとしてのOracle入門

1年くらい前に読んだこれならわかる Oracle 超入門教室 第2版 (DB Magazine SELECTION)とかぶるけど何故か買ってしまったので読んでみた。
以下メモ。

Oracle構成要素

データベース(ディスク)

• データファイル
  • ユーザーが使用するデータ
  • データディクショナリ(管理用データ)
• 制御ファイル(DBの物理構造を保持)
• REDOログファイル

インスタンス(メモリ)

• SGA(System Global Area)
  • データベースバッファキャッシュ(データファイルのユーザー使用データ配置場所)
  • REDOログバッファ(REDOログファイル配置場所)
  • 共有プール
    • ディクショナリキャッシュ(データディクショナリ配置場所)
    • ライブラリキャッシュ(SQLの実行計画を配置する、保存はされない)
• バックグラウンドプロセス
  • DBWR(Database Writer:データベースバッファキャッシュからデータファイルへ保存）
  • LGWR(Log Writer:REDOログバッファからREDOログファイルへ保存)
  • CKPT(CheckPoint:DBWRが保存するタイミング(CheckPoint)を指示、データファイル・制御ファイルに「DBは正常である」と書き込む)
  • SMON(System Monitor:システムを監視し、異常時はREDOログファイル等から復旧作業を行う)
  • PMON(Process Monitor:ユーザーのプロセスを監視する)

REDOログファイル冗長化

• 変更内容は複数のREDOログファイルに記録する。
• 同じ内容のREDOログファイルをメンバという。
• 一杯まで記録すると、次のメンバに切り替えて記録を行う。
• 最後のメンバの次は最初のメンバに切り替える(最初の変更内容は失われる)。

メモリ解放タイミング

LRU(Least Recently Used)

Oracle論理構造

BEST構造

Block

Oracleが認識する最小単位

Extent

連続したブロックの固まり

Segment

1つ以上のExtentの固まり(＝テーブル、索引)

TableSpace(表領域)

1つ以上のSegmentから成る。1つ以上のデータファイルから構成される。

表領域の種類

SYSTEM表領域

(必須)データディクショナリが保存される。

SYSAUX表領域

Auxiliary(補助)。オプションのデータディクショナリが保存される。

UNDO表領域

変更前の情報が保存される。

一時表領域

メモリが足らないときに使用される。

ユーザー作成表領域

ユーザー使用データが保存される。

データディクショナリの種類

名前の接頭句で識別

DBA_

全ての情報を保持。アクセスには管理者権限が必要。

ALL_

現在アクセスしているユーザがアクセスできる情報全て。一般ユーザでもアクセス可能。

USER_

現在アクセスしているユーザが所有する情報全て。一般ユーザでもアクセス可能。

文字型

CHAR型(固定長)

設定したサイズ上限に見たない分はスペースが入る。サイズ上限は2000バイト。

VARCHAR2型(可変長)

設定したサイズ上限に関わらず格納した分だけ領域を確保。サイズ上限は4000バイト。更新後サイズが減る場合は減少分をスペースで埋める。更新後サイズが増える場合は更新前サイズを超える分は分断されて格納される。

ワイルドカード(%,_)

%

複数文字の代用

_

1文字の代用

関数

単一行間数

1行に対して1つの結果を返す(LENGTH,UPPER,SYSDATE,...)

グループ関数

複数行に対して結果を返す(AVG,SUM,...)

dualテーブル

以下のように使用するダミーテーブル

select sysdate from dual;

NVL関数

select nvl(comm, 0) from emp;

AVG関数

select avg(sal) from emp;
--グループ化する列を指定
select deptno, avg(sal) from emp group by deptno;

結合

--等価結合
select d.deptno, e.ename, e.sal, d.loc from dept d, emp e where d.deptno = e.deptno;
--外部結合(e.deptnoはマッチしなくてもすべて表示)
select d.deptno, e.ename, e.sal, d.loc from dept d, emp e where d.deptno = e.deptno(+);

比較演算子

IN

いずれかと等しい。

ANY

<や>と併用。いずれかが直前の演算子の条件を満たす。

ALL

<や>と併用。全てが直前の演算子の条件を満たす。

--マネージャより給料がいいヤツ
select ename from emp where sal > ANY (select sal from emp where job = 'MANAGER') and job <> 'MANAGER';

列追加、削除

alter table tbl_test add (column5 varchar2(20));
alter table tbl_test drop (column5);

SQL*Plus

SPOOL機能

ログを残す

spool c:\select.log
select * from dept;
spool off

SETコマンド

--1行に表示される文字数を設定
set line 90

設定値表示

spool c:\setting.txt
show all
--設定値一覧が表示される
spool off

置き換え機能

C[HANGE]/(変更したい文字列)/(変更後の文字列)

> select * form dept;
--エラー発生
> list --バッファ内のSQL表示
  1 select * form dept;
> change/form/from
  1 select * from dept;
> list
  1 select * from dept;
> / --バッファ内のSQLを実行

条件追加

A[PPEND] <文字列>

> list
  1 select * from emp;
> a where deptno = 10;
  1 select * from emp where deptno = 10;
> list
  1 select * from emp where deptno = 10;
> /

ユーザー

ユーザーの種類

• 管理者ユーザー
  • sysユーザー
  • systemユーザー
• 一般ユーザー
  • scottユーザー

ユーザーの作成

• ユーザー情報
  • ユーザー名
  • パスワード
  • デフォルト表領域
    • 指定しないとオブジェクトがSYSTEM表領域に作成されてしまうので必ず作成する
  • Quota(非必須)
    • 使用できる表領域の上限
  • デフォルト一時表領域(非必須)

ユーザー情報の確認

--dba_usersデータディクショナリビューを使用
select username, password, default_tablespace, temporary_tablespace from dba_users;
--Quota情報の確認
select tablespace_name, username, max_bytes from dba_ts_quotas;

権限とロール

権限の種類

• システム権限
  • 管理者ユーザーが明示的に作成した各ユーザーに付与する必要がある
  • システム権限を付与しないと一般ユーザーはDBへの接続もできない
  • CREATE SESSION, CREATE TABLESPACE等
• オブジェクト権限
  • 特定の操作を実行する為の権限
  • オブジェクトの所有者が付与する
  • SELECT, INSERT, UPDATE等

権限の確認

--システム権限はuser_sys_privsデータディクショナリビューを使用
select username, privilege, admin_option from user_sys_privs;
--オブジェクト権限はuser_tab_privsデータディクショナリビューを使用
select grantee, owner, table_name, privilege from user_tab_privs;

ロール

• 複数の権限を1つにまとめたもの
• デフォルトで用意されているロール
  • CONNECT
    • CREATEセッションシステム権限のみ
  • RESOURCE
  • DBA

ロールの確認

--dba_rolesデータディクショナリビューを使用
select * from dba_roles;

索引(インデッス)

• Oracleでは索引での検索時に「B*Tree」を使用
• 索引は等価検索か前方一致検索で使用する

索引の確認

--indデータディクショナリビューを使用
select index_name, table_name, tablespace_name from ind;
--索引の構造の確認
select table_name, index_name, column_name from user_ind_collumns;

ビュー

• 「仮想表」とも呼ばれる
• ビューにはデータそのものは含まれない
• データディクショナリに登録される

ビューのメリット

• SQL簡略化
• セキュリティ向上
• 利便性向上

ビューの確認

--user_viewsデータディクショナリビューを使用
select view_name, text from user_views;

シノニム

オブジェクトに別名を付けて登録しておくオブジェクト

シノニムのメリット

• セキュリティ向上
• オブジェクト名の簡略化

シノニムの種類

• プライベートシノニム
• パブリックシノニム

シノニムの確認

select synonym_name, table_owner, table_name from syn;
--tabデータディクショナリビューでも確認できる(TABTYPEがSYNONYMになっている)
select * from tab;

パブリックシノニムの確認

select owner, synonym_name, table_owner, table_name from all_synonyms;