Extent management local and undo Tablespace
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Extent management local and undo Tablespace

  1. #1
    Join Date
    May 2002
    Posts
    163

    Extent management local and undo Tablespace

    Hi All,

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


    Thanks
    Nwcomer
    Student

  2. #2
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    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
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  3. #3
    Join Date
    May 2002
    Posts
    163
    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.
    Nwcomer
    Student

  4. #4
    Join Date
    May 2002
    Posts
    163
    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
    Nwcomer
    Student

  5. #5
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    do you have an spfile?

  6. #6
    Join Date
    May 2002
    Posts
    163
    I am not sure. First time doing the same. Can u tell me the possible location.
    Nwcomer
    Student

  7. #7
    Join Date
    May 2002
    Posts
    163
    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.
    Nwcomer
    Student

  8. #8
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    you dont have to have an spfile (although they are useful things)

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

  9. #9
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    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...
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  10. #10
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317

    Re: Extent management local and undo Tablespace

    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...
    An expert is one who knows more and more about less and less until he knows absolutely everything about nothing.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width