Skip to content
pvmehta.com

pvmehta.com

  • Home
  • About Me
  • Toggle search form
  • AWR settings- MMON is not taking snapshot. Oracle
  • ORA-3136 Oracle
  • tblwopk.sql tablewopk.sql Oracle
  • Oracle Metalink useful notes Oracle
  • Ports used by Oracle Software Oracle
  • sql_plan9i.sql Oracle
  • Locally Managed Tablespace and Dictionary managed tablespace (LMT-DMT) Oracle
  • Absolute file number and relative file number Oracle
  • catting.sh Linux/Unix
  • Zip and unzip with tar Linux/Unix
  • OEM-troubleshooting on 20-MAY-08 Oracle
  • Metalink Note: Note:250655.1 : ADDM Basics USING THE AUTOMATIC DATABASE DIAGNOSTIC MONITOR Oracle
  • Follwoing korn shell is used to move files from source folder to destination folder and gzip it in destination folder. mv_iotto.ksh Linux/Unix
  • online_bkup.sql Oracle
  • Drop database in Oracle 10g Oracle

V$ROLLSTAT status is Full

Posted on 13-May-2008 By Admin No Comments on V$ROLLSTAT status is Full

Problem Description:

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

You receive the following error:

ORA-01562: “failed to extend rollback segment number %s”

Cause: Failure occurred when trying to extend rollback segment

Action: This is normally followed by another error message that caused

the failure. You may take the rollback segment offline to

perform maintainence. Use the alter rollback segment offline

command to take the rollback segment offline.

You check the “v$rollstat” view to find the status of rollback segment is

listed as FULL. What does this mean?

Solution Description:

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

A rollback segment is marked as FULL when an error ORA-1562 is signalled.

When the rollback segment is marked with a status of FULL, no new transactions

will use the rollback segment. The FULL status is intended to signal

transactions to use other valid rollback segments. All transactions in

that rollback segment must complete before the full status is cleared. This

means that all transactions must either be committed or rolled back.

If it is not possible to commit every transaction, possible workarounds are:

a. Taking the rollback segment offline and bringing back online should also

clear the full status. This could potentially take awhile if there are a

large number of transaction that need to be rolled back.

Alter rollback segment offline;

Alter rollback segment online;

b. Adding a datafile to the rollback segment tablespace so that the segment

can extend. You can then continue with the *current* txn (that caused the

rollback segment to get status FULL) and the status will then get reset to

ONLINE.

If you are unable to wait for the full status to be cleared, then bouncing

the database will clear the rollback segment.

Possible workaround is to use set transaction. This is supposed to override

the full flag and still use the full rollback segment.

Set transaction use rollback segment ;

Explanation:

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

The full status on a rollback segment is cleared when all transactions in the

rollback segment are completed (committed or rolled back) and it is determined

that the segment can wrap. It is also cleared if the number of active

transactions in the rollback segment is zero.

Oracle, SQL scripts

Post navigation

Previous Post: Rman Notes -1
Next Post: OEM-Commnds

Related Posts

  • Disbaling DBA_SCHEDULER_JOBS Oracle
  • Very Good Oralce Internal Tuning Book Oracle
  • Histogram information Oracle
  • Default User Profile Oracle
  • find_open_cur.sql Find open cursorts per session Oracle
  • How to choose Driver table in SQL statement Oracle

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Categories

  • AWS (2)
  • Azure (1)
  • Linux/Unix (149)
  • Oracle (392)
  • PHP/MYSQL/Wordpress (10)
  • Power-BI (0)
  • Python/PySpark (7)
  • RAC (17)
  • rman-dataguard (26)
  • shell (149)
  • SQL scripts (341)
  • Uncategorized (0)
  • Videos (0)

Recent Posts

  • load SPM baseline from cursor cache05-Jun-2025
  • Drop all SPM baselines for SQL handle05-Jun-2025
  • Load SPM baseline from AWR05-Jun-2025
  • Drop specific SQL plan baseline – spm05-Jun-2025
  • findinfo.sql (SQL for getting CPU and Active session info)27-May-2025
  • SQL Tracker by SID sqltrackerbysid.sql22-Apr-2025
  • How to connect to Oracle Database with Wallet with Python.21-Mar-2025
  • JSON/XML Types in Oracle18-Mar-2025
  • CPU Core related projections12-Mar-2025
  • Exadata Basics10-Dec-2024

Archives

  • 2025
  • 2024
  • 2023
  • 2010
  • 2009
  • 2008
  • 2007
  • 2006
  • 2005
  • segment_wise_space.sql segspace.sql Segment wise space usage (allocated and used) Oracle
  • Search and Replace vi editor command. Linux/Unix
  • Gather Stats manually using DBMS_STATS after disabling DBMS_SCHEDULER jobs as previous entry Oracle
  • Processes parameter and its dependencies on OS kernel parameters Linux/Unix
  • Configure ssh authentications for RAC Oracle
  • Find sort details from Db find_sort.sql Oracle
  • To seee semaphores and shared memory segments in Solaris Linux/Unix
  • oracle 11g RAC on vmware Oracle

Copyright © 2025 pvmehta.com.

Powered by PressBook News WordPress theme