Database Cloning Mistake !!!!!

Dear Friends,

The following errors were generated continuously in the test database alert log file.

1). ORA-25153: Temporary Tablespace is Empty

2). GATHER_STATS_JOB encountered errors. Check the trace file.

3). ORA-20000: Unable to analyze TABLE "SIRSI"."HEADING4", insufficient privileges or does not exist.

But when we query to see the information about temporary tablespace and tempfiles in the database, the following findings were observed.

## To check TEMP tablespace and tempfile details.

SQL> SELECT file_name, bytes FROM dba_temp_files;

no rows selected

SQL> SELECT * FROM v$tempfile;

no rows selected

SQL> SELECT * FROM v$tablespace WHERE name='TEMP';

TS# NAME INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
3 TEMP NO NO YES

SQL> SELECT tablespace_name, contents FROM dba_tablespaces WHERE tablespace_name='TEMP';

TABLESPACE_NAME CONTENTS
------------------------------ ---------
TEMP TEMPORARY

SQL> SELECT DISTINCT temporary_tablespace FROM dba_users;

TEMPORARY_TABLESPACE
------------------------------
TEMP

## Gather Schema Statistics.

In Oralce 10g, when the data of a table is grown to certain threshold value i.e. 10% of data, Automatically Gather Schema Statistics is performed on those tables.And also it needs enough free space in the temporary tablespace for sorting. Due to this reason above messages generated in the alert log file.

Cause: The TEST Database has recently been cloned from PRODuction Database. So, we realized that the step, adding a tempfile to the temp tablespace, was missed mistakenly. In fact the tempfile was copied and it is physically existing in the server, but not added to the TEMP tablespace as part of the cloning.

Solution:

ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/product/10.2.0/oradata/test/temp01.dbf' REUSE;

After adding a tempfile to the TEMP tablespace, no errors generated regarding Gather Schema Statistics and temp tablespace afterward !!!.

Note: if you are receiving an error "ORA-20000: Unable to analyze TABLE "SIRSI"."HEADING4", insufficient privileges or does not exist." due to other reasons. Please take a look at the Oracle Metalink Note ID: 333966.1

This article, written by me, is also available in Oracle Metalink. Note ID: 427974.1

Best Regards,
Sabdar Syed.