If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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

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.

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.

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.