Oracleデータベース管理&SQL v23ai

長い年数使ってきましたOracleについて、その管理機能をまとめたものです。ITご利用者の方々のご参考になれば幸いです。

◎Oracleデータベース管理
イベント、トリガー
SGA(システムグローバル領域)メモリ構造、PGA(プログラム・グローバル領域)
ディスパッチャー
プロセス
リスナー
データベース・サービス名　== グローバル・データベース名
ネットワーク・ホスト名
例. connect scott/tiger@KAIKEI //接続識別子
・ネットサービス名・・・ローカルネーミング
Enterprise Manager、Net Manager
Oracle サーバープロセス・・・バックグラウンドプロセス・サーバープロセス・ユーザープロセス

◎Oracleインスタンスの管理
システム・グローバル領域(SGA)・・・データベース・バッファ・キャッシュ、REDOログバッファ、共有プール(SQL解析)、ラージ・プール、Javaプール
Oracleプロセス・・・データベース・ライター、ログ・ライター、システム・モニター、プロセス・モニター、チェックポイント・プロセス、アーカイバー・プロセス(REDOログ)
インスタンスの起動・・・バックグラウンドプロセスの起動
データベースのマウント・・・インスタンスにデータベースを割り当て
データベースのオープン・・・REDOログファイルとデータベースファイルをオープン
init.ora・・・PFILE(テキスト形式静的パラメータ・ファイル)・・・テキスト形式
spfile.ora・・・SPFile(サーバー・パラメータ・ファイル)・・・バイナリ形式
自動共有メモリ管理

◎データベース記憶構造の管理
制御ファイル・データファイル・REDOログファイル
物理構造・論理構造・表領域・セグメント・エクステント
データベースの表領域・・・SYSTEM・SYSAUX・TEMP・UNDO・USERS・EXAMPLE

◎ユーザーおよびセキュリティの管理
ユーザー属性・・・認証方式・パスワード・デフォルト表領域・一時表領域・割当制限・アカウントのロック
Oracleのユーザー・・・DBSNMP・SYSMAN・SYS・SYSTEM・サンプルユーザー
権限・・・システム権限・オブジェクト権限・・・CREATE TABLE権限・SELECT権限など
ロール・・・権限をグルーブ化したもの
システム権限・・・SYSDBA・SYSOPER
ロールの追加・取り消し

◎スキーマ・オブジェクトの管理
スキーマ・・・SYSスキーマ、SYSTEMスキーマ
データ型
スカラー型・・・CHAR、VARCHAR2、NCHAR、NVARCHR2
数値型・・・NUMBER、BINARY_FLOAT、BINARY_DOUBLE
日付型・・・DATE、TIMESTAMP、TIMESTAMP WITHTIME_ZONE、TIMESTAMP WITHLOCAL_TIME_ZONE、INTERVAL YEAR、INTERVAL DAY TO SECOND
RAW型・・・可変長、バイナリ
ROWID型・・・行のアドレス
UROWID型・・・ユニバーサルROWID型、オブジェクトのアドレス
LOB型・・・Large Object型
LONG、LONGRAW
コレクション型・・・VARRAY型、ネストしたテーブル型
関連型・・・REF型・・・オブジェクト・データへのポインタ
データ整合性制約・・・NOT NULL、一意キー、主キー、外部キー制約(参照整合性制約)、チェック制約
索引
ビュー
常駐型プログラム・ユニット・・・パッケージ・プロシージャ・ファンクション、データベース・トリガー、Javaソース
データのロード・・・SQL*Loader、Enterprise Managerのメンテナンス

◎Oracleのリカバリ機能
RMAN
インスタンス・リカバリ
データ・ファイル・メディア・リカバリ
フラッシュバック
一貫性バックアップ、非一貫性バックアップ
完全リカバリ、Point-in-time(不完全)リカバリ
メディア・リカバリ
フラッシュ・バック・リカバリ領域のサイズ・・・データ・ファイル領域×2 + REDOログ7領域 + 増分バックアップ領域
バックアップのタイプ・・・データ・ファイル全体のバックアップ、データファイルの増分バックアップ、データベース全体のバックアップ
バックアップ・ファイルのタイプ・・・イメージ・コピー、バックアップ・セット(全体バックアップ・増分バックアップ)
バックアップ設定の構成・・・ディスク設定、ポリシー設定
データベース全体のバックアップ・・・ARCHIVELOGモード
推奨バックアップ計画
バックアップの管理・・・現行バックアップの管理ページの使用、バックアップ・メンテナンス・タスクの実行(クロスチェック・期限切れ/不要バックアップの削除・追加バックアップのカタログ化)、データベース全体のリストアおよびリカバリ
フラッシュバック・テーブル・・・表における行移動の有効化、表のフラッシュバックの実行、フラッシュバック・ドロップの使用による表のリカバリ

◎データベースの監視とアドバイザの使用
・パフォーマンス分析の表示と結果への対応・・・デフォルト、アドバイザの使用、SQLチューニング・アドバイザ、SQLアクセス・アドバイザ、メモリー・アドバイザ
・一般的なデータベースの状態およびワークロードの監視・パフォーマンスの監視・アラートの使用
・しきい値・メールによる通知

Oracle Database 23ai: 主要機能

1. DML (Data Manipulation Language)

データの追加、変更、削除を行うためのSQL文です。

  • INSERT (データの挿入)
    • Oracle 23aiでの拡張: 従来の1行ずつの挿入に加え、VALUES句で複数行を一度に挿入できるようになりました。
    • 実装例:
      INSERT INTO employees (employee_id, first_name, last_name, email) VALUES
      (101, ‘John’, ‘Doe’, ‘john.doe@example.com’),
      (102, ‘Jane’, ‘Smith’, ‘jane.smith@example.com’),
      (103, ‘Peter’, ‘Jones’, ‘peter.jones@example.com’);
      “`
  • UPDATE (データの更新)
    • Oracle 23aiでの拡張: FROM句を使用して、他のテーブルとの結合に基づいて更新できるようになりました。
    • 実装例:
      UPDATE employees e
      SET e.salary = e.salary * 1.10
      FROM departments d
      WHERE d.department_id = e.department_id
      AND d.department_name = ‘IT’;
  • DELETE (データの削除)
    • Oracle 23aiでの拡張: FROM句を使用して、他のテーブルとの結合に基づいて削除できるようになりました。
    • 実装例:
      — Oracle 23aiでの直接JOINによるDELETE
      DELETE FROM employees e
      FROM departments d
      WHERE d.department_id = e.department_id
      AND d.department_name = ‘HR’;
      “`
  • MERGE (結合と更新/挿入)
    • 機能: ソーステーブルのデータに基づいて、ターゲットテーブルの行を挿入または更新します。

2. DDL (Data Definition Language)

データベースオブジェクトの作成、変更、削除を行うためのSQL文です。

  • CREATE (オブジェクトの作成)
    • Oracle 23aiでの拡張: IF NOT EXISTS句を使用できるようになり、オブジェクトが存在しない場合にのみ作成する条件付きDDLが可能になりました。
      — Oracle 23aiでのCREATE IF NOT EXISTS
      CREATE TABLE IF NOT EXISTS my_table (id NUMBER, name VARCHAR2(100));
      — (my_tableが存在しない場合のみ作成される)
      “`
  • ALTER (オブジェクトの変更)
    “`
  • DROP (オブジェクトの削除)
    • Oracle 23aiでの拡張: IF EXISTS句を使用できるようになり、オブジェクトが存在する場合にのみ削除する条件付きDDLが可能になりました。
    • 実装例:
      — Oracle 23aiでのDROP IF EXISTS
      DROP TABLE IF EXISTS my_table;
      — (my_tableが存在する場合のみ削除される)
      “`

3. SELECT

データベースからデータを取得するためのSQL文です。

  • 基本的なSELECT
    • Oracle 23aiでの拡張: FROM句なしで、関数や式の結果を直接選択できるようになりました。
    • 実装例:
      — Oracle 23aiでのFROM句なしSELECT
      SELECT SYSDATE;
      SELECT 1 + 1;

4. ソート (ORDER BY)

クエリ結果を指定した列の順序で並べ替えます。

5. 単一行関数 (Single-Row Functions)

各行に対して1つの結果を返す関数です。

  • 機能: 数値、文字、日付などの操作を行います。

6. GROUP BY

指定した列の値に基づいて行をグループ化し、集計関数を適用します。

  • 機能: COUNT, SUM, AVG, MAX, MINなどの集計関数と組み合わせて使用します。
  • Oracle 23aiでの拡張: SELECTリストで定義した列の別名をGROUP BY句で使用できるようになりました。
  • 実装例:
    — Oracle 23aiでのGROUP BY (列別名を使用)
    SELECT department_id AS dept_id, COUNT(employee_id) AS num_employees
    FROM employees
    GROUP BY dept_id; — dept_id 別名を使用
    “`

7. 結合 (Joins)

複数のテーブルから関連するデータを結合して取得します。

  • Oracle 23aiでの拡張: UPDATE文とDELETE文で直接JOIN句を使用できるようになりました（上記DMLのセクション参照）。

8. 副問い合わせ (Subqueries)

別のSQL文の内側にネストされたクエリです。

  • 機能: 外部クエリの条件として使用したり、データソースとして使用したりします。

9. 集合演算 (Set Operations)

複数のSELECT文の結果を結合または比較します。

  • 機能: UNION, UNION ALL, INTERSECT, MINUSがあります。

10. スキーマオブジェクト (Schema Objects)

データベース内に存在する論理的な構造です。

  • 主要なスキーマオブジェクト:
    • TABLES (表): データを格納する基本的なオブジェクト。
    • VIEWS (ビュー): 1つ以上のテーブルから派生した仮想テーブル。
    • INDEXES (索引): データ検索のパフォーマンスを向上させるための構造。
    • SEQUENCES (シーケンス): 一意の数値を生成するオブジェクト（主キーの自動生成などに使用）。
    • SYNONYMS (シノニム): オブジェクトの代替名。
    • PROCEDURES / FUNCTIONS / PACKAGES / TRIGGERS (プロシージャ/ファンクション/パッケージ/トリガー): PL/SQLで記述されたプログラム単位。
  • Oracle 23aiでの新しいスキーマオブジェクト (JSON機能関連):
    • JSON Relational Duality Views (JSONリレーショナル二面性ビュー): リレーショナルデータとJSONドキュメントの両方としてアクセスできる新しいタイプのビュー。リレーショナルモデルの効率性とドキュメントAPIの使いやすさを組み合わせます。

11. データディクショナリの機能・実装例 (Data Dictionary Functions/Implementation Examples)

Oracleデータベースのメタデータ（データベースオブジェクトに関する情報）を格納する一連のシステムテーブルとビューです。

  • 機能: データベースの構造、ユーザー、権限、オブジェクトの定義などを確認できます。
  • Oracle 23aiでの拡張: オブジェクトにメタデータを追加できる「アノテーション (Annotations)」が導入され、データディクショナリビュー (USER_ANNOTATIONS, ALL_ANNOTATIONS, DBA_ANNOTATIONS) で確認できます。
  • 実装例:
    — Oracle 23aiでのアノテーションの例
    — テーブルにアノテーションを追加
    ALTER TABLE employees ADD ANNOTATION ‘This table stores employee information including personal details and salary.’; — アノテーションの確認
    SELECT object_name, annotation_text
    FROM user_annotations
    WHERE object_name = ‘EMPLOYEES’;
  • ・・

Oracle 23aiは、開発者向けのSQL機能の強化、JSONデータ処理能力の向上、そしてAI機能の統合に力を入れています。
※下記調査中
Oracle Database 23aiの主なAI機能
・AI Vector Search (AIベクトル検索)
・SQL ML (SQL Machine Learning)

下記より、各項目についてご確認・ダウンロードいただけます。

Oracleデータベース管理ダウンロード
Oracle SQL v23aiダウンロード