--******************

-- 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

?

??? Copyright (c) 1982, 2009, Oracle and/or its affiliates. ? All rights reserved.

?

??? 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

?

??? Copyright (c) 1982, 2009, Oracle and/or its affiliates. ? All rights reserved.

?

??? 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 硬解析與軟解析

共享池的調整與優化(Shared pool Tuning)

Buffer cache 的調整與優化( 一)

Oracle 表緩存(caching table) 的使用

?

有關閃回特性請參考

??????? Oracle 閃回特性(FLASHBACK DATABASE)

Oracle 閃回特性(FLASHBACK DROP & RECYCLEBIN)

Oracle 閃回特性(Flashback Query 、Flashback Table)

Oracle 閃回特性(Flashback Version 、Flashback Transaction)

?

有關基于用戶管理的備份和備份恢復的概念請參考：

??????? Oracle 冷備份

??????? Oracle 熱備份

??????? Oracle 備份恢復概念

??????? Oracle 實例恢復

??????? Oracle 基于用戶管理恢復的處理 ( 詳細描述了介質恢復及其處理 )

???????

??? 有關 RMAN 的恢復與管理請參考：

??????? RMAN 概述及其體系結構

??????? RMAN 配置、監控與管理

??????? RMAN 備份詳解

??????? RMAN 還原與恢復

???????

??? 有關 Oracle 體系結構請參考：

??????? Oracle 實例和Oracle 數據庫(Oracle 體系結構)

??????? Oracle 表空間與數據文件

??????? Oracle 密碼文件

??????? Oracle 參數文件

Oracle 數據庫實例啟動關閉過程

??????? Oracle 聯機重做日志文件(ONLINE LOG FILE)

??????? Oracle 控制文件(CONTROLFILE)

??????? Oracle 歸 檔日志

???

ORA-01658 錯誤