Temp Tablespace In Local Managed
I am running Oracle 18.104.22.168.0 and now I am upgrading to 22.214.171.124.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:
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???
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.
The user was default to TEMP tablespace. and I am sure that you can have LOCAL MANAGED for your TEMP tablespace.
Any other thought???
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
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;'
where tablespace_name = 'TEMP'
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.
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;
Click Here to Expand Forum to Full Width