はじめに
こちらは主にトランザクションや、実行中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 --ユーザー名
;
過去に実行したＳＱＬを調査(時間指定)
過去に実行したＳＱＬを調査(時間指定)
--------------------------------------------------------------------
--過去に実行したＳＱＬを調査 (SQL文から-V$SQLのみ) 時間指定して取得
--------------------------------------------------------------------
--*Setparameter I_SERCH_LIKE '%DEKIDAKAKN%' String
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.LAST_ACTIVE_TIME > to_date('2020/12/28 11:00:00','YYYY/MM/DD HH24:MI:SS')
and A.LAST_ACTIVE_TIME > (SYSDATE - 5/1440) --5分前までの発行SQL
order by A.LAST_ACTIVE_TIME DESC
;
過去に実行したＳＱＬを調査(実行者から)
過去に実行したＳＱＬを調査(実行者から)
----------------------------------------------------------------------
--過去に実行したＳＱＬを調査(実行者から)
----------------------------------------------------------------------
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で実行者も表示)
過去に実行したＳＱＬを調査
--------------------------------------------------------------------
--過去に実行したＳＱＬを調査(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文を作成
テーブル名から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('SYSTEM','BDA','XXX','XXX')
--対象テーブル
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込み)
---------------------------------------------
--名称のみで検索 全体を検索(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文を付けると強制的になる
--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;