Temp Tablespace In Local Managed
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Temp Tablespace In Local Managed

Hybrid View

  1. #1
    Join Date
    Sep 2002
    Posts
    411

    Temp Tablespace In Local Managed

    Hi all,

    I am running Oracle 8.1.7.0.0 and now I am upgrading to 8.1.7.3.0. Everything went well till I ran the catalog.sql,catproc.sql and I got some error something about "ORA-03212: Temporary Segment cannot be created in locally-managed tablespace"

    Any way, when my upgrade completed, I look into any objects belong to SYS or SYSTEM that were INVALID and I got the punch of them. SO I tried to run the UTLRP.SQL scripts to compile those INVALID objects and I got the below errors:

    SQL> @C:\ORACLE\ORA817\RDBMS\ADMIN\UTLRP.SQL
    DECLARE
    *
    ERROR at line 1:
    ORA-04045: errors during recompilation/revalidation of SYS.DBMS_SQL
    ORA-03212: Temporary Segment cannot be created in locally-managed tablespace
    ORA-06508: PL/SQL: could not find program unit being called
    ORA-06512: at line 44


    Have anyone encountered this problems???

    Any solutions???

  2. #2
    Join Date
    Nov 2002
    Posts
    170
    Check the temporary tablespace for the user you are logged on as to run these scripts. It should be a temporary tablespace or a dictionary managed permanent tablespace. It can not be locally managed permanent tablespace.

  3. #3
    Join Date
    Sep 2002
    Posts
    411
    The user was default to TEMP tablespace. and I am sure that you can have LOCAL MANAGED for your TEMP tablespace.


    Any other thought???

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Code:
    Error:	ORA-3212 
    Text:	Temporary Segment cannot be created in locally-managed tablespace  
    ----------------------------------------------------------------------
    Cause:	Attempt to create a temporary segment for sort/hash/lobs in permanent tablespace of kind locally-managed  
    Action:	Alter temporary tablespace of user to a temporary tablespace or dictionary-managed permanent tablespace

  5. #5
    Join Date
    Jul 2000
    Location
    Amsterdam
    Posts
    234
    Mike2000

    Run these two selects against your database and use the output.
    Than try again to run utlrp.

    Tycho

    ==========

    select 'drop tablespace temp;' from dual
    /
    select 'create temporary tablespace temp tempfile '''||file_name||''' reuse
    extent management local uniform size 4 M;'
    from dba_data_files
    where tablespace_name = 'TEMP'
    /

  6. #6
    Join Date
    Sep 2002
    Posts
    411
    I agree with everybody and well understood what the error messages said; however, I am a bit confused b/c I thought I can created the LOCAL MANAGED for my TEMP tablespace and then I change the SYS user to user the TEMP tablespace.

    I still don't know why I have this problem.

  7. #7
    Join Date
    Mar 2001
    Posts
    314
    Did you use DATAFILE instead of TEMPFILE while creating the TEMPORARY tablespace? As tycho as suggested already, if you want your TEMPORARY tablespace to be Locally Managed then you should use the TEMPFILE clause and NOT DATAFILE while creating the TEMPORARY TABLESPACE.

    create temporary tablespace temp tempfile
    '/u10/oradata/cprpt/temp01.dbf' size 250M reuse,
    '/u10/oradata/cprpt/temp02.dbf' size 250M reuse,
    '/u10/oradata/cprpt/temp03.dbf' size 250M reuse,
    '/u10/oradata/cprpt/temp04.dbf' size 250M reuse
    extent management local uniform size 3M;

    -amar

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