转自：http://blog.csdn.net/evils798/article/details/7296519
收集数据库信息时候报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