Table reorganization process cancelled after process went to materialized view mode.

Table Re org issues. ORACLE

BALADAT Table reorganization process cancelled after process went to materialized view mode.Cannot restart again it says BALDAT have materialized view.

SQL> DROP MATERIALIZED VIEW LOG ON SAPSR3.BALDAT.

 Again the process is still active in V$session table which cannot even process the –f stats collection .

Again required to kill the session from v$session table

SQL> ALTER SYSTEM KILL SESSION ‘SID,SERIAL#’

After this process –stats collect successfully ran.

Use below Qry to check.

I USE DROP TABLE

SQL> DROP TABLE SAPSR3.BALDAT#$ 

WHICH CREATED FOR RE-ORAGANIZATION Check the uncompleted update process on table

SQL> SELECT sid,TIMESTAMP,USERNAME,LAST_UPDATE_TIME, to_char(start_time,'hh24:mi:ss') stime,

message,( sofar/totalwork)* 100 percent
FROM v$session_longops
WHERE sofar/totalwork < 1

 

DBSIZE.TPL file will create while exporting the SAP or else you can copy from export CD.

DBA JOBS

==================
SQL> select job, last_date last_refresh,
   next_date next_refresh, total_time,
   broken, failures, what
   from dba_jobs;
  select r.rowner, r.rname, r.job, c.name,
   r.next_date next_refresh, r.broken
   from all_refresh r, all_refresh_children c
   where r.job = c.job;
----------------------
The following query will indicate when an mview was last successfully refreshed and what type of refresh was done.

   SQL> column last_refresh_type format a18
   column owner format a7
   column mview_name format a12
   select owner, mview_name, last_refresh_type, last_refresh_date
   from dba_mviews;

M_view checking
==================
SQL> SELECT MVIEW_NAME, FAST_REFRESHABLE FROM USER_MVIEWS;
select mview_name, last_refresh_date from all_mviews;
select * from  dba_mviews;


SQL> select a.name, b.value
 from v$statname a, v$mystat b
  where a.statistic# = b.statistic#
  and a.name = 'redo size'

SQL> SELECT DISTINCT(TRUNC(last_refresh))
FROM dba_snapshot_refresh_times;
SELECT CURRMVOWNER, CURRMVNAME FROM V$MVREFRESH


SQL> SELECT   S.USERNAME,   s.osuser,
         S.SID,
         S.SERIAL#,
         L.TYPE,
         L.ID1,
         L.ID2
FROM     V$LOCK L, V$SESSION S
WHERE    L.SID=S.SID
AND S.SID='477'
UPDATE v$SESSION SET STATUS='INACTIVE' WHERE SID='477';

=============================================================

SQL> column "MVIEW BEING REFRESHED" format a30
column INSERTS format 9999999
column UPDATES format 9999999
column DELETES format 9999999
column sid_knst format 9999999
select CURRMVOWNER_KNSTMVR || '.' || CURRMVNAME_KNSTMVR
"MVIEW BEING REFRESHED",
decode( REFTYPE_KNSTMVR, 1, 'FAST', 2, 'COMPLETE', 'UNKNOWN' ) REFTYPE,
decode(GROUPSTATE_KNSTMVR, 1, 'SETUP', 2, 'INSTANTIATE',
3, 'WRAPUP', 'UNKNOWN' ) STATE,
TOTAL_INSERTS_KNSTMVR INSERTS,
TOTAL_UPDATES_KNSTMVR UPDATES,
TOTAL_DELETES_KNSTMVR DELETES
from X$KNSTMVR X
WHERE type_knst=6 and
exists (select 1 from v$session s
where s.sid=x.sid_knst and
s.serial#=x.serial_knst);
ALTER SYSTEM KILL SESSION  'SID , SERIAL'
UPDATE v$SESSION SET STATUS='INACTIVE' WHERE SID='477';
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
==============================================================
SQL> column "pid" format a30
column spid format 9999999
SELECT   P.PID,
         P.SPID,
         DECODE (P.USERNAME,
                 '?', DECODE(S.USERNAME,
                             NULL, P.USERNAME,
                             '(' || S.USERNAME || ')'),
                 P.USERNAME),
         NVL(S.TERMINAL, P.TERMINAL),
         P.LATCHWAIT,
         NVL(S.PROGRAM, P.PROGRAM)
FROM     V$PROCESS P, V$SESSION S
WHERE    P.ADDR = S.PADDR(+) and s.sid='477'
order by 4
===================================================================

SQL> SELECT s.sid,
       s.serial#,
       s.osuser,
       s.program,
       s.status,
       S.CLIENT_IDENTIFIER,
       S.MODULE,
       S.MACHINE
FROM   v$session s
   WHERE TYPE = 'USER'
   AND AUDSID != USERENV('SESSIONID')
   and s.status='ACTIVE'
order by  s.osuser


SQL> SELECT sid
  FROM v$lock
 WHERE type = 'TX'
   AND id1 = (SELECT object_id
                FROM all_objects
               WHERE owner = owner
                 AND object_name = object_name)



SQL> SELECT s.sid,
       s.serial#,
       s.osuser,
       s.program,
       s.status,
       S.CLIENT_IDENTIFIER,
       S.CLIENT_INFO,
       S.MODULE,
       S.MACHINE,
       S.LOGON_TIME,
       S.STATE
FROM   v$session s
   WHERE TYPE = 'USER'
   AND AUDSID != USERENV('SESSIONID')
   and s.status='ACTIVE'
order by  s.osuser


Thanks
Yoonus