Extent management local and undo Tablespace

[newcomer]

Hi All,

After the completion of Oracle 9i database creation, can I make the database 'Extent Management Local' and 'define Undo tablespace' later.


Thanks

[TimHall]

Hi.

You can convert DMTs to LMTs using DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL. There are a few limitations relating to space allocation when using this method. It's actually better to create a new tablespace and move the contents across to it.

http://download-west.oracle.com/docs...ce.htm#1001957

You can start Automatic Undo Management at any time. See:

http://www.oracle-base.com/Articles/...Management.asp

Cheers

[newcomer]

Tim,

But can we directly update or add any new parameter in our init file. Don't we have to do the same thru SPFILE.

[newcomer]

Hi all

At the time of creating the database I did not create Undo Tablespace. Then after installation I tried below options, Can you please tell me which process is correct.

1. I update my init parameter file with
UNDO_MANAGEMENT=Auto
UNDO_TABLEPSACE= undotbs01
UNDO_RETENTION=900

and created a Undo tablespace too.

Then I startup my database. Is it fine? Will it work?



2. Else tried thru SPFILE...

alter system set UNDO_MANAGEMENT=auto
comment='Making Undo tablespace'
Scope=SPFILE;

I am getting this error ' ORA-32001: write to SPFILE requested but no SPFILE specified at startup'


and for other two scopes, it is not working.


Please help me

[davey23uk]

do you have an spfile?

[newcomer]

I am not sure. First time doing the same. Can u tell me the possible location.

[newcomer]

Sorry, it is not under $ORACLE_HOME/dbs. I hope it is not there in my database. Is it fine without SPFILE?

Or I must create the same.

[davey23uk]

you dont have to have an spfile (although they are useful things)

but if you dont you cant do things like scope=spfile

[TimHall]

Hi.

If the ALTER SYSTEM SET .... SCOPE=SPFILE fails it means you are using an init.ora file. In this case the changes to the init.ora file will be picked up the next time you restart the instance.

If you want to start using an spfile you should check out:

http://www.oracle-base.com/Articles/...Parameters.asp

Cheers

Tim...

[efrijters]

Originally posted by newcomer
Hi All,

After the completion of Oracle 9i database creation, can I make the database 'Extent Management Local' and 'define Undo tablespace' later.


Thanks

There's another option: let the Oracle Database Configuration Assistent create batch files when you design a database. Edit the scripts that are called from the create batch file and alter your 'Extent Management Local' and 'Undo tablespace'-information there. After that run the batch file...