91 lines
3.4 KiB
SQL
91 lines
3.4 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display materialized view's details
|
|
* Parameters : 1 - OWNER
|
|
* 2 - VIEW_NAME
|
|
*
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 21-May-14 Vishal Gupta Created
|
|
*
|
|
*
|
|
*/
|
|
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
UNDEFINE OWNER
|
|
UNDEFINE VIEW_NAME
|
|
|
|
DEFINE OWNER="&&1"
|
|
DEFINE VIEW_NAME="&&2"
|
|
|
|
|
|
|
|
COLUMN _owner NEW_VALUE owner NOPRINT
|
|
COLUMN _view_name NEW_VALUE view_name NOPRINT
|
|
|
|
set term off
|
|
SELECT SUBSTR(UPPER('&&owner'), 1 , CASE INSTR('&&owner','.') WHEN 0 THEN LENGTH ('&&owner') ELSE INSTR('&&owner','.') - 1 END ) "_owner"
|
|
, DECODE(UPPER('&&view_name'),'',SUBSTR(UPPER('&&owner'),INSTR('&&owner','.')+1),UPPER('&&view_name')) "_view_name"
|
|
FROM DUAL;
|
|
set term on
|
|
|
|
|
|
Prompt
|
|
Prompt ************************************************************
|
|
Prompt ** Materialized View Details
|
|
Prompt ************************************************************
|
|
|
|
|
|
set pagesize 0
|
|
|
|
SELECT 'MView Name : ' || m.owner || '.' || m.mview_name
|
|
|| chr(10) || 'Comments : ' || c.comments
|
|
|| chr(10) || 'Compile State : ' || m.compile_state
|
|
|| chr(10) || 'Query Length : ' || m.query_len
|
|
|| chr(10) || 'Created : ' || TO_CHAR(o.created,'DD-MON-YY HH24:MI:SS')
|
|
|| chr(10) || 'Last Refresh Type : ' || m.last_refresh_type
|
|
|| chr(10) || 'Last Refresh Date : ' || TO_CHAR(m.last_refresh_date,'DD-MON-YY HH24:MI:SS')
|
|
|| chr(10) || 'Staleness : ' || m.staleness
|
|
|| chr(10) || 'Stale Since : ' || TO_CHAR(m.stale_since,'DD-MON-YY HH24:MI:SS')
|
|
|| chr(10) || 'Refresh Mode : ' || m.refresh_mode
|
|
|| chr(10) || 'Refresh Method : ' || m.refresh_method
|
|
|| chr(10) || 'Build Mode : ' || m.build_mode
|
|
|| chr(10) || 'Rewrite Enabled : ' || m.rewrite_enabled
|
|
|| chr(10) || 'Rewrite Capability : ' || m.rewrite_capability
|
|
|| chr(10) || 'Updatable : ' || m.updatable
|
|
|| chr(10) || 'Update Log : ' || m.update_log
|
|
|| chr(10) || 'Master Rollback Segment : ' || m.master_rollback_seg
|
|
|| chr(10) || 'Master Link : ' || m.master_link
|
|
|| chr(10) || 'Fast Refreshable : ' || m.fast_refreshable
|
|
|| chr(10) || 'After Fast Refresh : ' || m.after_fast_refresh
|
|
|| chr(10) || 'Use NO Index : ' || m.use_no_index
|
|
|| chr(10) || 'Unknown Prebuilt : ' || m.unknown_prebuilt
|
|
|| chr(10) || 'Unknown PL/SQL Function : ' || m.unknown_plsql_func
|
|
|| chr(10) || 'Unknown External Table : ' || m.unknown_external_table
|
|
|| chr(10) || 'Unknown Consider Fresh : ' || m.unknown_consider_fresh
|
|
|| chr(10) || 'Unknown Imported : ' || m.unknown_import
|
|
|| chr(10) || 'Unknown Trused Cons'' : ' || m.unknown_trusted_fd
|
|
--|| chr(10) || 'Number of PCT Tables : ' || m.num_pct_tables
|
|
--|| chr(10) || 'Updatables : ' || m.num_fresh_pct_regions
|
|
--|| chr(10) || 'Updatables : ' || m.num_stale_pct_regions
|
|
FROM dba_mviews m
|
|
JOIN dba_mview_comments c ON c.owner = m.owner AND c.mview_name = m.mview_name
|
|
JOIN dba_objects o ON m.owner = o.owner AND m.mview_name = o.object_name AND o.object_type in ('MATERIALIZED VIEW','SNAPSHOT')
|
|
WHERE m.owner = upper('&&OWNER')
|
|
AND m.mview_name = upper('&&VIEW_NAME')
|
|
;
|
|
|
|
|
|
set pagesize 200
|
|
|
|
@@footer
|