Oracle Errors

ORA-25153
While applying a MP through Change Assistant, a dms script failed giving below error-

Import  PSPROJECTDEFN  1
SQL Error. Error Position: 0  Return: 25153 - ORA-25153: Temporary Tablespace is Empty ORA-06512: at "SYS.DBMS_STATS", line 20337 ORA-06512: at "SYS.DBMS_STATS", line 20360 ORA-06512: at line 1
RECSTATS PSPROJECTDEFN LOW
Error: SQL execute error for %UpdateStats(PSPROJECTDEFN)
Error: Import Record name PSPROJECTDEFN



Troubleshooting  -

SQL> select tablespace_name,file_name from dba_temp_files;
no rows selected

SQL> select name from v$tempfile;
no rows selected

SQL> select property_value from database_properties where property_name like '%D
EFAULT_TEMP_TABLESPACE%';
PROPERTY_VALUE
--------------------------------------------------------------------------------
TEMP


The sql lists the default temporary tablespace of database - TEMP. It is created during database creation process (createdb10  DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '... ). I verified the corresponding data file - xxx/temp01.dbf and it was already there.


Solution -

SQL> ALTER TABLESPACE temp ADD TEMPFILE 'xxx\temp01.dbf' SIZE 20
M REUSE AUTOEXTEND ON NEXT  640K MAXSIZE UNLIMITED;

SQL> select tablespace_name,file_name from dba_temp_files;
TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
TEMP
P:\xxx\TEMP01.DBF


Next was to manually import - PSPROJECTDEFN through Datamover, and updating the statistics.

SQL>  exec dbms_stats.gather_table_stats('sysadm', 'PSPROJECTDEFN');
PL/SQL procedure successfully completed.

SQL> SELECT LAST_ANALYZED FROM DBA_TABLES WHERE TABLE_NAME='PSPROJECTDEFN';
LAST_ANAL
---------
17-JUL-13