how to enable undo_management=auto after i upgrade 8i to 10g now that spfile is used?

hi,
i hv upgraded my 8i test db to 10g. I wanted to switch to 10g's undo tablespace management and i hv created the undo tablespace...but i am at a lost how to switch the upgraded database to 10g's undo management as I cant seem to find a place to set the initialisation parameter UNDO_MANAGEMENT=AUTO as 10g is not using the text init.ora anymore...and i cant set it using 'Alter system set undo_management=auto' either it returns an error....wonder if anyone could help?

many thanks

hmm what do you mean 10g does not use pfile anymore...? delete your spfile and you will see how it uses plain pfile

when you issue alter system you should add scope = BOTH

1. CREATE PFILE FROM SPFILE.

2. Edit pfile and set following
UNDO_MANAGEMENT
UNDO_TABLESPACE
UNDO_RETENTION

3. STARTUP pfile=your_initora_file

4. CREATE SPFILE FROM PFILE

Next time you can startup without specifying pfile.

HTH

pando/sanjay,

thks for the enlightenment...i gave a try...new to 10g pardon my ignorance :)

hi,

I have tried Sanhay's method and manage to get the undo initialisation parameters set as follow:

SQL> show parameters undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string undotbs_01

and i m sure that my undo ts is valid....however i created a table and inserted some values...and then drop it...i run "show recyclebin" and there's nothing...wonder where did i miss anything?? any other parameters i hv to set?

I don't think UNDO is problem here. Make sure you are not into space pressure.

http://download-west.oracle.com/docs...sh.htm#1019427

hi sanjay,

attached my free tablespace list for your reference:

TABLESPACE_NAME ALLOCATED_MB FREE_MB
------------------------------ ------------ ----------
DRSYS 40 15
INDX 20 20
RBS 600 22
SYSAUX 500 406.1
SYSTEM 6600 3
TEMP 1960 20
TOOLS 150 6.8
UNDOTBS_01 10 7.5
USERS 20 19.9


i hv created juz a 2 rows content table in USER ts and my undo ts is undotbs_01....how can there be space pressure? any comments?

thks

i found out that after upgrading 8i to 10g even with the proper undo parameters set...flashback is still not possible is due to the fact that tablespaces are in EXTENT_MANAGEMENT=DICTIONARY instead of LOCAL.

wonder if the rest encounter this before?
so my next qn is how do i set the Extent Management to LOCAL from DICT ??? any guru can assist??

thank in advance :)

hi,

anyway i hv found out how to switch DICT to LOCAL manage using DBMS_SPACE_ADMIN package.

thanks