oracle匯入匯出資料常見報錯處理整理

報錯1：ORA-01756: quoted string not properly terminated

字元集編碼不一致造成的中文內容無法插入
查詢當前資料庫的字元編碼設定：
SQL> select * from v$nls_parameters T WHERE t.PARAMETER = ‘NLS_LANGUAGE’ or t.PARAMETER = ‘NLS_CHARACTERSET’;
或者
SQL> select userenv(‘language’) from dual;
查詢用戶端字元集編碼
linux下：
echo $NLS_LANG
Windows下：
echo %NLS_LANG%
設定用戶端字元集編碼：
LINUX下：
# 常用unicode字元集
export NLS_LANG=american_america.AL32UTF8
# 常用中文字元集
可以編輯 bash_profile 檔案進行永久設定
vi .bash_profile
export NLS_LANG=”SIMPLIFIED CHINESE_CHINA.ZHS16GBK”
or export NLS_LANG=”Simplified Chinese_china”.ZHS16GBK
# 使 bash_profile 設定生效
source .bash_profile
Windows下：
# 常用中文字元集
set NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK
# 常用unicode字元集
set NLS_LANG=american_america.AL32UTF8
可以通過修改註冊表索引值永久設定
HKEY_LOCAL_MACHINESOFTWAREORACLEHOMExxNLS_LANG

修改伺服器端字元集：
SQL>conn / as sysdba;
若此時資料庫伺服器已啟動，則先執行 SHUTDOWN IMMEDIATE 命令關閉資料庫伺服器，然後執行以下命令:

SQL>shutdown immediate;
SQL>STARTUP MOUNT;
SQL>ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL>ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
SQL>ALTER SYSTEM SET AQ_TM_PROCESSES=0;
SQL>ALTER DATABASE OPEN;
SQL>ALTER DATABASE CHARACTER SET ZHS16GBK;
ORA-12712: new character set must be a superset of old character set
提示我們的字元集：新字元集必須為舊字元集的超集，這時我們可以跳過超集的檢查做更改：需使用INTERNAL_USE可以跳過超集的檢查
SQL>ALTER DATABASE CHARACTER SET INTERNAL_USE ZHS16GBK;

ERROR at line 1:
ORA-12721: operation cannot execute when other sessions are active

若報以上錯誤則：select sid,serial#,username,machine from v$session;查看有哪些SESSION，然後
alter system kill session ‘sid’serial#’ immediate;然後
alter system kill session ’158,7′ immediate;如這樣
SQL>ALTER DATABASE CHARACTER SET INTERNAL_USE ZHS16GBK;
SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP;

報錯2：插入欄位含有&(安特)符號

方法一：
SQL> show define;
define “&” (hex 26)
SQL> set define off;
SQL> show define;
define OFF
這個是Oracle裡面用來識別自訂變數的設定，在SQL*PLUS下要將其關閉。
注意：如果是在TOAD中執行，建議在每一個要匯入的指令碼第一行加上前面那句關閉define的話，否則當你匯入第二個含有特殊字元的指令碼的時候，又會出錯。
如果是在SQL*PLUS中執行，則只需要設定一次define OFF，後面就可以連續匯入了。直到你重新設定define ON為止。
方法二：
在SQL語句中將’&’替換成chr(38)，因為chr(38)是‘&’的ASCII碼
SQL> Select ‘Tom’ || chr(38) || ‘Jerry’ from dual;
方法三：
分拆原來的字串
SQL> Select ‘Tom’ || ‘&’ || ‘Jerry’ from dual;

報錯3：插入欄位含有’(單引號)符號

方法一：使用逸出字元
SQL > Select ‘test’ || ”” from dual;
注意：這裡的””四個單引號是什麼意思呢？首先第一個和最後一個都是Oracle中的字串串連符，這個沒有異議。那麼第二個’和第三’又表示什麼意思呢？第二個’是一個逸出字元
第三個’才是我們真正的內容
方法二：
同樣是使用逸出字元，只不過方式不同而已
SQL > Select ‘test ”’ from dual;
注意：這裡的第二個，第三個’就是我們上面方法一中提到的轉義符和真正的內容
方法三：
在SQL中將’替換成chr(39)，因為chr(39)是’的ASCII碼
SQL > Select ‘It’ || chr(39) || ‘fine’ from dual;

報錯4：ORA-00001: unique constraint violated

主鍵有唯一性限制式，不能重複插入相同的數值。

匯入匯出 EXP/IMP（含特殊欄位imp報錯處理）

EXP:

擷取協助 EXP help=y

完全匯出：full=y

方案匯出 owner=(system,sys,.....)

表匯出 tables=(a,b,....)

查詢結果匯出

tables=(a) query="where filed ='xxxx'"

 表結構不含資料，索引......

rows=n triggers=n index=n statistics=none

.......

 IMP

完整匯出

full=y ignore=y

 使用者所屬資料匯入另一個使用者

fromuser=u1 touser=u2

表匯入

fromuser=u1 tables=(a,b,....)

匯入資料時 當表中含有LOB等特殊欄位時會報錯

"找不到XXX 空間..........."

解決方案：

1. 用PL SQL 匯出USER 'S OBJECT 的SQL檔案後修改tablespace xxx;

2.在將sql檔案執行產生表結構。

3.再通過imp匯入資料。

impdp匯入資料報錯ora-14102

匯入語句：impdp username/********@orcl remap_schema=hpchss:lwchss remap_tablespace=hpchss:lwchss remap_tablespace=lwchss_indx:lwchss_idx directory=hyhp dumpfile=HP_2013-11-07.DMP logfile=phis.log
報錯資訊：
處理物件類型 SCHEMA_EXPORT/TABLE/INDEX/INDEX
ORA-39083: 物件類型 INDEX 建立失敗, 出現錯誤:
ORA-14102: 只能指定一個 LOGGING 或 NOLOGGING 子句
失敗的 sql 為:
CREATE UNIQUE INDEX "LWCHSS"."PK_PHYSICALEXAMINATION_ROSTER" ON "LWCHSS"."PHEALTH_PHYSICALEXAM_ROSTER" ("ID") PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING NOCOMPRESS LOGGING STORAGE( INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "LWCHSS_IDX" PARALLEL 1

ORA-39083: 物件類型 CONSTRAINT 建立失敗, 出現錯誤:
ORA-14102: 只能指定一個 LOGGING 或 NOLOGGING 子句
失敗的 sql 為:
ALTER TABLE "LWCHSS"."REPORT_ORG_WORKER_EDU" ADD CONSTRAINT "PK_FACT_ORG_WORKER_EDU" PRIMARY KEY ("REPORT_DATE", "ORG_ID", "EDU_ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING NOCOMPRESS LOGGING STORAGE( INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "LWCHSS_IDX"  ENABLE

解決辦法

將那兩條sql，去掉nologging或logging，在sqlplus下重新執行一次就OK了

另外，你的impdp，兩個remap_tablespace，是可以寫在一起的，用半形逗號隔開就可以了，remap_tablespace=hpchss:lwchss,lwchss_indx:lwchss_idx