自分用メモ(SQL編)

SQLメモ

トランザクションによって取得されているロックを取得.sql

/*
 トランザクションによって取得されているロックを取得
    xidusn: ロールバックセグメント番号
    object_id: ロック中のオブジェクトID
    owner: オブジェクトのオーナー
    object_name: オブジェクト名
    oracle_user_name: Oracleユーザー名
    os_user_name: OSユーザー名
    process: OSプロセスID
    locked_mode: ロックモード
*/
select l.xidusn, l.object_id, o.owner, o.object_name, 
    l.session_id, l.oracle_username, l.os_user_name, l.process, 
    decode(l.locked_mode, 0, 'なし',
        1, 'NULL',
        2, '行共有(SS)',
        3, '行排他(SX)',
        4, '共有(S)',
        5, '共有／行排他(SSX)',
        6, '排他(X)',
        '???') locked_mode
from v$locked_object l, dba_objects o
where l.object_id = o.object_id
--and l.OS_USER_NAME = 'INOUEKOUJI'
--and o.OBJECT_NAME='STM_TR_DYNAMIC_STS_CTRL'

セッションの一覧を取得する.sql

/*
 セッションの一覧を取得する
*/
select program,username, osuser, machine, terminal,  
     sid, serial#, status, sql_address
from v$session
where type = 'USER'
  and osuser = :I_osuser --ユーザー名
;

過去に実行したＳＱＬを調査(実行者から)

----------------------------------------------------------------------
--過去に実行したＳＱＬを調査(実行者から)
----------------------------------------------------------------------
SELECT 
       A.LAST_ACTIVE_TIME
      ,B.OSUSER
      ,A.SQL_FULLTEXT
      ,A.SQL_TEXT
      ,A.SQL_ID
      ,A.HASH_VALUE
      ,A.ADDRESS
FROM V$SQL A
    ,V$SESSION B
WHERE 1 = 1 
  AND A.HASH_VALUE = B.SQL_HASH_VALUE
  AND A.ADDRESS = B.SQL_ADDRESS
  AND UPPER(B.OSUSER) = UPPER(:I_OSUSER) --ユーザー名
order by A.LAST_ACTIVE_TIME
;

過去に実行したＳＱＬを調査

--------------------------------------------------------------------
--過去に実行したＳＱＬを調査 (SQL文から-V$SQLのみ)
--------------------------------------------------------------------
SELECT 
       A.LAST_ACTIVE_TIME
      ,A.SQL_FULLTEXT
      ,A.SQL_TEXT
      ,A.SQL_ID
      ,A.HASH_VALUE
      ,A.ADDRESS
FROM V$SQL A
WHERE 1 = 1 
  AND A.SQL_TEXT LIKE :I_SERCH_LIKE         --検索するSQLを指定
order by A.LAST_ACTIVE_TIME

過去に実行したＳＱＬを調査

--------------------------------------------------------------------
--過去に実行したＳＱＬを調査(SQL文から-V$SQL+V$SESSIONで実行者も表示)
--------------------------------------------------------------------
SELECT 
       A.LAST_ACTIVE_TIME
      ,B.OSUSER
      ,A.SQL_FULLTEXT
      ,A.SQL_TEXT
      ,A.SQL_ID
      ,A.HASH_VALUE
      ,A.ADDRESS
FROM V$SQL A
    ,V$SESSION B
WHERE 1 = 1 
  AND A.HASH_VALUE = B.SQL_HASH_VALUE(+)
  AND A.ADDRESS = B.SQL_ADDRESS(+)
  AND A.SQL_TEXT LIKE :I_SERCH_LIKE         --検索するSQLを指定
order by A.LAST_ACTIVE_TIME
;

テーブル名からselect文を作成

--テーブル名称設定[OsqlEdit用]
!bind set TABLE_NAME_LIKE = 'XXXXXXXXXXXXXXXXX';

--------------------------------------------
--[テーブル名からselect文を作成]
--------------------------------------------
SELECT 
      ----[先頭部分]---------------------------
      CASE  
        WHEN TBL.COLUMN_ID = 0 THEN 'SELECT ' 
        WHEN TBL.COLUMN_ID = 1 THEN '   A.' 
        WHEN TBL.COLUMN_NAME is null THEN '' 
        ELSE        '  ,A.' 
      END  ||              --AS COLAM

      ---------[カラム名]----------------------
       TBL.COLUMN_NAME  ||  

      ----------[ AS 付与 ]--------------------
      --コメントがあれば 'as' を付与
       CASE 
        WHEN TBL.COMMENTS is null THEN '' 
        WHEN TBL.COLUMN_NAME is null THEN '' 
        ELSE           ' as ' 
       END                || --AS asName

      ---------[コメント名表示]----------------
      --但しコメントで"[]"記号がある場合は除去する
      CASE  
        --テーブル名部分
        WHEN TBL.COLUMN_ID = 0 THEN 
           '     ' 
        --カラム名称以外([select][from] 等…)
        WHEN TBL.COLUMN_NAME is null THEN 

          REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TBL.COMMENTS,'[',''),']',''),'（','_'),'）','') ,'　',' --')

        --カラム名称のみ【[]括りあり】
        WHEN substr(TBL.COMMENTS,INSTR(TBL.COMMENTS,' ')+1) is null THEN 

            REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(substr(TBL.COMMENTS,0,INSTR(TBL.COMMENTS,' ')),'[',''),']',''),'（','_'),'）',''),'　',' --') 

        --カラム名称のみ【[]括りなし】
        WHEN REPLACE(REPLACE(REPLACE(REPLACE(substr(TBL.COMMENTS,0,INSTR(TBL.COMMENTS,' ')),'[',''),']',''),'（','_'),'）','') is null THEN 

            REPLACE(substr(TBL.COMMENTS,INSTR(TBL.COMMENTS,' ')+1),'　',' --')

        --カラム名称＋説明がある
        ELSE
            REPLACE(REPLACE(REPLACE(REPLACE(substr(TBL.COMMENTS,0,INSTR(TBL.COMMENTS,' ')),'[',''),']',''),'（','_'),'）','') || '--' ||

            substr(TBL.COMMENTS,INSTR(TBL.COMMENTS,' ')+1) 

        END    --AS 名称

from(
   ---------------------------------------------------------------------------
   --データ取得
   ---------------------------------------------------------------------------

    --カラムののコメント
    SELECT A.OWNER
          ,A.COLUMN_ID
          ,A.TABLE_NAME
          ,A.COLUMN_NAME
          ,A.DATA_TYPE
          ,A.DATA_LENGTH
          ,A.NULLABLE
          ,TRANSLATE (SUBSTR(B.COMMENTS,0,40), '#' || CHR(10), '#' ) AS COMMENTS --改行は削除
    FROM SYS.DBA_TAB_COLUMNS A
        ,SYS.DBA_COL_COMMENTS B
    WHERE 1=1
      AND A.OWNER = B.OWNER(+)
      AND A.TABLE_NAME = B.TABLE_NAME(+)
      AND A.COLUMN_NAME = B.COLUMN_NAME(+)

    --テーブルのコメント
    UNION ALL
    SELECT C.OWNER      as OWNER
          ,0            as COLUMN_ID
          ,C.TABLE_NAME as TABLE_NAME
          ,null         as COLUMN_NAME
          ,C.TABLE_TYPE as DATA_TYPE
          ,null         as DATA_LENGTH
          ,null         as NULLABLE
          ,TRANSLATE (SUBSTR(C.COMMENTS,0,40), '#' || CHR(10), '#' ) AS COMMENTS --改行は削除
    FROM   DBA_TAB_COMMENTS C


   ---------------------------------------------------------------------------
   --ＳＱＬ文用
   ---------------------------------------------------------------------------

    --テーブルのコメント(ヘッダ文)
    UNION ALL
    SELECT A.OWNER ,-1 ,A.TABLE_NAME,null,null,null,null
          ,'--【' || A.TABLE_NAME || '(' || A.COMMENTS || ')】'
    FROM SYS.DBA_TAB_COMMENTS A
    WHERE 1=1
    GROUP BY A.OWNER,A.TABLE_NAME,A.COMMENTS

    --テーブルのコメント(From句)
    UNION ALL
    SELECT A.OWNER ,MAX(A.COLUMN_ID) + 1 ,A.TABLE_NAME,null,null,null,null
          ,'FROM ' || A.OWNER || '.' || A.TABLE_NAME || ' A'
    FROM SYS.DBA_TAB_COLUMNS A
    WHERE 1=1
    GROUP BY A.OWNER,A.TABLE_NAME

    --テーブルのコメント(Where句)
    UNION ALL
    SELECT A.OWNER ,MAX(A.COLUMN_ID) + 2 ,A.TABLE_NAME,null,null,null,null
          ,'WHERE 1= 1'  
    FROM SYS.DBA_TAB_COLUMNS A
    WHERE 1=1
    GROUP BY A.OWNER,A.TABLE_NAME

    --テーブルのコメント(Where句　条件コメント状態で)
    UNION ALL
    SELECT A.OWNER ,MAX(A.COLUMN_ID) + 3 ,A.TABLE_NAME,null,null,null,null
          ,'--AND A. ='''' ' 
    FROM SYS.DBA_TAB_COLUMNS A
    WHERE 1=1
    GROUP BY A.OWNER,A.TABLE_NAME

    --テーブルのコメント(Where句　１００件まで取得)
    UNION ALL
    SELECT A.OWNER ,MAX(A.COLUMN_ID) + 4 ,A.TABLE_NAME,null,null,null,null
          ,'AND ROWNUM < 100'  
    FROM SYS.DBA_TAB_COLUMNS A
    WHERE 1=1
    GROUP BY A.OWNER,A.TABLE_NAME

    --テーブルのコメント(終了カラム)
    UNION ALL
    SELECT A.OWNER ,MAX(A.COLUMN_ID) + 5 ,A.TABLE_NAME,null,null,null,null
          ,';'  
    FROM SYS.DBA_TAB_COLUMNS A
    WHERE 1=1
    GROUP BY A.OWNER,A.TABLE_NAME

)TBL
WHERE  1=1
 --対象スキーマ
 and TBL.OWNER IN('XXX','XXX','XXX','XXX','XXXX')
 --対象テーブル
 and TBL.TABLE_NAME LIKE :TABLE_NAME_LIKE

order by TBL.OWNER
        ,TBL.TABLE_NAME
        ,TBL.COLUMN_ID
;

テーブル定義取得

--------------------------------------------
--[テーブルのカラムを取得]
--------------------------------------------

--テーブル名称設定
!bind set TABLE_NAME_LIKE = 'XXXXXXXXXXXX';

SELECT 
       TBL.COLUMN_ID    AS ID
      ,TBL.OWNER        AS OWNER
      ,TBL.TABLE_NAME   AS テーブル名
      ,TBL.COLUMN_NAME  AS 項目名
      ,TBL.DATA_TYPE    AS 型
      ,TBL.DATA_LENGTH  AS 長さ
      ,CASE TBL.NULLABLE 
        WHEN 'N' THEN '〇' 
        WHEN 'Y' THEN '×' 
       END              AS 必須
      ,TRANSLATE (SUBSTR(TBL.COMMENTS,0,40), '#' || CHR(10), '#' ) AS コメント
from(
    --テーブルの名称とコメント
    SELECT C.OWNER      as OWNER
          ,0            as COLUMN_ID
          ,C.TABLE_NAME as TABLE_NAME
          ,'<<<' || C.TABLE_NAME || '>>>' as COLUMN_NAME
          ,C.TABLE_TYPE as DATA_TYPE
          ,null         as DATA_LENGTH
          ,null         as NULLABLE
          ,C.COMMENTS   as COMMENTS
    FROM   DBA_TAB_COMMENTS C
    WHERE 1=1

    --カラムの名称とコメント
    UNION ALL
    SELECT A.OWNER
          ,A.COLUMN_ID
          ,A.TABLE_NAME
          ,A.COLUMN_NAME
          ,A.DATA_TYPE
          ,A.DATA_LENGTH
          ,A.NULLABLE
          ,B.COMMENTS
    FROM SYS.DBA_TAB_COLUMNS A
        ,SYS.DBA_COL_COMMENTS B
    WHERE 1=1
      AND A.OWNER = B.OWNER(+)
      AND A.TABLE_NAME = B.TABLE_NAME(+)
      AND A.COLUMN_NAME = B.COLUMN_NAME(+)
)TBL
WHERE  1=1
 --対象スキーマ
 and TBL.OWNER IN('TBL','TAB','SEG','NOW_SEG')
 --対象テーブル
 and TBL.TABLE_NAME LIKE :TABLE_NAME_LIKE
order by TBL.OWNER
        ,TBL.TABLE_NAME
        ,TBL.COLUMN_ID
;

名称のみで全体を検索

---------------------------------------------
--名称のみで検索 全体を検索
--------------------------------------------
SELECT A.OBJECT_TYPE
      ,A.OWNER
      ,A.OBJECT_NAME
FROM   DBA_OBJECTS A
WHERE  A.OBJECT_NAME Like :TABLE_NAME_LIKE --検索名称を設定
 and   NOT A.OBJECT_TYPE IN( 'SYNONYM','INDEX' ) --[SYNONYM][INDEX]を対象外()
order by A.OBJECT_TYPE DESC,A.OWNER
;

名称のみで全体を検索(SYNONYM込み)

---------------------------------------------
--名称のみで検索 全体を検索(SYNONYM込み)
--------------------------------------------
SELECT A.OBJECT_TYPE
      ,A.OWNER
      ,A.OBJECT_NAME
FROM   DBA_OBJECTS A
WHERE  A.OBJECT_NAME Like :TABLE_NAME_LIKE --検索名称を設定
 and   NOT A.OBJECT_TYPE IN( 'INDEX' ) --[INDEX]を対象外()
order by A.OBJECT_TYPE DESC,A.OWNER
;

--ORDER BYでCASE文を付けると強制的になる

 SELECT A.処理名
       ,A.ステイタス
       ,A.起動日
       ,A.終了日 
 FROM  処理テーブル A 
 WHERE 1=1
   and A.処理名  in ('JOB_001','JOB_002','JOB_003','JOB_004','JOB_005','JOB_006')
ORDER BY
    CASE A.処理名 WHEN 'JOB_006' THEN 1 ELSE 2 END
   ,CASE A.処理名 WHEN 'JOB_005' THEN 1 ELSE 2 END
   ,CASE A.処理名 WHEN 'JOB_004' THEN 1 ELSE 2 END
   ,CASE A.処理名 WHEN 'JOB_003' THEN 1 ELSE 2 END
   ,CASE A.処理名 WHEN 'JOB_002' THEN 1 ELSE 2 END
   ,CASE A.処理名 WHEN 'JOB_001' THEN 1 ELSE 2 END
   ,A.処理名
;

---------------------------------------------
--過去の時間帯のデータを取得（日付指定）
--------------------------------------------
select * 
FROM 処理テーブル as of timestamp to_timestamp('2020-05-13 16:10:00', 'yyyy-mm-dd hh24:mi:ss') A
where 1 = 1
 and A.ステイタス = 'OK'
;

---------------------------------------------
--過去の時間帯のデータを取得（分指定）
--------------------------------------------
select *
FROM 
  処理テーブル  as of timestamp (systimestamp - interval '3' minute ) A
WHERE 
  A.起動日 = '2020/05/13'
;



---------------------------------------------
--過去の時間帯のデータを取得（時間指定）
--------------------------------------------
select *
FROM 
  処理テーブル  aas of timestamp (systimestamp - interval '1' hour ) A
WHERE 
  A.起動日 = '2020/05/13'
;

値から名称をコーディングで付ける

---------------------------------------------
--CASE文を使用して名称を付ける
---------------------------------------------

SELECT 
   to_char(sysdate - 0, 'yyyy/mm/dd') as 本日
  ,to_char(sysdate - 0, 'D')     as 曜日値
  ,CASE to_char(sysdate - 0, 'D') 
        WHEN '1'           THEN '日曜日'
        WHEN '2'           THEN '月曜日'
        WHEN '3'           THEN '火曜日'
        WHEN '4'           THEN '水曜日'
        WHEN '5'           THEN '木曜日'
        WHEN '6'           THEN '金曜日'
        WHEN '7'           THEN '土曜日'
        ELSE '不明' 
  END as 曜日
FROM dual

曜日を求める

------------------
--曜日を求める
------------------
select 
   to_char(sysdate - 0, 'yyyy/mm/dd') as 本日
  ,to_char(sysdate - 0, 'DAY')        as 曜日_日本語 
  ,to_char(sysdate - 0, 'D')          as 曜日数値
from dual;