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