システムエンジニアの技術メモ

  1. ホーム
  2. システムエンジニアの技術メモ
  3. Oracle Database

Oracle Database

001. 実行計画の確認（その１）

「Enterprise Managerコンソール」→「SQLスクラッチパッド」

002. 実行計画の確認（その２）

--1 create table PLAN_TABLE（初回のみ）
SQLPLUS
@%ORACLE_HOME%\rdbms\admin\utlxplan.sql

--2 表関数 VIEW 設定（参照し易くするための設定、初回のみ）
CREATE VIEW plan AS SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('plan_table',NULL,'serial'));
(%ORACLE_HOME%\rdbms\admin\utlxpls.sql)

--3 SQL の前に EXPLAIN PLAN FOR を付ける
EXPLAIN PLAN FOR
SELECT * FROM ～;

--4 実行計画の確認
SELECT * FROM plan;

003. AUTOTRACE/実行計画とパフォーマンス統計情報

--1 ロールの作成（初回のみ、DBA 権限が必要）
SQLPLUS
@%ORACLE_HOME%\sqlplus\admin\plustrce.sql

--2 権限の追加（必要に応じて）
GRANT PLUSTRACE TO [ユーザ名] WITH ADMIN OPTION;

--3 AUTOTRACE の開始
SET AUTOTRACE TRACEONLY;

--4 AUTOTRACE の実行
SELECT * FROM ～;

--5 AUTOTRACE の終了
SET AUTOTRACE OFF;

/*--------------------------------------------------*
 * consistent gets : メモリへのアクセス数
 * physical reads  : 物理ディスクへのアクセス数
 * sorts (memory)  : メモリ内でのソート操作数
 * sorts (disk)    : 物理ディスク上でのソート操作数
 *--------------------------------------------------*/

sqldeveloper ＞ 権限設定を行うことで使えるようになる

SQL*Plusのチューニング

004. 一時表領域の再構築

--1 一時表領域(TEMP02.DBF)追加
ALTER TABLESPACE TEMP ADD TEMPFILE 'D:\ORACLE\ORADATA\ORCL\TEMP02.DBF' SIZE 10M AUTOEXTEND ON NEXT 640K;

--2 TEMP01.DBF オフライン
ALTER DATABASE TEMPFILE 'D:\ORACLE\ORADATA\ORCL\TEMP01.DBF' OFFLINE;

--3 TEMP01.DBF 削除
ALTER DATABASE TEMPFILE 'D:\ORACLE\ORADATA\ORCL\TEMP01.DBF' DROP INCLUDING DATAFILES;

--4 TEMP01.DBF 再作成
ALTER TABLESPACE TEMP ADD TEMPFILE 'D:\ORACLE\ORADATA\ORCL\TEMP01.DBF' SIZE 10M AUTOEXTEND ON NEXT 640K;

--5 TEMP02.DBF オフライン
ALTER DATABASE TEMPFILE 'D:\ORACLE\ORADATA\ORCL\TEMP02.DBF' OFFLINE;

--6 TEMP02.DBF 削除
ALTER DATABASE TEMPFILE 'D:\ORACLE\ORADATA\ORCL\TEMP02.DBF' DROP INCLUDING DATAFILES;

-- リサイズ
ALTER DATABASE TEMPFILE 'D:\ORACLE\ORADATA\ORCL\TEMP01.DBF' RESIZE 10M;

005. よく使う動的パフォーマンス(V$)・ビュー

-- セッション情報
SELECT * FROM V$SESSION;

-- 実行中のトランザクション
SELECT * FROM V$TRANSACTION;

-- ロック情報
SELECT * FROM V$LOCK;

-- 予約語
SELECT * FROM V$RESERVED_WORDS;

-- SQL文のテキスト
SELECT * FROM V$SQLTEXT;
SELECT * FROM V$SQLTEXT_WITH_NEWLINES;

-- 実行計画情報
SELECT * FROM V$SQL_PLAN;

-- ライセンス制限の情報
SELECT * FROM V$LICENSE;

-- NLSパラメータの現在の設定値
SELECT * FROM V$NLS_PARAMETERS;

-- NLSパラメータのすべての有効値
SELECT * FROM V$NLS_VALID_VALUES;
動的パフォーマンス・ビュー

006. 階層問合せ (Hierarchical Query)

▽親（情報システム本部）から子孫を検索

SELECT
    LEVEL
  , CONNECT_BY_ISLEAF        -- リーフ（子）がいない場合に 1 を戻す
  , CONNECT_BY_ROOT 組織名
  , PRIOR AS 組織名
  , LPAD(' ',LEVEL*2-2) || 組織名
  , SYS_CONNECT_BY_PATH(組織名,'/')
  , T1.*
FROM
    組織マスタ T1
START WITH
    組織名 = '情報システム本部'
CONNECT BY
    PRIOR 子コード = 親コード

▽子（情報システム本部）から親を検索

SELECT
    LEVEL
  , CONNECT_BY_ISLEAF
  , CONNECT_BY_ISCYCLE       -- これ以上検索できない場合に 1 を戻す（NOCYCLE を指定すると使える）
  , CONNECT_BY_ROOT 組織名
  , PRIOR 組織名
  , LPAD(' ',LEVEL*2-2) || 組織名
  , SYS_CONNECT_BY_PATH(組織名,'/')
  , T1.*
FROM
    組織マスタ
START WITH
    組織名 = '情報システム本部'
CONNECT BY NOCYCLE
    PRIOR 親コード = 子コード

▽親（情報システム本部）の子だけを検索

SELECT
    組織名
FROM
    組織マスタ
WHERE
    PRIOR 子コード = 親コード
START WITH
    組織名 = '情報システム本部'
CONNECT BY
    PRIOR 子コード = 親コード
階層問合せ

007. RANK/ランク

▽購入履歴テーブルから顧客コードごとに最近の購入日を取得する

SELECT * FROM (
    SELECT
        RANK() OVER(PARTITION BY 顧客コード ORDER BY 購入日 DESC) v_rank
      , T1.*
    FROM
        購入履歴テーブル T1
    WHERE
        購入日 <= SYSDATE
)
WHERE v_rank = 1

▽RANK を使わない方法

SELECT * FROM 購入履歴テーブル WHERE (顧客コード, 購入日) IN (
      SELECT 顧客コード, MAX(購入日)
        FROM 購入履歴テーブル
       WHERE 購入日 <= SYSDATE
    GROUP BY 顧客コード
)

※Teradata だと Qualify 句があるのでもう少し便利です。

008. テーブル定義

SELECT T1.table_name
     , T1.column_id
     , T1.column_name
     , T1.data_type
     , NVL(T1.data_precision, T1.char_col_decl_length) AS data_length
     , T1.data_scale
     , T1.nullable
     , T2.comments
  FROM user_tab_columns T1
       LEFT OUTER JOIN user_col_comments T2
        ON T1.table_name = T2.table_name AND T1.column_name = T2.column_name
 WHERE T1.table_name = 'テーブル名'
ORDER BY 1,2

009. インデックス定義

SELECT *
  FROM user_ind_columns 
 WHERE table_name = 'テーブル名'
ORDER BY table_name, index_name, column_position

010. 数値切り上げ/ROUNDUP

--
/**********************************************************************************************
 * Function Name : ROUNDUP
 * Description   : 数値切り上げ（小数点第？位を切り上げ）
 * Argument      : i_number                       IN     NUMBER  数値
 *               : i_scale                        IN     NUMBER  小数点の桁数
 **********************************************************************************************/
--
CREATE OR REPLACE FUNCTION roundup (i_number IN NUMBER, i_scale IN NUMBER)
RETURN NUMBER
IS
    ret_value                      NUMBER;  -- 戻り値
    v_sign                         NUMBER := +1;  -- i_number が正:+1、負:-1
BEGIN
    -- プラス／マイナスの符号
    IF i_number < 0 THEN
        v_sign := -1;
    END IF;
    -- 絶対値を取る
    ret_value := ABS(i_number);
    -- 切り上げ処理
    ret_value := TRUNC(ret_value + POWER(0.1, i_scale + 1) * 9, i_scale);
    -- 絶対値を戻す
    ret_value := ret_value * v_sign;
    RETURN ret_value;
EXCEPTION
    WHEN OTHERS THEN
        RETURN 0;
END roundup;
/
SHOW ERRORS

011. 横持ち→縦持ち (UNPIVOT)

▽横持ち→縦持ち（その１）

WITH
v_tab1 AS (
            SELECT 'amt' AS account, 40 AS apr, 50 AS may, NULL AS jun, 70 AS jul, 80 AS aug, 90 AS sep FROM dual
  UNION ALL SELECT 'qty' AS account, 41 AS apr, 51 AS may, 61   AS jun, 71 AS jul, 81 AS aug, 91 AS sep FROM dual
)
          SELECT account, 'apr', apr FROM v_tab1
UNION ALL SELECT account, 'may', may FROM v_tab1
UNION ALL SELECT account, 'jun', jun FROM v_tab1
UNION ALL SELECT account, 'jul', jul FROM v_tab1
UNION ALL SELECT account, 'aug', aug FROM v_tab1
UNION ALL SELECT account, 'sep', sep FROM v_tab1
;

▽横持ち→縦持ち（その２）

WITH
v_tab1 AS (
            SELECT 'amt' AS account, 40 AS apr, 50 AS may, NULL AS jun, 70 AS jul, 80 AS aug, 90 AS sep FROM dual
  UNION ALL SELECT 'qty' AS account, 41 AS apr, 51 AS may, 61   AS jun, 71 AS jul, 81 AS aug, 91 AS sep FROM dual
),
v_col1 AS (
            SELECT 'apr' AS period FROM dual
  UNION ALL SELECT 'may'           FROM dual
  UNION ALL SELECT 'jun'           FROM dual
  UNION ALL SELECT 'jul'           FROM dual
  UNION ALL SELECT 'aug'           FROM dual
  UNION ALL SELECT 'sep'           FROM dual
)
SELECT T1.account
     , V1.period
     ,   CASE WHEN V1.period = 'apr' THEN T1.apr ELSE 0 END
       + CASE WHEN V1.period = 'may' THEN T1.may ELSE 0 END
       + CASE WHEN V1.period = 'jun' THEN T1.jun ELSE 0 END
       + CASE WHEN V1.period = 'jul' THEN T1.jul ELSE 0 END
       + CASE WHEN V1.period = 'aug' THEN T1.aug ELSE 0 END
       + CASE WHEN V1.period = 'sep' THEN T1.sep ELSE 0 END AS num
  FROM v_tab1 T1
     , v_col1 V1
;

▽横持ち→縦持ち（その３） [11g]

WITH
v_tab1 AS (
            SELECT 'amt' AS account, 40 AS apr, 50 AS may, NULL AS jun, 70 AS jul, 80 AS aug, 90 AS sep FROM dual
  UNION ALL SELECT 'qty' AS account, 41 AS apr, 51 AS may, 61   AS jun, 71 AS jul, 81 AS aug, 91 AS sep FROM dual
)
SELECT
    *
FROM
    v_tab1
UNPIVOT INCLUDE NULLS
    (num FOR period IN (apr, may, jun, jul, aug, sep))
;

012. 縦持ち→横持ち (PIVOT)

▽縦持ち→横持ち（その１）

WITH
v_tab2 AS (
            SELECT 'amt' AS account, 'apr' AS period, 40   AS num FROM dual
  UNION ALL SELECT 'amt'           , 'may'          , 50          FROM dual
  UNION ALL SELECT 'amt'           , 'jun'          , NULL        FROM dual
  UNION ALL SELECT 'amt'           , 'jul'          , 70          FROM dual
  UNION ALL SELECT 'amt'           , 'aug'          , 80          FROM dual
  UNION ALL SELECT 'amt'           , 'sep'          , 90          FROM dual
  UNION ALL SELECT 'qty'           , 'apr'          , 41          FROM dual
  UNION ALL SELECT 'qty'           , 'may'          , 51          FROM dual
  UNION ALL SELECT 'qty'           , 'jun'          , 61          FROM dual
  UNION ALL SELECT 'qty'           , 'jul'          , 71          FROM dual
  UNION ALL SELECT 'qty'           , 'aug'          , 81          FROM dual
  UNION ALL SELECT 'qty'           , 'sep'          , 91          FROM dual
)
SELECT account
     , SUM(CASE WHEN period = 'apr' THEN num ELSE 0 END) AS apr
     , SUM(CASE WHEN period = 'may' THEN num ELSE 0 END) AS may
     , SUM(CASE WHEN period = 'jun' THEN num ELSE 0 END) AS jun
     , SUM(CASE WHEN period = 'jul' THEN num ELSE 0 END) AS jul
     , SUM(CASE WHEN period = 'aug' THEN num ELSE 0 END) AS aug
     , SUM(CASE WHEN period = 'sep' THEN num ELSE 0 END) AS sep
  FROM v_tab2
GROUP BY account
;

▽縦持ち→横持ち（その２） [11g]

WITH
v_tab2 AS (
            SELECT 'amt' AS account, 'apr' AS period, 40   AS num FROM dual
  UNION ALL SELECT 'amt'           , 'may'          , 50          FROM dual
  UNION ALL SELECT 'amt'           , 'jun'          , NULL        FROM dual
  UNION ALL SELECT 'amt'           , 'jul'          , 70          FROM dual
  UNION ALL SELECT 'amt'           , 'aug'          , 80          FROM dual
  UNION ALL SELECT 'amt'           , 'sep'          , 90          FROM dual
  UNION ALL SELECT 'qty'           , 'apr'          , 41          FROM dual
  UNION ALL SELECT 'qty'           , 'may'          , 51          FROM dual
  UNION ALL SELECT 'qty'           , 'jun'          , 61          FROM dual
  UNION ALL SELECT 'qty'           , 'jul'          , 71          FROM dual
  UNION ALL SELECT 'qty'           , 'aug'          , 81          FROM dual
  UNION ALL SELECT 'qty'           , 'sep'          , 91          FROM dual
)
SELECT
    account
  , apr
  , may
  , jun
  , jul
  , aug
  , sep
FROM
    v_tab2
PIVOT
    (SUM(num) FOR period IN ('apr' AS apr, 'may' AS may, 'jun' AS jun, 'jul' AS jul, 'aug' AS aug, 'sep' AS sep))
;

▽縦持ち→横持ちの考え方（横持ち→縦持ちはこの逆）

　１．元データ

accountperiodnum
amtapr40
amtmay50
amtjun
amtjul70
amtaug80
amtsep90
qtyapr41
qtymay51
qtyjun61
qtyjul71
qtyaug81
qtysep91

　２．データを斜めに配置する

aprmayjunjulaugsep
amt40
amt50
amt0
amt70
amt80
amt90
qty41
qty51
qty61
qty71
qty81
qty91

　３．ぎゅっと縦に圧縮 (GROUP BY) する

aprmayjunjulaugsep
amt40500708090
qty415161718191
※ここではデータが数字なので SUM を使ったが、データが文字列の場合は MAX, MIN を使う。

013. YYYYMM ⇔ FY, Mon 変換

ALTER SESSION SET NLS_DATE_LANGUAGE = 'AMERICAN';

-- YYYYMM を FY, Mon に変換
SELECT year_month
     , 'FY' || SUBSTR(year_month - 4,3,2) AS fiscal_year
     , TO_CHAR(TO_DATE(year_month,'YYYYMM'),'Mon','NLS_DATE_LANGUAGE=AMERICAN') AS period
FROM (
          SELECT '200901' AS year_month FROM dual
    UNION SELECT '200902' AS year_month FROM dual
    UNION SELECT '200903' AS year_month FROM dual
    UNION SELECT '200904' AS year_month FROM dual
    UNION SELECT '200905' AS year_month FROM dual
    UNION SELECT '200906' AS year_month FROM dual
    UNION SELECT '200907' AS year_month FROM dual
    UNION SELECT '200908' AS year_month FROM dual
    UNION SELECT '200909' AS year_month FROM dual
    UNION SELECT '200910' AS year_month FROM dual
    UNION SELECT '200911' AS year_month FROM dual
    UNION SELECT '200912' AS year_month FROM dual
)
ORDER BY 1
;

-- FY, Mon を YYYYMM に変換
SELECT fiscal_year
     , period
     , TO_CHAR(
           TO_DATE(
               SUBSTR(fiscal_year,3,2) + CASE WHEN period IN ('Jan','Feb','Mar') THEN 1 ELSE 0 END || period
             , 'YYMon'
             , 'NLS_DATE_LANGUAGE=AMERICAN'
           )
         , 'YYYYMM'
       ) AS year_month
FROM (
          SELECT 'FY08' AS fiscal_year, 'Jan' AS period FROM dual
    UNION SELECT 'FY08' AS fiscal_year, 'Feb' AS period FROM dual
    UNION SELECT 'FY08' AS fiscal_year, 'Mar' AS period FROM dual
    UNION SELECT 'FY09' AS fiscal_year, 'Apr' AS period FROM dual
    UNION SELECT 'FY09' AS fiscal_year, 'May' AS period FROM dual
    UNION SELECT 'FY09' AS fiscal_year, 'Jun' AS period FROM dual
    UNION SELECT 'FY09' AS fiscal_year, 'Jul' AS period FROM dual
    UNION SELECT 'FY09' AS fiscal_year, 'Aug' AS period FROM dual
    UNION SELECT 'FY09' AS fiscal_year, 'Sep' AS period FROM dual
    UNION SELECT 'FY09' AS fiscal_year, 'Oct' AS period FROM dual
    UNION SELECT 'FY09' AS fiscal_year, 'Nov' AS period FROM dual
    UNION SELECT 'FY09' AS fiscal_year, 'Dec' AS period FROM dual
)
ORDER BY 3
;

014. SGA設定変更

SGA（システム・グローバル・エリア）

-- 共有プールのサイズを大きくする
ALTER SYSTEM SET SHARED_POOL_SIZE = 256M;

-- データベース・キャッシュ・サイズを大きくする
ALTER SYSTEM SET DB_CACHE_SIZE = 256M;

-- パラメータを表示
SHOW PARAMETERS SHARED_POOL_SIZE;
SHOW PARAMETERS DB_CACHE_SIZE;
SHOW PARAMETERS;

015. 正規表現（数字か文字かの判断）

SELECT
    CASE WHEN REGEXP_LIKE('AB3',     '^-?[0-9]{0,11}\.?[0-9]{0,2}$') THEN '数字' ELSE '文字' END
  , CASE WHEN REGEXP_LIKE('123',     '^-?[0-9]{0,11}\.?[0-9]{0,2}$') THEN '数字' ELSE '文字' END
  , CASE WHEN REGEXP_LIKE('123.45',  '^-?[0-9]{0,11}\.?[0-9]{0,2}$') THEN '数字' ELSE '文字' END
  , CASE WHEN REGEXP_LIKE('-123.45', '^-?[0-9]{0,11}\.?[0-9]{0,2}$') THEN '数字' ELSE '文字' END
  , CASE WHEN REGEXP_LIKE('123.456', '^-?[0-9]{0,11}\.?[0-9]{0,2}$') THEN '数字' ELSE '文字' END
  , CASE WHEN REGEXP_LIKE(' 123 ',   '^-?[0-9]{0,11}\.?[0-9]{0,2}$') THEN '数字' ELSE '文字' END
  , CASE WHEN REGEXP_LIKE('',        '^-?[0-9]{0,11}\.?[0-9]{0,2}$') THEN '数字' ELSE '文字' END
  , CASE WHEN REGEXP_LIKE('-123.45', '^-') THEN '負' ELSE '正' END
FROM
    dual
;

016. UPDATE（その１）

▽組織マスタ

組織コード(PK)組織名称
01ＡＢＣ株式会社
0101情報システム本部
010101アプリケーション開発部
UPDATE 組織マスタ
   SET 組織名称 = 'システム開発部'
 WHERE 組織コード = '010101'
;

▽組織マスタ （UPDATE 結果）

組織コード(PK)組織名称
01ＡＢＣ株式会社
0101情報システム本部
010101システム開発部

017. UPDATE（その２）

▽社員マスタ

社員番号(PK)氏名組織コード組織名称
10001俵屋　宗達01ＡＢＣ株式会社
10002前　大峰0101情報システム本部
10003酒井田　柿右衛門010101アプリケーション開発部
10004今泉　今右衛門010101アプリケーション開発部
10005牧谿010101アプリケーション開発部

▽組織マスタ

組織コード(PK)組織名称
01ＡＢＣ株式会社
0101情報システム本部
010101システム開発部
UPDATE 社員マスタ T0
   SET T0.組織名称 = (
         SELECT M1.組織名称
           FROM 組織マスタ M1
          WHERE M1.組織コード = T0.組織コード
       )
;

▽社員マスタ （UPDATE 結果）

社員番号(PK)氏名組織コード組織名称
10001俵屋　宗達01ＡＢＣ株式会社
10002前　大峰0101情報システム本部
10003酒井田　柿右衛門010101システム開発部
10004今泉　今右衛門010101システム開発部
10005牧谿010101システム開発部

018. UPDATE（その３）

▽社員マスタ

社員番号(PK)氏名組織コード組織名称
10001俵屋　宗達01ＡＢＣ株式会社
10002前　大峰0101情報システム本部
10003酒井田　柿右衛門010101システム開発部
10004今泉　今右衛門010101システム開発部
10005牧谿010101システム開発部
10006関羽　雲長NEW

▽新組織マスタ

組織コード(PK)旧組織コード組織名称
0101ＡＢＣ株式会社
0101NEW人事・総務本部
01020101情報システム本部
010201010101第一システム開発部
UPDATE 社員マスタ T0
   SET (組織コード, 組織名称) = (
         SELECT M2.組織コード, M2.組織名称
           FROM 新組織マスタ M2
          WHERE M2.旧組織コード = T0.組織コード
       )
WHERE EXISTS (
    SELECT NULL
      FROM 新組織マスタ M2
     WHERE M2.旧組織コード = T0.組織コード
       AND M2.組織コード <> M2.旧組織コード
);

▽社員マスタ （UPDATE 結果）

社員番号(PK)氏名組織コード組織名称
10001俵屋　宗達01ＡＢＣ株式会社
10002前　大峰0102情報システム本部
10003酒井田　柿右衛門010201第一システム開発部
10004今泉　今右衛門010201第一システム開発部
10005牧谿010201第一システム開発部
10006関羽　雲長0101人事・総務本部

019. UPDATE（その４）

▽社員マスタ

社員番号(PK)氏名組織コード組織名称
10001俵屋　宗達01ＡＢＣ株式会社
10002前　大峰0102情報システム本部
10003酒井田　柿右衛門010201第一システム開発部
10004今泉　今右衛門010201第一システム開発部
10005牧谿010201第一システム開発部
10006関羽　雲長0101人事・総務本部 
UPDATE (
    SELECT
        社員番号
      , 組織コード
      , 組織名称
    FROM
        社員マスタ
    WHERE 組織名称 = '第一システム開発部'
)
   SET 組織コード = '01020A'
     , 組織名称   = '第二システム開発部'
;

▽社員マスタ （UPDATE 結果）

社員番号(PK)氏名組織コード組織名称
10001俵屋　宗達01ＡＢＣ株式会社
10002前　大峰0102情報システム本部
10003酒井田　柿右衛門01020A第二システム開発部
10004今泉　今右衛門01020A第二システム開発部
10005牧谿01020A第二システム開発部
10006関羽　雲長0101人事・総務本部

020. UPDATE（その５）

▽社員マスタ

社員番号(PK)氏名組織コード組織名称
10001俵屋　宗達01ＡＢＣ株式会社
10002前　大峰0102情報システム本部
10003酒井田　柿右衛門01020A第二システム開発部
10004今泉　今右衛門01020A第二システム開発部
10005牧谿01020A第二システム開発部
10006関羽　雲長0101人事・総務本部

▽新組織マスタ

組織コード(PK)旧組織コード組織名称
0101ＡＢＣ株式会社
0101NEW人事・総務本部
01020101情報システム本部
010201010101第一システム開発部

↓組織コード(PK)が結合しないところは NULL となる

UPDATE (
    SELECT T0.社員番号
         , T0.氏名
         , T0.組織コード
         , T0.組織名称
         , M2.組織コード AS UP_組織コード
         , M2.組織名称   AS UP_組織名称
      FROM 社員マスタ T0
           LEFT OUTER JOIN 新組織マスタ M2
             ON T0.組織コード = M2.組織コード
)
   SET 組織コード = UP_組織コード
     , 組織名称   = UP_組織名称
;

▽社員マスタ （UPDATE 結果）

社員番号(PK)氏名組織コード組織名称
10001俵屋　宗達01ＡＢＣ株式会社
10002前　大峰0102情報システム本部
10003酒井田　柿右衛門
10004今泉　今右衛門
10005牧谿
10006関羽　雲長0101人事・総務本部

↓キーで結合していないため、エラーが発生する。

UPDATE (
    SELECT T0.社員番号
         , T0.氏名
         , T0.組織コード
         , T0.組織名称
         , M2.組織コード AS UP_組織コード
         , M2.組織名称   AS UP_組織名称
      FROM 社員マスタ T0
           LEFT OUTER JOIN 新組織マスタ M2
             ON T0.組織コード = M2.組織コード OR
                (REGEXP_INSTR(T0.組織名称,'システム開発部$') > 0 AND REGEXP_INSTR(M2.組織名称,'システム開発部$') > 0)
)
   SET 組織コード = UP_組織コード
     , 組織名称   = UP_組織名称
;

SQLエラー: ORA-01779: キー保存されていない表にマップする列は変更できません

↓キーで結合していないが、ヒント(BYPASS_UJVC)によりエラーを回避できる。

　SELECT 結果の内容をよく確認することが大事。

　基本は、更新するテーブルのキーが SELECT 内でユニークになっていること。ユニークでない場合、UPDATE が複数回実行される。

　万一、UPDATE が複数回実行されてしまう場合でも、更新内容（UP_組織コード, UP_組織名称）が

　社員番号(PK)に対してユニークであること。

　社員番号(PK)に対して更新内容がユニークでないと、更新内容が保障されない。

　※ORACLE のドキュメント「SQLリファレンス」等にヒント(BYPASS_UJVC)は掲載されていないため、使用には注意が必要。

UPDATE (
    SELECT /*+ BYPASS_UJVC */
           T0.社員番号
         , T0.氏名
         , T0.組織コード
         , T0.組織名称
         , M2.組織コード AS UP_組織コード
         , M2.組織名称   AS UP_組織名称
      FROM 社員マスタ T0
           LEFT OUTER JOIN 新組織マスタ M2
             ON T0.組織コード = M2.組織コード OR
                (REGEXP_INSTR(T0.組織名称,'システム開発部$') > 0 AND REGEXP_INSTR(M2.組織名称,'システム開発部$') > 0)
)
   SET 組織コード = UP_組織コード
     , 組織名称   = UP_組織名称
;

▽社員マスタ （UPDATE 結果）

社員番号(PK)氏名組織コード組織名称
10001俵屋　宗達01ＡＢＣ株式会社
10002前　大峰0102情報システム本部
10003酒井田　柿右衛門010201第一システム開発部
10004今泉　今右衛門010201第一システム開発部
10005牧谿010201第一システム開発部
10006関羽　雲長0101人事・総務本部

↓MERGE ではヒント(BYPASS_UJVC)を使わなくてもエラーが発生しない。

　やっていることは UPDATE /*+ BYPASS_UJVC */ と同じ。UPDATE が複数回実行されないように SELECT 結果の内容をよく確認することが大事。

MERGE INTO 社員マスタ T0
USING (
    SELECT T1.社員番号
         , T1.氏名
         , T1.組織コード
         , T1.組織名称
         , M2.組織コード AS UP_組織コード
         , M2.組織名称   AS UP_組織名称
      FROM 社員マスタ T1
           LEFT OUTER JOIN 新組織マスタ M2
             ON T1.組織コード = M2.組織コード OR SUBSTR(T1.組織名称,3) = SUBSTR(M2.組織名称,3)
) T1
  ON (T0.社員番号 = T1.社員番号)
WHEN MATCHED THEN
    UPDATE SET
        組織コード = T1.UP_組織コード
      , 組織名称   = T1.UP_組織名称
;

▽社員マスタ （UPDATE 結果）

社員番号(PK)氏名組織コード組織名称
10001俵屋　宗達01ＡＢＣ株式会社
10002前　大峰0102情報システム本部
10003酒井田　柿右衛門010201第一システム開発部
10004今泉　今右衛門010201第一システム開発部
10005牧谿010201第一システム開発部
10006関羽　雲長0101人事・総務本部

021. 集計関数の結果で UPDATE → MERGE

▽社員マスタ

社員番号(PK)氏名組織コード組織名称同位組織人数
10001俵屋　宗達01ＡＢＣ株式会社
10002前　大峰0102情報システム本部
10003酒井田　柿右衛門010201第一システム開発部
10004今泉　今右衛門010201第一システム開発部
10005牧谿010201第一システム開発部
10006関羽　雲長0101人事・総務本部

↓集計関数の結果で UPDATE しているためエラーが発生する

UPDATE (
    SELECT
        社員番号
      , 氏名
      , 組織コード
      , 組織名称
      , 同位組織人数
      , COUNT(*) OVER(PARTITION BY 組織コード) AS UP_同位組織人数
    FROM
        社員マスタ
)
   SET 同位組織人数 = UP_同位組織人数
;

SQLエラー: ORA-01732: このビューではデータ操作が無効です

↓集計関数の結果で UPDATE するには MERGE を使う

MERGE INTO 社員マスタ T0
USING (
    SELECT
        社員番号
      , 氏名
      , 組織コード
      , 組織名称
      , 同位組織人数
      , COUNT(*) OVER(PARTITION BY 組織コード) AS UP_同位組織人数
    FROM
        社員マスタ
) T1
  ON (T0.社員番号 = T1.社員番号)
WHEN MATCHED THEN
    UPDATE SET
        同位組織人数 = T1.UP_同位組織人数
;

▽社員マスタ （UPDATE 結果）

社員番号(PK)氏名組織コード組織名称同位組織人数
10001俵屋　宗達01ＡＢＣ株式会社1
10002前　大峰0102情報システム本部1
10003酒井田　柿右衛門010201第一システム開発部3
10004今泉　今右衛門010201第一システム開発部3
10005牧谿010201第一システム開発部3
10006関羽　雲長0101人事・総務本部1

022. 表領域の使用率(%)

SELECT_CATALOG_ROLE 権限が必要

SELECT
    T1.tablespace_name
  , T1.autoextensible
  , T1.maxbytes                           / 1024 AS max_kb
  , T1.tablespace_bytes                   / 1024 AS tablespace_kb
  , T2.free_bytes                         / 1024 AS free_kb
  , (T1.tablespace_bytes - T2.free_bytes) / 1024 AS used_kb
  , ROUND((T1.tablespace_bytes - T2.free_bytes) / T1.maxbytes         * 100,2) AS "MAX_USED(%)"
  , ROUND((T1.tablespace_bytes - T2.free_bytes) / T1.tablespace_bytes * 100,2) AS "TABLESPACE_USED(%)"
FROM
    -- Information about database data files
    (
      SELECT tablespace_name
           , MAX(autoextensible) AS autoextensible
           , SUM(GREATEST(maxbytes,bytes)) AS maxbytes
           , SUM(bytes) AS tablespace_bytes
        FROM sys.dba_data_files
      GROUP BY tablespace_name
    ) T1
    -- Free extents in all tablespaces
    LEFT OUTER JOIN (
      SELECT tablespace_name, SUM(bytes) AS free_bytes FROM sys.dba_free_space GROUP BY tablespace_name
    ) T2
      ON T1.tablespace_name = T2.tablespace_name
ORDER BY
    T1.tablespace_name

023. データファイルの使用率(%)

SELECT_CATALOG_ROLE 権限が必要

SELECT
    T1.file_id
  , T1.file_name
  , T1.tablespace_name
  , T1.autoextensible
  , GREATEST(T1.maxbytes,T1.bytes)        / 1024 AS max_kb
  , T1.bytes                              / 1024 AS file_kb
  , T2.free_bytes                         / 1024 AS free_kb
  , (T1.bytes - T2.free_bytes)            / 1024 AS used_kb
  , ROUND((T1.bytes - T2.free_bytes) / GREATEST(T1.maxbytes,T1.bytes) * 100,2) "MAX_USED(%)"
  , ROUND((T1.bytes - T2.free_bytes) / T1.bytes                       * 100,2) "FILE_USED(%)"
FROM
    sys.dba_data_files T1
    LEFT OUTER JOIN (
      SELECT tablespace_name
           , file_id
           , SUM(bytes) AS free_bytes
        FROM sys.dba_free_space
      GROUP BY
             tablespace_name
           , file_id
    ) T2
      ON T1.tablespace_name = T2.tablespace_name AND T1.file_id = T2.file_id
ORDER BY
    T1.file_id

024. テーブルサイズ（バイト数）

SELECT DISTINCT
       user AS owner
     , T1.table_name
     , T3.comments
     , T2.status
     , T1.temporary
     , T1.num_rows
     , T1.avg_row_len
     , T1.avg_row_len * T1.num_rows AS estimated_size
     , (SELECT COUNT(*) FROM sys.user_tab_columns  T WHERE T1.table_name = T.table_name     ) AS tab_columns
     , (SELECT COUNT(*) FROM sys.user_indexes      T WHERE T1.table_name = T.table_name     ) AS indexeds
     , (SELECT COUNT(*) FROM sys.user_dependencies T WHERE T1.table_name = T.referenced_name) AS dependencies
     , (SELECT COUNT(*) FROM sys.user_triggers     T WHERE T1.table_name = T.table_name     ) AS triggers
     , T1.tablespace_name
     , T2.created
     , MAX(T2.last_ddl_time) OVER(PARTITION BY user, T1.table_name) last_ddl_time  -- パーティション用
     , T1.last_analyzed
  FROM sys.user_tables T1
       LEFT OUTER JOIN sys.user_objects T2
         ON T1.table_name = T2.object_name
       LEFT OUTER JOIN sys.user_tab_comments T3
         ON T1.table_name = T3.table_name
 WHERE T2.object_name NOT IN (SELECT object_name FROM recyclebin)
   AND T2.generated = 'N'
ORDER BY 1,2

025. MONTHS_BETWEEN の罠

MONTHS_BETWEEN は使い方に注意が必要。

WITH v_tab1 AS (
        SELECT TO_DATE('2012-01-28') AS start_date, TO_DATE('2012-02-29') AS end_date FROM dual
  UNION SELECT TO_DATE('2012-01-29') AS start_date, TO_DATE('2012-02-29') AS end_date FROM dual
  UNION SELECT TO_DATE('2012-01-30') AS start_date, TO_DATE('2012-02-29') AS end_date FROM dual
  UNION SELECT TO_DATE('2012-01-31') AS start_date, TO_DATE('2012-02-29') AS end_date FROM dual
  UNION SELECT TO_DATE('2013-01-28') AS start_date, TO_DATE('2013-02-28') AS end_date FROM dual
  UNION SELECT TO_DATE('2013-01-29') AS start_date, TO_DATE('2013-02-28') AS end_date FROM dual
  UNION SELECT TO_DATE('2013-01-30') AS start_date, TO_DATE('2013-02-28') AS end_date FROM dual
  UNION SELECT TO_DATE('2013-01-31') AS start_date, TO_DATE('2013-02-28') AS end_date FROM dual
)
SELECT
    start_date
  , end_date
  , TRUNC(MONTHS_BETWEEN(end_date,start_date)) AS v_months_between
FROM
    v_tab1
ORDER BY
    end_date
  , start_date

▽SELECT 結果

START_DATEEND_DATEV_MONTHS_BETWEEN
2012-01-282012-02-291
2012-01-292012-02-291
2012-01-302012-02-290※
2012-01-312012-02-291
2013-01-282013-02-281
2013-01-292013-02-280※
2013-01-302013-02-280※
2013-01-312013-02-281

上記、※印の結果が思った通りの結果であるかどうか判断してから使用すること。

もし意図しない結果であるなら、FUNCTION を作るなど対策を講じる必要がある。


▽CREATE FUNCTION ＞ 『期間の計算』MONTHS_BETWEEN（改）

CREATE OR REPLACE FUNCTION MONTHS_BETWEEN2 (
    i_from_date                    IN DATE
  , i_to_date                      IN DATE   := SYSDATE
  , i_parm                         IN NUMBER := 1
)
--
/*******************************************************************************
 * Object Name : MONTHS_BETWEEN2
 * Description : 『期間の計算』MONTHS_BETWEEN（改）
 * Arguments   : i_from_date                    IN DATE    開始日
 *             : i_to_date                      IN DATE    終了日
 *             : i_parm                         IN NUMBER  パラメータ
 * Created     : 2012/07/23
 * Modified    :
 *             :
 ******************************************************************************/
--
RETURN NUMBER
IS
    v_months_between               NUMBER;
BEGIN

    -- 【前提条件】開始日≦終了日であること
    IF i_from_date > i_to_date THEN
        RETURN NULL;
    END IF;

    /*----------------------------------------------------------------------------*/
    /* ▽MONTHS_BETWEEN ストレート
    /*----------------------------------------------------------------------------*/
    IF i_parm = 0 THEN
    /*
      ┏━━━━━━━━┳━━━━━━━━━━━━━┓
      ┃                ┃開始日                    ┃
      ┃                ┠─────────────┨
      ┃                ┃1月                 2月   ┃
      ┃                ┠─┬─┬─┬─┬─┬─┬─┨
      ┃                ┃27│28│29│30│31│ 1│ 2┃
      ┣━━━┯━━┯━╋━┿━┿━┿━┿━┿━┿━┫
      ┃終了日│ 2月│26┃ 0│  │  │  │  │  │  ┃
      ┃      │    │27┃ 1│ 0│  │  │ 0│  │  ┃
      ┃      │    │28┃  │ 1│ 0│ 0│ 1│ 0│ 0┃
      ┃      │ 3月│ 1┃  │  │ 1│ 1│  │ 1│ 0┃
      ┗━━━┷━━┷━┻━┷━┷━┷━┷━┷━┷━┛
      ┏━━━━━━━━┳━━━━━━━━━┓
      ┃                ┃開始日            ┃
      ┃                ┠─────────┨
      ┃                ┃2月         3月   ┃
      ┃                ┠─┬─┬─┬─┬─┨
      ┃                ┃26│27│28│ 1│ 2┃
      ┣━━━┯━━┯━╋━┿━┿━┿━┿━┫
      ┃終了日│ 3月│25┃ 0│  │  │  │  ┃
      ┃      │    │26┃ 1│ 0│  │  │  ┃
      ┃      │    │27┃  │ 1│ 0│  │  ┃
      ┃      │    │28┃  │  │ 1│ 0│  ┃
      ┃      │    │29┃  │  │ 1│ 0│  ┃
      ┃      │    │30┃  │  │ 1│ 0│  ┃
      ┃      │    │31┃  │  │ 1│ 0│  ┃
      ┃      │ 4月│ 1┃  │  │  │ 1│ 0┃
      ┗━━━┷━━┷━┻━┷━┷━┷━┷━┛
    */
        SELECT + TRUNC(MONTHS_BETWEEN(i_to_date,i_from_date))
          INTO v_months_between
          FROM dual
        ;
    END IF;

    /*----------------------------------------------------------------------------*/
    /* ▽翌月同日が1ヶ月となるパターン（同日がなければ前日）
    /*----------------------------------------------------------------------------*/
    IF i_parm = 1 THEN
    /*
      ┏━━━━━━━━┳━━━━━━━━━━━━━┓
      ┃                ┃開始日                    ┃
      ┃                ┠─────────────┨
      ┃                ┃1月                 2月   ┃
      ┃                ┠─┬─┬─┬─┬─┬─┬─┨
      ┃                ┃27│28│29│30│31│ 1│ 2┃
      ┣━━━┯━━┯━╋━┿━┿━┿━┿━┿━┿━┫
      ┃終了日│ 2月│26┃ 0│  │  │  │  │  │  ┃
      ┃      │    │27┃ 1│ 0│ 0│ 0│ 0│  │  ┃
      ┃      │    │28┃  │ 1│ 1│ 1│ 1│ 0│  ┃
      ┃      │ 3月│ 1┃  │  │  │  │  │ 1│ 0┃
      ┗━━━┷━━┷━┻━┷━┷━┷━┷━┷━┷━┛
      ┏━━━━━━━━┳━━━━━━━━━┓
      ┃                ┃開始日            ┃
      ┃                ┠─────────┨
      ┃                ┃2月         3月   ┃
      ┃                ┠─┬─┬─┬─┬─┨
      ┃                ┃26│27│28│ 1│ 2┃
      ┣━━━┯━━┯━╋━┿━┿━┿━┿━┫
      ┃終了日│ 3月│25┃ 0│  │  │  │  ┃
      ┃      │    │26┃ 1│ 0│  │  │  ┃
      ┃      │    │27┃  │ 1│ 0│  │  ┃
      ┃      │    │28┃  │  │ 1│ 0│  ┃
      ┃      │    │29┃  │  │ 1│ 0│  ┃
      ┃      │    │30┃  │  │ 1│ 0│  ┃
      ┃      │    │31┃  │  │ 1│ 0│  ┃
      ┃      │ 4月│ 1┃  │  │  │ 1│ 0┃
      ┗━━━┷━━┷━┻━┷━┷━┷━┷━┛
    */
        SELECT + MONTHS_BETWEEN(TRUNC(i_to_date,'MM'),TRUNC(i_from_date,'MM'))
               - CASE
                   WHEN DECODE(i_to_date,LAST_DAY(i_to_date),'31',TO_CHAR(i_to_date,'DD'))
                      < TO_CHAR(i_from_date,'DD')
                   THEN 1 ELSE 0
                 END
          INTO v_months_between
          FROM dual
        ;
    END IF;

    /*----------------------------------------------------------------------------*/
    /* ▽翌月同日が1ヶ月となるパターン（同日がなければ翌日）
    /*----------------------------------------------------------------------------*/
    IF i_parm = 2 THEN
    /*
      ┏━━━━━━━━┳━━━━━━━━━━━━━┓
      ┃                ┃開始日                    ┃
      ┃                ┠─────────────┨
      ┃                ┃1月                 2月   ┃
      ┃                ┠─┬─┬─┬─┬─┬─┬─┨
      ┃                ┃27│28│29│30│31│ 1│ 2┃
      ┣━━━┯━━┯━╋━┿━┿━┿━┿━┿━┿━┫
      ┃終了日│ 2月│26┃ 0│  │  │  │  │  │  ┃
      ┃      │    │27┃ 1│ 0│  │  │  │  │  ┃
      ┃      │    │28┃  │ 1│ 0│ 0│ 0│ 0│  ┃
      ┃      │ 3月│ 1┃  │  │ 1│ 1│ 1│ 1│ 0┃
      ┗━━━┷━━┷━┻━┷━┷━┷━┷━┷━┷━┛
      ┏━━━━━━━━┳━━━━━━━━━┓
      ┃                ┃開始日            ┃
      ┃                ┠─────────┨
      ┃                ┃2月         3月   ┃
      ┃                ┠─┬─┬─┬─┬─┨
      ┃                ┃26│27│28│ 1│ 2┃
      ┣━━━┯━━┯━╋━┿━┿━┿━┿━┫
      ┃終了日│ 3月│25┃ 0│  │  │  │  ┃
      ┃      │    │26┃ 1│ 0│  │  │  ┃
      ┃      │    │27┃  │ 1│ 0│  │  ┃
      ┃      │    │28┃  │  │ 1│ 0│  ┃
      ┃      │    │29┃  │  │ 1│ 0│  ┃
      ┃      │    │30┃  │  │ 1│ 0│  ┃
      ┃      │    │31┃  │  │ 1│ 0│  ┃
      ┃      │ 4月│ 1┃  │  │  │ 1│ 0┃
      ┗━━━┷━━┷━┻━┷━┷━┷━┷━┛
    */
        SELECT + MONTHS_BETWEEN(TRUNC(i_to_date,'MM'),TRUNC(i_from_date,'MM'))
               - CASE WHEN TO_CHAR(i_from_date,'DD') <= TO_CHAR(i_to_date,'DD') THEN 0 ELSE 1 END
          INTO v_months_between
          FROM dual
        ;
    END IF;

    /*----------------------------------------------------------------------------*/
    /* ▽翌月前日が1ヶ月となるパターン（定期券方式／民法第１４３条第２項方式）
    /*----------------------------------------------------------------------------*/
    IF i_parm = 3 THEN
    /*
      ┏━━━━━━━━┳━━━━━━━━━━━━━┓
      ┃                ┃開始日                    ┃
      ┃                ┠─────────────┨
      ┃                ┃1月                 2月   ┃
      ┃                ┠─┬─┬─┬─┬─┬─┬─┨
      ┃                ┃27│28│29│30│31│ 1│ 2┃
      ┣━━━┯━━┯━╋━┿━┿━┿━┿━┿━┿━┫
      ┃終了日│ 2月│26┃ 1│ 0│  │  │  │  │  ┃
      ┃      │    │27┃  │ 1│ 0│ 0│ 0│ 0│  ┃
      ┃      │    │28┃  │  │ 1│ 1│ 1│ 1│ 0┃
      ┃      │ 3月│ 1┃  │  │  │  │  │  │ 1┃
      ┗━━━┷━━┷━┻━┷━┷━┷━┷━┷━┷━┛
      ┏━━━━━━━━┳━━━━━━━━━┓
      ┃                ┃開始日            ┃
      ┃                ┠─────────┨
      ┃                ┃2月         3月   ┃
      ┃                ┠─┬─┬─┬─┬─┨
      ┃                ┃26│27│28│ 1│ 2┃
      ┣━━━┯━━┯━╋━┿━┿━┿━┿━┫
      ┃終了日│ 3月│25┃ 1│ 0│  │  │  ┃
      ┃      │    │26┃  │ 1│ 0│  │  ┃
      ┃      │    │27┃  │  │ 1│ 0│  ┃
      ┃      │    │28┃  │  │ 1│ 0│  ┃
      ┃      │    │29┃  │  │ 1│ 0│  ┃
      ┃      │    │30┃  │  │ 1│ 0│  ┃
      ┃      │    │31┃  │  │  │ 1│ 0┃
      ┃      │ 4月│ 1┃  │  │  │  │ 1┃
      ┗━━━┷━━┷━┻━┷━┷━┷━┷━┛
    */
        SELECT + MONTHS_BETWEEN(TRUNC(i_to_date,'MM'),TRUNC(i_from_date - 1,'MM'))
               - CASE
                   WHEN DECODE(i_to_date,LAST_DAY(i_to_date),'31',TO_CHAR(i_to_date,'DD'))
                      < DECODE(i_from_date - 1,LAST_DAY(i_from_date - 1),'31',TO_CHAR(i_from_date - 1,'DD'))
                   THEN 1 ELSE 0
                 END
          INTO v_months_between
          FROM dual
        ;
    END IF;

    /*----------------------------------------------------------------------------*/
    /* ▽ロジック確認
    /*----------------------------------------------------------------------------*/
    /*
        WITH
        v_tab1 AS (
          SELECT i_from_date, i_to_date
            FROM (
                         SELECT TO_DATE('2010-01-27','YYYY-MM-DD') AS i_from_date FROM dual
                   UNION SELECT TO_DATE('2010-01-28','YYYY-MM-DD') AS i_from_date FROM dual
                   UNION SELECT TO_DATE('2010-01-29','YYYY-MM-DD') AS i_from_date FROM dual
                   UNION SELECT TO_DATE('2010-01-30','YYYY-MM-DD') AS i_from_date FROM dual
                   UNION SELECT TO_DATE('2010-01-31','YYYY-MM-DD') AS i_from_date FROM dual
                   UNION SELECT TO_DATE('2010-02-01','YYYY-MM-DD') AS i_from_date FROM dual
                   UNION SELECT TO_DATE('2010-02-02','YYYY-MM-DD') AS i_from_date FROM dual
                 ) T1
               , (
                         SELECT TO_DATE('2010-02-26','YYYY-MM-DD') AS i_to_date FROM dual
                   UNION SELECT TO_DATE('2010-02-27','YYYY-MM-DD') AS i_to_date FROM dual
                   UNION SELECT TO_DATE('2010-02-28','YYYY-MM-DD') AS i_to_date FROM dual
                   UNION SELECT TO_DATE('2010-03-01','YYYY-MM-DD') AS i_to_date FROM dual
                 ) T2
        ),
        v_tab2 AS (
          SELECT i_from_date, i_to_date
            FROM (
                         SELECT TO_DATE('2010-02-26','YYYY-MM-DD') AS i_from_date FROM dual
                   UNION SELECT TO_DATE('2010-02-27','YYYY-MM-DD') AS i_from_date FROM dual
                   UNION SELECT TO_DATE('2010-02-28','YYYY-MM-DD') AS i_from_date FROM dual
                   UNION SELECT TO_DATE('2010-03-01','YYYY-MM-DD') AS i_from_date FROM dual
                   UNION SELECT TO_DATE('2010-03-02','YYYY-MM-DD') AS i_from_date FROM dual
                 ) T1
               , (
                         SELECT TO_DATE('2010-03-25','YYYY-MM-DD') AS i_to_date FROM dual
                   UNION SELECT TO_DATE('2010-03-26','YYYY-MM-DD') AS i_to_date FROM dual
                   UNION SELECT TO_DATE('2010-03-27','YYYY-MM-DD') AS i_to_date FROM dual
                   UNION SELECT TO_DATE('2010-03-28','YYYY-MM-DD') AS i_to_date FROM dual
                   UNION SELECT TO_DATE('2010-03-29','YYYY-MM-DD') AS i_to_date FROM dual
                   UNION SELECT TO_DATE('2010-03-30','YYYY-MM-DD') AS i_to_date FROM dual
                   UNION SELECT TO_DATE('2010-03-31','YYYY-MM-DD') AS i_to_date FROM dual
                   UNION SELECT TO_DATE('2010-04-01','YYYY-MM-DD') AS i_to_date FROM dual
                 ) T2
        )
        SELECT i_from_date, i_to_date, months_between2(i_from_date,i_to_date,1) AS v_months_between
          FROM (SELECT * FROM v_tab1 UNION ALL SELECT * FROM v_tab2)
        ORDER BY 1,2
    */

    RETURN v_months_between;

EXCEPTION
    WHEN OTHERS THEN
    RETURN NULL;
END;
/
SHOW ERRORS
民法第１４３条（暦による期間の計算）

026. ADD_MONTHS（改）

MONTHS_BETWEEN 同様、ADD_MONTHS も使い方に注意が必要。

CREATE OR REPLACE FUNCTION ADD_MONTHS2 (
    i_date                         IN DATE
  , i_months                       IN NUMBER
  , i_parm                         IN NUMBER := 1
)
--
/*******************************************************************************
 * Object Name : ADD_MONTHS2
 * Description : 『日付＋月数』ADD_MONTHS（改）
 * Arguments   : i_date                         IN DATE    日付（DD 未満切り捨て）
 *             : i_months                       IN NUMBER  月数（整数）
 *             : i_parm                         IN NUMBER  パラメータ
 * Created     : 2012/08/08
 * Modified    :
 *             :
 ******************************************************************************/
--
RETURN DATE
IS
    v_date                         DATE;  -- 日付
    v_add_months                   DATE;  -- 結果
BEGIN

    v_date := TRUNC(i_date,'DD');

    /*----------------------------------------------------------------------------*/
    /* ▽ADD_MONTHS ストレート
    /*----------------------------------------------------------------------------*/
    IF i_parm = 0 THEN
    /*
      ▽月数 = 12n                                                                          ▽月数 = 1
      ┏━━━━━━━┳━━━━━┓┏━━━━━━━┳━━━┓┏━━━━━━━┳━━━━━┓┏━━━━━━━┳━━━┓
      ┃              ┃日付      ┃┃              ┃日付  ┃┃              ┃日付      ┃┃              ┃日付  ┃
      ┃              ┠─────┨┃              ┠───┨┃              ┠─────┨┃              ┠───┨
      ┃              ┃2月       ┃┃              ┃2月   ┃┃              ┃2月       ┃┃              ┃4月   ┃
      ┃              ┠─┬─┬─┨┃              ┠─┬─┨┃              ┠─┬─┬─┨┃              ┠─┬─┨
      ┃              ┃27│28│29┃┃              ┃27│28┃┃              ┃27│28│29┃┃              ┃29│30┃
      ┣━━┯━━┯━╋━┿━┿━┫┣━━┯━━┯━╋━┿━┫┣━━┯━━┯━╋━┿━┿━┫┣━━┯━━┯━╋━┿━┫
      ┃結果│ 2月│27┃○│  │  ┃┃結果│ 2月│27┃○│  ┃┃結果│ 2月│27┃○│  │  ┃┃結果│ 5月│29┃○│  ┃
      ┃    │    │28┃  │○│  ┃┃    │    │28┃  │  ┃┃    │    │28┃  │○│○┃┃    │    │30┃  │  ┃
      ┃    │    │29┃  │  │○┃┃    │    │29┃  │●┃┃    │    │  ┃  │  │  ┃┃    │    │31┃  │●┃
      ┗━━┷━━┷━┻━┷━┷━┛┗━━┷━━┷━┻━┷━┛┗━━┷━━┷━┻━┷━┷━┛┗━━┷━━┷━┻━┷━┛
    */
        SELECT ADD_MONTHS(v_date,i_months)
          INTO v_add_months
          FROM dual
        ;
    END IF;

    /*----------------------------------------------------------------------------*/
    /* ▽起算日に応当する日、ないときはその月の末日（民法第１４３条第２項方式）
    /*----------------------------------------------------------------------------*/
    IF i_parm = 1 THEN
    /*
      ▽月数 = 12n                                                                          ▽月数 = 1
      ┏━━━━━━━┳━━━━━┓┏━━━━━━━┳━━━┓┏━━━━━━━┳━━━━━┓┏━━━━━━━┳━━━┓
      ┃              ┃日付      ┃┃              ┃日付  ┃┃              ┃日付      ┃┃              ┃日付  ┃
      ┃              ┠─────┨┃              ┠───┨┃              ┠─────┨┃              ┠───┨
      ┃              ┃2月       ┃┃              ┃2月   ┃┃              ┃2月       ┃┃              ┃4月   ┃
      ┃              ┠─┬─┬─┨┃              ┠─┬─┨┃              ┠─┬─┬─┨┃              ┠─┬─┨
      ┃              ┃27│28│29┃┃              ┃27│28┃┃              ┃27│28│29┃┃              ┃29│30┃
      ┣━━┯━━┯━╋━┿━┿━┫┣━━┯━━┯━╋━┿━┫┣━━┯━━┯━╋━┿━┿━┫┣━━┯━━┯━╋━┿━┫
      ┃結果│ 2月│27┃○│  │  ┃┃結果│ 2月│27┃○│  ┃┃結果│ 2月│27┃○│  │  ┃┃結果│ 5月│29┃○│  ┃
      ┃    │    │28┃  │○│  ┃┃    │    │28┃  │●┃┃    │    │28┃  │○│○┃┃    │    │30┃  │●┃
      ┃    │    │29┃  │  │○┃┃    │    │29┃  │  ┃┃    │    │  ┃  │  │  ┃┃    │    │31┃  │  ┃
      ┗━━┷━━┷━┻━┷━┷━┛┗━━┷━━┷━┻━┷━┛┗━━┷━━┷━┻━┷━┷━┛┗━━┷━━┷━┻━┷━┛
    */
        SELECT CASE
                 WHEN TO_CHAR(v_date,'DD') >= TO_CHAR(ADD_MONTHS(v_date,i_months),'DD') THEN ADD_MONTHS(v_date,i_months)
                 ELSE ADD_MONTHS(TRUNC(v_date,'MM'),i_months) + TO_CHAR(v_date,'DD') - 1
               END
          INTO v_add_months
          FROM dual
        ;
    END IF;

    /*----------------------------------------------------------------------------*/
    /* ▽ロジック確認
    /*----------------------------------------------------------------------------*/
    /*
        SELECT i_date, i_months, add_months2(i_date,i_months,1) AS v_add_months
          FROM (
                       SELECT TO_DATE('2008-02-27','YYYY-MM-DD') AS i_date, 48 AS i_months FROM dual
                 UNION SELECT TO_DATE('2008-02-28','YYYY-MM-DD') AS i_date, 48 AS i_months FROM dual
                 UNION SELECT TO_DATE('2008-02-29','YYYY-MM-DD') AS i_date, 48 AS i_months FROM dual
                 UNION SELECT TO_DATE('2010-02-27','YYYY-MM-DD') AS i_date, 24 AS i_months FROM dual
                 UNION SELECT TO_DATE('2010-02-28','YYYY-MM-DD') AS i_date, 24 AS i_months FROM dual
                 UNION SELECT TO_DATE('2012-02-27','YYYY-MM-DD') AS i_date, 12 AS i_months FROM dual
                 UNION SELECT TO_DATE('2012-02-28','YYYY-MM-DD') AS i_date, 12 AS i_months FROM dual
                 UNION SELECT TO_DATE('2012-02-29','YYYY-MM-DD') AS i_date, 12 AS i_months FROM dual
                 UNION SELECT TO_DATE('2012-04-29','YYYY-MM-DD') AS i_date,  1 AS i_months FROM dual
                 UNION SELECT TO_DATE('2012-04-30','YYYY-MM-DD') AS i_date,  1 AS i_months FROM dual
               )
        ORDER BY 1
    */

    RETURN v_add_months;

EXCEPTION
    WHEN OTHERS THEN
    RETURN NULL;
END;
/
SHOW ERRORS
民法第１４３条（暦による期間の計算）

027. Oracle SQL Developer で SQL Server に接続する

Oracle SQL Developer Migrations > サード・パーティ接続の構成

028. 36進数(BASE36)⇔10進数

『BASE36 → 10進数』変換

CREATE OR REPLACE FUNCTION base36_to_number (
    i_base36                       VARCHAR2
)
RETURN NUMBER
IS
    v_number                       NUMBER := 0;
    v_char                         CHAR(1);
BEGIN
    --
    FOR n IN 1..LENGTHB(i_base36)
    LOOP
        v_char := UPPER(SUBSTRB(i_base36,n,1));
        IF v_char BETWEEN '0' AND '9' THEN
            v_number := v_number * 36 + ASCII(v_char) - ASCII('0');
        ELSIF v_char BETWEEN 'A' AND 'Z' THEN
            v_number := v_number * 36 + ASCII(v_char) - ASCII('A') + 10;
        END IF;
    END LOOP;
    --
    RETURN v_number;
    --
EXCEPTION
    WHEN OTHERS THEN
    RETURN NULL;
END;
/
SHOW ERRORS

『10進数 → BASE36』変換

CREATE OR REPLACE FUNCTION number_to_base36 (
    i_number                       NUMBER
)
RETURN VARCHAR2
IS
    v_base36                       VARCHAR2(40);
    v_number                       NUMBER;
    v_mod                          NUMBER(2);
BEGIN
    --
    v_number := i_number;
    --
    LOOP
        v_mod := MOD(v_number,36);
        IF v_mod < 10 THEN
            v_base36 := CHR(ASCII('0') + v_mod) || v_base36;
        ELSE
            v_base36 := CHR(ASCII('A') + v_mod - 10) || v_base36;
        END IF;
        v_number := FLOOR(v_number / 36);
        EXIT WHEN v_number = 0;
    END LOOP;
    --
    RETURN v_base36;
    --
EXCEPTION
    WHEN OTHERS THEN
    RETURN NULL;
END;
/
SHOW ERRORS

029. DDL のログを記録する ⇒ SCHEMA トリガー

▽参考ページ

Oracle Database PL/SQL言語リファレンス 12cリリース1 (12.1) > PL/SQLトリガー

▽DDL を保管するテーブルを作成

CREATE TABLE ddl_log (
    ddl_time                       DATE
  , ora_sysevent                   VARCHAR2(20)
  , ora_dict_obj_owner             VARCHAR2(30)
  , ora_dict_obj_name              VARCHAR2(30)
  , ora_dict_obj_type              VARCHAR2(20)
  , ora_sql_txt                    CLOB
);

▽DDL を記録するトリガーを作成

CREATE OR REPLACE TRIGGER insert_ddl_log
--  AFTER CREATE OR ALTER OR RENAME OR DROP OR TRUNCATE ON SCHEMA  -- ※FLASHBACK 不可
    AFTER DDL ON SCHEMA
DECLARE
    sql_text                       DBMS_STANDARD.ora_name_list_t;
    n                              PLS_INTEGER;
    v_sql_text                     CLOB;
BEGIN

    n := ora_sql_txt(sql_text);

    FOR i IN 1..n LOOP
        v_sql_text := v_sql_text || sql_text(i);
    END LOOP;

    INSERT INTO ddl_log
    SELECT SYSDATE
         , ora_sysevent
         , ora_dict_obj_owner
         , ora_dict_obj_name
         , ora_dict_obj_type
         , v_sql_text
      FROM dual
    ;

END;
/
SHOW ERRORS

▽テスト実行

CREATE TABLE zz_test AS SELECT * FROM dual;

ALTER TABLE zz_test ADD (dummy2 VARCHAR2(1));

TRUNCATE TABLE zz_test;

RENAME zz_test TO zz_test2;

DROP TABLE zz_test2 CASCADE CONSTRAINTS;

おわり


↓ＮＧ（GRANT は DDL のため二重起動となり同一トランザクションでは不可）

CREATE OR REPLACE TRIGGER grant_to_user
    AFTER INSERT ON ddl_log FOR EACH ROW
    WHEN (NEW.ora_sysevent = 'CREATE' AND NEW.ora_dict_obj_type IN ('TABLE','VIEW'))
DECLARE
    v_sql                          VARCHAR2(32767);
BEGIN
    v_sql := 'GRANT SELECT ON ' || :NEW.ora_dict_obj_owner || '.' || :NEW.ora_dict_obj_name || ' TO ユーザ';
    EXECUTE IMMEDIATE v_sql;
END;
/
SHOW ERRORS

↓ＮＧ（これもダメ）

CREATE OR REPLACE TRIGGER grant_to_user
    AFTER CREATE ON SCHEMA
DECLARE
    sql_text                       DBMS_STANDARD.ora_name_list_t;
    v_sql                          CLOB;
BEGIN
    IF ora_dict_obj_type IN ('TABLE','VIEW') THEN
        v_sql := 'GRANT SELECT ON ' || ora_dict_obj_owner || '.' || ora_dict_obj_name || ' TO ユーザ';
        EXECUTE IMMEDIATE v_sql;
    END IF;
END;
/
SHOW ERRORS

★CREATE されたオブジェクトに対して権限を付与するには、DBMS_SCHEDULER などで定期的に実行させる。

030. データ値をカラム名に使用

▽準備

CREATE TABLE zz_column (
    col1                           NUMBER
  , col2                           NUMBER
  , col3                           DATE
  , col4                           DATE
);

INSERT INTO zz_column VALUES (1000, 2000, '2011-11-01', '2011-12-01');
COMMIT;

　　　　┌───┬───┬───┬───┐
カラム　│col1  │col2  │col3  │col4  │
　　　　├───┼───┼───┼───┤
データ行│  1000│  2000│Nov-11│Dec-11│
　　　　└───┴───┴───┴───┘

▽ほしい結果

　　　　┌───┬───┐
カラム　│Nov-11│Dec-11│
　　　　├───┼───┤
データ行│  1000│  2000│
　　　　└───┴───┘

▽SELECT

COLUMN col3 NEW_VALUE v_col3 NOPRINT
COLUMN col4 NEW_VALUE v_col4 NOPRINT

SELECT TO_CHAR(col3,'Mon-YY','NLS_DATE_LANGUAGE=AMERICAN') AS col3 FROM zz_column;
SELECT TO_CHAR(col4,'Mon-YY','NLS_DATE_LANGUAGE=AMERICAN') AS col4 FROM zz_column;

SELECT col1 AS "&v_col3"
     , col2 AS "&v_col4"
  FROM zz_column
;

031. アーカイブログのサイズ確認

▽ログモードの確認

SELECT log_mode FROM v$database;

▽REDO ログ

SELECT * FROM v$log ORDER BY group#;

▽ARCHIVE ログ

SELECT * FROM v$archived_log WHERE deleted = 'NO' ORDER BY recid;

▽ARCHIVE ログ／ファイル別

SELECT recid
     , name
     , thread#
     , sequence#
     , completion_time
     , TRUNC(blocks * block_size / 1024 / 1024,1) AS "FILE_SIZE(MB)"
  FROM v$archived_log
 WHERE deleted = 'NO'
ORDER BY 1
;

▽ARCHIVE ログ／日別

SELECT SYSDATE
     , TRUNC(completion_time,'DD') AS arc_date
     , COUNT(*) AS arc_count
     , TRUNC(SUM(blocks * block_size / 1024 / 1024),1) AS "FILE_SIZE(MB)"
  FROM v$archived_log
 WHERE deleted = 'NO'
GROUP BY TRUNC(completion_time,'DD')
ORDER BY 2
;

▽ディスク使用率／ASM (Automatic Storage Management)

SELECT name, total_mb, free_mb, TRUNC((total_mb - free_mb) / total_mb * 100,1) AS "ディスク使用率" FROM v$asm_diskgroup;

032. MD5 ファンクション

CREATE OR REPLACE FUNCTION md5 (
    i_string                       IN VARCHAR2
)
RETURN VARCHAR2
IS
    v_raw VARCHAR2(16);
    v_md5 VARCHAR2(32);
BEGIN
    v_raw := DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT_STRING => i_string);
    SELECT RAWTOHEX(v_raw) INTO v_md5 FROM dual;
    RETURN v_md5;
END;
/

▽実行

SELECT md5('テスト') FROM dual;

▽結果

3F0326F4E56C3F4B54FEEDE9071CAFBF

033. 実行中のプロシージャ名 (OBJECT_NAME) を取得

CREATE OR REPLACE PROCEDURE xx_object_name
AUTHID CURRENT_USER
IS
    v_object_name                  VARCHAR2(30);
BEGIN
    SELECT T2.object_name
      INTO v_object_name
      FROM v$session T1
           LEFT OUTER JOIN all_objects T2
             ON T1.plsql_entry_object_id = T2.object_id
     WHERE sid = USERENV('SID')
    ;
    DBMS_OUTPUT.PUT_LINE('OBJECT_NAME = ' || v_object_name);
END;
/

▽実行

SET SERVEROUTPUT ON FORMAT WRAPPED
BEGIN xx_object_name; END;
/

※別スキーマから実行してもＯＫ

034. 100レコード作成

SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 100;

035. BLOB インポート／エクスポート （VBScript）

PNG サンプルファイル (5KB)

１．テーブル作成

CREATE TABLE blob_test (
    file_name                      VARCHAR2(256)
  , file_data                      BLOB
);

ALTER TABLE blob_test ADD CONSTRAINT blob_test$pk PRIMARY KEY (
    file_name
);

２．BLOB インポート.vbs

Set fso = CreateObject("Scripting.FileSystemObject")
Set objADO = CreateObject("ADODB.Connection")
Set objStrm = CreateObject("ADODB.Stream")
Set objUtil = CreateObject("CAPICOM.Utilities")

'// ADODB.StreamTypeEnum
Const adTypeBinary = 1
Const adTypeText = 2

'// Oracle Database 接続 [Microsoft OLE DB Provider for ODBC Drivers]
objADO.Open "Provider=MSDASQL; Data Source=********; User ID=********; Password=********;"

'// フォルダ内のファイルを取得
For Each objFile In fso.GetFolder(".").Files
    '// 拡張子が png のファイルのみ処理
    If fso.GetExtensionName(objFile) = "png" Then
        '// ファイル読み込み
        objStrm.Open
        objStrm.Type = adTypeBinary
        objStrm.Position = 0
        objStrm.LoadFromFile objFile
        file_data = objUtil.BinaryToHex(objStrm.Read)
        objStrm.Close
        '// INSERT
        strSQL = "INSERT INTO blob_test VALUES ('" & objFile.Name & "', HEXTORAW('" & file_data & "'))"
        Set rs = objADO.Execute(strSQL)
    End If
Next

objADO.Close

MsgBox "おわったよん"

WScript.Quit

３．BLOB エクスポート.vbs

Set objADO = CreateObject("ADODB.Connection")
Set objStrm = CreateObject("ADODB.Stream")

'// ADODB.StreamTypeEnum
Const adTypeBinary = 1
Const adTypeText = 2

'// ADODB.SaveOptionsEnum
Const adSaveCreateNotExist = 1
Const adSaveCreateOverWrite = 2

'// Oracle Database 接続 [Microsoft OLE DB Provider for ODBC Drivers]
objADO.Open "Provider=MSDASQL; Data Source=********; User ID=********; Password=********;"

strSQL = "SELECT * FROM blob_test"

Set rs = objADO.Execute(strSQL)

Do Until rs.EOF
    '// Windows のファイル名に適さない文字が入っているときは注意
    file_name = rs.Fields("file_name").Value
    '// ファイル書き出し
    objStrm.Open
    objStrm.Type = adTypeBinary
    objStrm.Position = 0
    objStrm.Write rs.Fields("file_data").Value
    objStrm.SaveToFile file_name, adSaveCreateOverWrite
    objStrm.Close
    rs.Movenext
Loop

objADO.Close

MsgBox "おわったんだよん"

WScript.Quit

036. テーブル・ファンクション／開始日と終了日を渡すと、その期間の月数分のレコードを返す

CREATE OR REPLACE PACKAGE table_function
AUTHID DEFINER
IS
    -- レコード型
    TYPE rec_term IS RECORD (
        fy                             NUMBER  -- 年度（4月期首）
      , half                           NUMBER  -- 半期
      , quarter                        NUMBER  -- 四半期
      , months                         NUMBER  -- 月数
      , ym01                           DATE    -- 月初日付
    );
    -- テーブル型
    TYPE tbl_term IS TABLE OF rec_term;
    -- テーブル・ファンクション
    FUNCTION get_term (i_start_ymd IN DATE, i_end_ymd IN DATE) RETURN tbl_term PIPELINED;
END;
/

CREATE OR REPLACE PACKAGE BODY table_function
IS
    FUNCTION get_term (i_start_ymd IN DATE, i_end_ymd IN DATE) RETURN tbl_term PIPELINED
    IS
        pipe_row                       rec_term;
        v_start_ymd                    DATE;
        v_end_ymd                      DATE;
    BEGIN
        --
        v_start_ymd := TRUNC(i_start_ymd,'MM');
        v_end_ymd   := TRUNC(i_end_ymd  ,'MM');
        --
        IF v_start_ymd <= v_end_ymd THEN
            FOR rec IN (
               SELECT        TO_CHAR(ADD_MONTHS(v_start_ymd,LEVEL - 4),'YYYY') * 1     AS fy
                    , TRUNC((TO_CHAR(ADD_MONTHS(v_start_ymd,LEVEL - 4),'MM') + 5) / 6) AS half
                    , TRUNC((TO_CHAR(ADD_MONTHS(v_start_ymd,LEVEL - 4),'MM') + 2) / 3) AS quarter
                    ,                                       LEVEL                      AS months
                    ,                ADD_MONTHS(v_start_ymd,LEVEL - 1)                 AS ym01
                 FROM dual
              CONNECT BY
                      LEVEL <= MONTHS_BETWEEN(v_end_ymd,v_start_ymd) + 1
            )
            LOOP
                pipe_row := rec;
                PIPE ROW(pipe_row);
            END LOOP;
        END IF;
        RETURN;
    END;
    --
END;
/

SELECT * FROM TABLE(table_function.get_term('2000-01-31','2016-08-31'));

▽SELECT 結果

FYHALFQUARTERMONTHSYM01
19992412000-01-01
19992422000-02-01
19992432000-03-01
20001142000-04-01
20001152000-05-01
20001162000-06-01
20001272000-07-01
20001282000-08-01
20001292000-09-01
200023102000-10-01
200023112000-11-01
200023122000-12-01
200024132001-01-01
200024142001-02-01
200024152001-03-01
・・・    
2016111962016-04-01
2016111972016-05-01
2016111982016-06-01
2016121992016-07-01
2016122002016-08-01

037. 「EXP-00003: セグメントに対する記憶域定義がありません」対処法

「EXP-00003: セグメントに対する記憶域定義がありません」

下記の条件１～４を全て満たすとき、このメッセージが出る。


【条件１】 Oracle Database のバージョンが 11.2 以降

-- Oracle Database のバージョン
SELECT * FROM v$version;

【条件２】 パラメータの設定／DEFERRED_SEGMENT_CREATION = TRUE（デフォルト）

-- システムパラメータ
SELECT * FROM v$parameter WHERE name = 'deferred_segment_creation';

-- システムパラメータ変更（ALTER SESSION 可）
ALTER SYSTEM SET DEFERRED_SEGMENT_CREATION = FALSE;
DEFERRED_SEGMENT_CREATION

（抜粋）

DEFERRED_SEGMENT_CREATIONは、遅延セグメント作成のセマンティクスを指定します。
trueを設定すると、表のセグメントと依存オブジェクト(LOB、索引)は、表に最初の行が挿入されるまで作成されません。

一連の表を作成する場合で、多数の表に移入が行われないことが明らかなときは、
その前にこのパラメータにtrueを設定することを検討してください。
この設定によって、ディスク領域が節約され、インストール時間も短縮されます。

【条件３】 セグメントが存在しないテーブル ≒ データが INSERT されたことのないテーブル

-- セグメントが存在しないテーブル
SELECT * FROM user_tables WHERE segment_created = 'NO';

【条件４】 EXP コマンドでエクスポート



【対処法１】 DBMS_SPACE_ADMIN が使える場合

-- 遅延セグメント作成が設定された表(および表の一部)とその依存オブジェクトに対してセグメントを実体化します。
EXECUTE DBMS_SPACE_ADMIN.MATERIALIZE_DEFERRED_SEGMENTS (schema_name => 'スキーマ');

-- 空の表(または表の一部)とその依存オブジェクトからセグメントを削除します。
EXECUTE DBMS_SPACE_ADMIN.DROP_EMPTY_SEGMENTS (schema_name => 'スキーマ');

-- SYS で実行／もしくは GRANT EXECUTE で権限付与 ⇒ 他のスキーマも更新できるため NG ⇒ SYS で実行すべし
/*
GRANT EXECUTE ON DBMS_SPACE_ADMIN TO スキーマ;
REVOKE EXECUTE ON DBMS_SPACE_ADMIN FROM スキーマ;
*/
DBMS_SPACE_ADMIN パッケージ

【対処法２】 DBMS_SPACE_ADMIN が使えない場合

-- 統計情報の収集
EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS (USER);

-- セグメントが空のテーブルにデータを INSERT & DELETE
SET SERVEROUTPUT ON FORMAT WRAPPED
DECLARE
    v_sql                          VARCHAR2(32767);  -- SQL
BEGIN
    FOR tab IN (SELECT table_name FROM user_tables WHERE segment_created = 'NO')
    LOOP
        --
        v_sql := 'INSERT INTO ' || tab.table_name || ' VALUES (';
        FOR col IN (SELECT * FROM user_tab_columns WHERE table_name = tab.table_name ORDER BY column_id)
        LOOP
            IF col.column_id > 1 THEN
                v_sql := v_sql || ',';
            END IF;
            IF col.data_type LIKE '%CHAR%' THEN
                v_sql := v_sql || '''1''';
            ELSIF col.data_type LIKE '%LOB%' THEN
                v_sql := v_sql || '''1''';
            ELSIF col.data_type LIKE 'TIME%' THEN
                v_sql := v_sql || 'SYSDATE';
            ELSIF col.data_type = 'DATE' THEN
                v_sql := v_sql || 'SYSDATE';
            ELSE
                v_sql := v_sql || '1';
            END IF;
        END LOOP;
        v_sql := v_sql || ')';
        --
        BEGIN
            EXECUTE IMMEDIATE v_sql;
            DBMS_OUTPUT.PUT_LINE(tab.table_name);
        EXCEPTION
            WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('>> FAILURE : ' || tab.table_name);
            DBMS_OUTPUT.PUT_LINE(v_sql);
        END;
        --
        EXECUTE IMMEDIATE 'DELETE FROM ' || tab.table_name;
        --
        COMMIT;
        --
    END LOOP;
END;
/

038. オプティマイザのバージョン 11.2.0.3 以降で挙動不審になる

オプティマイザのバージョン 11.2.0.3 以降、パフォーマンスが大きく劣化することがある。


▼パフォーマンス問題が起きるケース

-- SELECT句で副問い合わせをしたとき
SELECT 列名１, (SELECT 列名２ FROM ... ) FROM ... ;
SELECT 列名１, ファンクション名 (列名１) FROM ... ;

　実行計画や AUTOTRACE が 11.2.0.2 以前と大きく変わり、recursive calls（再帰コール数）が増大することがある。


▽解決策１

-- SQLレベル
SELECT /*+ OPT_PARAM('_complex_view_merging' 'FALSE') */ * FROM ... ;

-- セッションレベル
ALTER SESSION SET "_complex_view_merging" = FALSE;

-- システムレベル
ALTER SYSTEM SET "_complex_view_merging" = FALSE;

▽解決策２

-- SQLレベル
SELECT /*+ OPT_PARAM('optimizer_features_enable' '11.2.0.2') */ * FROM ... ;
SELECT /*+ optimizer_features_enable('11.2.0.2') */ * FROM ... ;

-- セッションレベル
ALTER SESSION SET "optimizer_features_enable" = '11.2.0.2';

-- システムレベル
ALTER SYSTEM SET "optimizer_features_enable" = '11.2.0.2';

▽解決策３

-- ORDER BY を入れると遅くなるもの
SELECT /*+ FIRST_ROWS(1) */ * FROM ... ;

▽解決策４

-- 統計情報が取れないものを結合すると遅くなるもの（TABLE FUNCTION など CARDINALITY が正しく取れない場合）
SELECT /*+ DYNAMIC_SAMPLING(テーブル名 2) */ * FROM ... ;

▼パラメータの確認

-- オプティマイザ
SELECT * FROM v$ses_optimizer_env WHERE sid = USERENV('SID');
SELECT * FROM v$sql_optimizer_env;
SELECT * FROM v$sys_optimizer_env;
SELECT * FROM v$parameter WHERE name LIKE '%optim%';

-- 隠しパラメータ
SELECT * FROM v$parameter WHERE name LIKE '\_%' ESCAPE '\';
    _complex_view_merging   DEFAULT TRUE
    _optim_peek_user_binds  DEFAULT TRUE
    _optimizer_use_feedback DEFAULT TRUE