18 December 2006

Solution Of "ORA-12910: cannot specify temporary tablespace as default tablespace" Error

A few days ago i have done a full database export-import. During import I got


IMP-00017: following statement failed with ORACLE error 12910:
"CREATE USER "FOO" IDENTIFIED BY VALUES 'FOO' DEFAULT TABLESPACE "DATA" TEMPORARY TABLESPACE "TEMP""
IMP-00003: ORACLE error 12910 encountered
ORA-12910: cannot specify temporary tablespace as default tablespace


error. When i look at tablespaces and database properties, I realized that default temporary tablespace has been set "DATA" and default tablespace has been set "USERS". So during import, I was getting specified errors.


The solution was to check tablespaces.


SELECT * FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DEFAULT_%';
SELECT * FROM dba_data_files;
SELECT * FROM v$tablespace;



Then I dropped tablespace and recreate it.


DROP TABLESPACE DATA;



CREATE TABLESPACE "DATA" DATAFILE
'/data09/oradata/db02/data01.dbf' REUSE
AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT MANUAL;



Lastly I altered the database


ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;
ALTER DATABASE DEFAULT TABLESPACE DATA;



That is all.



No comments: