ORA-01658 错误

比眉伴天荒 2022-05-15 05:12 90阅读 0赞

\--\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

\-- ORA-01658 错误

\--\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

最近重新装个了Oracle 11g，在对分区表导入导出时碰到了ORA\-01658错误的问题，因为刚刚才装的新系统，一导出就碰到了下面的问题，

自己可是纳闷了半天，后来才发现是因为磁盘空间不够引发的。本人的这个Oracle 11g安装在rhel 5.5之上，当时的分区时总共分了16G，8G用

来安装操作系统，8G用来安装Oracle 数据库。具体错误请看下文。

1.导出数据时产生ORA\-01658 错误

\[oracle@ora11g ~\]$ expdp scott/tiger directory=dmp dumpfile=tb\_pt.dmp logfile=tb\_pb.log tables=tb\_pt parallel=3

Export: Release 11.2.0.1.0 - Production on Sun Mar 13 18:05:37 2011

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

ORA-31626: job does not exist

ORA-31637: cannot create job SYS\_EXPORT\_TABLE\_01 for user SCOTT

ORA-06512: at "SYS.DBMS\_SYS\_ERROR", line 95

ORA-06512: at "SYS.KUPV$FT\_INT", line 798

ORA-39244: Event to disable dropping null bit image header during relational select

ORA-06512: at "SYS.DBMS\_SYS\_ERROR", line 86

ORA-06512: at "SYS.KUPC$QUE\_INT", line 1825

ORA-02320: failure in creating storage table for nested table column TREAT("USER\_DATA" AS "KUPC$\_BAD\_FILE")."ERROR"

ORA-01658: unable to create INITIAL extent for segment in tablespace SYSTEM

2.根据错误号定位问题

SQL> ho oerr ora 31637 \-- ORA-31637不能创建作业

31637, 00000, "cannot create job %s for user %s"

// \*Cause: Unable to create or restart a job. Refer to any following or

// prior error messages for clarification.

// \*Action: Eliminate the problems indicated.

SQL> ho oerr ora 01658

01658, 00000, "unable to create INITIAL extent for segment in tablespace %s"

// \*Cause: Failed to find sufficient contiguous space to allocate INITIAL

// extent for segment being created.

// \*Action: Use ALTER TABLESPACE ADD DATAFILE to add additional space to the

// tablespace or retry with a smaller value for INITIAL

从上面的错误描述来看，是因为空间不够不能够创建段，需要添加数据文件或为区间设置更细粒度值，因是新装的系统没有做个任何调整，

所以extent应该是没有问题的，查看一下系统空间的使用情况

SQL> ho df

Filesystem 1K-blocks Used Available Use% Mounted on

/dev/sda2 5944440 3088836 2548764 55% /

/dev/sdb1 8123168 7701056 2820 100% /u02

/dev/sda1 155543 11436 136077 8% /boot

tmpfs 517552 245624 271928 48% /dev/shm

结果是大吃一惊，原来安装Oracle 的u02挂载点空间使用率达到100%

3.解决空间问题，不再出现ORA\-01658错误

SQL> ho ls /u02

database dmp lost+found oracle oraInventory

SQL> ho mv /u02/database /

mv: cannot create directory \`/database': Permission denied

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

\[oracle@ora11g ~\]$ su - root

Password:

\[root@ora11g ~\]\# mv /u02/database / \--将Oracle 原始安装文件转移到/分区

\[root@ora11g ~\]\# df

Filesystem 1K-blocks Used Available Use% Mounted on

/dev/sda2 5944440 5440008 197592 97% /

/dev/sdb1 8123168 5349900 2353976 70% /u02

/dev/sda1 155543 11436 136077 8% /boot

tmpfs 517552 245624 271928 48% /dev/shm

再次导入正常

\[oracle@ora11g ~\]$ expdp scott/tiger directory=dmp dumpfile=tb\_pt.dmp logfile=tb\_pb.log tables=tb\_pt parallel=3

Export: Release 11.2.0.1.0 - Production on Sun Mar 13 18:34:51 2011

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SCOTT"."SYS\_EXPORT\_TABLE\_01": scott/\*\*\*\*\*\*\*\* directory=dmp dumpfile=tb\_pt.dmp logfile=tb\_pb.log tables=tb\_pt parallel=3

Estimate in progress using BLOCKS method...

Processing object type TABLE\_EXPORT/TABLE/TABLE\_DATA

Total estimation using BLOCKS method: 512 KB

. . exported "SCOTT"."TB\_PT":"SAL\_OTHER" 71.73 KB 2880 rows

. . exported "SCOTT"."TB\_PT":"SAL\_11" 12.45 KB 293 rows

. . exported "SCOTT"."TB\_PT":"SAL\_12" 14.23 KB 366 rows

. . exported "SCOTT"."TB\_PT":"SAL\_13" 14.21 KB 365 rows

. . exported "SCOTT"."TB\_PT":"SAL\_14" 14.20 KB 365 rows

. . exported "SCOTT"."TB\_PT":"SAL\_15" 14.21 KB 365 rows

. . exported "SCOTT"."TB\_PT":"SAL\_16" 14.22 KB 366 rows

Processing object type TABLE\_EXPORT/TABLE/TABLE

Processing object type TABLE\_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE\_EXPORT/TABLE/INDEX/STATISTICS/INDEX\_STATISTICS

Master table "SCOTT"."SYS\_EXPORT\_TABLE\_01" successfully loaded/unloaded

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

Dump file set for SCOTT.SYS\_EXPORT\_TABLE\_01 is:

/u02/dmp/tb\_pt.dmp

Job "SCOTT"."SYS\_EXPORT\_TABLE\_01" successfully completed at 18:35:59

4.更多参考

有关性能优化请参考

[Oracle 硬解析与软解析][Oracle]

[共享池的调整与优化(Shared pool Tuning)][Shared pool Tuning]

[Buffer cache 的调整与优化(一)][Buffer cache]

[Oracle 表缓存(caching table)的使用][Oracle _caching table]

有关闪回特性请参考

[Oracle 闪回特性(FLASHBACK DATABASE)][Oracle _FLASHBACK DATABASE]

[Oracle 闪回特性(FLASHBACK DROP & RECYCLEBIN)][Oracle _FLASHBACK DROP _ RECYCLEBIN]

[Oracle 闪回特性(Flashback Query、Flashback Table)][Oracle _Flashback Query_Flashback Table]

[Oracle 闪回特性(Flashback Version、Flashback Transaction)][Oracle _Flashback Version_Flashback Transaction]

有关基于用户管理的备份和备份恢复的概念请参考：

[Oracle 冷备份][Oracle 1]

[Oracle 热备份][Oracle 2]

[Oracle 备份恢复概念][Oracle 3]

[Oracle 实例恢复][Oracle 4]

[Oracle 基于用户管理恢复的处理][Oracle 5](详细描述了介质恢复及其处理)

有关RMAN的恢复与管理请参考：

[RMAN 概述及其体系结构][RMAN]

[RMAN 配置、监控与管理][RMAN 1]

[RMAN 备份详解][RMAN 2]

[RMAN 还原与恢复][RMAN 3]

有关Oracle体系结构请参考：

[Oracle 实例和Oracle数据库(Oracle体系结构)][Oracle _Oracle_Oracle]

[Oracle 表空间与数据文件][Oracle 6]

[Oracle 密码文件][Oracle 7]

[Oracle 参数文件][Oracle 8]

[Oracle 数据库实例启动关闭过程][Oracle 9]

[Oracle 联机重做日志文件(ONLINE LOG FILE)][Oracle _ONLINE LOG FILE]

[Oracle 控制文件(CONTROLFILE)][Oracle _CONTROLFILE]

[Oracle 归档日志][Oracle 10]

[Oracle]: http://blog.csdn.net/robinson_0612/archive/2011/02/19/6195483.aspx
[Shared pool Tuning]: http://blog.csdn.net/robinson_0612/archive/2011/02/25/6208268.aspx
[Buffer cache]: http://blog.csdn.net/robinson_0612/archive/2011/02/25/6208594.aspx
[Oracle _caching table]: http://blog.csdn.net/robinson_0612/archive/2011/03/06/6226767.aspx
[Oracle _FLASHBACK DATABASE]: http://blog.csdn.net/robinson_0612/archive/2010/12/27/6100429.aspx
[Oracle _FLASHBACK DROP _ RECYCLEBIN]: http://blog.csdn.net/robinson_0612/archive/2010/12/29/6105327.aspx
[Oracle _Flashback Query_Flashback Table]: http://blog.csdn.net/robinson_0612/archive/2011/01/02/6112607.aspx
[Oracle _Flashback Version_Flashback Transaction]: http://blog.csdn.net/robinson_0612/archive/2011/01/02/6112981.aspx
[Oracle 1]: http://blog.csdn.net/robinson_0612/archive/2010/08/05/5791597.aspx
[Oracle 2]: http://blog.csdn.net/robinson_0612/archive/2010/08/16/5816012.aspx
[Oracle 3]: http://blog.csdn.net/robinson_0612/archive/2010/08/05/5791585.aspx
[Oracle 4]: http://blog.csdn.net/robinson_0612/archive/2010/07/27/5768233.aspx
[Oracle 5]: http://blog.csdn.net/robinson_0612/archive/2010/11/28/6041217.aspx
[RMAN]: http://blog.csdn.net/robinson_0612/archive/2010/11/24/6032525.aspx
[RMAN 1]: http://blog.csdn.net/robinson_0612/archive/2010/11/24/6032597.aspx
[RMAN 2]: http://blog.csdn.net/robinson_0612/archive/2010/11/24/6032739.aspx
[RMAN 3]: http://blog.csdn.net/robinson_0612/archive/2010/11/28/6041247.aspx
[Oracle _Oracle_Oracle]: http://blog.csdn.net/robinson_0612/archive/2010/04/26/5529239.aspx
[Oracle 6]: http://blog.csdn.net/robinson_0612/archive/2010/05/20/5611738.aspx
[Oracle 7]: http://blog.csdn.net/robinson_0612/archive/2010/05/20/5611672.aspx
[Oracle 8]: http://blog.csdn.net/robinson_0612/archive/2010/05/05/5559174.aspx
[Oracle 9]: http://blog.csdn.net/robinson_0612/archive/2010/04/29/5542983.aspx
[Oracle _ONLINE LOG FILE]: http://blog.csdn.net/robinson_0612/archive/2010/07/20/5749556.aspx
[Oracle _CONTROLFILE]: http://blog.csdn.net/robinson_0612/archive/2010/07/15/5737527.aspx
[Oracle 10]: http://blog.csdn.net/robinson_0612/archive/2010/08/03/5784713.aspx