ORA-1652: unable to extend temp segment by xxx in tablespace
收集数据库信息时候报ORA-01652错 如下
|
1 2 3 4 5 6 7 8 9 10 11 |
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表空间过小，而且没有自动扩展，因此无法完成数据库信息收集
|
1 2 3 4 5 |
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
|
1 2 3 4 5 |
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
|
1 2 3 |
SQL> alter database default temporary tablespace temp02; Database altered. |
删除原来的默认临时表空间TEMP01
|
1 2 3 |
SQL> drop tablespace temp01 including contents and datafiles; Tablespace dropped. |
创建新的临时表空间TEMP01
|
1 2 3 4 5 |
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
|
1 2 3 |
SQL> alter database default temporary tablespace temp01; Database altered. |
删除临时表空间TEMP02
|
1 2 3 |
SQL> drop tablespace temp02 including contents and datafiles; Tablespace dropped. |
查询新建的临时表空间TEMP01信息，自动扩展已经为“YES”
|
1 2 3 4 5 |
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 |
此时再收集数据库信息，收集完毕
|
1 2 3 |
SQL> EXEC DBMS_STATS.gather_database_stats; PL/SQL procedure successfully completed. |
临时表空间常用操作
更改临时表空间大小
|
1 |
SQL>alter database tempfile '/u01/app/oracle/product/10.2.0/db_1/dbs/temp01.dbf' RESIZE 1000m; |
查看临时表空间大小
|
1 |
SQL>select file_name,bytes/1024/1024 "MB",autoextensible,tablespace_name from dba_temp_files |
分类: oracle