Home » RDBMS Server » Server Administration » undo_tablespace - rollback segment OFFLINE
undo_tablespace - rollback segment OFFLINE [message #238530] Fri, 18 May 2007 08:09 Go to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi,

HP-UX, Oracle 9.2.0.7

I have a doubt regarding the following...
SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     3600
undo_suppress_errors                 boolean     FALSE
undo_tablespace                      string      SYS_UNDOTS
SQL>
SQL> select file_id, tablespace_name, bytes/1024/1024 size_MB, status
  2  from  dba_data_files where tablespace_name = 'SYS_UNDOTS';

   FILE_ID TABLESPACE_NAME                   SIZE_MB STATUS
---------- ------------------------------ ---------- ---------
         2 SYS_UNDOTS                           2000 AVAILABLE
        31 SYS_UNDOTS                           2048 AVAILABLE
        47 SYS_UNDOTS                           2048 AVAILABLE
        87 SYS_UNDOTS                           2048 AVAILABLE
       100 SYS_UNDOTS                           2048 AVAILABLE
       101 SYS_UNDOTS                           2048 AVAILABLE
       102 SYS_UNDOTS                           4096 AVAILABLE

7 rows selected.

SQL> l
  1  SELECT b.TableSpace_Name,
  2     Tbs_Size "Total MB",
  3     a.Free_Space "Free MB",
  4     (b.Tbs_Size - a.Free_Space) "Used MB",
  5     Round((100 * (b.Tbs_Size - a.Free_Space)) / b.Tbs_Size) "Pct. Used",
  6     Round((100 * a.Free_Space) / b.Tbs_Size) "Pct. Free"
  7     FROM   (SELECT   TableSpace_Name,
  8     Round(SUM(Bytes) / 1024 / 1024,2) AS Free_Space
  9  FROM     dba_Free_Space
 10  GROUP BY TableSpace_Name) a,
 11     (SELECT   TableSpace_Name,
 12          SUM(Bytes) / 1024 / 1024 AS Tbs_Size
 13      FROM     dba_Data_Files
 14      GROUP BY TableSpace_Name
 15      UNION
 16      SELECT   TableSpace_Name,
 17          SUM(Bytes) / 1024 / 1024 Tbs_Size
 18      FROM     dba_temp_Files
 19      GROUP BY TableSpace_Name) b
 20      WHERE  a.TableSpace_Name (+)  = b.TableSpace_Name
 21*     and b.TableSpace_Name='SYS_UNDOTS'
SQL> /

TABLESPACE_NAME   Total MB    Free MB    Used MB  Pct. Used  Pct. Free
--------------- ---------- ---------- ---------- ---------- ----------
SYS_UNDOTS           16336    4551.63   11784.37         72         28

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

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

23 rows selected.

SQL>

In the above scenario, eventhough undo tablespace is 70% full, as seen in the dba_rollback_segs, rollback segments are allocated only from file 2. Furthermore, only few segments are online(I understand that, in managemet AUTO oracle will automatically make the rollback segments online/offline).

My doubt is, eventhough undo tablespace is almost full, why segments are not made ONLINE or new segments are aloocated!!.

Note: Here SYS_UNDOTS is switched from another undo-tablespace.

Brayan.

Re: undo_tablespace - rollback segment OFFLINE [message #238570 is a reply to message #238530] Fri, 18 May 2007 09:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
My doubt is, eventhough undo tablespace is almost full, why segments are not made ONLINE or new segments are aloocated!!.

Does Oracle have any need of them?
When it'll have it'll take them online.

Regards
Michel
Re: undo_tablespace - rollback segment OFFLINE [message #239398 is a reply to message #238530] Tue, 22 May 2007 04:48 Go to previous messageGo to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi Michel,

Since the undo tablespace SYS_UNDOTS was almost full, it has created segments in file 2 only(i.e. not created in 31, 47, 47,100, 101,102).

Moreover, Since it created segments in file_id 2 only, where did the rest of the undo-data stored.

Thanks in advance,

Regards,
Brayan.
Re: undo_tablespace - rollback segment OFFLINE [message #239407 is a reply to message #239398] Tue, 22 May 2007 05:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Search in dba_extents. I can't do it for you.

Regards
Michel
Re: undo_tablespace - rollback segment OFFLINE [message #239419 is a reply to message #238530] Tue, 22 May 2007 05:35 Go to previous messageGo to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi Michel,

dba_extents shows correct used space.

My doubt is not that, I'll drill down further,

SQL> select tablespace_name, file_id from dba_rollback_segs where segment_name = '_SYSSMU1$' ;

TABLESPACE_NAME    FILE_ID
--------------- ----------
SYS_UNDOTS               2

SQL> select tablespace_name, segment_name, file_id, extent_id from dba_extents where segment_name = '_SYSSMU1$' ;

TABLESPACE_NAME SEGMENT_NAME       FILE_ID  EXTENT_ID
--------------- --------------- ---------- ----------
SYS_UNDOTS      _SYSSMU1$                2          0
SYS_UNDOTS      _SYSSMU1$               31          1
SYS_UNDOTS      _SYSSMU1$               47          2
SYS_UNDOTS      _SYSSMU1$               87          3
SYS_UNDOTS      _SYSSMU1$              100          4
SYS_UNDOTS      _SYSSMU1$              101          5
SYS_UNDOTS      _SYSSMU1$              102          6
SYS_UNDOTS      _SYSSMU1$                2          7
SYS_UNDOTS      _SYSSMU1$               31          8
SYS_UNDOTS      _SYSSMU1$               47          9
SYS_UNDOTS      _SYSSMU1$               87         10
SYS_UNDOTS      _SYSSMU1$              100         11
SYS_UNDOTS      _SYSSMU1$              101         12
SYS_UNDOTS      _SYSSMU1$              101         13

14 rows selected.

SQL>


In the above case, DBA_ROLLBACK_SEGS show "_SYSSMU1$" present in file_id 2 only. But DBA_EXTENTS shows it is spread across many file_id's. Actually I do not the meaning of FILE_ID in dba_rollback_segs. Is it the starting extent present in file_id 2??

Regards,
Brayan.
Re: undo_tablespace - rollback segment OFFLINE [message #239422 is a reply to message #239419] Tue, 22 May 2007 06:07 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
dba_rollbacks_segs gives only the file_id/block_id of segment header (this is in the documentation on the view).

Regards
Michel
Previous Topic: Number of instance
Next Topic: RLM$SCHDNEGACTION
Goto Forum:
  


Current Time: Sun Dec 04 02:38:27 CST 2016

Total time taken to generate the page: 0.08161 seconds