DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2002

    Temp Tablespace In Local Managed

    Hi all,

    I am running Oracle and now I am upgrading to 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:

    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
    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
    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
    Madrid, Spain
    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

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



    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
    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
    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;


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

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.