Skip to main content

Oracleの基本

書籍リンク​

感想​

  • Oracle 使用時の基本用語が大体 分かるようになる
    • SQL のおさらいにも良い
  • 実際に運用してメンテナンスするときの勘どころ的な視点でも書かれている（ように見える）
    • 何のためにこの機能があるのか、というのが分かる
    • オプティマイザ統計情報とか断片化解消とかのくだり
  • Oracle の Windows への OTNライセンスでのインストール方法が書かれてる
    • 自力で試行錯誤する前に読んでおけば、どの設定が何であるか分かって効率的だったはず

以下はメモ

〜〜〜〜

outer join の oracle 固有記法 (+)​

sql
select EMPNO, ENAME, DNAME from EMP e
left outer join DEPT d
on e.DEPTNO = d.DEPTNO

↑

sql
select EMPNO, ENAME, DNAME from  EMP e, DEPT d
where e.DEPTNO = d.DEPTNO(+);

同じらしい

  • (+) がついている方が必須ということか
  • 非推奨ということなので、新しくは書かない
  • けど、読むときに困るので覚えておく

SQL*Plus コマンド​

表示系​

sql
set LINESIZE <1行の文字数>
set PAGESIZE <1ページあたりに表示する行数>

列の表示幅を指定​

sql
COLUMN <列名> FORMAT A<バイト数>

日時の表示フォーマットを指定​

sql
alter session set NLS_DATE_FORMAT = 'YYYY/MM/DD HH24:MI:SS'

これは sqlplus コマンドではなく、初期化パラメータという

  • DATE型のとき：　NLS_DATE_FORMAT
  • TIMESTAMP型のとき：NLS_TIMESTAMP_FORMAT

SQLの種類​

  • DML : Data Manipulation Language
    • データの変更
    • select / insert / update / delete
  • トランザクション制御文
    • commit / rollback
  • DDL : Data Definition Language
    • alter / create / drop / grant / revoke
    • truncate も DDL

index について​

index 作成​

sql
create index <インデックス名> on <テーブル名>(<列名1>,..)
  • 検索条件に指定するものを指定する
  • 増やしすぎると、データ更新時のインデックス更新の負荷が増える

index を使える検索条件​

index を使われる検索条件

  • 等価条件 : COL1 = 100
  • 範囲検索 : COL1 > 100
  • LIKEの先頭一致条件 : COL1 like 'ABC%'

index が使われない検索条件

  • COL1 != 100
  • COL1 like '%BC%'
  • COL1 is null

VIEW について​

なぜVIEWが必要か（これは書いてあったことではないがメモに追加用）​

  • 見る範囲が狭められる
  • 影響確認などが楽
  • オブジェクト指向のクラスの公開範囲と考え方は一緒

マテリアライズドビューとは？​

  • select 結果を都度検索せず保持するもの
  • VIEW自体の条件が複雑で、かつ同じVIEW何度も使う場合に効率的ということか

制約について​

制約の一覧​

  • not null 制約
    • null が無いこと
  • check 制約
    • 列(または列の組み合わせ) が条件を満たすこと
  • unique key 制約（一意制約）
    • 重複がないこと
    • null可？
  • primary key 制約（主キー制約）
    • 重複も null も無いこと
    • 1テーブルに1つのみ設定可
    • not null と両方つけると冗長？またはつける必要がある？？
  • foreign key 制約（外部キー制約、参照整合制制約）
    • 外部TBLのキーと一致すること

外部キー制約の設定のしかた​

sql
create table TBL_CHILD(
    ID number primary key
  , PARENT_ID references TBL_PARENT(ID)
  , NAME varchar2(10)
sql
[constarints <制約名>] references <親TBL名>(<キー列名1>,..)

これ設定するのは順番を気にしたりする必要が出てくる？ transaction 管理していれば問題ないということ？

重くなりそうではある。 といっても更新時だけか

チェック制約の設定しかた​

sql
create table TBL1 (
    NAME  varchar2(10)
  , PRICE number(8) check(PRICE >= 100 and PRICE <= 500))
;

primary key 制約の設定のしかた​

1つの場合

sql
create table TBL1 (
    ID  number primary key
  , NAME  varchar2(10)
);

複数キーにする場合

sql
create table TBL1 (
    FIRST_NAME  varchar2(10)
  , LAST_NAME   varchar2(10)
  , primary key (FIRST_NAME, LAST_NAME)
;

シーケンスについて​

連番を生成するもの

  • 「振り出す」というらしい
  • 「払い出す」も同じ？

使いかた

sql
insert into TBL_TEST1
  (ID, NAME)
  values
  (SEQ_TEST1.nextval, 'テスト1')
;

ユーザについて​

説明​

  • ローカルユーザ ： 特定の PDB でのみ有効
  • 共通ユーザ ： CDB 全体で有効

マルチテナント環境の構成例

  • データベース全体 : cdb$root
    • PDB(1) : APP1DB
      • USER1
    • PDB(2) : APP2DB
      • USER2
    • PDB(3) : APP3DB
      • USER1 ← APP1DB.USER1 とは別管理

事前作成済みの共通ユーザ（SYS, SYSTEM）の有効範囲はデータベース全体（cdb$root）

create user​

sql
create user <ユーザ名> identified by <ぱすわーど>
  [default tablespace <デフォルト表領域>]
  [temporary tablespace <デフォルト一時表領域>]
  [quota <割当てサイズ> on <表領域> ...]
;
  • 表領域
    • オブジェクト作成時のデフォルトの格納先
    • 省略時は USERS
  • 一時表領域
    • 大量のデータ処理（ソートなど）を実行する時に使用される領域
    • 省略時は TEMP
  • quota
    • 指定しないとオブジェクト作成できない？
    • 無制限にするには unlimited を指定

パスワード変更​

sql
alter user <ユーザ> identified by <パスワード>;

Oracle 11g から

  • パスワードの有効期限は 180 日
  • パスワード大文字/小文字が区別されるようになった
    • 無効にするには、初期化パラメータ　SEC_CASE_SENSITIVE_LOGON を FALSE に設定する

drop user​

sql
drop user <ユーザ名> [cascade]
  • cascade オプション
    • ユーザのオブジェクト（TBLとか）も削除する場合に指定
    • 指定しない場合は、あらかじめ各オブジェクトを drop しておく必要あり

他ユーザが所有するオブジェクトへのアクセス​

sql
select * from user1.TBL_TEST1

省略すると自分ユーザのオブジェクトにアクセスするが、 別に自分ユーザでも指定するようにしても良い。

オブジェクトは、ユーザ毎の「箱」に格納されているイメージだが、 この、ユーザ毎の箱を「スキーマ」と呼ぶこともある

権限付与​

  • 権限付与は PDB ごとに独立

権限付与/取り消し

  • grant
  • revoke

ロール​

ユーザ毎ではなく ロール単位で権限付与できる

sql
grant <システム権限> to <ロール>;
revoke <システム権限> from <ロール>;

grant <オブジェクト権限> on <オブジェクト> to <ロール>;
revoke <オブジェクト権限> on <オブジェクト> from <ロール>;

ユーザは ロールに所属させれば良い

sql
grant <ロール> to <ユーザ>;
revoke <ロール> from <ユーザ>;

定義済みロール

  • CONNECT
  • RESOURCE
  • DBA
  • DATAPUMP_EXP_FULL_DATABASE
  • DATAPUMP_IMP_FULL_DATABASE

システム権限

  • CREATE SESSION ← Oracle に接続するために必要
  • CREATE TABLE, SEQUENCE, .. etc

付与された権限を他のユーザにも付与できるようにするには？

  • with grant option 句をつける

サンプルユーザ？​

connect stott/tiger@localhost:1521/orclpdb

id/pw

  • scott/tiger
  • sh/sh ← これも？

共通ユーザへの権限付与​

sql
grant <権限/ロール> to <共通ユーザ> container=all;
grant <権限/ロール> to <共通ユーザ> container=current;

共通ユーザはcdb$root 単位でのユーザのこと（＝PDB単位ではなく）

表領域​

表領域とは​

複数のデータファイルを束ねた、オブジェクトにデータ格納用領域を割り当てるための「仮想的な箱」のようなもの

表領域の作成​

sql
create tablespace <表領域名>
    datafile '<データファイルのパス>' size <サイズ> [reuse] [autoextend on]
  , datafile '<データファイルのパス>' size <サイズ> [reuse] [autoextend on]
;

↑ 複数ファイルある場合は、このように複数指定

表領域の削除​

sql
drop tablespace <表領域名> [including contents [and datafiles]];
  • including contents : 格納されたオブジェクトも消す
    • and datafiles : データファイルも消す

データファイル残して drop して、再利用できたりするんだろうか・・？

テーブル作成時に表領域を指定する方法​

sql
create table (
  ...
) tablespace <表領域名>
;

ユーザのデフォルト表領域を確認する方法​

sql
select DEFAULT_TABLESPACE from DBA_USERS where USERNAME = '<ユーザ名>';

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

  • DBA_USERS
  • V$PARAMETER

いったんスキップした章​

  • 5.1. テーブル設計
  • 物理設計

運用系​

アーカイブログモードとは​

リカバリ機能のために変更履歴（REDOログ）を残す仕組み

データベースの変化と必要なメンテナンスの作業​

  1. データ量の変化にともなうオプティマイザ統計情報の再収集
  2. データ変更にともなうテーブルの断片化の解消
  3. 動作環境の変化に追従するために初期化パラメータ変更

オプティマイザ統計情報の手動取得​

sql
execute DBMS_STATS.GATHER_DATABASE_STATS;
execute DBMS_STATS.GATHER_SCHEMA_STATS('sugoi_user');
execute DBMS_STATS.GATHER_TABLE_STATS('sugoi_user', 'sugoi_table');
  • ANALYZE 文 は非推奨らしい

断片化の解消​

  • ALTER TABLE MOVE
    • この操作の間、データ変更はできない
    • 一時的に倍の記憶領域が必要となる
  • ALTER TABLE SHRINK
    • 自動セグメント管理方式の場合 ← これなんだろ
  • オンライン再定義
    • Enterprise Edition 限定
  • テーブルの truncate + データ再投入
    • exp → truncate → imp
    • 推奨されないらしい
sql
alter table <テーブル名> shrink space [compact] [cascde];

これは上記3つとは別路線？

sql
alter index IDX_TEST1 rebuild;

設定系​

初期化パラメータ​

  • 初期化パラメータはコンテナデータベース（CDB）単位
  • ただし、下記は PDB 単位で設定可
    • NLS_LENGTH_SEMANTICS ← byte / char
    • SEC_CASE_SENSITIVE_LOGON
    • DIAGNOSTIC_DEST
    • LOCAL_LISTENER

初期化パラメータファイル​

  • init<ORACLE_SID>.ora
    • 「イニット・オラ」などと呼ばれる、
    • テキスト形式の初期化パラメータファイル
    • いまはデフォルトでは使われていないし、推奨されない
  • spfile
    • サーバパラメータファイル
    • バイナリ形式
    • こっちが標準

メモリ関連の初期化パラメータ​

  • SGA_TARGET
    • Oracle 全体で共有するメモリ（SGA）のサイズ
  • PGA_AGGREGATE_TARGET
    • プロセスが使用するメモリ（PGA）の総サイズ
  • MEMORY_TARGET
    • SGA + PGA の合計

初期化パラメータの値を確認​

  1. SQL*Plus の show parameters で確認
    • 指定は部分一致で良い
    • show parameter pga
  2. V$PARAMETER ビューで確認
    • select * from V$PARAMETER where name like '%pga%'

その他​

おすすめされたもの​

  • マニュアル「Oracle Database 概要」

簡易接続ネーミングメソッド（EZCONNECT）とは？​

tnsnames.ora を指定せずに接続する方法

[//]<データベースサーバのホスト名>[:<リスナのTCPポート番号>] [/<接続先データベースのサービス名>]

Oracle Database Instant Client とは​

  • 最低限のランタイムライブラリ？
  • 再頒布可能ライブラリ、的なもの？

ネットサービス名​

以下のパスに定義

<ORACLE_HOME>/network/admin/tnsnames.ora

リスナの起動/停止/サービス登録状況確認​

lsnrctl start [リスナ名]
lsnrctl stop [リスナ名]
lsnrctl services [リスナ名]
  • 通常はデフォルトの1個のリスナ（LISTENER）だけ起動する
  • その場合は上記コマンドでもリスナ名を省略可

Net Configuration Assistant (NetCA) とは​

  • リスナを構成するするためのツール？

読み取り専用 Oracle ホーム​

Oracle 18c から導入された構成

spfile, listener.ora, tnsnames.ora など環境固有のファイルを Oracle ホームから排除したというもの

↑ インストールしてみたら変なフォルダ構成になったと思ったら、こういうことか

Oracle の常時起動プロセスと役割​

  • SMON
    • Oracle の起動時に必要に応じてリカバリを実行
  • PMON
    • 他のプロセスの起動状態を監視する
  • LGWR
    • REDO ログを書き込む
  • CKPT
    • データファイル書き込み状況を示すチェックポイント情報を記録（？）

見るべきログ​

  • アラートログ
    • alert_<ORACLE_SID>.log
  • 自動診断リポジトリ？
    • ADR (Automatic Diagnostic Repository)

sar コマンドなど（Oracle ではなく Linux 一般知識）​

  • vmstat : 現在のリソース使用状況を見る
  • sar : 現在＋過去のリソース使用状況を見る
  • top : 現在のプロセス単位のリソース使用状況を見る

sar コマンドd収集できるリソース統計

  • %sys : CPU使用率（OSが使うもの）
  • %usr : CPU使用率（OS以外のプロセスの）
  • runq-sz : CPU割り当て待ちとなっているプロセス数
  • %iowait : ディスクI/O処理していた時間の割合
  • avque : ディスク内の処理競合により待ちとなっている I/O要求の数

Windows パフォーマンスモニタの指標についても書いてある​

省略

statpack レポート​

定期的に取得されたスナップショットからレポートを生成するもの。

  • Oracle 同梱の spreport.sql を実行

RMAN とは​

  • Oracle に標準で付属しているユーティリティ「Recovery Manager」のこと
  • バックアップ/リカバリを簡単に行う管理ツール
  • 差分バックアップしている？
Last updated on