Home » RDBMS Server » Server Administration » undo_tablespace - rollback segment OFFLINE
| undo_tablespace - rollback segment OFFLINE [message #238530] |
Fri, 18 May 2007 08:09  |
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 #239398 is a reply to message #238530] |
Tue, 22 May 2007 04:48  |
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 #239419 is a reply to message #238530] |
Tue, 22 May 2007 05:35 |
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.
|
|
|
|
|
|
Goto Forum:
Current Time: Mon Dec 02 12:36:46 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
