If
you try to sort anything other than a small amount of data, and the
sort has to use disk space, you may see the following error message:
SQL> select a.table_name, b.table_name
2 from dba_tables a, dba_tables b
3 order by 1;
from dba_tables a, dba_tables b
*
ERROR at line 2:
ORA-25153: Temporary Tablespace is Empty
SQL>
This will happen if you:
(1) Copy the datafiles from a source to a target database.
(2) Recreate the target database’s control file.
(3) Forget to add a file to its temporary tablespace.
You can diagnose this fault as follows. First you have to find the name(s) of the target database’s temporary tablespace(s). In this example there is only one:
SQL> select distinct temporary_tablespace
2 from dba_users
3 /
TEMPORARY_TABLESPACE
------------------------------
TEMP
SQL>
Then you have to see if it contains any temp files:
SQL> select file_name from dba_temp_files
2 where tablespace_name = 'TEMP'
3 /
no rows selected
SQL>
If it doesn’t, you have to add one. On this occasion, there was still an old tempfile so I was able to reuse it:
1 alter tablespace temp add tempfile
2 'test10/andrew/temp_files/temp01.dbf'
3* reuse
SQL> /
Tablespace altered.
SQL>
Then the sort should work:
SQL> col table_name format a25
SQL> l
1 select a.table_name, b.table_name
2 from dba_tables a, dba_tables b
3* order by 1
SQL> /
TABLE_NAME TABLE_NAME
------------------------- -------------------------
ACCESS$ AQ$_ALERT_QT_H
ACCESS$ WRI$_DBU_FEATURE_USAGE
ACCESS$ WRI$_ALERT_HISTORY
ACCESS$ AQ$_ALERT_QT_T
ACCESS$ WRH$_FILESTATXS_BL
ACCESS$ AQ$_ALERT_QT_G
ACCESS$ AQ$_ALERT_QT_I
ACCESS$ WRH$_FILESTATXS
ACCESS$ WRH$_WAITSTAT
ACCESS$ WRH$_TEMPSTATXS
ACCESS$ WRH$_SQLSTAT
Etc
No comments:
Post a Comment