Home » RDBMS Server » Server Administration » undo_tablespace - rollback segment OFFLINE
undo_tablespace - rollback segment OFFLINE [message #229395] Sat, 07 April 2007 05:17 Go to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi,

I have set undo_management and undo_tablespace. When I query dba_rollback_segs, some segments are ONLINE and some are OFFLINE. Is it normal behavior?

SQL> select name, value from v$parameter where name like 'undo_tablespace';

NAME VALUE
------------------------- ------------------------------
undo_tablespace SYS_UNDOTS

SQL> select name, value from v$parameter where name like 'undo_management';

NAME VALUE
------------------------- ------------------------------
undo_management AUTO

SQL>

1 select owner, segment_name, tablespace_name, status from dba_rollback_segs
2* where tablespace_name = 'SYS_UNDOTS'
SQL> /

OWNER SEGMENT_NAME TABLESPACE_NAME STATUS
------ ------------------------------ ------------------------------ ----------------
PUBLIC _SYSSMU1$ SYS_UNDOTS ONLINE
PUBLIC _SYSSMU2$ SYS_UNDOTS ONLINE
PUBLIC _SYSSMU3$ SYS_UNDOTS ONLINE
PUBLIC _SYSSMU4$ SYS_UNDOTS ONLINE
PUBLIC _SYSSMU5$ SYS_UNDOTS ONLINE
PUBLIC _SYSSMU6$ SYS_UNDOTS ONLINE
PUBLIC _SYSSMU7$ SYS_UNDOTS ONLINE
PUBLIC _SYSSMU8$ SYS_UNDOTS ONLINE
PUBLIC _SYSSMU9$ SYS_UNDOTS ONLINE
PUBLIC _SYSSMU10$ SYS_UNDOTS ONLINE
PUBLIC _SYSSMU21$ SYS_UNDOTS OFFLINE
PUBLIC _SYSSMU22$ SYS_UNDOTS OFFLINE
PUBLIC _SYSSMU23$ SYS_UNDOTS OFFLINE
PUBLIC _SYSSMU24$ SYS_UNDOTS OFFLINE
PUBLIC _SYSSMU25$ SYS_UNDOTS OFFLINE
PUBLIC _SYSSMU26$ SYS_UNDOTS OFFLINE
PUBLIC _SYSSMU27$ SYS_UNDOTS OFFLINE
PUBLIC _SYSSMU28$ SYS_UNDOTS OFFLINE
PUBLIC _SYSSMU29$ SYS_UNDOTS OFFLINE
PUBLIC _SYSSMU30$ SYS_UNDOTS OFFLINE
PUBLIC _SYSSMU31$ SYS_UNDOTS OFFLINE
PUBLIC _SYSSMU32$ SYS_UNDOTS OFFLINE
PUBLIC _SYSSMU33$ SYS_UNDOTS OFFLINE

23 rows selected.

Brayan

Report message to a moderator

Re: undo_tablespace - rollback segment OFFLINE [message #229461 is a reply to message #229395] Sun, 08 April 2007 07:39 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member
Post Deleted.

[Updated on: Sun, 08 April 2007 08:32]

Report message to a moderator

Re: undo_tablespace - rollback segment OFFLINE [message #229470 is a reply to message #229395] Sun, 08 April 2007 13:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Do you have several undo tablespaces?
Did you switch to another one?
Post the result of:
select owner, segment_name, tablespace_name, status 
from dba_rollback_segs
where tablespace_name in (
  select tablespace_name from dba_tablespaces where contents='UNDO'
)
/

Did you switch to manual?
Post the result of "show parameter undo".

Regards
Michel

[add the 2 last lines]

[Updated on: Sun, 08 April 2007 13:22]

Report message to a moderator

Re: undo_tablespace - rollback segment OFFLINE [message #229584 is a reply to message #229470] Mon, 09 April 2007 04:53 Go to previous messageGo to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi Michel,

>Do you have several undo tablespaces?
Yes

>Did you switch to another one?
May be yes. This is the setup before I joined.

>select owner, segment_name, tablespace_name, status
>from dba_rollback_segs
>where tablespace_name in ( select tablespace_name from >dba_tablespaces where contents='UNDO');

OWNER SEGMENT_NAME TABLESPACE_NAME STATUS
------ --------------- --------------- ----------
PUBLIC _SYSSMU1$ SYS_UNDOTS ONLINE
PUBLIC _SYSSMU2$ SYS_UNDOTS ONLINE
PUBLIC _SYSSMU3$ SYS_UNDOTS ONLINE
PUBLIC _SYSSMU4$ SYS_UNDOTS ONLINE
PUBLIC _SYSSMU5$ SYS_UNDOTS ONLINE
PUBLIC _SYSSMU6$ SYS_UNDOTS ONLINE
PUBLIC _SYSSMU7$ SYS_UNDOTS ONLINE
PUBLIC _SYSSMU8$ SYS_UNDOTS ONLINE
PUBLIC _SYSSMU9$ SYS_UNDOTS ONLINE
PUBLIC _SYSSMU10$ SYS_UNDOTS ONLINE
PUBLIC _SYSSMU21$ SYS_UNDOTS OFFLINE
PUBLIC _SYSSMU22$ SYS_UNDOTS OFFLINE
PUBLIC _SYSSMU23$ SYS_UNDOTS OFFLINE
PUBLIC _SYSSMU24$ SYS_UNDOTS OFFLINE
PUBLIC _SYSSMU25$ SYS_UNDOTS OFFLINE
PUBLIC _SYSSMU26$ SYS_UNDOTS OFFLINE
PUBLIC _SYSSMU27$ SYS_UNDOTS OFFLINE
PUBLIC _SYSSMU28$ SYS_UNDOTS OFFLINE
PUBLIC _SYSSMU29$ SYS_UNDOTS OFFLINE
PUBLIC _SYSSMU30$ SYS_UNDOTS OFFLINE
PUBLIC _SYSSMU31$ SYS_UNDOTS OFFLINE
PUBLIC _SYSSMU32$ SYS_UNDOTS OFFLINE
PUBLIC _SYSSMU33$ SYS_UNDOTS OFFLINE
PUBLIC _SYSSMU11$ ROLLBACK_TS OFFLINE
PUBLIC _SYSSMU12$ ROLLBACK_TS OFFLINE
PUBLIC _SYSSMU13$ ROLLBACK_TS OFFLINE
PUBLIC _SYSSMU14$ ROLLBACK_TS OFFLINE
PUBLIC _SYSSMU15$ ROLLBACK_TS OFFLINE
PUBLIC _SYSSMU16$ ROLLBACK_TS OFFLINE
PUBLIC _SYSSMU17$ ROLLBACK_TS OFFLINE
PUBLIC _SYSSMU18$ ROLLBACK_TS OFFLINE
PUBLIC _SYSSMU19$ ROLLBACK_TS OFFLINE
PUBLIC _SYSSMU20$ ROLLBACK_TS OFFLINE


>Did you switch to manual?
No

>Post the result of "show parameter undo".

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_suppress_errors boolean FALSE
undo_tablespace string SYS_UNDOTS


Regards,
Ronald.

Report message to a moderator

Re: undo_tablespace - rollback segment OFFLINE [message #229585 is a reply to message #229584] Mon, 09 April 2007 05:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
So, if you were on SYS_UNDOTS (default at startup), then switch to ROLLBACK_TS and switch back to SYS_UNDOTS, you can have what you have.
It will be cleaned at the next startup (if clean shutdown that is not abort).

Regards
Michel

Report message to a moderator

Re: undo_tablespace - rollback segment OFFLINE [message #229651 is a reply to message #229585] Mon, 09 April 2007 08:36 Go to previous message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi,

I forgot to mention that, Inspite of bouncing the DB rollback segments remains OFFLINE.

Regards,
Ronald.

Report message to a moderator

Previous Topic: ORA-04031: unable to allocate 16384 bytes
Next Topic: Need help with these interview questions.
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Dec 03 17:06:01 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.