Thursday, June 18, 2020

DBA Checklist (DBA Activities)

Contents of this Post

  1. Daily DBA Checklist
  2. Daily Night DBA Checklist
  3. Weekly DBA Checklist
  4. Weekly Tuning DBA Checklist
  5. Monthly DBA Checklist
  6. Quarterly DBA Checklist
  7. One Time Activity DBA Checklist

Daily DBA Checklist

  • Health check of the Database Instance and Listener.
  • Monitor Alert log file and/or check Alert log in regular intervals to solve the ORA errors.
  • Check any session blocking the other session and oracle locks. Clear locks
  • Check long-running UNIX process
  • Ensure that there are no DBMS_JOBS with the status of failed or broken. Also, last refresh times of all running jobs should be current.
  • Check all CRONTAB housekeeping script logs
  • Daily Tablespace utilization and growth.
  • The rebuilding of Indexes, if a bulk load of data is inserted.
  • Check the temporary tablespace/files.
  • Check locked and expired users in the database and unlock/reset/inform to business users. Monitor user account GRACE period.
  • Check the UNDO tablespace and retention.
  • Monitor the Unix /tmp and /var location
  • Monitor the UTL_FILE location.
  • Monitor all Database file system or drive.
  • Monitor the Archive Log location.
  • Verify success of database archiving to tape
  • Monitoring Backups.
  • Monitoring the log files, backups, database space usage, and the use of system resources.
  • Monitoring Production Database Performance
  • Find high CPU/Memory/Physical IO consuming processes and trace the SQL/From/Report running behind the database and update to application team/users.
  • Check OEM Agent is running Or not in each node.
  • Verify DBSNMP is running
  • Verify success of database backup
  • Daily RMAN(Incremental & Cumulative)/Data Pump export backups after business hours.
  • User Management. User Profile monitoring.
  • Check Invalid objects and recompile.
  • Check and monitor the audit log or table for new audit entries.
  • Monitor daily failed login attempts in the database and update to respective end uses.
  • Backup your CRONTAB or Windows job scheduler
  • Most Important - read DBA manuals for one hour daily.
  • Most Important - Check your oracle license and do not run/execute/create anything beyond the oracle license policy.

Daily Night DBA Checklist

  • Look for objects that break rules (Check for Huge NEXT_EXTENT or MAX_EXTENT)
  • Check the objects reaching to it’s Max extents
  • Note, All tables should have unique primary keys, so check missing/disabled PK and
  • Check for Block corruption


Weekly DBA Checklist

  • Database growth comparison.
  • Identify bad growth projections.
  • Monitor weekly report of RMAN full database backup and incremental backups.
  • Manage weekly cold backup during maintenance windows.
  • Analyze database and schemas to gather statistics
  • Check index monitoring usage to validate the index usage which is not used yet. Drop/Mark unusable the unused indexes with proper change management.
  • Make sure all indexes should use INDEXES tablespace and should not use DATA/Other tablespaces. If so, then move all indexes to its Index Tablespace.
  • All index data files should not be in the same file system where the DATA tablespace or SYSTEM/SYSAUX/UNDO/TEMP datafiles are. Move and manage this to fix the IO performance.
  • Look in SQL*Net logs for errors, issues (Both in Client-side & Server-side)
  • Archive and compress all Alert Logs and application logs to historical location.
  • Archive or delete trace files from the diagnostic destination.
  • Archive and compress audio files to a separate location.
  • Archive or delete the listener log file.
  • Check the number of log switches per hour/day/week. Generate analysis report and resize you file system/drive. 
  • Check how much redo generated per hour
  • Check free quota limited available of each user
  • Truncate the listener.log file in the $ORACLE_HOME/network/log, if the listener log has increased to a size > than 500 MB. Ensure the space is released, otherwise 'reload' listener.

Weekly Tuning DBA Checklist

  • Check the Chaining & Migrated Rows
  • Check the size of tables & check weather it needs to partition or not
  • Check the objects having the more extents
  • Check the tables having FK but there is no Index
  • Check the tables having no Indexes and tables having more Indexes
  • Check the frequently pin objects & place them in separate tablespace & in cache
  • Check the objects reload in memory
  • Check open cursor not reaching to the max limit
  • Check locks not reaching to the max lock
  • Check I/O of each data file


Monthly DBA Checklist

  • Index Rebuild.
  • Tablespace reorganization.
  • Bounce critical database once a month (If no cold backup configured)
  • Look for Harmful Growth Rates
  • Review database file activity.  Compare to past output to identify trends that could lead to possible contention.
  • Investigate fragmentation (e.g. row chaining, etc.).
  • Check the location of data file also check auto extendable or not
  • Check default tablespace & temporary tablespace of each user
  • Check the Extents of each object and compare if any object extent are overridden which is define at tablespace level
  • Tablespace need coalescing
  • Check the overall database statistics
  • Trend analysis of objects with tablespace, last analyzed, no. of Rows, Growth in days & growth in KB

Quarterly DBA Checklist

  • Patching
  • Database Reorganization
  • Check the quota of non-system tables in the system tablespace.
  • Bounce most critical database once a month (If no cold backup configured)
  • Review common Oracle tuning points such as cache hit ratio, latch contention, and other points dealing with memory management

One Time Activity DBA Checklist

  • Database user creation with required privileges
  • Make the portal of Oracle predefined error with a possible solution.
  • Check database start-up time(if not 24X7)
  • Check the location and size of the control file
  • Check the redo log file size and use separate location of redo log members.
  • Prepare the Backup strategy and test all the recovery scenario.

Friday, May 8, 2015

Temporary Tablespace


 Contents of this Post
 
  • Manage Temporary Tablespace
  • Temporary Tablespace Group
  • Monitor Temporary Tablespace
  • Tune Temporary Tablespace

 
Manage Temporary Tablespace

Query Tablespace Details:-

set lines 200 pages 200
column TABLESPACE_NAME for a13
col EXTENT_MANAGEMENT for a6
col STATUS format a7
select * from dba_tablespaces where CONTENTS='TEMPORARY';

Temporary Tablespace Size:-

select sum(BYTES)/1024/1024/1024 "TEMP Size In GB" from dba_temp_files;

select tablespace_name, sum(BYTES)/1024/1024/1024 "TEMP Size In GB" from dba_temp_files group by tablespace_name;

 
Temporary Files:-

set lines 200 pages 200
col STATUS for a10
col FILE_NAME for a40
col TABLESPACE_NAME for a16
col USER_MB for 9,99,999
col MAX_MB for 9,99,999
col INCREMENT_Mb for 9,99,999
select FILE_NAME,FILE_ID,TABLESPACE_NAME,BYTES/1024/1024/1024 Size_GB, STATUS, USER_BYTES/1024/1024 USER_MB,
AUTOEXTENSIBLE,MAXBYTES/1024/1024 MAX_MB,(INCREMENT_BY*8192)/1024/1024 INCREMENT_Mb,
(MAXBYTES-BYTES)/1024/1024 "Increase Upto MB",STATUS
from dba_temp_files
order by TABLESPACE_NAME,FILE_ID;

 
Temp Tablespace Assigned To Users:-

select username,temporary_tablespace from dba_users order by temporary_tablespace;


Database Default TEMPORARY TABLESPACE:-

column PROPERTY_VALUE for a20
column DESCRIPTION for a40
SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

 
Create/Alter Temporary tablespace & Temp Files:-

create temporary tablespace TEMP1 tempfile '/u02/oradata/DBNAME/temp11.dbf' size 3072m;

ALTER DATABASE TEMPFILE '/u02/oradata/DBNAME/temp11.dbf ' RESIZE 4096M;

ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/oradata/temp1.dbf ' SIZE 2048M AUTOEXTEND OFF;

DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;

ALTER DATABASE TEMPFILE '/u02/oradata/DBNAME/temp11.dbf ' DROP INCLUDING DATAFILES;

ALTER TABLESPACE temp TEMPFILE OFFLINE;

ALTER TABLESPACE temp TEMPFILE ONLINE;

ALTER DATABASE TEMPFILE '/u02/oradata/DBNAME/temp11.dbf ' ONLINE;

ALTER DATABASE TEMPFILE '/u02/oradata/DBNAME/temp11.dbf ' OFFLINE;


Rename Temporary Files:-

ALTER DATABASE TEMPFILE '/u01/temp01.dbf' OFFLINE;

!cp -p /u01/temp01.dbf /u02/temp01.dbf
OR
!mv /u01/temp01.dbf /u02/temp01.dbf

ALTER DATABASE RENAME FILE '/u01/temp01.dbf' to '/u02/temp01.dbf';
ALTER DATABASE TEMPFILE '/u02/temp01.dbf' online;


Shrink Locally Managed Temporary Tablespace:-

ALTER TABLESPACE temp SHRINK SPACE;
ALTER TABLESPACE temp SHRINK SPACE KEEP 20M;
ALTER TABLESPACE temp SHRINK TEMPFILE '/u02/oradata/DBNAME/temp11.dbf ';


TEMPORARY TABLESPACE GROUP (10g or latter):-

Temporary tablespace group has the following benefits:

It allows multiple default temporary tablespaces to be specified at the database level.
It allows the user to use multiple temporary tablespaces in different sessions at the same time.
It allows a single SQL operation to use multiple temporary tablespaces for sorting.

select tablespace_name, group_name from DBA_TABLESPACE_GROUPS;


Size of Temporary Tablespace Group:-

select sum(bytes)
from dba_temp_files
where tablespace_name in (select tablespace_name from dba_tablespace_groups where group_name = 'GROUP1');

 
Create/Add To temporary tablespace group:-

CREATE TEMPORARY TABLESPACE temp01
TEMPFILE ‘/u02/oradata/temp01.dbs’ SIZE 500M
TABLESPACE GROUP tempgroup_a;
 

Add Existing Temporary tablespace to Temporary tablespace group:-

ALTER TABLESPACE temp03 TABLESPACE GROUP tempgroup_b;


Remove Temporary tablespace from Temporary tablespace group:-

ALTER TABLESPACE temp04 TABLESPACE GROUP '';

 
Assign Default Temporary Tablespace GROUP to Database:-

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tempgroup_A;


Assign User to a default temporary tablespace group:-

In this case, user Scott will have multiple default temporary tablespaces.
A single SQL operation by Scott can use more than one temporary tablespace for sorting.

ALTER USER scott TEMPORARY TABLESPACE tempgroup_A;

 
Monitor Temporary Tablespace

/*TO SEE THE BLOCKS */ (Who Are the Users)

without user list>>

column tablespace format a10 heading 'Tablespace|Name'
column segfile# format 9,999 heading 'File|ID'
column segblk# format 999,999,999 heading 'Block|ID'
column blocks format 999,999,999 heading 'Blocks'
break on tablespace on segfile#
select b.tablespace,b.segfile#,b.segblk#,b.blocks
from v$sort_usage b
order by b.tablespace,b.segfile#,b.segblk#;


With user list>>

column username format a10
SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#,
a.username, a.osuser, a.status
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr
ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;


/*TO SEE THE TOTAL FREESPACE AND USED SPACE IN TEMPORARY TABLESPACE */

SELECT   A.tablespace_name tablespace, D.mb_total,
         SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
         D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM     v$sort_segment A,
         (
         SELECT   B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
         FROM     v$tablespace B, v$tempfile C
         WHERE    B.ts#= C.ts#
         GROUP BY B.name, C.block_size
         ) D
WHERE    A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;


OR From v$temp_space_header

select tablespace_name, SUM(bytes_used)/(1024*1024) USED_MB, sum(bytes_free)/(1024*1024) FREE_MB
from v$temp_space_header
where tablespace_name = 'TEMP'
group by tablespace_name;


/*It will say how much extents is taking space*/

select u.tablespace, u.contents, u.segtype, u.extents,u.blocks,
round(((u.blocks*P.VALUE)/1024/1024),2) MB
from v$session s,v$sort_usage u,SYS.V_$SYSTEM_PARAMETER P
where s.saddr = u.session_addr
and UPPER(P.NAME)='DB_BLOCK_SIZE'
order by MB DESC;


/*To see the users taking the temporary tablespace/*

col "User,SID,SERIAL" for a18
col "Tablsp Name, ID" for a15
col SEGTYPE for a7
col MODULE for a30
col TERMINAL for a10
col PROGRAM for a18
col ACTION for a10
col OSUSER for a12
col MACHINE for a17
select u."USER"||','||s.sid||','||s.SERIAL# "User,SID,SERIAL"
,u.tablespace||', '||u.SEGRFNO# "Tablsp Name, ID", u.contents, u.segtype
,round(u.blocks*8192/1024/1024,2) Temp_MB
,s.MODULE,s.TERMINAL
--,s.PROGRAM
,s.OSUSER,s.MACHINE
from v$session s, v$sort_usage u
where s.saddr = u.session_addr
and u.contents = 'TEMPORARY'
order by Temp_MB DESC;


/* How many extents are allocated and actually used per datafile */

select tablespace_name,
       file_id,
       extents_cached extents_allocated,
       extents_used,
       bytes_cached/1024/1024 mb_allocated,
       bytes_used/1024/1024 mb_used
from v$temp_extent_pool;

 
/* Identify temp segment usages per session */

col TABLESPACE for a10
col SID_SERIAL for a12
col USERNAME for a10
col OSUSER for a10
col MODULE for a35
col PROGRAM for a35
SELECT  S.sid ||','|| S.serial# sid_serial
                ,S.username,S.osuser,P.spid,S.module,P.PROGRAM
                ,SUM(T.blocks)*(TBS.block_size/1024/1024) mb_used
                ,T.tablespace,COUNT(*) statements
FROM   v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr
AND       S.paddr = P.addr
AND       T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module, P.program, TBS.block_size, T.tablespace
ORDER BY mb_used;

 
/* Identify temp segment usages per SQL statement */

col TABLESPACE for a10
col SID_SERIAL for a12
col USERNAME for a10
SELECT  S.sid || ',' || S.serial# sid_serial, S.username, Q.hash_value, Q.sql_text,
                T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace
FROM    v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
WHERE   T.session_addr = S.saddr
AND     T.sqladdr = Q.address
AND     T.tablespace = TBS.tablespace_name
ORDER BY mb_used;


/* Who's using temp space, what queries are they executing and how much of that space is each one consuming? */

In 10g and later releases

select u.username, s.sql_fulltext, u.segtype, u.extents, u.blocks
from v$tempseg_usage u, v$sql s
where s.sql_id = u.sql_id;
 
For 9iR2 and 9iR1

select u.username, s.sql_text, u.segtype, u.extents, u.blocks
from v$tempseg_usage u, v$sql s
where s.address = u.sqladdr
and s.hash_value = u.sqlhash;
 
For 8.1.7, 8.1.6 and 8.1.5
 
select u.username, s.sql_text, u.extents, u.blocks
from v$sort_usage u, v$sql s
where s.address = u.sqladdr
and s.hash_value = u.sqlhash;
 

How can you estimate the amouant of temp space a query may use?

V$SQL_PLAN can provide that information if you know the SQL_ID of the query:
select temp_space from v$sql_plan where sql_id = '< some sql_id value >';


Tune Temporary Tablespace
 
  • Note If below Error coming repeatedly to user after increasing the TEMP tablespace then
          ORA-01652: unable to extend temp segment by 64 in tablespace TEMP
          Increase the pga_aggregate_target size to to keep more temp info
          ALTER SYSTEM SET pga_aggregate_target='800M' SCOPE=BOTH;

  • Generate query explain plan and see what the estimated size of the sort operation is, this will help you to sizing your TEMP Tablespace.
           V$SQL_PLAN can provide that information if you know the SQL_ID of the query:
          select temp_space from v$sql_plan where sql_id = '< some sql_id value >';

  • Gather DATABASE and SCHEMA statistics (DBMS_STATS) is very help full to reduce the occupying of the TEMP space

 
Thanks......

Saturday, February 28, 2015

MATERIALIZED VIEW


Contents of this Post

  • Materialized View Concept
  • Use Of Materialized View
  • Create Materialized View
  • Query Materialized View
    • Materialized View Refresh duration
    • Get Materialized View Data Definition
    • Find All JOBs/MViews Related to Master DB
  • Materialized View Error/Bug



Materialized View Concept

Oracle uses materialized views (also known as snapshots in prior releases) to replicate data to non-master sites in a replication environment and to cache expensive queries in a data warehouse environment. 
A materialized view is a replica of a target master from a single point in time. The master can be either a master table at a master site or a master materialized view at a materialized view site. Whereas in multi-master replication tables are continuously updated by other master sites, materialized views are updated from one or more masters through individual batch updates, known as a refreshes, from a single master site or master materialized view site.



One of the biggest benefit of using a materialized view is that Oracle takes care of keeping the data in sync. If you have a separate aggregate table, you are responsible for keeping the data synchronized. That generally requires a reasonable amount of code and a decent amount of testing and most organizations manage to make mistakes that leave holes that cause the aggregate table to get out of sync. This is particularly true when you try to implement incremental refreshes of the aggregate table.
Another major benefit is that, depending on the settings, Oracle can use query rewrite to use materialized views when users issue queries against base tables. So, for example, if you have a bunch of existing reports against a detail table that produce daily, monthly, and yearly aggregate results, you can create a materialized view on the base table that aggregates the data at a daily level and the optimizer can utilize that materialized view for all your existing queries. This makes it much easier to optimize reporting workloads in a data warehouse without trying to go and rewrite dozens of reports to use your new aggregate table or to mess with DBMS_ADVANCED_REWRITE to force your own rewrites of the queries.
Materialized views can be set to refresh manually, on a set schedule, or based on the database detecting a change in data from one of the underlying tables. Materialized views can be incrementally updated (delta changes) by combining them with materialized view logs, which act as change data capture sources on the underlying tables.
Materialized views are most often used in data warehousing / business intelligence applications where querying large fact tables with thousands of millions of rows would result in query response times that resulted in an unusable application.

Materialized views are primarily used to increase the application performance when it isn't feasible or desirable to use a standard view with indexes applied to it. Index can be created on Materialized views whereas the indexing is not possible in force view as the views are essentially logical table-like structures populated on the fly by a given query and the results of a view query are not stored anywhere on disk/datafile/segment.


Create Materialized View




Master Database Site

SELECT * FROM DBA_MVIEW_LOGS WHERE MASTER='<BASE TABLE NAME>',

CREATE MATERIALIZED VIEW LOG ON <BASE TABLE> TABLESPACE <TABLESPACE NAME> WITH <PRIMARY KEY>/<ROWID>;

DROP MATERIALIZED VIEW LOG ON <Schema>.<Table_Name>;


MView Database Site

CREATE DATABASE LINK <NAME> CONNECT TO <Master DB Schema Name> 
IDENTIFIED BY < Master DB Schema Password> USING '<Master DB TNS>';


CREATE MATERIALIZED VIEW "USERNAME"."MVIEW_NAME"
ORGANIZATION HEAP PCTFREE 10 PCTUSED 0 INITRANS 2 MAXTRANS 255 NOCOMPRESS NOLOGGING
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "<Tablespace Name>"
BUILD IMMEDIATE
--BUILD DEFERRED                                                      -- Oracle Database does not execute the defining query immediately
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE ""<Tablespace Name>"
REFRESH FAST ON DEMAND START WITH sysdate+0 NEXT TRUNC(SYSDATE+1)+6/24 -- Morning 6 AM
-- REFRESH FAST ON DEMAND START WITH sysdate+0 NEXT SYSDATE + 5/(60*24)  -- Every 5 Mins
-- REFRESH COMPLETE ON DEMAND START WITH sysdate+0 NEXT SYSDATE + 30/(60*24) -- Every 30 Mins
-- REFRESH COMPLETE ON DEMAND START WITH sysdate+0 NEXT SYSDATE + 1
WITH PRIMARY KEY USING DEFAULT LOCAL ROLLBACK SEGMENT
DISABLE QUERY REWRITE
--WITH ROWID USING DEFAULT LOCAL ROLLBACK SEGMENT
DISABLE QUERY REWRITE
AS SELECT
COLUMN_1 Col_Alias_Name ,
COLUMN_2 Col_Alias_Name,
COLUMN_3 Col_Alias_Name,
--NULL Col_Alias_Name,,
--CAST(NULL as varchar2(1)) Col_Alias_Name,,

FROM <Master Schema>.<Master Base Table>@<DB_LINK Name>;


Refresh Complex MViews Using Trigger

create or replace trigger X
after insert or update or delete on T
declare
   l_job number;
begin
   dbms_job.submit( l_job, 'dbms_mview.refresh( ''OPS$TKYTE.T_MV'' );' );
end;



ALTER/DROP MATERIALIZED VIEW

ALTER MATERIALIZED VIEW <MVIEW_NAME>

REFRESH COMPLETE ON DEMAND START WITH sysdate+0 NEXT SYSDATE + 30/(60*24);

DROP MATERIALIZED VIEW LOG ON <MVIEW_NAME>;


Query Materialized View

Find All JOBs/MViews Related to Master DB

col JOB FOR 9999
col OWNER for a8
col MASTER_LINK for a15
col HOST for a10
col FAST_REFRESHABLE for a8
col STALENESS for a9
col COMPILE_STATE for a8
col MVIEW_NAME for a20
col "REFRH MOD-METHOD BLD_MOD" for a22
col INTERVAL FOR a24

SELECT 
         JOB
,a.OWNER,MVIEW_NAME
--,CONTAINER_NAME
,UPDATABLE
,REWRITE_ENABLED
,MASTER_LINK
,c.HOST
,REFRESH_MODE||'-'||REFRESH_METHOD||'-'||BUILD_MODE "REFRH MOD-METHOD BLD_MOD"
,FAST_REFRESHABLE
,LAST_REFRESH_TYPE
,to_char(LAST_REFRESH_DATE,'dd-Mon-yy hh24:mi:ss') LAST_REFRESH_DATE
,COMPILE_STATE
,INTERVAL
--,STALENESS
--,STALE_SINCE 
,USE_NO_INDEX
FROM DBA_MVIEWS a, DBA_REFRESH_CHILDREN b, DBA_DB_LINKS c
WHERE a.MVIEW_NAME=b.NAME
AND a.OWNER=b.OWNER
And MASTER_LINK in (SELECT '@'||DB_LINK from dba_db_links where UPPER(HOST) ='<Master DB Name>')
AND SUBSTR(a.MASTER_LINK,2) = c.DB_LINK
AND UPPER(c.HOST) ='<Master DB Name>'                                              -- PUT DB Name Here
Order by MASTER_LINK, MVIEW_NAME;

OR For Multiple DBs

SELECT
         JOB
,a.OWNER,MVIEW_NAME
--,CONTAINER_NAME,UPDATABLE,REWRITE_ENABLED
,MASTER_LINK
,REFRESH_MODE||'-'||REFRESH_METHOD||'-'||BUILD_MODE "REFRH MOD-METHOD BLD_MOD"
,FAST_REFRESHABLE
,LAST_REFRESH_TYPE
,to_char(LAST_REFRESH_DATE,'dd-Mon-yy hh24:mi:ss') LAST_REFRESH_DATE
,COMPILE_STATE
,INTERVAL
--,STALENESS,USE_NO_INDEX,STALE_SINCE  
from dba_mviews a, dba_refresh_children b
where a.MVIEW_NAME=b.NAME
And a.OWNER=b.OWNER
And MASTER_LINK in (SELECT '@'||DB_LINK from dba_db_links where UPPER(HOST) in (<Master DBs Name>))
Order by MASTER_LINK, MVIEW_NAME;


DBMS_JOBs + Materialized View

SELECT OWNER,NAME,JOB,INTERVAL from dba_refresh_children order by NAME;


Query All COLUMNS Of Materialized View

SELECT * FROM DBA_MVIEW_LOG_FILTER_COLS;


Get Materialized View Data Definition

SET LONG 2000000

SELECT DBMS_METADATA.GET_DDL( 'MATERIALIZED_VIEW','<MView Name>','<Schama Name>') FROM DUAL;

Query Materialized View Log in Master DB Site

SELECT * FROM DBA_MVIEW_LOGS WHERE MASTER='<BASE TABLE NAME>';


Materialized View Refresh duration

select 
         owner,mview_name, last_refresh_date as "Refresh Start"
,round(fullrefreshtim/60) as "Full Minutes to Refresh"
,round(increfreshtim/60) as "Inc Minutes to Refresh"
from ALL_MVIEW_ANALYSIS
where mview_name = '<MView Name>';

FULLREFRESHTIM >> Last Apx time that it took to fully refresh the materialized view
INCREFRESHTIM >> Last Apx time that it took to incrementally refresh the materialized view


Below query will provide more details of MView refresh duration.


set pages 600
col OWNER for a15
col MVIEW_NAME for a30
col "Current Refresh Schedule" trunc head "Current Refresh|Schedule"
col "Last Complete Refresh In Sec" trunc head "Last Complete|Refresh In Sec"
col "Last Increment Refresh In Sec" trunc head "Last Increment|Refresh In Sec"
col "Total Refresh Time In Hours" trunc head "Total Refresh|Time In Hours"

SELECT
DISTINCT Job_Id
,OWNER
,mview_name
,REFRESH_METHOD "Current Refresh Schedule"
,TO_CHAR(START_TIME,'DD-Mon-YY HH24:MI:SS')||' TO '||TO_CHAR(END_TIME,'DD-Mon-YY HH24:MI:SS') "Last Refresh Start-End Time"
,fullrefreshtim "Last Complete Refresh In Sec"
,increfreshtim "Last Increment Refresh In Sec"
,round(TOTAL_TIME/60/60,2) "Total Refresh Time In Hours"
FROM (
SELECT
a.job Job_Id,a.TOTAL_TIME
    ,c.OWNER,c.mview_name,c.last_refresh_date "START_TIME",c.REFRESH_METHOD
    ,CASE
      WHEN c.fullrefreshtim <> 0 THEN
          c.LAST_REFRESH_DATE + c.fullrefreshtim/60/60/24
      WHEN c.increfreshtim <> 0 THEN
          c.LAST_REFRESH_DATE + c.increfreshtim/60/60/24
      ELSE
          c.LAST_REFRESH_DATE
    END "END_TIME"
    ,c.fullrefreshtim
    ,c.increfreshtim
FROM 
         dba_jobs a
,dba_refresh_children b
,dba_mview_analysis c
WHERE a.job=b.job
AND   a.SCHEMA_USER=b.OWNER
AND   b.OWNER=c.OWNER
AND   b.OWNER=c.MVIEW_TABLE_OWNER
AND   b.NAME=c.mview_name)
ORDER BY OWNER,mview_name;




Other Materialized View Related Query

col INTERVAL for a35
select * from dba_refresh_children;
select * from dba_refresh;
SELECT * From dba_rgroup;


DBA_BASE_TABLE_MVIEWS
DBA_MVIEWS
DBA_MVIEW_REFRESH_TIMES
DBA_MVIEW_AGGREGATES
DBA_MVIEW_ANALYSIS
DBA_MVIEW_LOGS
DBA_MVIEW_DETAIL_RELATIONS
DBA_MVIEW_JOINS
DBA_MVIEW_KEYS
DBA_MVIEW_LOG_FILTER_COLS
DBA_REGISTERED_MVIEWS
DBA_REGISTERED_MVIEW_GROUPS


Materialized View Errors/Bug

Bug 1

Assumption: Source is 11.2 (11gR2) DB and Destination is 9.2 (9i) DB

CREATE MATERIALIZED VIEW "SCHEMA"."TABBLE"

*

ERROR at line 1:
ORA-04052: error occurred when looking up remote object T.C@DB_LINK
ORA-00604: error occurred at recursive SQL level 1
ORA-03120: two-task conversion routine: integer overflow
ORA-02063: preceding line from DB_LINK


Cause :-
Internet sources indicate that ORA-03120 is associated with bug 5671074 on Oracle Enterprise Edition; Version 9.2.0.8, occurring on any platform basically while the source DB is 11gR2

Action :-

It will be necessary to apply the one-off patch for Bug 5671074 to the 9.2.0.8 database. 

One-off patches to 9.2.0.8 for most platforms are already available for download on MOSC. 

Bug 5671074 to the 9.2.0.8 database. One-off patches to 9.2.0.8 for most platforms are already available for download on MOSC. 

Bug 5671074 : MATERIALIZED VIEW REFRESH FAILS WITH ORA-04052 ERROR 
https://support.oracle.com/epmos/faces/BugDisplay?id=5671074&_adf.ctrl-state=vb5ewdw7y_40&_afrLoop=394925371245165


Bug 2

Assumption: Source is 11.2 (11gR2) DB and Destination is 9.2 (9i) DB
 
CREATE MATERIALIZED VIEW TEST.TABLE_1
* 
ERROR at line 1: 
ORA-03113: end-of-file on communication channel 



Alert Log Says



Errors in file /u01/app/oracle/92/rdbms/log/DBUAT92_ora_3428.trc: 
ORA-07445: exception encountered: core dump [kghfrf()+32] [SIGSEGV] [Address not mapped to object] [0xFFFFFFFFFFFFFFE8] [] []  

Cause :-
The Oracle 9.2 database encounters a known bug.
ORA-07445 [kghfrf] Error When Trying to Create a Materialized View (Doc ID 1208712.1)

Action :-
Apply Patch 5879082 to the 9.2 database that should hold the Materialized View definition.
https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=20352788489886&id=1208712.1&_afrWindowMode=0&_adf.ctrl-state=kemj13thf_142