EXPORT TERMINATED ABNORMAL

[ashley75]

Hi all,

I have this problem on one of my database, I am running Oracle 8.1.7.3 on windows 2000 server. Below is the error when I export my database:


About to export the entire database ...
. exporting tablespace definitions
. exporting profiles
. exporting user definitions
EXP-00008: ORACLE error 3212 encountered
ORA-03212: Temporary Segment cannot be created in locally-managed tablespace
EXP-00000: Export terminated unsuccessfully


The weird thing about this database is I couldn't see any users on my database when I tried to view DBA_USERS;however, I saw on of the schema name. The database is running fine without having any problems

Please advise

Thanks

[sreddy]

$oerr ora 3212
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

[ashley75]

Thanks for you reply,

but I couldn't even view any users in my database, and I have over 100 users so how can I change all of them if I can write a scripts to change them????


Any adviseS???

[sreddy]

What does USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE columns of DBA_USERS view ?

If necessary you have write a dynamic sql with ALTER USER command to default the users to right tablespace.

[ashley75]

there are nothing show up in DBA_USERS as I mentioned earlier so that I think it's weird; but I was able to see all of the schemas in the database.

Wow, write dynamic SQL for over 100 users is alot of work.

I don't know what happened to DBA_USERS tables????

[stecal]

Originally posted by ashley75
Wow, write dynamic SQL for over 100 users is alot of work.

Ah Grasshopper, wise man write procedure for one, and do for many.

[pando]

Originally posted by ashley75

Wow, write dynamic SQL for over 100 users is alot of work.

umm dynamic sql is very simple.... just some

select 'hihihi '||username||' lololo;'
from dba_users;

[sreddy]

Seems Data Dictionary is not populated on this database. Go ahead and run catalog.sql/catproc.sql to get populated views with the database information. USE the following sql to build your alter commands.

select 'alter user '||username||' default tablespace users temporary tablespace temp ;' from dba_users where username not in ('SYS','SYSTEM','DBSNMP','OUTLN','TRACESVR','SCOTT')