Re: Unable do DROP ROLLBACK SEGEMENT

The DBA_ROLLBACK_SEGS view shows a rollback segment as "PARTLY AVAILABLE"

   if it is otherwise OFFLINE and either:

1. it contains active transactions that SMON has not yet finished recovering or
2. it contains in-doubt transactions

   In case 'a' SMON will continue to roll out any active transactions in the    rollback segment and finally mark it as 'OFFLINE'. If an error is    encountered preventing a transaction from rolling out the status is changed    to "NEEDS RECOVERY".

   In case 'b' the in-doubt transaction has to be resolved.

   It is possible to ONLINE a "PARTLY AVAILABLE" rollback segment - doing so    will change its status to "ONLINE".

To check for any active transactions on a rollback segment:

	  SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */
		 KTUXESTA Status,
		 KTUXECFL Flags
	    FROM x$ktuxe 
	   WHERE ktuxesta!='INACTIVE' 
	     AND ktuxeusn=<SEGMENT_ID>
	  ;

   If this returns rows then these transactions must commit or roll back    before the rollback segment can be considered fully recovered.    

   Possible combinations of returned values include:

    Status    Flags	  	Meaning
    ~~~~~~    ~~~~~	  	~~~~~~~
    ACTIVE    NONE (or null)	Transaction is currently active awaiting to
			        either commit or roll back.
    ACTIVE    DEAD	  	Transaction is dead and needs to be rolled out.

    PREPARED  SCO|COL|REV|DEAD 	This is part of a distributed transaction.
				The DEAD indicates it is a failed distributed
				transaction but as it is PREPARED it may need
				to commit or roll back.

   Note: If the select returns no rows do not immediately assume that the 
         rollback segment is clear of any transactions - if Oracle cannot
         see the rollback segment header then the above select will show no
         rows. If you are not sure if the rollback segment header is
	 accessible then perform these steps:

regards
Srivenu Received on Thu Mar 11 2004 - 00:02:10 CST