Re: ORA-03212: Temporary Segment cannot be created in locally-managed tablespace

"Epicentre team A" <oracle_at_epicentre.fr> wrote

> I've this error when connect.

Here is the full text:

03212, 00000, "Temporary Segment cannot be created in locally-managed tablespace"

// *Cause: Attempt to create a temporary segment for sort/hash/lobs in
//         in permanent tablespace of kind locally-managed
// *Action: Alter temporary tablespace of user to a temporary tablespace
//          or a dictionary-managed permanent tablespace

> why ?

Temporary objects created by Oracle, can not reside in a permanent tablespace.

When a database is created, it is the norm to create a tablespace called TEMP and specify it as a temporary tablespace. (refer to the CREATE TABLEAPACE command)

When Oracle users are created, it is the norm to specify that this tablespace be used as their temporary tablespace. (refer to the CREATE USER command)

What seems to be happening here, is that either the temp tablespace for the Oracle user that you are using has not been specified (resulting in an invalid default and permanent tablespace being assigned), or that the temp tablespace assigned for that user is no longer (or never was) a temporary tablespace.

To fix:
- determine the name of the temp tablespace on your database   (refer to the view DBA_TABLESPACES or USER_TABLESPACES) - correct your Oracle user using the ALTER USER command   (e.g. ALTER USER johndoe TEMPORARY TABLESPACE temp)

It is unlikely that you will have the required permission to the 2nd step - in which case you need to use the an Oracle user with DBA privs (e.g. sys).

--
Billy