Skip to content
pvmehta.com

pvmehta.com

  • Home
  • About Me
  • Toggle search form
  • tuning commmand for cpu, ip and memory stats Linux/Unix
  • setting prompt display with .profile Linux/Unix
  • Oracle Material from OTN Oracle
  • Giving Grant on v$DATABASE Oracle
  • Adding addidional hard drive and attach it to a linux box. Linux/Unix
  • logminer and my_lbu Oracle
  • How to see which patches are applied. Oracle
  • Oracle 11g Environment Setup Oracle
  • Remove DOS CR/LFs (^M) Linux/Unix
  • Search and replace editor command in vi Linux/Unix
  • CTAS with LONG Column for 7.x and 8 and 8i Oracle
  • To seee semaphores and shared memory segments in Solaris Linux/Unix
  • sess_server.sql Oracle
  • Changing unix system clock when Oracle database is running. Oracle
  • _B_TREE_BITMAP_PLANS issue during 8.1.7 to 9.2.0.8 upgrade 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

  • How to set Processes Parameter Oracle
  • find_pk.sql /* Find Primary Key */ Oracle
  • DBMS_SQL for alter session. Oracle
  • compile_inv.sql Oracle
  • How to Decide upto what level you can decrement your datafile size. ( Shrink Datafile) Oracle
  • Find sort details from Db find_sort.sql 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 (388)
  • PHP/MYSQL/Wordpress (10)
  • Power-BI (0)
  • Python/PySpark (7)
  • RAC (17)
  • rman-dataguard (26)
  • shell (149)
  • SQL scripts (337)
  • Uncategorized (0)
  • Videos (0)

Recent Posts

  • 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
  • Reading config file from other folder inside class24-Sep-2024
  • Python class import from different folders22-Sep-2024
  • Transfer SQL Profiles from One database to other database.05-Sep-2024
  • Load testing on Oracle 19C RAC with HammerDB18-Jan-2024

Archives

  • 2025
  • 2024
  • 2023
  • 2010
  • 2009
  • 2008
  • 2007
  • 2006
  • 2005
  • How to start CRS manually Oracle
  • on IBM-AIX for display Linux/Unix
  • Adding or Dropping Online Redo Log Files When Physical Standby in place Oracle
  • Is It Recommended To Apply Patch Bundles When PSU Is Available? -ID 743554.1 Oracle
  • pvm_metric.sql for gathering report from vmstat tables Oracle
  • SCRIPT TO LIST RECURSIVE DEPENDENCY BETWEEN OBJECTS UTLDTREE.sql Oracle
  • Generating XML from SQLPLUS Oracle
  • age_alert.ksh aging out alert.log Linux/Unix

Copyright © 2025 pvmehta.com.

Powered by PressBook News WordPress theme