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