Oracle Knowledge - KROWN: 135543 (Doc ID: 1746428.1)Last update : 2024-09-19
[ 対象リリース ]
Oracle9i Database Release 2 以降のすべてのリリース
[ 対象プラットフォーム ]
すべてのプラットフォーム
[ 詳細 ]
マテリアライズド・ビューの問題を診断する為に役立つ情報を取得するスクリプト。
スクリプトはマスター表に関する情報を取得するマスターサイト向けのスクリプトと、マテリアライズド・ビューに関する情報を取得するマテリアライズド・ビューサイト向けの２つのスクリプトを記載しています。
マスター表とマテリアライズド・ビューが同一サイトに存在する場合は２つのスクリプトを同一サイトにて実行し、情報を取得してください。
< 実行方法 >
(1) 後述のスクリプトを setup.sql や mview.sql などの sql ファイルとして保存してください。
(2) 各サイトにて、SQL*Plus よりスクリプトを実行します。
- マスターサイト
% sqlplus /nolog @setup.sql
- マテリアライズド・ビューサイト
% sqlplus /nolog @mview.sql
(3) 上記実行後、以下が出力されますので、結果を出力するファイル名を指定してください。
Please enter the pathname / filename of the spool file to write the output to below:
Filename/Pathname:
< スクリプト >
-------------- setup.sql (マスターサイト用スクリプト) ココから --------------------------------------------------
connect / as sysdba
set echo off
alter session set nls_date_format='HH24:Mi:SS YY/MM/DD';
set feedback off
set heading off
set long 200000
set line 100
set pages 100
set trimspool on
set serveroutput on
PROMPT
PROMPT Please enter the pathname / filename of the spool file to write the output to below:
PROMPT
ACCEPT pathname PROMPT 'Filename/Pathname: '
spool &pathname
PROMPT
PROMPT ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
select 'Materialized Miew Master Site Environment Script Ouput
for global_name='||global_name||' on Instance='||instance_name||' hostname='||HOST_NAME||' generated: '||sysdate o from global_name, v$instance;
PROMPT ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
PROMPT
set heading on timing off
prompt
prompt
prompt ++ VERSION ++
select * from v$version;
prompt
prompt
prompt ++ PARAMETER INFORMATION ++
show parameter
prompt
prompt
prompt ++ SYS.MLOG$ INFORMATION ++
select * from sys.mlog$;
prompt
prompt
prompt ++ DBA_MVIEW_LOGS INFORMATION ++
select * from DBA_MVIEW_LOGS;
prompt
prompt
prompt ++ DBA_MVIEW_LOG_FILTER_COLS INFORMATION ++
select * from DBA_MVIEW_LOG_FILTER_COLS;
prompt
prompt
prompt ++ SYS.SLOG$ (DBA_BASE_TABLE_MVIEWS) INFORMATION ++
select * from sys.slog$;
prompt
prompt
prompt ++ DBA_REGISTERED_MVIEWS INFORMATION ++
select * from DBA_REGISTERED_MVIEWS;
prompt
prompt
prompt ++ DBA_INTERNAL_TRIGGERS INFORMATION ++
select * from DBA_INTERNAL_TRIGGERS;
prompt
prompt
prompt ++ DIAGNOSTIC ORA-12004 (Document 179469.1), ORA-12034 (Document 204127.1) ++
SELECT m.mowner, m.master, m.log,m.youngest,s.snapid,s.snaptime,oldest_pk,oldest
from sys.mlog$ m, sys.slog$ s
WHERE s.mowner (+) = m.mowner
and s.master (+) = m.master;
prompt
prompt
prompt ++ MVIEW REGISTRATION INFORMATION (Document 236233.1) ++
select l.snapshot_id, owner, name, substr(snapshot_site,1,30) snapshot_site, current_snapshots
from dba_registered_snapshots r, dba_snapshot_logs l
where r.snapshot_id = l.snapshot_id (+);
prompt
prompt
prompt ++ DBA_SEGMENTS INFORMATION ++
col SEGMENT_NAME form a30
select owner,segment_name,partition_name,bytes/1024 KB ,blocks,extents
from dba_segments where ((owner,segment_name) in (select mowner,master from sys.mlog$)) or segment_name like 'MLOG$%'
order by owner,segment_name;
prompt
prompt
prompt ++ DBA_OBJECTS INFORMATION ++
col OBJECT_NAME form a30
select * from dba_objects where ((owner , object_name) in (select mowner,master from sys.mlog$)) or object_name like 'MLOG$%'
order by owner,object_name;
prompt
prompt
prompt ++ MLOG RECORD INFORMATION ++
declare
cursor c_mlog is select mowner, master, log, flag from sys.mlog$;
cnt number;
mindate date;
bnum number;
begin
for r_mlog in c_mlog loop
execute immediate 'select count(*),min(SNAPTIME$$),count(distinct dbms_rowid.rowid_block_number(rowid))
from '||r_mlog.mowner||'.'||r_mlog.log into cnt , mindate ,bnum;
dbms_output.put_line(r_mlog.mowner||'.'||r_mlog.log||' num_records=>'||cnt||' min_snaptime=>'|| to_char(mindate,'yyyy/mm/dd hh24:mi:ss')||' num_blocks=>'||bnum);
end loop;
end;
/
prompt
prompt
prompt End script.
spool off
prompt
prompt Done spooling to &pathname
prompt
exit;
-------------- マスターサイト用スクリプト ココまで --------------------------------------------------------------
-------------- mview.sql (マテリアライズド・ビューサイト用スクリプト) ココから -----------------------------------
connect / as sysdba
set echo off
alter session set nls_date_format='HH24:Mi:SS MM/DD/YY';
set feedback off
set heading off
set long 200000
set line 100
set pages 100
set trimspool on
set serveroutput on
PROMPT
PROMPT Please enter the pathname / filename of the spool file to write the output to below:
PROMPT
ACCEPT pathname PROMPT 'Filename/Pathname: '
spool &pathname
PROMPT
PROMPT ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
select 'Materialized Miew Mview Site Environment Script Ouput
for global_name='||global_name||' on Instance='||instance_name||' hostname='||HOST_NAME||' generated: '||sysdate o from global_name, v$instance;
PROMPT ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
PROMPT
set heading on timing off
prompt
prompt
prompt ++ VERSION ++
select * from v$version;
prompt
prompt
prompt ++ PARAMETER INFORMATION ++
show parameter
prompt
prompt
prompt ++ SYS.SNAP$ INFORMATION ++
select * from sys.snap$;
prompt
prompt
prompt ++ SYS.SNAP_REFTIME$(DBA_MVIEW_REFRESH_TIMES) INFORMATION ++
select * from sys.snap_reftime$;
prompt
prompt
prompt ++ DBA_MVIEWS INFORMATION ++
select * from DBA_MVIEWS;
prompt
prompt
prompt ++ DBA_MVIEW_ANALYSIS INFORMATION ++
select * from DBA_MVIEW_ANALYSIS;
prompt
prompt
prompt ++ DBA_MVIEW_AGGREGATES INFORMATION ++
select * from DBA_MVIEW_AGGREGATES;
prompt
prompt
prompt ++ DBA_MVIEW_DETAIL_RELATIONS INFORMATION ++
select * from DBA_MVIEW_DETAIL_RELATIONS;
prompt
prompt
prompt ++ DBA_MVIEW_KEYS INFORMATION ++
select * from DBA_MVIEW_KEYS;
prompt
prompt
prompt ++ DBA_MVIEW_JOINS INFORMATION ++
select * from DBA_MVIEW_JOINS;
prompt
prompt
prompt ++ DBA_REGISTERED_MVIEW_GROUPS INFORMATION ++
select * from DBA_REGISTERED_MVIEW_GROUPS;
prompt
prompt
prompt ++ DBA_MVIEW_DETAIL_PARTITION INFORMATION (11g onward)++
select * from DBA_MVIEW_DETAIL_PARTITION;
prompt
prompt
prompt ++ DBA_MVIEW_DETAIL_SUBPARTITION INFORMATION (11g onward)++
select * from DBA_MVIEW_DETAIL_SUBPARTITION;
prompt
prompt
prompt ++ DBA_REFRESH INFORMATION ++
select * from DBA_REFRESH;
prompt
prompt
prompt ++ DBA_REFRESH_CHILDREN INFORMATION ++
select * from DBA_REFRESH_CHILDREN;
prompt
prompt
prompt ++ DBA_RGROUP INFORMATION ++
select * from DBA_RGROUP;
prompt
prompt
prompt ++ DBA_RCHILD INFORMATION ++
select * from DBA_RCHILD;
prompt
prompt
prompt ++ DBA_DB_LINKS INFORMATION ++
select * from DBA_DB_LINKS;
prompt
prompt
prompt ++ DBA_JOBS INFORMATION ++
select * from DBA_JOBS;
prompt
prompt
prompt ++ DBA_JOBS_RUNNING INFORMATION ++
select * from DBA_JOBS_RUNNING;
prompt
prompt
prompt ++ GV$MVRESRESH INFORMATION ++
SELECT * FROM GV$MVREFRESH;
prompt
prompt
prompt ++ DBA_SEGMENTS INFORMATION ++
col SEGMENT_NAME form a30
select owner,segment_name,partition_name,bytes/1024 KB ,blocks,extents
from dba_segments where (owner,segment_name) in (select sowner,vname from sys.snap$)
order by owner,segment_name;
prompt
prompt
prompt ++ DBA_OBJECTS INFORMATION ++
col OBJECT_NAME form a30
select * from dba_objects where (owner , object_name) in (select sowner,vname from sys.snap$)
order by owner,object_name,object_type;
prompt
prompt
prompt ++ DBA_INDEXES INFORMATION ++
col INDEX_NAME form a30
select * from dba_indexes where (table_owner,table_name) in
(select sowner,vname from sys.snap$)
order by owner,index_name;
prompt
prompt
prompt End script.
spool off
prompt
prompt Done spooling to &pathname
prompt
exit;
-------------- マテリアライズド・ビューサイト用スクリプト ココまで -----------------------------------
[ 補足事項 ]
DBA_MVIEW_DETAIL_PARTITION 及び DBA_MVIEW_DETAIL_SUBPARTITION のデータ
ディクショナリビューは 11.1.0 以後のリリースのみ検索可能です。10.2.0 以
前のリリースでは ORA-942 のエラーが出力されますが問題はございません。