Home » RDBMS Server » Server Administration » how to find all undo tablespace (from list of dba_tablespaces)
how to find all undo tablespace [message #335278] Mon, 21 July 2008 12:44 Go to next message
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 #335280 is a reply to message #335278] Mon, 21 July 2008 12:51 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above


select view_name from dba_views where view_name like '%UNDO%';
does output from SQL above give you any clues?
Re: how to find all undo tablespace [message #335284 is a reply to message #335278] Mon, 21 July 2008 12:53 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
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

[Updated on: Mon, 21 July 2008 12:54]

Report message to a moderator

Re: how to find all undo tablespace [message #335287 is a reply to message #335280] Mon, 21 July 2008 13:06 Go to previous messageGo to next message
IT Guru
Messages: 59
Registered: January 2007
Member
Thanks.


But still not able to get solution.

anacedent wrote on Mon, 21 July 2008 12:51
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above


select view_name from dba_views where view_name like '%UNDO%';
does output from SQL above give you any clues?

Re: how to find all undo tablespace [message #335288 is a reply to message #335287] Mon, 21 July 2008 13:07 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> But still not able to get solution.
Only clues are given.
You can figure the solution.
Re: how to find all undo tablespace [message #335289 is a reply to message #335278] Mon, 21 July 2008 13:14 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
>But still not able to get solution.
Then I wonder how much damage you could do with the answer.
Re: how to find all undo tablespace [message #335290 is a reply to message #335284] Mon, 21 July 2008 13:14 Go to previous messageGo to next message
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 #335291 is a reply to message #335278] Mon, 21 July 2008 13:17 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
What happens when instance has no UNDO?

[Updated on: Mon, 21 July 2008 13:19] by Moderator

Report message to a moderator

Re: how to find all undo tablespace [message #335346 is a reply to message #335291] Mon, 21 July 2008 22:47 Go to previous messageGo to next message
IT Guru
Messages: 59
Registered: January 2007
Member
anacedent wrote on Mon, 21 July 2008 13:17
What happens when instance has no UNDO?

it use system tablespace only.
Re: how to find all undo tablespace [message #335347 is a reply to message #335278] Mon, 21 July 2008 22:48 Go to previous messageGo to next message
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 #335348 is a reply to message #335278] Mon, 21 July 2008 22:53 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
>it use system tablespace only.
Not necessarily.
UNDO segments replaced what type of segments?
Re: how to find all undo tablespace [message #335352 is a reply to message #335348] Mon, 21 July 2008 23:04 Go to previous messageGo to next message
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
Re: how to find all undo tablespace [message #335375 is a reply to message #335278] Tue, 22 July 2008 00:42 Go to previous message
IT Guru
Messages: 59
Registered: January 2007
Member
Got resolve.


previously 8i catlog.sql/catproc.sql was run.

run 9i ones.

Working fine now.

Can close this thread.







Previous Topic: how to find instance is started with which pfile
Next Topic: lost roles
Goto Forum:
  


Current Time: Wed Dec 07 06:34:53 CST 2016

Total time taken to generate the page: 0.27620 seconds