Oracle-L: RE: undo tablespace and rollback segments in oracle9i

From: Kirtikumar Deshpande <kirtikumar_deshpande_at_yahoo.com>
Date: Wed, 30 Jul 2003 08:14:23 -0800
Message-ID: <F001.005C7D91.20030730081423@fatcity.com>

Your attachment was removed by the list server. However, your direct e-mail to me had the following:

SQL> show parameter undo ;

NAME                                 TYPE        VALUE                          
------------------------------------ ----------- ------------------------------ 
undo_management                      string      MANUAL                         
undo_retention                       integer     900                            
undo_suppress_errors                 boolean     FALSE                          
undo_tablespace                      string      rbs

SQL>
SQL> select segment_name, tablespace_name from dba_rollback_segs;

SEGMENT_NAME                   TABLESPACE_NAME                                  
------------------------------ ------------------------------                   
SYSTEM                         SYSTEM                                           
_SYSSMU1$                      RBS                                              
_SYSSMU2$                      RBS                                              
_SYSSMU3$                      RBS                                              
_SYSSMU4$                      RBS                                              
_SYSSMU5$                      RBS                                              
_SYSSMU6$                      RBS                                              
_SYSSMU7$                      RBS                                              
_SYSSMU8$                      RBS                                              
_SYSSMU9$                      RBS                                              
_SYSSMU10$                     RBS                                              

SEGMENT_NAME                   TABLESPACE_NAME                                  
------------------------------ ------------------------------                   
RBS01                          RBS                                              
RBS02                          RBS                                              
RBS03                          RBS                                              
RBS04                          RBS                                              
RBS05                          RBS                                              
RBS06                          RBS

17 rows selected.

SQL> select a.usn, a.name, b.status
  2 from v$rollname a,
  3 v$rollstat b
  4 where a.usn = b.usn;

       USN NAME                           STATUS                                
---------- ------------------------------ ---------------                       
         0 SYSTEM                         ONLINE                                
        11 RBS01                          ONLINE                                
        12 RBS02                          ONLINE                                
        13 RBS03                          ONLINE                                
        14 RBS04                          ONLINE                                
        15 RBS05                          ONLINE                                
        16 RBS06                          ONLINE

7 rows selected.

So, it is clear that RBSnn were created in an undo tablespace titled RBS.

To remove system managed undo segments (_SYSSMUn$) one must drop the undo tablespace itself.

But, in your case, you will first have to: 1. Create a new, normal tablespace, say, rollback_ts, to hold new rollback segments (do not create it using "create undo tablespace" )

2. Create required rollback segments (rbs07, rbs08 etc) in rollback_ts. 
3. Bring them all online.
4. Offline RBS01 to RBS06 and drop them. 
5. Drop tablespace RBS.

Modify init.ora parameter as required.

Hope this helps.

Kirti
- A.Bahar_at_billing-components.com wrote:
  > hi ,
  > please look attached file ;
  >
  >
  >
  > -----Original Message-----
  > Sent: Wednesday, July 30, 2003 3:52 PM
  > To: ORACLE-L_at_fatcity.com
  > Cc: Bahar, Arslan
  >
  >
  > Your question is not very clear. Sorry.
  >
  > Can you please post results of following commands?
  >
  > Thanks.
  >
  > - Kirti
  >
  > SQL> show parameter undo
  >
  > SQL> select segment_name, tablespace_name from dba_rollback_segs;
  >
  > SQL> select a.usn, a.name, b.status
  > 2 from v$rollname a,
  > 3 v$rollstat b
  > 4 where a.usn = b.usn;
  >
  >
  >
  > --- A.Bahar_at_billing-components.com wrote:
  >
  >

Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Kirtikumar Deshpande
  INET: kirtikumar_deshpande_at_yahoo.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Wed Jul 30 2003 - 11:14:23 CDT