Temp Tablespace In Local Managed

[mike2000]

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

[dbasupuser]

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.

[mike2000]

The user was default to TEMP tablespace. and I am sure that you can have LOCAL MANAGED for your TEMP tablespace.


Any other thought???

[pando]

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

[tycho]

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'
/

[mike2000]

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.

[amar]

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