Wednesday, 8 December 2021

long running query session longops

long running query session longops ....

long running query session longops


--   http://anuj-singh.blogspot.com/2014_08_25_archive.html

set linesize 300 pagesize 1000
COLUMN instance_name FORMAT a10 			HEADING 'Instance'
COLUMN sid 						HEADING 'Oracle|SID'
COLUMN serial_num 					HEADING 'Serial|#'
--COLUMN opname FORMAT a30 				HEADING 'RMAN|Operation'
COLUMN start_time 		FORMAT a18 		HEADING 'Start|Time'
COLUMN totalwork 					HEADING 'Total|Work'
COLUMN sofar 						HEADING 'So|Far'
COLUMN pct_done 					HEADING 'Percent|Done'
COLUMN elapsed_seconds 					HEADING 'Elapsed|Seconds'
COLUMN time_remaining 					HEADING 'Seconds|Remaining'
COLUMN done_at 			FORMAT a18 		HEADING 'Done|At'
col kill for a15
col EVENT for a30
col PROGRAM for a20
SELECT ''''||a.sid ||','|| a.serial#||',@'||a.inst_id ||'''' kill
,a.sql_id
,prev_sql_id
, b.opname opname
, to_char(b.start_time, 'dd/mm/yy hh24:mi:ss') start_time
, b.totalwork totalwork
, b.sofar sofar
, ROUND( (b.sofar/DECODE( b.totalwork, 0, 0.001, b.totalwork)*100),2) pct_done
, b.elapsed_seconds elapsed_seconds
, b.time_remaining time_remaining
,a.event
, DECODE( b.time_remaining, 0, TO_CHAR((b.start_time + b.elapsed_seconds/3600/24), 'dd/mm/yy HH24:MI:SS')
, TO_CHAR((SYSDATE + b.time_remaining/3600/24), 'dd/mm/yy HH24:MI:SS')) done_at
,PROGRAM
FROM gv$session a, gv$session_longops b
where 1=1
and a.sid=b.sid
and a.serial#=b.serial#
and a.inst_id=b.inst_id
-- and a.program LIKE 'rman%' AND b.opname LIKE 'RMAN%' AND b.opname NOT LIKE '%aggregate%'
AND b.totalwork > 0
ORDER BY  b.start_time
;


set lines 500
col opname 	format a35
col target 	format a10
col units 	format a10
col kill  	for a15
col message for a50 wrap
col TOTALWORK for 999999999999
select * from (
      select
      ''''||sid ||','|| serial#||',@'||inst_id ||'''' kill, sql_id,
      opname, target, sofar, totalwork, round(sofar/totalwork, 4)*100 pct, units, round(elapsed_seconds/60,2) elap_min, round(time_remaining/60,2) remaining_min  , sql_plan_hash_value, sql_plan_operation, sql_plan_options, sql_plan_line_id,  TO_CHAR(sql_exec_start, 'DD-MM-YYYY HH24:MI:SS') sql_exec_start  ,message
      from gv$session_longops
      WHERE 1=1
	--  and sofar < totalwork
    -- and sql_id='g75x2hpgkgqcg'  <<<< for this sql 
      order by start_time desc
	  )
/


-- from Web
set pages 500 lines 500
col kill        for a15
col message 	for a45
col totalwork 	for a20
col target 		for a27
col operation 	for a35
select 
''''||l.sid ||','|| l.serial#||',@'||l.inst_id ||'''' kill
     , case when l.time_remaining > 0 or l.sofar < l.totalwork then 'Yes' end as "Active?"
     , l.opname as operation
     , l.totalwork || ' ' || l.units as totalwork
     , nvl(l.target,l.target_desc) as target
     , l.start_time
     , round(100 * l.sofar/greatest(l.totalwork,1),1) as "Complete %"
     , rtrim(rtrim(ltrim(ltrim(numtodsinterval(l.elapsed_seconds,'SECOND'),'+0'),' '),'0'),'.') as elapsed
     , ltrim(ltrim
       ( cast(numtodsinterval(l.elapsed_seconds * greatest(nullif((l.totalwork - l.sofar)/nullif(l.sofar,0),0),0),'SECOND')
              as interval day(2) to second(0))
       ,'+0'),' ') as remaining
     , case
           when l.sofar >= l.totalwork then l.last_update_time
           else sysdate + numtodsinterval( l.elapsed_seconds * greatest(nullif((l.totalwork - l.sofar)/nullif(l.sofar,0),0),0),'SECOND')
       end as est_completion
 , l.sql_id
     , l.sql_address
     , l.sql_hash_value
	  , message
from gv$session_longops l
where 1=1
--and :sid in (sid,qcsid)
--and  l.start_time >= to_date(:logon_time,'dd/mm/yyyy hh24:mi:ss')
-- and sofar != totalwork
order by l.start_time desc
;

                                                                                                                                          

set linesize 300 pagesize 300
column username       cle
column sid            cle
column lock_type      cle
column MODE_HELD      cle
column MODE_REQUESTED cle
column LOCK_ID1       cle
column LOCK_ID2       cle


col sid 			for 99999
col serial# 		for 9999999
col machine 		for a30
col program 		for a30 trunc
col progress_pct 	for 99999999.00
col elapsed 		for a10
col remaining 		for a10
col kill 			for a15
SELECT 
''''||s.sid||','||s.serial#||',@'||s.inst_id||''''  kill ,
--s.sid,
--    s.serial#,
       s.sql_id,
       s.machine,
       s.program,
       lpad(TRUNC(sl.elapsed_seconds/60),3,' ') || ':' || lpad(MOD(sl.elapsed_seconds,60),2,' ') elapsed,
       lpad(TRUNC(sl.time_remaining/60),3,' ')  || ':' || lpad(MOD(sl.time_remaining,60),2,' ') remaining,
       ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct
FROM   gv$session s, gv$session_longops sl
WHERE  s.sid     = sl.sid
AND    s.serial# = sl.serial#
 and s.inst_id     = sl.inst_id 
and    sl.time_remaining > 0
order by 6
/

Wednesday, 17 November 2021

Which sql is causing my cluster waits?


from Cluster Waits to sql 

define p_inst=1
define p_days=7
column dt heading 'Date/Hour' format a11
set linesize 500  pages 9999	 
select * from (
select min(snap_id) as snap_id,  
		     to_char(start_time,'MM/DD/YY') as dt, to_char(start_time,'HH24') as hr
	from (
	select snap_id, s.instance_number, begin_interval_time start_time, 
		   end_interval_time end_time, snap_level, flush_elapsed,
		   lag(s.startup_time) over (partition by s.dbid, s.instance_number 
		   					   order by s.snap_id) prev_startup_time,
		   s.startup_time
	from  dba_hist_snapshot s, gv$instance i
	where begin_interval_time between trunc(sysdate)-&p_days and sysdate 
	and   s.instance_number = i.instance_number
	and   s.instance_number = &p_inst
	order by snap_id
	)
	group by to_char(start_time,'MM/DD/YY') , to_char(start_time,'HH24') 
	order by snap_id, start_time )
	pivot
	(sum(snap_id)
	 for hr in ('00','01','02','03','04','05','06','07','08','09','10','11','12','13','14','15','16','17','18','19','20','21','22','23')
	 )
	 order by dt;


'              '11'              '12'              '13'              '14'              '15'              '16'              '17'              '18'              '19'              '20'              '21'              '22'              '23'
----------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- -----------------
11/10/21                37346             37347             37348             37349             37350             37351             37352             37353             37354             37355             37356             37357             37358             37359             37360             37361             37362             37363             37364             37365             37366             37367             37368             37369
11/11/21                37370             37371             37372             37373             37374             37375             37376             37377             37378             37379             37380   



col wait_class for a20
select wait_class_id, wait_class, count(*) cnt
from dba_hist_active_sess_history
where snap_id between 37346 and 37347
group by wait_class_id, wait_class
order by 3;



    WAIT_CLASS_ID WAIT_CLASS                         CNT
----------------- -------------------- -----------------
       4166625743 Administrative                       1
       2723168908 Idle                                 1
       4108307767 System I/O                          10
       1893977003 Other                               22
       3871361733 Cluster                            528
       1740759767 User I/O                           669
                                                    1072

7 rows selected.


col event for a30
select event_id, event, count(*) cnt from dba_hist_active_sess_history
where snap_id between 37346 and 37347 
and wait_class_id=1740759767
group by event_id, event
order by 3;


col event for a30
select event_id, SQL_ID,event, count(*) cnt from dba_hist_active_sess_history
where snap_id between 37346 and 37347
and wait_class_id=1740759767
group by event_id,sql_id, event
order by 3;


         EVENT_ID SQL_ID        EVENT                                        CNT
----------------- ------------- ------------------------------ -----------------
       2652584166 8cnh50qfgwg73 db file sequential read                      478
       3056446529 8cnh50qfgwg73 read by other session                        191


select sql_id, count(*) cnt from dba_hist_active_sess_history
where snap_id between 37346 and 37347
and event_id in ( 2652584166,3056446529 )
group by sql_id
having count(*)>200
order by 2
 ;
 
 
 define sql_id='8cnh50qfgwg73'
 select sql_text from dba_hist_sqltext where sql_id='&sql_id';


define sql_id='8cnh50qfgwg73'
col b.sql_text 	for a70 wrap
col username 	for a20
col sql_opname 	for a15
col sample_time for a25
col program 	for a25
col module 		for a20
col machine 	for a20
select a.SQL_ID,c.username,a.sample_time, a.sql_opname, a.sql_exec_start, a.program, a.module, a.machine,b.sql_text 
from dba_hist_active_sess_history a, dba_hist_sqltext b, dba_users c
where a.SQL_ID = b.SQL_ID(+)
and a.user_id=c.user_id
--and c.username='username'
and a.sql_id='&sql_id'
and rownum<10
order by a.sql_exec_start asc;


define sql_id='8cnh50qfgwg73'
select * from table(DBMS_XPLAN.DISPLAY_CURSOR('&SQL_ID', format=>'ADVANCED'));


set lines 500 pages 5000 trimspool on trimout on verify off

define sql_id='8cnh50qfgwg73'
DEF from_time=sysdate-1/24/60*5
DEF to_time=sysdate

--DEF cols=session_state,event,sql_id

DEF cols=session_state,event,sql_id


SELECT * FROM (
  SELECT
        &cols
      , count(*)
      , lpad(round(ratio_to_report(count(*)) over () * 100)||'%',10,' ') percent
    FROM
        -- active_session_history_bak
        gv$active_session_history
        -- dba_hist_active_sess_history
    WHERE
        sample_time BETWEEN &from_time AND &to_time
  --  AND where_clause
  and sql_id='&sql_id'
    GROUP BY
        &cols
    ORDER BY  percent DESC
)
WHERE ROWNUM <= 20
/



SESSION EVENT                                                            SQL_ID          COUNT(*) PERCENT
------- ---------------------------------------------------------------- ------------- ---------- ----------------------------------------
WAITING db file sequential read                                          8cnh50qfgwg73        198        35%
WAITING gc buffer busy acquire                                           8cnh50qfgwg73        166        29%
WAITING read by other session                                            8cnh50qfgwg73         87        15%
WAITING gc cr disk read                                                  8cnh50qfgwg73         62        11%
ON CPU                                                                   8cnh50qfgwg73         58        10%
WAITING gc cr request                                                    8cnh50qfgwg73          1         0%

6 rows selected.




select
    sql_id
  , sql_hash_value
  , sql_child_number
  , count(*)
from
    gv$session
where
    status='ACTIVE'
and type !='BACKGROUND'
and sid != (select sid from v$mystat where rownum=1)
 and sql_id='&sql_id'
group by
    sql_id
  , sql_hash_value
  , sql_child_number
order by  count(*) desc
/



col parsed format a6
col sql_text format a40
set lines 200  pages 300

select     
 sql_text,
 parsing_schema_name as parsed,
 elapsed_time_delta/1000/1000 as elapsed_sec,
 stat.snap_id,
 to_char(snap.end_interval_time,'dd.mm hh24:mi:ss') as snaptime,
 txt.sql_id
from     
 dba_hist_sqlstat stat,
 dba_hist_sqltext txt,
 dba_hist_snapshot snap
where     
 stat.sql_id=txt.sql_id 
 and  stat.snap_id=snap.snap_id and
 snap.begin_interval_time>=sysdate-1 
-- and  lower(sql_text) like '%t%' 
 and  parsing_schema_name not in ('SYS','SYSMAN','MDSYS','WKSYS')
order by elapsed_time_delta asc;

Wednesday, 3 November 2021

Archivelog info..

set linesize 300 pagesize 300

col name for a90

col first_time for a16

select thread#,sequence#, name ,creator

-- , to_char(first_time,'DD-MON HH24:MI') first_time

, to_char(completion_time,'DD-MON HH24:MI') arc_completion,decode (STATUS,'A','Available','D', 'Deleted','U','Unavailable','X' , 'Expired' ) arc_status

from v$archived_log

where 1=1

-- and first_time > sysdate -3

-- and CREATOR!='LGWR'

--and SEQUENCE#>11482

-- and THREAD# =2

and name is null

and first_time < to_date('2021-11-03:11:00:00', 'yyyy-mm-dd:hh24:mi:ss')

order by 5

;

Friday, 15 October 2021

Oracle Undo Tablespace recovery

Oracle Undo TTablespace recovery

oracle@apt-amd-02:/opt/app/oracle/oradata/orcl> !sql

sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Nov 3 14:46:15 2011

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Sql > update scott.emp set sal=99999;

14 rows updated.

Sql > !mv undotbs01.dbf undotbs01.orig

Sql > select * from scott.emp ;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7369 SMITH CLERK 7902 17-DEC-80 99999 20

7499 ALLEN SALESMAN 7698 20-FEB-81 99999 300 30

7521 WARD SALESMAN 7698 22-FEB-81 99999 500 30

7566 JONES MANAGER 7839 02-APR-81 99999 20

7654 MARTIN SALESMAN 7698 28-SEP-81 99999 1400 30

7698 BLAKE MANAGER 7839 01-MAY-81 99999 30

7782 CLARK MANAGER 7839 09-JUN-81 99999 10

7788 SCOTT ANALYST 7566 19-APR-87 99999 20

7839 KING PRESIDENT 17-NOV-81 99999 10

7844 TURNER SALESMAN 7698 08-SEP-81 99999 0 30

7876 ADAMS CLERK 7788 23-MAY-87 99999 20

7900 JAMES CLERK 7698 03-DEC-81 99999 30

7902 FORD ANALYST 7566 03-DEC-81 99999 20

7934 MILLER CLERK 7782 23-JAN-82 99999 10

14 rows selected.

Sql > alter system flush sharepool;

alter system flush sharepool

ERROR at line 1:

ORA-02000: missing SHARED_POOL/BUFFER_CACHE/GLOBAL CONTEXT keyword

Sql > alter system flush SHARED_POOL ;

System altered.

Sql > alter system flush BUFFER_CACHE ;

14 rows selected.

Sql > rollback;

^Crollback

ERROR at line 1:

ORA-00603: ORACLE server session terminated by fatal error

ORA-01116: error in opening database file 3

ORA-01110: data file 3: '/opt/app/oracle/oradata/orcl/undotbs01.dbf'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

ORA-01116: error in opening database file 3

ORA-01110: data file 3: '/opt/app/oracle/oradata/orcl/undotbs01.dbf'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

Process ID: 20893

Session ID: 47 Serial number: 347

Sql > shu immediate;

ORA-01116: error in opening database file 3

ORA-01110: data file 3: '/opt/app/oracle/oradata/orcl/undotbs01.dbf'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

/opt/app/oracle/oradata/orcl

create undo tablespace UNDOTBR datafile '/opt/app/oracle/oradata/orcl/undotbsR.dbf' size 500m autoextend on;

ORA-01157: cannot identify/lock data file 3 - see DBWR trace file

alter database datafile '/opt/app/oracle/oradata/orcl/undotbs01.dbf' offline drop;

Sql > alter database datafile '/opt/app/oracle/oradata/orcl/undotbs01.dbf' offline drop;

Database altered.

create undo tablespace UNDOTBS2

datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\UNDOTBS2' size 200M;

Sql > create undo tablespace UNDOTBR datafile '/opt/app/oracle/oradata/orcl/undotbsR.dbf' size 500m autoextend on;

create undo tablespace UNDOTBR datafile '/opt/app/oracle/oradata/orcl/undotbsR.dbf' size 500m autoextend on

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS'

ORA-06512: at line 957

ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS'

alter system set UNDO_TABLESPACE='UNDOTBR' scope=spfile;

Sql > alter system set UNDO_TABLESPACE='UNDOTBR' scope=spfile;

System altered.

Sql > create undo tablespace UNDOTBR datafile '/opt/app/oracle/oradata/orcl/undotbsR.dbf' size 500m autoextend on ;

create undo tablespace UNDOTBR datafile '/opt/app/oracle/oradata/orcl/undotbR.dbf' size 500m autoextend on

ERROR at line 1:

ORA-01109: database not open

Sql > select * from dba_rollback_segs;

SEGMENT_NAME OWNER TABLESPACE_NAME SEGMENT_ID FILE_ID BLOCK_ID INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE STATUS

------------------------------ ------ ------------------------------ ---------- ---------- ---------- -------------- ----------- ----------- ----------- ------------ ----------------

INSTANCE_NUM RELATIVE_FNO

---------------------------------------- ------------

SYSTEM SYS SYSTEM 0 1 128 114688 57344 1 32765 ONLINE

_SYSSMU1_3780397527$ PUBLIC UNDOTBS1 1 3 128 131072 65536 2 32765 NEEDS RECOVERY

_SYSSMU2_2232571081$ PUBLIC UNDOTBS1 2 3 144 131072 65536 2 32765 NEEDS RECOVERY

_SYSSMU3_2097677531$ PUBLIC UNDOTBS1 3 3 160 131072 65536 2 32765 NEEDS RECOVERY

_SYSSMU4_1152005954$ PUBLIC UNDOTBS1 4 3 176 131072 65536 2 32765 NEEDS RECOVERY

_SYSSMU5_1527469038$ PUBLIC UNDOTBS1 5 3 192 131072 65536 2 32765 NEEDS RECOVERY

_SYSSMU6_2443381498$ PUBLIC UNDOTBS1 6 3 208 131072 65536 2 32765 NEEDS RECOVERY

_SYSSMU7_3286610060$ PUBLIC UNDOTBS1 7 3 224 131072 65536 2 32765 NEEDS RECOVERY

_SYSSMU8_2012382730$ PUBLIC UNDOTBS1 8 3 240 131072 65536 2 32765 NEEDS RECOVERY

_SYSSMU9_1424341975$ PUBLIC UNDOTBS1 9 3 256 131072 65536 2 32765 NEEDS RECOVERY

_SYSSMU10_3550978943$ PUBLIC UNDOTBS1 10 3 272 131072 65536 2 32765 NEEDS RECOVERY

Sql > select tablespace_name,status from dba_tablespaces ;

TABLESPACE_NAME STATUS

------------------------------ ---------

SYSTEM ONLINE

SYSAUX ONLINE

UNDOTBS1 ONLINE

TEMP ONLINE

USERS ONLINE

EXAMPLE ONLINE

ANUJTEST ONLINE

TSAPEXF ONLINE

TSAPEXU ONLINE

TEST ONLINE

RMAN ONLINE

DROP1 ONLINE

PERFSTAT ONLINE

13 rows selected.

Sql > select file#,status from v$datafile;

FILE# STATUS

---------- -------

1 SYSTEM

2 ONLINE

3 RECOVER

4 ONLINE

5 ONLINE

6 ONLINE

7 ONLINE

8 ONLINE

9 ONLINE

10 ONLINE

11 ONLINE

12 ONLINE

12 rows selected.

Sql > select segment_name, tablespace_name, initial_extent,status from dba_rollback_segs;

SEGMENT_NAME TABLESPACE_NAME INITIAL_EXTENT STATUS

------------------------------ ------------------------------ -------------- ----------------

SYSTEM SYSTEM 114688 ONLINE

_SYSSMU10_3550978943$ UNDOTBS1 131072 NEEDS RECOVERY

_SYSSMU9_1424341975$ UNDOTBS1 131072 NEEDS RECOVERY

_SYSSMU8_2012382730$ UNDOTBS1 131072 NEEDS RECOVERY

_SYSSMU7_3286610060$ UNDOTBS1 131072 NEEDS RECOVERY

_SYSSMU6_2443381498$ UNDOTBS1 131072 NEEDS RECOVERY

_SYSSMU5_1527469038$ UNDOTBS1 131072 NEEDS RECOVERY

_SYSSMU4_1152005954$ UNDOTBS1 131072 NEEDS RECOVERY

_SYSSMU3_2097677531$ UNDOTBS1 131072 NEEDS RECOVERY

_SYSSMU2_2232571081$ UNDOTBS1 131072 NEEDS RECOVERY

_SYSSMU1_3780397527$ UNDOTBS1 131072 NEEDS RECOVERY

11 rows selected.

select segment_name,status,tablespace_name from dba_rollback_segs where status not in ('ONLINE','OFFLINE') ;

Select status ,name,file# from v$datafile where ts# in (Select ts# from v$tablespace where name='UNDOTBS1');

col NAME format a70

Select status ,name,file# from v$datafile where ts# in (Select ts# from v$tablespace where name='UNDOTBS1')

STATUS NAME FILE#

------- ---------------------------------------------------------------------- ----------

RECOVER /opt/app/oracle/oradata/orcl/undotbs01.dbf 3

Select checkpoint_change# from v$datafile_header where file_id=3

DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS;

Sql > DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS;

DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS'

ORA-06512: at line 957

ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS'

Sql > show parameters rollback

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

fast_start_parallel_rollback string LOW

rollback_segments string

transactions_per_rollback_segment integer 5

Sql > show parameters undo

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

undo_management string MANUAL

undo_retention integer 900

undo_tablespace string UNDOTBR

Sql > alter system set undo_management=auto scope=spfile;

System altered.

Sql > alter system set undo_management=manual scope=spfile;

System altered.

Sql > startup force;

_corrupted_rollback_segments=('_SYSSMU10_3550978943$','_SYSSMU9_1424341975$','_SYSSMU8_2012382730$','_SYSSMU7_3286610060$','_SYSSMU6_2443381498$','_SYSSMU5_1527469038$','_SYSSMU4_1152005954$','_SYSSMU3_2097677531$','_SYSSMU2_2232571081$','_SYSSMU1_3780397527$')

Sql > create pfile='/tmp/anuj.txt' from spfile;

File created.

Sql > ed /tmp/anuj.txt

drop rollback segment "_SYSSMU10_3550978943$";

create undo tablespace UNDOTBR datafile '/opt/app/oracle/oradata/orcl/undotbR.dbf' size 500m autoextend on ;

drop tablespace UNDOTBS1

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS'

ORA-06512: at line 957

ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS'

Sql > select segment_name, status, tablespace_name from dba_rollback_segs where status='NEEDS RECOVERY';

SEGMENT_NAME STATUS TABLESPACE_NAME

------------------------------ ---------------- ------------------------------

_SYSSMU1_3780397527$ NEEDS RECOVERY UNDOTBS1

_SYSSMU2_2232571081$ NEEDS RECOVERY UNDOTBS1

_SYSSMU3_2097677531$ NEEDS RECOVERY UNDOTBS1

_SYSSMU4_1152005954$ NEEDS RECOVERY UNDOTBS1

_SYSSMU5_1527469038$ NEEDS RECOVERY UNDOTBS1

_SYSSMU6_2443381498$ NEEDS RECOVERY UNDOTBS1

_SYSSMU7_3286610060$ NEEDS RECOVERY UNDOTBS1

_SYSSMU8_2012382730$ NEEDS RECOVERY UNDOTBS1

_SYSSMU9_1424341975$ NEEDS RECOVERY UNDOTBS1

_SYSSMU10_3550978943$ NEEDS RECOVERY UNDOTBS1

10 rows selected.

drop undo segment "_SYSSMU1_3780397527$";

drop rollback segment "_SYSSMU1_3780397527$";

drop tablespace UNDOTBS1 including contents and datafiles;

ALTER TABLESPACE UNDOTBS1 offline ;

ALTER SYSTEM SET undo_tablespace='UNDOTBR' ;

startup pfile='/tmp/anuj.txt' ;

CREATE ROLLBACK SEGMENT rbs_dummy

TABLESPACE system

STORAGE (INITIAL 10k NEXT 10k MINEXTENTS 2);

_corrupted_rollback_segments =('_SYSSMU1$','_SYSSMU2$','_SYSSMU3$','_SYSSMU4$',

'_SYSSMU5$','_SYSSMU6$','_SYSSMU7$','_SYSSMU8$','_SYSSMU9$','_SYSSMU10$')

_OFFLINE_ROLLBACK_SEGMENTS=('_SYSSMU1$','_SYSSMU2$','_SYSSMU3$','_SYSSMU4$','_SYSSMU5$','_SYSSMU6$','_S

if you get this error the set

*._system_trig_enabled=false

Sql > DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;

DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS'

ORA-06512: at line 957

ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS'

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

orcl.__db_cache_size=205520896

orcl.__java_pool_size=4194304

orcl.__large_pool_size=4194304

orcl.__oracle_base='/opt/app/oracle'#ORACLE_BASE set from environment

orcl.__pga_aggregate_target=293601280

orcl.__sga_target=549453824

orcl.__shared_io_pool_size=0

orcl.__shared_pool_size=322961408

orcl.__streams_pool_size=4194304

*.audit_file_dest='/opt/app/oracle/admin/orcl/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='/opt/app/oracle/oradata/orcl/control01.ctl','/opt/app/oracle/flash_recovery_area/orcl/control02.ctl'

*.db_block_size=8192

*.db_domain='XXXXX.co.uk'

*.db_name='orcl'

*.db_recovery_file_dest_size=4070572032

*.db_recovery_file_dest=''

*.diagnostic_dest='/opt/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'

*.event='10468 trace name context forever, level 4'

*.fast_start_mttr_target=30

*.log_archive_dest='/opt/app/oracle/admin/arch'

*.memory_target=843055104

*.open_cursors=300

*.processes=150

*.recyclebin='OFF'

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace=UNDOTBS2

*.undo_management='MANUAL'

*._offline_rollback_segments=('_SYSSMU10_3550978943$','_SYSSMU9_1424341975$','_SYSSMU8_2012382730$','_SYSSMU7_3286610060$','_SYSSMU6_2443381498$','_SYSSMU5_1527469038$','_SYSSMU4_1152005954$','_SYSSMU3_2097677531$','_SYSSMU2_2232571081$','_SYSSMU1_3780397527$')

*._corrupted_rollback_segments=('_SYSSMU10_3550978943$','_SYSSMU9_1424341975$','_SYSSMU8_2012382730$','_SYSSMU7_3286610060$','_SYSSMU6_2443381498$','_SYSSMU5_1527469038$','_SYSSMU4_1152005954$','_SYSSMU3_2097677531$','_SYSSMU2_2232571081$','_SYSSMU1_3780397527$')

*._system_trig_enabled=false

Sql > startup pfile='/tmp/anuj.txt' ;

ORACLE instance started.

Total System Global Area 839282688 bytes

Fixed Size 2217992 bytes

Variable Size 629147640 bytes

Database Buffers 205520896 bytes

Redo Buffers 2396160 bytes

Database mounted.

Database opened.

Sql > drop rollback segment "_SYSSMU1_3780397527$" ;

Rollback segment dropped.

Sql > drop rollback segment "_SYSSMU2_2232571081$";

Rollback segment dropped.

Sql > drop rollback segment "_SYSSMU3_2097677531$" ;

Rollback segment dropped.

Sql > drop rollback segment "_SYSSMU4_1152005954$" ;

Rollback segment dropped.

Sql > drop rollback segment "_SYSSMU5_1527469038$" ;

Rollback segment dropped.

Sql > drop rollback segment "_SYSSMU6_2443381498$" ;

Rollback segment dropped.

Sql > drop rollback segment "_SYSSMU7_3286610060$" ;

Rollback segment dropped.

Sql > drop rollback segment "_SYSSMU8_2012382730$" ;

Rollback segment dropped.

Sql > drop rollback segment "_SYSSMU9_1424341975$" ;

Rollback segment dropped.

Sql > drop rollback segment "_SYSSMU10_3550978943$" ;

Rollback segment dropped.

Sql > select segment_name, status, tablespace_name from dba_rollback_segs where status='NEEDS RECOVERY';

View Description

V$UNDOSTAT Contains statistics for monitoring and tuning undo space. Use this view to help estimate the amount of undo

space required for the current workload. Oracle uses this view information to tune undo usage in the system.

V$ROLLSTAT For automatic undo management mode, information reflects behavior of the undo segments in the undo tablespace.

V$TRANSACTION Contains undo segment information.

DBA_UNDO_EXTENTS Shows the status and size of each extent in the undo tablespace.

WRH$_UNDOSTAT Contains statistical snapshots of V$UNDOSTAT information.

WRH$_ROLLSTAT Contains statistical snapshots of V$ROLLSTAT information.

Undo Space = UNDO_RETENTION in seconds * undo blocks for each second + overhead

My alias

alias alias alias +='pushd .' alias -='popd' alias ..='cd ..' alias ...='cd ../..' alias apt-win1='rdesktop -T "apt-winsvr-01 Production" -g 1024x768 apt-winsvr-01' alias aptwin='rdesktop -T "apt-win-01 Production" -g 1024x768 apt-win-01' alias beep='echo -en "\007"' alias cd..='cd ..' alias dir='ls -l | grep '\''^d'\''' alias l='ls -alF' alias la='ls -la' alias ll='ls -l' alias ls='ls $LS_OPTIONS' alias ls-l='ls -l' alias lsd='ls -l | grep '\''^d'\''' alias md='mkdir -p' alias mv='mv -i' alias o='less' alias rd='rmdir' alias rdpapt1='rdesktop -T "Aptus01 Production" -g 1024x768 aptus01' alias rdpapt2='rdesktop -T "Aptus02 Production" -g 1024x768 aptus02' alias rdpapt3='rdesktop -T "Aptus02 Production" -g 1270x950 aptus02' alias rdpcc='rdesktop -T "Windows Mangment - Prod" -g 1024x768 lon-cccwinmgt-01' alias rdpccdr='rdesktop -T "Windows Mangment - Prod" -g 1024x768 lon-cccwinmgt-01dr' alias rdpelmerk='rdesktop -T "Elmerk System" -g 1270x950 66.162.210.164' alias rdpelmerk-int='rdesktop -T "Elmerk Internal System" -g 1270x950 aptvm-elmerk1' alias rdplam='rdesktop -T "Lamentations" -g 1270x950 lamentations' alias rdppublic='rdesktop -T " Public Workstation" -g 1270x950 APT-HUB6430CYD' alias rehash='hash -r' alias rm='rm -i' alias sshcc='ssh -X anujs@lon-cccsolmgt-01' alias sshccdr='ssh -X anujs@lon-cccsolmgt-01dr' alias sshdan='ssh -X root@daniel' alias sshhose='ssh -X root@hosea' alias sshjerm='ssh -X root@jeremiah' alias sshjonah='ssh -X root@jonah' alias sshjosh='ssh -X root@joshua' alias sshjudge='ssh -X root@judges' alias sshlev='ssh -X root@leviticus' alias sshnahum='ssh -X root@nahum' alias sshnovag='ssh -X root@novagenesis' alias sshnumbers='ssh -X root@numbers' alias sshprov='ssh -X root@proverbs' alias sshsol='ssh -X root@solomon' alias sshtest='ssh -X root@joshua-test' alias sshwoogie='ssh -X marko@mail.woogie.nu' alias unmount='echo "Error: Try the command: umount" 1>&2; false' alias you='if test "$EUID" = 0 ; then /sbin/yast2 online_update ; else su - -c "/sbin/yast2 online_update" ; fi'

DBMS_REPAIR SCRIPT (Doc ID 556733.1)

BEGIN
DBMS_REPAIR.ADMIN_TABLES (
TABLE_NAME => 'REPAIR_TABLE',
TABLE_TYPE => dbms_repair.repair_table,
ACTION => dbms_repair.create_action,
TABLESPACE => '&tablespace_name');
END;
/

set serveroutput on
DECLARE num_corrupt INT;
BEGIN
num_corrupt := 0;
DBMS_REPAIR.CHECK_OBJECT (
SCHEMA_NAME => 'SCOTT',
OBJECT_NAME => 'EMP',
REPAIR_TABLE_NAME => 'REPAIR_TABLE',
corrupt_count => num_corrupt);
DBMS_OUTPUT.PUT_LINE('number corrupt: ' || TO_CHAR (num_corrupt));
END;
/

set serveroutput on
DECLARE num_corrupt INT;
BEGIN
num_corrupt := 0;
DBMS_REPAIR.CHECK_OBJECT (
SCHEMA_NAME => 'SCOTT',
OBJECT_NAME => 'EMP',
REPAIR_TABLE_NAME => 'REPAIR_TABLE',
corrupt_count => num_corrupt);
DBMS_OUTPUT.PUT_LINE('number corrupt: ' || TO_CHAR (num_corrupt));
END;
/
number corrupt: 0

PL/SQL procedure successfully completed.

REM Optionally display any corrupted block identified by check_object:

select BLOCK_ID, CORRUPT_TYPE, CORRUPT_DESCRIPTION
from REPAIR_TABLE;

REM Mark the identified blocks as corrupted

DECLARE num_fix INT;
BEGIN
num_fix := 0;
DBMS_REPAIR.FIX_CORRUPT_BLOCKS (
SCHEMA_NAME => '&schema_name',
OBJECT_NAME=> '&object_name',
OBJECT_TYPE => dbms_repair.table_object,
REPAIR_TABLE_NAME => 'REPAIR_TABLE',
FIX_COUNT=> num_fix);
DBMS_OUTPUT.PUT_LINE('num fix: ' || to_char(num_fix));
END;
/

REM Allow future DML statements to skip the corrupted blocks:

BEGIN
DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (
SCHEMA_NAME => '&schema_name',
OBJECT_NAME => '&object_name',
OBJECT_TYPE => dbms_repair.table_object,
FLAGS => dbms_repair.SKIP_FLAG);
END;
/

session_wait_history.sql

SET TIME ON VERIFY OFF

COLUMN event FORMAT A30 HEADING 'Event' TRUNCATE

COLUMN wait_state FORMAT A7 HEADING 'State'

COLUMN seconds_in_wait FORMAT 999999 HEADING 'Time|Waiting'

COLUMN wait_time FORMAT 999999 HEADING 'Time|Waited'

COLUMN p1 FORMAT A20 HEADING 'P1' TRUNCATE

COLUMN p2 FORMAT A20 HEADING 'P2' TRUNCATE

COLUMN p3 FORMAT A20 HEADING 'P3' TRUNCATE

SELECT vswh.event,

CASE WHEN vswh.state = 'WAITING' THEN vswh.state

ELSE 'WAITED'

END wait_state,

vswh.seconds_in_wait,

vswh.wait_time,

DECODE(vswh.p1text, NULL, NULL, vswh.p1text||'='||vswh.p1) p1,

DECODE(vswh.p2text, NULL, NULL, vswh.p2text||'='||vswh.p2) p2,

DECODE(vswh.p3text, NULL, NULL, vswh.p3text||'='||vswh.p3) p3

FROM gv$session_wait_history vswh

WHERE vswh.sid = &&user_sid

ORDER BY vswh.seq# DESC

UNDEFINE user_sid

Anuj Singh Oracle DBA

Search This Blog

Total Pageviews

Wednesday, 8 December 2021