ORA-01652:unable to exted temp segment by 128 in tablespace TEMP 01


          ORA-01652    


SQL> EXEC DBMS_STATS.gather_database_stats;

BEGIN DBMS_STATS.gather_database_stats; END;
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP01
ORA-06512: at "SYS.DBMS_STATS", line 13210
ORA-06512: at "SYS.DBMS_STATS", line 13556
ORA-06512: at "SYS.DBMS_STATS", line 13700
ORA-06512: at "SYS.DBMS_STATS", line 13664
ORA-06512: at line 1


     temp01     ,        ,             

SQL>select file_name,bytes/1024/1024 "MB",autoextensible,tablespace_name from dba_temp_files

FILE_NAME                                                   MB  AUT    TABLESPACE_NAME
-------------------------------------------------------- ----- -----  ------------------
/u01/app/oracle/product/10.2.0/db_1/dbs/temp01.dbf         512  NO     TEMP01


          ,             temp02


SQL> create temporary tablespace TEMP02 
     TEMPFILE '/u01/app/oracle/product/10.2.0/db_1/dbs/temp02.dbf' SIZE 512M 
     REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED; 

Tablespace created.


              temp02

SQL> alter database default temporary tablespace temp02;

Database altered.

***          TEMP01

SQL> drop tablespace temp01 including contents and datafiles;

Tablespace dropped.

         TEMP01
SQL> create temporary tablespace TEMP01 
TEMPFILE '/u01/app/oracle/product/10.2.0/db_1/dbs/temp01.dbf' SIZE 512M 
REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED;

Tablespace created.

              TEMP01

SQL> alter database default temporary tablespace temp01;

Database altered.

***     TEMP02

SQL> drop tablespace temp02 including contents and datafiles;

Tablespace dropped.

          TEMP01  ,       “YES”
SQL>select file_name,bytes/1024/1024 "MB",autoextensible,tablespace_name from dba_temp_files

FILE_NAME                                                   MB  AUT    TABLESPACE_NAME
-------------------------------------------------------- ----- -----  ------------------
/u01/app/oracle/product/10.2.0/db_1/dbs/temp01.dbf         512  YES     TEMP01

          ,    

SQL> EXEC DBMS_STATS.gather_database_stats;

PL/SQL procedure successfully completed.




         

         

SQL>alter database tempfile '/u01/app/oracle/product/10.2.0/db_1/dbs/temp01.dbf' RESIZE 1000m;

         
SQL>select file_name,bytes/1024/1024 "MB",autoextensible,tablespace_name from dba_temp_files