| how to find all undo tablespace [message #335278] |
Mon, 21 July 2008 12:44  |
IT Guru
Messages: 59
Registered: January 2007
|
Member |
|
|
Oracle 9i, windows / Linux
Show parameter undo
Provide only one active undo table active undo tablespace information.
But if one has created many undo tablespace name abc , xyz etc.
Which can be listed from
select tablespace_name from dba_tablespaces
But how to find out which are undo tablespaces ?
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: how to find all undo tablespace [message #335290 is a reply to message #335284] |
Mon, 21 July 2008 13:14  |
IT Guru
Messages: 59
Registered: January 2007
|
Member |
|
|
thanks.
Yes I just tried on my home pc / windows.
it worked as I was hoping.
But when in noon had tried in office was giving PERMANENT for all tablespace. Screen shot was not taken. but will try again if get chance paste screen shot.
| Mahesh Rajendran wrote on Mon, 21 July 2008 12:53 | look into dba_tablespaces.contents
dbadmin@xxx > /
TABLESPACE_NAME STATUS CONTENTS
------------------------------ --------- ---------
SYSTEM ONLINE PERMANENT
UNDOTBS1 ONLINE UNDO
INDX ONLINE PERMANENT
TOOLS ONLINE PERMANENT
UNDOTBS2 ONLINE UNDO
|
|
|
|
|
|
|
|
|
| Re: how to find all undo tablespace [message #335347 is a reply to message #335278] |
Mon, 21 July 2008 22:48 |
IT Guru
Messages: 59
Registered: January 2007
|
Member |
|
|
Actually it should gave UNDO in contents column of dba_tablespaces
but it is giving PERMANENT value only.
Oracle 9i, Linux
SQL> create undo tablespace undotest ;
Tablespace created.
SQL> select tablespace_name , contents from dba_tablespaces ;
TABLESPACE_NAME CONTENTS
------------------------------ ---------
SYSTEM PERMANENT
SYS_UNDOTS PERMANENT
UNDOTBS PERMANENT
TEST PERMANENT
UT1 PERMANENT
U101 PERMANENT
UNDOTEST PERMANENT
7 rows selected.
SQL>
|
|
|
|
|
|
| Re: how to find all undo tablespace [message #335352 is a reply to message #335348] |
Mon, 21 July 2008 23:04 |
IT Guru
Messages: 59
Registered: January 2007
|
Member |
|
|
| anacedent wrote on Mon, 21 July 2008 22:53 | >it use system tablespace only.
Not necessarily.
UNDO segments replaced what type of segments?
|
SQL> select segment_name,tablespace_name,file_id,segment_id from dba_rollback_segs ;
SEGMENT_NAME TABLESPACE_NAME FILE_ID
------------------------------ ------------------------------ ----------
SEGMENT_ID
----------
SYSTEM SYSTEM 1
0
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
