我今天在impdp导入数据，当导入索引时，一直卡在那里。
去查看alter日志，发现： ORA-01652: unable to extend temp segment by 128 in tablespace TMPGRP
于是增加临时文件的大小解决该问题。
另外，导入过程中，还有一个报错：
这个是统计信息的报错，本来导出了统计信息，无法导入，就忽略导入统计信息，
如下：
impdp system/oracle@prod1 directory=dir1 dumpfile=dir1:vpm20150610.dmp schemas=VPM exclude=statistics
收集数据库信息时候报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

ITPub博客

ORA-01652: unable to extend temp segment by 128 in tablespace TEMP01

最新文章