Works > 逆引きSQL比較 > ORACLE > データベースの情報
ORACLE
[ SQL ]
[ データベースの情報 ]
[ 手続き型言語 ]
[ その他 ]
■データベースの情報（DBA・チューニング関連）
ユーザーの一覧を表示するには？ ▲TOP
ORACLE MSSQL SSA
select username from dba_users;
テーブルの一覧を表示するには？ ▲TOP
ORACLE MSSQL SSA Postgres MySQL Jet
select table_name "テーブル名",owner "所有者" from dba_tables order by 1;
--または
select table_name "テーブル名" from user_tables order by 1;
--または
select * from tab;
テーブルの列の一覧を表示するには？ ▲TOP
ORACLE MSSQL SSA Postgres MySQL
desc テーブル名; --SQL*Plusなどのネイティブツールで使えるDESCRIBEコマンド
--または
select owner,table_name,column_name from dba_tab_columns
order by owner,table_name,column_id;
--または
select table_name,column_name from user_tab_columns
order by table_name,column_id;
テーブルのINDEX列の一覧を表示するには？ ▲TOP
ORACLE MSSQL SSA
select index_owner,table_name,column_name from dba_ind_columns
order by index_owner,table_name,column_position;
--または
select table_name,column_name from user_ind_columns
order by table_name,column_position;
-- ※ xxx_indexes xxx_constraints も参考にしてください
ストアドのソースを表示するには？ ▲TOP
ORACLE MSSQL SSA
select text from dba_source
where name = upper('&プログラム名')
and owner = upper('&所有者')
order by line;
--または
select text from user_source
where name = upper('&プログラム名')
order by line;
ビューのSELECT文を表示するには？ ▲TOP
ORACLE MSSQL SSA
select view_name,text from dba_views
where view_name = upper('&ビュー名')
and owner = upper('&所有者');
--または
select view_name,text from user_views
where view_name = upper('&ビュー名');
-- ※ SQL＊Plusで見る時はLONG列長を長く設定する必要あり → SQL> set long 100000
-- ※ 表示されるソースはSELECT文のみなので注意が必要
任意の日付以降に作成したオブジェクトの一覧を表示するには？ ▲TOP
ORACLE MSSQL
select object_name,object_type,created,owner
from dba_objects
where created >= to_date('01-04-10','yy-mm-dd') --任意の日付
order by 4,2,3,1;
--または
select object_name,object_type,created
from user_objects
where created >= to_date('01-04-10','yy-mm-dd') --任意の日付
order by 2,3,1;
セッション一覧（プロセス一覧） ▲TOP
ORACLE MSSQL
-- sessions.sql
-- Usage :
-- SQL> @sessions
--
set lines 120;
set feed on;
col sid format 990;
col USERNAME format a10;
col STATUS format a10;
col OSUSER format a12;
col MACHINE format a10;
col PROGRAM format a50;
select SID,SERIAL#,
USERNAME,
STATUS,
OSUSER,
MACHINE,
PROGRAM ||
decode(AUDSID,USERENV('SESSIONID'),' <= This session',null) as
PROGRAM
from sys.v_$session
--where USERNAME = USER
order by decode(AUDSID,USERENV('SESSIONID'),1,0),SID
;
ロックしているセッションと対象オブジェクトを確認するには？ ▲TOP
ORACLE MSSQL
どのセッションがロックしているか、その種類と対象オブジェクトを確認します。（ちょっと修正しました）
-- wholock.sql
-- Usage :
-- SQL> @wholock
--
col sid format 999;
col LOCK_TYPE format a12;
col MODE_HELD format a20;
col MODE_REQUESTED format a20;
col USERNAME format a10;
col object_name format a30;
select a.sid , z.SERIAL#, o.object_name,
z.USERNAME,
decode ( a.type, 'MR', 'Media_recovery',
'RT', 'Redo_thread',
'UN', 'User_name',
'TX', 'Transaction',
'TM', 'Dml',
'UL', 'PLSQL User_lock',
'DX', 'Distrted_Transaxion',
'CF', 'Control_file',
'IS', 'Instance_state',
'FS', 'File_set',
'IR', 'Instance_recovery',
'ST', 'Diskspace Transaction',
'IV', 'Libcache_invalidation',
'LS', 'LogStaartORswitch',
'RW', 'Row_wait',
'SQ', 'Sequence_no',
'TE', 'Extend_table',
'TT', 'Temp_table' ) "LOCK_TYPE",
decode ( a.lmode, 0, 'None',
1, 'NoLock',
2, 'Row-Share',
3, 'Row-Exclusive',
4, 'Share-Table',
5, 'Share-Row-Exclusive',
6, 'Exclusive' ) "MODE_HELD",
decode ( a.request, 0, 'None',
1, 'NoLock',
2, 'Row-Share',
3, 'Row-Exclusive',
4, 'Share-Table',
5, 'Share-Row-Exclusive',
6, 'Exclusive' ) "MODE_REQUESTED"
from v$lock a, v$session z, user_objects o
where
a.sid = z.sid
and a.id1 = o.object_id (+)
and z.username is not null --NOT SYSTEM BACKGROUND PROCESS
/
SQLの実行計画（プラン）を知るには？ ▲TOP
ORACLE MSSQL SSA Postgres MySQL Jet
TRACE機能は割愛します。
--SQL*Plusで実行時に表示させる場合
set autotrace on;
select * from kemp;
--SQL*Plusで実行させずにプランのみ表示させる場合（これが一番便利です）
set autotrace trace exp;
select * from kemp;
--Explain PlanとSELECT ～ PLAN_TABLEで見る場合
explain plan set statement_id = '任意の文字列' for select * from kemp;
※PLAN_TABLEからの検索方法については、後述の『AUTOTRACE と同じ表示形式でプランを見る（コレ、結構便利）』を参考にしてください。
※Explain Plan, autotrace, TRACE機能、いずれの場合もプランを見るためには PLAN_TABLE が必要です。
アクティブセッションが実行しているSQLは？ ▲TOP
（少し修正しました）
-- sqlmon.sql
-- Usage :
-- SQL> @sqlmon
--
set serveroutput on;
set lines 180;
set feed off;
col sid format 999;
col PROGRAM format a30;
col MACHINE format a20;
col SQL文60バイト format a60;
var vADDRESSn varchar2(8);
prompt ＳＱＬモニター
accept tsid prompt モニターするSIDを入力：
set hea on;
select c.SID,s.PROGRAM,s.MACHINE ,c.SQL_TEXT "SQL文60バイト"
from v$open_cursor c,v$session s
where c.SID = s.SID
and s.SID = to_number('&tsid');
set pages 0;
set hea off;
prompt SQL全文
prompt -------------------------------------------------------------------;
select t.SQL_TEXT
from v$sqltext t
where t.ADDRESS in
(select c.ADDRESS
from v$open_cursor c,v$session s
where c.SID = s.SID
and s.SID = to_number('&tsid')
)
order by t.ADDRESS,t.PIECE;
prompt
set hea on;
set pages 24;
オブジェクトを誰が、いつ作り、変更したか？ ▲TOP
-- whatis.sql
-- Usage :
-- SQL> @whatis
--
set ver off;
col owner format a14
col object_name format a30
prompt
prompt 入力した名前を持つオブジェクトは、誰が、いつ作り、変更したか？
prompt
accept obj prompt オブジェクト名を入力：
prompt ソート順を選択（選択なしの場合、１＝所有者）
accept sortcd prompt １＝所有者，２＝オブジェクト型，３＝変更日：
select object_name, owner, object_type, last_ddl_time, created
from dba_objects
where object_name = upper('&obj')
and status = 'VALID'
order by
decode(nvl('&sortcd',1),'1',owner,'2',object_type,'3',last_ddl_time,owner),
decode(nvl('&sortcd',1),'1',object_type,'2',owner,'3',owner,object_type),
decode(nvl('&sortcd',1),'1',last_ddl_time,'2',last_ddl_time,'3',object_type,last_ddl_time)
;
set ver on;
オブジェクトが、どのオブジェクトから呼ばれているか？ ▲TOP
utldtree.sqlでSYSTEM表領域に追加したくない場合の代替方法です。
-- which.sql
-- Usage :
-- SQL> @which
--
col object_name format a30
accept target_obj prompt 対象オブジェクトを入力してください：
select p.object_id, o.object_name,object_type from public_dependency p,
user_objects o
where o.object_id = p.object_id and referenced_object_id=
(select object_id from user_objects where object_name = upper('&target_obj')
and object_type != 'PACKAGE BODY')
and o.object_name != upper('&target_obj')
;
INDEX列の表示 ▲TOP
-- indexcols.sql
-- Usage :
-- SQL> @indexcols
--
col OWNER format a10
col COLUMN_NAME format a30
col INDEX_NAME format a30
col CONSTRAINT_TYPE format a15
accept tblnam prompt テーブル名入力：
accept ownnam prompt 所有者入力：
prompt 主キー表示
select c.table_owner as owner, c.index_name , c.column_name
from dba_ind_columns c, dba_indexes i, dba_constraints cns
where c.index_name = i.index_name
and c.table_owner = i.table_owner
and c.index_name = cns.constraint_name
and cns.constraint_type= 'P'
and c.table_owner = cns.owner
and c.table_name = upper('&tblnam')
and c.table_owner = upper('&ownnam')
order by c.table_owner , c.index_name , column_position;
prompt 全インデックス表示
select c.table_owner as owner, c.index_name , c.column_name, i.uniqueness
from dba_ind_columns c, dba_indexes i
where c.index_name = i.index_name
and c.table_owner = i.table_owner
and c.table_name = upper('&tblnam')
and c.table_owner = upper('&ownnam')
order by c.table_owner , c.index_name , column_position;
;
IIS(inetinfo.exe)が実行しているASPファイルはどれか？ ▲TOP
IIS でWEBアプリケーションを運用している時、V$SESSION を見ると、"inetinfo.exe"
というプログラム名で複数のセッションが起動しています。
この時、inetinfo.exeセッションが、どのASPファイルにアクセスしているか確認するには、
DBMS_APPLICATION_INFO パッケージを使用する必要があります。以下の方法で、モジュール名、
アクション名、クライアント情報を更新しておけば、V$SESSION の MODULE , ACTION , CLIENT_INFO
を見ることで、どのセッションが、どのASPファイルを実行しているかがわかります。
どちらかと言うと、開発・デバッグ・トラブル時の切り分け用の小技と考えた方がよいかもしれません。
ASPからストアドを実行する方法については、
データベース接続あれこれ
を参考にしてください（WSHですけど）。
dbms_application_info.SET_MODULE('ASPファイル名','処理名')
dbms_application_info.SET_CLIENT_INFO('その他の情報')
大量データをループで更新中に何件まで更新したかを確認するには？ ▲TOP
移行処理などで、何10万とか何100万オーダーの大量データを、全件なめて更新するような場合、
巨大なロールバックセグメントを作るなり、PL/SQLで小まめにコミットしながらループするなりの方法がありますが、
私はPL/SQL派です（プラットフォーム、サーバーの性能にもよりけり）。
この時、何件まで更新したかを確認するのにもいくつか方法があります。
因みに、dbms_output.put_line は、すべての処理が終わらないと出力されない上、表示サイズにも上限があるので、
この手の確認には向きません。
・UTL_TILEでログファイルを出力
・ログテーブルを作成し、そのテーブルを更新
といった辺りが最も一般的だと思います。しかし、これはプログラムの作成・修正が面倒です。
V$SQL_AREA の EXECUTIONS のカウントを数えるなんて方法もあるでしょう。
これは何も修正する必要はありませんが、更新DMLにバインド変数を使用していて、
V$SQL_AREA の SQL_TEXT が固定である必要があります。また、 EXECUTIONS
は、フラッシュしない限り、それだけを初期化する事はできません。
以下の方法は、最低限の修正で、（ほぼ）確かな監視が可能です。ここでも 前述のDBMS_APPLICATION_INFO パッケージを使用します。
ループ中に CLIENT_INFO にカウントをセットしていけば、V$SESSION の MODULE , ACTION , CLIENT_INFO を見ることで、
今何件目まで更新しているかがるかがわかります。
--ホスト変数を定義
def modulenm=モジュール名
def actionnm=アクション名
--初期化しておく
execute dbms_application_info.SET_MODULE('&modulenm','&actionnm');
execute dbms_application_info.SET_CLIENT_INFO('0');
DECLARE
・
・
・
BEGIN
LOOP
・
・
・
cnt := cnt + 1;
--CLIENT_INFOをセットする
dbms_application_info.SET_CLIENT_INFO(to_char(cnt));
END LOOP;
END;
/
AUTOTRACE と同じ表示形式でプランを見る（コレ、結構便利） ▲TOP
実行計画を見るときに、SET AUTOTRACE ON で表示されるのと同じ形式で表示する。
AUTOTRACE を使い慣れている人には、こっちの方が見やすいでしょう。
因みに、AUTOTRACE で表示を折り返さないようにするには、このスクリプトで行っているように、
col PLAN_PLUS_EXP format a80;
のレングスを変更すればよいです。
-- showplan.sql
-- Usage :
-- SQL> @showplan
--
set autotrace off;
set linesize 300;
set trims on;
set ver off;
set hea off;
set feed off;
set pagesize 0;
col ID_PLUS_EXP format B999;
col PLAN_PLUS_EXP format a90;
--col PLAN_PLUS_EXP format a108;
col OBJECT_NODE_PLUS_EXP format a12
col OTHER_TAG_PLUS_EXP format a33;
col OTHER_PLUS_EXP format a44;
prompt;
prompt 実行プラン;
prompt ------------------------------------------------------------------------------------------;
SELECT
ID ID_PLUS_EXP,PARENT_ID PARENT_ID_PLUS_EXP,
LPAD(' ',2*(LEVEL-1)) || OPERATION ||
DECODE(OTHER_TAG,NULL,'','*') ||
DECODE(OPTIONS,NULL,'',' (' || OPTIONS || ')') ||
DECODE(OBJECT_NAME,NULL,'',' OF ''' || OBJECT_NAME || '''') ||
DECODE(OBJECT_TYPE,NULL,'',' (' || OBJECT_TYPE || ')') ||
DECODE(ID,0,DECODE(OPTIMIZER,NULL,'',' Optimizer=' || OPTIMIZER )) ||
DECODE(COST,NULL,'',' (Cost=' || COST ||
DECODE(CARDINALITY,NULL,'',' Card=' || CARDINALITY) ||
DECODE(BYTES,NULL,'',' Bytes=' || BYTES) || ')') PLAN_PLUS_EXP,
OBJECT_NODE OBJECT_NODE_PLUS_EXP
FROM PLAN_TABLE
START WITH ID=0 AND STATEMENT_ID='&stid'
CONNECT BY PRIOR ID=PARENT_ID AND STATEMENT_ID='&stid'
ORDER BY ID,POSITION;
prompt;
prompt;
SELECT
ID ID_PLUS_EXP,OTHER_TAG OTHER_TAG_PLUS_EXP,
OTHER OTHER_PLUS_EXP
FROM PLAN_TABLE
WHERE STATEMENT_ID= '&stid'
AND OTHER_TAG IS NOT NULL
ORDER BY ID;
set feed on;
rollback;
set hea on;
set pagesize 24
col PLAN_PLUS_EXP format a80;
※これも最近のバージョンであれば、set autot trace で済む話ですけどね。(^^;
AUTOTRACE 時に折り返さないで表示する ▲TOP
上記にも書きましたが、よく聞かれるので別に書きます。
AUTOTRACE で表示を折り返さないようにするには、
col PLAN_PLUS_EXP format a80; （初期値）
のレングスを大きくすれば、長いオブジェクト名で、
ネストが深いプランでも折り返さないで表示できます。
SQL> set trims on;
SQL> set lines 200;
SQL> set autotrace on;
SQL> col PLAN_PLUS_EXP format a100;
カレントユーザーがどのような権限を持っているかを確認するには？ ▲TOP
自分がどのようなROLEや権限を与えられているか確認します。
-- whatcanido.sql
-- Usage :
-- SQL> @whatcanido
--
set lines 120;
col ADMIN_OPTION format a12
col DEFAULT_ROLE format a12
col OS_GRANTED format a10
set hea off;
select 'USER：' || USER from dual;
set hea on;
select 'USER_ROLE_PRIVS' "PRIVILEGES",GRANTED_ROLE,ADMIN_OPTION,DEFAULT_ROLE,OS_GRANTED from USER_ROLE_PRIVS;
prompt
select 'USER_SYS_PRIVS' "PRIVILEGES",PRIVILEGE,ADMIN_OPTION from USER_SYS_PRIVS;
prompt
set hea off;
prompt SYSTEMオブジェクトへのアクセス
select OWNER || ' の ' || TABLE_NAME || ' を ' || PRIVILEGE || ' 可能'
from USER_TAB_PRIVS
where USER not like 'SYS%'
and OWNER like 'SYS%';
set hea on;
ロールバックセグメントを使用中のセッションを確認するには？ ▲TOP
どのセッションがロールバックセグメントを使用しているか確認します。
-- whouserbs.sql
-- Usage :
-- SQL> @whouserbs
--
col name format a15
col username format a10
col osuser format a10
select
r.usn,r.name,s.program,
s.machine,s.sid,x.waits,
x.xacts "active trans"
from sys.v_$rollstat x,
sys.v_$rollname r,
sys.v_$session s,
sys.v_$transaction t
where t.addr = s.taddr (+)
and x.usn (+) = r.usn
and t.xidusn (+) = r.usn
order by r.usn;
オブジェクトの依存関係を知るには？ ▲TOP
自分は何に依存にしているか、自分に依存にしているのは何かを確認します。
-- idependyou.sql
set ver off;
set hea off;
prompt このオブジェクト『が』依存するオブジェクトは？
/* ※動的SQLを使用している場合は正確な依存関係は掴めない */
select
OWNER || '.' || NAME || ' (' || TYPE ||
')『が』依存→' ||
REFERENCED_OWNER || '.' || REFERENCED_NAME || ' (' || REFERENCED_TYPE || ')'
"結果"
from all_dependencies
where NAME = upper('&オブジェクト名')
and OWNER = upper('&所有者')
/*
TYPEでも絞るべきだろうが、パッケージの時は仕様と本体両方見たいので、
この方が使い勝手がいいのだ
*/
order by REFERENCED_OWNER,REFERENCED_TYPE,REFERENCED_NAME
;
set ver on;
set hea on;
-- youdependme.sql
set ver off;
set hea off;
prompt このオブジェクト『に』依存するオブジェクトは？
/* ※動的SQLを使用している場合は正確な依存関係は掴めない */
select
REFERENCED_OWNER || '.' || REFERENCED_NAME || ' (' || REFERENCED_TYPE ||
')『に』依存←' ||
OWNER || '.' || NAME || ' (' || TYPE || ')'
"結果"
from all_dependencies
where REFERENCED_NAME = upper('&オブジェクト名')
and REFERENCED_OWNER = upper('&所有者')
/*
TYPEでも絞るべきだろうが、パッケージの時は仕様と本体両方見たいので、
この方が使い勝手がいいのだ
*/
order by OWNER,TYPE,NAME
;
set hea on;
set ver on;
このページの先頭へ
ORACLE
[ SQL ]
[ データベースの情報 ]
[ 手続き型言語 ]
[ その他 ]
Works > 逆引きSQL比較 > ORACLE > データベースの情報