oracle資料庫字符集characterset遷移及變更系列二
背景
閒言少述，繼續測試資料庫字符集相關的知識，前幾文連結如下：oracle資料庫字符集characterset遷移及變更系列一
http://blog.itpub.net/9240380/viewspace-1849953/
本文主要熟悉下資料庫字符集遷移要考慮一些因素。
結論
1,zhs16gbk及al32utf8，消耗的空間是相同的，但二者採用編碼是不同的2,如果源字符集與目標字符集不相同，匯入時會根據目標資料庫字符集把資料進行重組然後儲存
3,基於char固定寬度的字元資料，如果從zhs16gbk到al32utf8,會提示如下錯誤資訊：
Column 1 我們
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "SCOTT"."T_CHARSET_BAK"."A" (actual: 13, maximum: 10)
為了解決上述問題，有2種方法：
A,根據報錯增加遷移表的列長度，然後重新IMP資料即可
B,配置引數blank_trimming=true，不過要重啟庫方可生效，即移除列尾部的空格字元
4，與CHAR不同，同樣是上述的測試環境，如果是VARCHAR2，則可以遷移成功，可見VARCHAR2的優點明顯高於CHAR，且消耗空間小，效能更高
所以在設計資料庫時要考慮到這些因素
5，同樣是基於上述的測試環境，可以把字元資料定義為clob,就可以避免VARCHAR或CHAR型別引發的資料損失或丟失或破壞
6，資料庫使用者及密碼只能是ASCII字元，不能是非ASCII字元，否則資料會無法遷移，且會報錯退出
IMP-00058: ORACLE error 1017 encountered
ORA-01017: invalid username/password; logon deniedUsername:
Password:
不過可以基於源資料庫的使用者，在目標庫構建對應許可權的新使用者，然後IMP FROMUSER TOUSER即可
當然最高效的方法就是在遷移資料時，確保源資料庫的使用者密碼只能包括ASCII字元
分析思路
測試
---為了測試不同資料庫字符集之間的變更及遷移的原理，DBCA建立了不同資料庫字符集的資料庫例項
[oracle@seconary admin]$ ll
total 24
drwxr-x--- 5 oracle oinstall 4096 Nov 29 18:58 charzhs --ZHS16GBK
drwxr-x--- 5 oracle oinstall 4096 Apr 17 2014 guowang --WE8MSWIN1252
drwxr-x--- 5 oracle oinstall 4096 Sep 11 00:44 newb
drwxr-x--- 5 oracle oinstall 4096 Nov 28 16:45 onetime
drwxr-xr-x 3 oracle oinstall 4096 Aug 28 2013 second
drwxr-x--- 5 oracle oinstall 4096 Nov 29 18:35 utf8char -- AL32UTF8
--先看下不同資料庫字符集的空間佔用差異
---al32utf8
[oracle@seconary admin]$ export ORACLE_SID=utf8char
SQL> conn /as sysdba
Connected.
SQL> alter user scott identified by system account unlock;
User altered.
SQL> conn scott/system
Connected.
SQL> create table t_charset(a char(10));
Table created.
SQL> insert into t_charset values('我們');
1 row created.
SQL> commit;
Commit complete.
SQL> insert into t_charset select * from t_charset;
1 row created.
SQL> insert into t_charset select * from t_charset;
2 rows created.
中間略
SQL> insert into t_charset select * from t_charset;
262144 rows created.
SQL> commit;
Commit complete.
SQL>
SQL> select count(*) from t_charset;
COUNT(*)
----------
524288
SQL> select segment_name,bytes from user_segments where lower(segment_name)='t_charset';
SEGMENT_NAME BYTES
-------------------------------------------------- ----------
T_CHARSET 10485760
SQL> select a,dump(a) from t_charset where rownum<=10;
A DUMP(A)
---------- ----------------------------------------------------------------------------------------------------
我們 Typ=96 Len=10: 233,142,180,230,136,156,230,187,145,32
我們 Typ=96 Len=10: 233,142,180,230,136,156,230,187,145,32
我們 Typ=96 Len=10: 233,142,180,230,136,156,230,187,145,32
我們 Typ=96 Len=10: 233,142,180,230,136,156,230,187,145,32
我們 Typ=96 Len=10: 233,142,180,230,136,156,230,187,145,32
我們 Typ=96 Len=10: 233,142,180,230,136,156,230,187,145,32
我們 Typ=96 Len=10: 233,142,180,230,136,156,230,187,145,32
我們 Typ=96 Len=10: 233,142,180,230,136,156,230,187,145,32
我們 Typ=96 Len=10: 233,142,180,230,136,156,230,187,145,32
我們 Typ=96 Len=10: 233,142,180,230,136,156,230,187,145,32
10 rows selected.
--zhs16gbk
SQL> conn scott/system
Connected.
SQL> create table t_charset(a char(10));
Table created.
SQL> insert into t_charset values('我們');
1 row created.
SQL> commit;
SQL> select count(*) from t_charset;
COUNT(*)
----------
524288
可見zhs16gbk及al32utf8基於中文字元消耗的空間是相同的
SQL> select segment_name,bytes from user_segments where lower(segment_name)='t_charset';
SEGMENT_NAME BYTES
--------------------------------------------------------------------------------- ----------
T_CHARSET 10485760
不過2種資料庫字符集採用雖然都是10個位元組，但底層採用的內部編碼不一樣
SQL> select a,dump(a) from t_charset where rownum<=10;
A DUMP(A)
------------------------------ ----------------------------------------------------------------------------------------------------
我們 Typ=96 Len=10: 230,136,145,228,187,172,32,32,32,32
我們 Typ=96 Len=10: 230,136,145,228,187,172,32,32,32,32
我們 Typ=96 Len=10: 230,136,145,228,187,172,32,32,32,32
我們 Typ=96 Len=10: 230,136,145,228,187,172,32,32,32,32
我們 Typ=96 Len=10: 230,136,145,228,187,172,32,32,32,32
我們 Typ=96 Len=10: 230,136,145,228,187,172,32,32,32,32
我們 Typ=96 Len=10: 230,136,145,228,187,172,32,32,32,32
我們 Typ=96 Len=10: 230,136,145,228,187,172,32,32,32,32
我們 Typ=96 Len=10: 230,136,145,228,187,172,32,32,32,32
我們 Typ=96 Len=10: 230,136,145,228,187,172,32,32,32,32
10 rows selected.
---測試下基於2種資料庫字符集不同，EXP以及IMP的情況,al32utf8到zhs16gbk
---匯出AL32UTF8
[oracle@seconary admin]$ export ORACLE_SID=utf8char
[oracle@seconary admin]$ env|grep NLS_LANG
NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
[oracle@seconary admin]$ exp userid=scott/system file=exp_utf8char.dmp tables=t_charset
Export: Release 11.2.0.1.0 - Production on Sun Nov 29 21:53:12 2015
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 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
About to export specified tables via Conventional Path ...
. . exporting table T_CHARSET 524288 rows exported
Export terminated successfully without warnings.
[oracle@seconary admin]$
---備份下zhs16gbk的測試表，然後刪除測試表
[oracle@seconary admin]$ export ORACLE_SID=charzhs
[oracle@seconary admin]$ sqlplus scott/system
SQL*Plus: Release 11.2.0.1.0 Production on Sun Nov 29 21:55:29 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create table t_charset_bak as select * from t_charset;
Table created.
SQL> drop table t_charset purge;
Table dropped.
可見匯入目標資料庫字符集後，會基於目標資料庫字符集重新組織源資料庫字符集對應的字元資料
[oracle@seconary admin]$ imp userid=scott/system file=exp_utf8char.dmp tables=t_charset
Import: Release 11.2.0.1.0 - Production on Sun Nov 29 21:57:10 2015
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 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into SCOTT
. importing SCOTT's objects into SCOTT
. . importing table "T_CHARSET" 524288 rows imported
Import terminated successfully without warnings.
SQL> select a,dump(a) from t_charset where rownum<=10;
A DUMP(A)
------------------------- --------------------------------------------------------------------------------
我們 Typ=96 Len=10: 230,136,145,228,187,172,32,32,32,32
我們 Typ=96 Len=10: 230,136,145,228,187,172,32,32,32,32
我們 Typ=96 Len=10: 230,136,145,228,187,172,32,32,32,32
我們 Typ=96 Len=10: 230,136,145,228,187,172,32,32,32,32
我們 Typ=96 Len=10: 230,136,145,228,187,172,32,32,32,32
我們 Typ=96 Len=10: 230,136,145,228,187,172,32,32,32,32
我們 Typ=96 Len=10: 230,136,145,228,187,172,32,32,32,32
我們 Typ=96 Len=10: 230,136,145,228,187,172,32,32,32,32
我們 Typ=96 Len=10: 230,136,145,228,187,172,32,32,32,32
我們 Typ=96 Len=10: 230,136,145,228,187,172,32,32,32,32
10 rows selected.
---測試下由zhs16gbk到al32utf8
[oracle@seconary admin]$ env|grep SID
ORACLE_SID=charzhs
[oracle@seconary admin]$ exp userid=scott/system file=exp_charzhs.dmp tables=t_charset_bak
Export: Release 11.2.0.1.0 - Production on Sun Nov 29 22:00:57 2015
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 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table T_CHARSET_BAK 524288 rows exported
Export terminated successfully without warnings.
[oracle@seconary admin]$ imp userid=scott/system file=exp_charzhs.dmp tables=t_charset_bak
ORA-12899: value too large for column "SCOTT"."T_CHARSET_BAK"."A" (actual: 13, maximum: 10)
Column 1 我們
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "SCOTT"."T_CHARSET_BAK"."A" (actual: 13, maximum: 10)
Column 1 我們
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "SCOTT"."T_CHARSET_BAK"."A" (actual: 13, maximum: 10)
Column 1 我們
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "SCOTT"."T_CHARSET_BAK"."A" (actual: 13, maximum: 10)
Column 1 我們
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "SCOTT"."T_CHARSET_BAK"."A" (actual: 13, maximum: 10)
Column 1 我們
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "SCOTT"."T_CHARSET_BAK"."A" (actual: 13, maximum: 10)
--調整表的列長度為13位元組
SQL> desc t_charset_bak;
Name Null? Type
----------------------------------------- -------- ----------------------------
A CHAR(10)
SQL> alter table t_charset_bak modify a char(13);
Table altered.
--調整列長度後匯入成功
[oracle@seconary admin]$ imp userid=scott/system file=exp_charzhs.dmp tables=t_charset_bak ignore=y
Import: Release 11.2.0.1.0 - Production on Sun Nov 29 22:43:15 2015
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 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing SCOTT's objects into SCOTT
. importing SCOTT's objects into SCOTT
. . importing table "T_CHARSET_BAK" 524288 rows imported
Import terminated successfully without warnings.
[oracle@seconary admin]$
SQL> col dump(a) for a80
SQL> select a,dump(a) from t_charset_bak where rownum<=10;
A DUMP(A)
-------------------------- --------------------------------------------------------------------------------
我們 Typ=96 Len=13: 233,142,180,230,136,156,230,187,145,32,32,32,32
我們 Typ=96 Len=13: 233,142,180,230,136,156,230,187,145,32,32,32,32
我們 Typ=96 Len=13: 233,142,180,230,136,156,230,187,145,32,32,32,32
我們 Typ=96 Len=13: 233,142,180,230,136,156,230,187,145,32,32,32,32
我們 Typ=96 Len=13: 233,142,180,230,136,156,230,187,145,32,32,32,32
我們 Typ=96 Len=13: 233,142,180,230,136,156,230,187,145,32,32,32,32
我們 Typ=96 Len=13: 233,142,180,230,136,156,230,187,145,32,32,32,32
我們 Typ=96 Len=13: 233,142,180,230,136,156,230,187,145,32,32,32,32
我們 Typ=96 Len=13: 233,142,180,230,136,156,230,187,145,32,32,32,32
我們 Typ=96 Len=13: 233,142,180,230,136,156,230,187,145,32,32,32,32
10 rows selected.
SQL> select a,trim(a),dump(trim(a)) from t_charset_bak where rownum<=10;
A TRIM(A) DUMP(TRIM(A))
-------------------------- -------------------------- ------------------------------------------------------------
我們 我們 Typ=1 Len=9: 233,142,180,230,136,156,230,187,145
我們 我們 Typ=1 Len=9: 233,142,180,230,136,156,230,187,145
我們 我們 Typ=1 Len=9: 233,142,180,230,136,156,230,187,145
我們 我們 Typ=1 Len=9: 233,142,180,230,136,156,230,187,145
我們 我們 Typ=1 Len=9: 233,142,180,230,136,156,230,187,145
我們 我們 Typ=1 Len=9: 233,142,180,230,136,156,230,187,145
我們 我們 Typ=1 Len=9: 233,142,180,230,136,156,230,187,145
我們 我們 Typ=1 Len=9: 233,142,180,230,136,156,230,187,145
我們 我們 Typ=1 Len=9: 233,142,180,230,136,156,230,187,145
我們 我們 Typ=1 Len=9: 233,142,180,230,136,156,230,187,145
10 rows selected.
---官方說通過配置如下引數blank_trimming=TRUE可以，經測確實可以匯入ZHS1GGBK到AL32UTF8的CHAR資料
SQL> show parameter trim
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
blank_trimming boolean FALSE
SQL>
SQL> conn /as sysdba
Connected.
SQL> alter system set blank_trimming=true;
alter system set blank_trimming=true
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
SQL> alter system set blank_trimming=true scope=spfile;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1653518336 bytes
Fixed Size 2213896 bytes
Variable Size 956303352 bytes
Database Buffers 687865856 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.
SQL> show parameter trim
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
blank_trimming boolean TRUE
SQL>
SQL> conn scott/system
Connected.
SQL> drop table t_charset_bak purge;
Table dropped.
[oracle@seconary admin]$ env|grep SID
ORACLE_SID=utf8char
[oracle@seconary admin]$ imp userid=scott/system file=exp_charzhs.dmp tables=t_charset_bak
Import: Release 11.2.0.1.0 - Production on Sun Nov 29 22:56:02 2015
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 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing SCOTT's objects into SCOTT
. importing SCOTT's objects into SCOTT
. . importing table "T_CHARSET_BAK" 524288 rows imported
Import terminated successfully without warnings.
SQL> select a,trim(a),dump(trim(a)) from t_charset_bak where rownum<=10;
A TRIM(A) DUMP(TRIM(A))
-------------------- ------------------------------ --------------------------------------------------------------------------------
我們 我們 Typ=1 Len=9: 233,142,180,230,136,156,230,187,145
我們 我們 Typ=1 Len=9: 233,142,180,230,136,156,230,187,145
我們 我們 Typ=1 Len=9: 233,142,180,230,136,156,230,187,145
我們 我們 Typ=1 Len=9: 233,142,180,230,136,156,230,187,145
我們 我們 Typ=1 Len=9: 233,142,180,230,136,156,230,187,145
我們 我們 Typ=1 Len=9: 233,142,180,230,136,156,230,187,145
我們 我們 Typ=1 Len=9: 233,142,180,230,136,156,230,187,145
我們 我們 Typ=1 Len=9: 233,142,180,230,136,156,230,187,145
我們 我們 Typ=1 Len=9: 233,142,180,230,136,156,230,187,145
我們 我們 Typ=1 Len=9: 233,142,180,230,136,156,230,187,145
10 rows selected.
---測試下基於varchar2型別，由zhs16gbk到al32utf8,可見varchar就比char更好用，在資料遷移中，它會更好，且消耗空間少，所以在設計表結構時，一定要進行仔細考慮
---zhs16gbk
SQL> create table t_varchar(a varchar2(10));
Table created.
SQL> insert into t_charset values('我們');
1 row created.
SQL> commit;
Commit complete.
[oracle@seconary admin]$ exp userid=scott/system file=exp_charzhs_varchar.dmp tables=t_varchar
Export: Release 11.2.0.1.0 - Production on Sun Nov 29 23:12:39 2015
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 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table T_VARCHAR 1 rows exported
Export terminated successfully without warnings.
[oracle@seconary admin]$ export ORACLE_SID=utf8char
[oracle@seconary admin]$ imp userid=scott/system file=exp_charzhs_varchar.dmp tables=t_varchar
Import: Release 11.2.0.1.0 - Production on Sun Nov 29 23:13:21 2015
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 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing SCOTT's objects into SCOTT
. importing SCOTT's objects into SCOTT
. . importing table "T_VARCHAR" 1 rows imported
Import terminated successfully without warnings.
[oracle@seconary admin]$
SQL> select a,trim(a),dump(trim(a)) from t_charset_bak where rownum<=10;
A TRIM(A) DUMP(TRIM(A))
-------------------- ------------------------------ --------------------------------------------------------------------------------
我們 我們 Typ=1 Len=9: 233,142,180,230,136,156,230,187,145
我們 我們 Typ=1 Len=9: 233,142,180,230,136,156,230,187,145
我們 我們 Typ=1 Len=9: 233,142,180,230,136,156,230,187,145
我們 我們 Typ=1 Len=9: 233,142,180,230,136,156,230,187,145
我們 我們 Typ=1 Len=9: 233,142,180,230,136,156,230,187,145
我們 我們 Typ=1 Len=9: 233,142,180,230,136,156,230,187,145
我們 我們 Typ=1 Len=9: 233,142,180,230,136,156,230,187,145
我們 我們 Typ=1 Len=9: 233,142,180,230,136,156,230,187,145
我們 我們 Typ=1 Len=9: 233,142,180,230,136,156,230,187,145
我們 我們 Typ=1 Len=9: 233,142,180,230,136,156,230,187,145
10 rows selected.
---測試下zhs16gbk到al32utf8的CLOB列的遷移,可見把字元型別的資料，可以定義為clob,就可以避免VARCHAR或CHAR型別引發的資料損失或丟失或破壞
SQL> create table t_clob(a clob);
Table created.
SQL> insert into t_clob values('我們');
1 row created.
SQL> commit;
Commit complete.
SQL> select a,dump(a) from t_clob;
select a,dump(a) from t_clob
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CLOB
SQL> select a from t_clob;
A
--------------------------------------------------------------------------------
我們
ORACLE_SID=charzhs
[oracle@seconary admin]$ exp userid=scott/system file=exp_charzhs_clob.dmp tables=t_clob
Export: Release 11.2.0.1.0 - Production on Mon Nov 30 00:32:05 2015
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 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table T_CLOB 1 rows exported
Export terminated successfully without warnings.
[oracle@seconary admin]$
[oracle@seconary admin]$ export ORACLE_SID=utf8char
[oracle@seconary admin]$ imp userid=scott/system file=exp_charzhs_clob.dmp tables=t_clob
Import: Release 11.2.0.1.0 - Production on Mon Nov 30 00:32:36 2015
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 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing SCOTT's objects into SCOTT
. importing SCOTT's objects into SCOTT
. . importing table "T_CLOB" 1 rows imported
Import terminated successfully without warnings.
SQL> select a,dump(a) from t_clob;
select a,dump(a) from t_clob
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CLOB
SQL> select a from t_clob;
A
--------------------------------------------------------------------------------
我們
---測試下使用者密碼為非ASCII字元，基於ZHS16GBK到AL32UTF8
----可見使用者密碼不能包括中文字元
[oracle@seconary admin]$ env|grep SID
ORACLE_SID=charzhs
SQL> alter user scott identified by '1翟勳楊1' account unlock;
alter user scott identified by '1翟勳楊1' account unlock
*
ERROR at line 1:
ORA-00988: missing or invalid password(s)
SQL> desc user$;
Name Null? Type
----------------------------------------- -------- ----------------------------
USER# NOT NULL NUMBER
NAME NOT NULL VARCHAR2(30)
TYPE# NOT NULL NUMBER
PASSWORD VARCHAR2(30)
SQL> create user zxy翟z identified by system account unlock;
User created.
SQL> select username from dba_users where lower(username)='zxy翟z';
USERNAME
------------------------------
ZXY翟z
SQL> grant resource,connect to zxy翟z;
Grant succeeded.
SQL> conn zxy翟z/system
Connected.
SQL> create table t_testv(a int);
Table created.
SQL> insert into t_testv values(1);
1 row created.
SQL> commit;
Commit complete.
[oracle@seconary admin]$ exp userid=zxy翟z/system file=exp_user1.dmp
Export: Release 11.2.0.1.0 - Production on Mon Nov 30 00:56:04 2015
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 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user ZXY翟z
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user ZXY翟z
About to export ZXY翟z's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export ZXY翟z's tables via Conventional Path ...
. . exporting table T_TESTV 1 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.
[oracle@seconary admin]$
--可見確實使用者密碼只能是ASCII，不能包括特殊字元，比如中文，否則會報錯，無法遷移資料，只能採用重建對應的使用者，然後遷移到這個新使用者
[oracle@seconary admin]$ export ORACLE_SID=utf8char
[oracle@seconary admin]$ imp userid=zxy翟z/system file=exp_user1.dmp
Import: Release 11.2.0.1.0 - Production on Mon Nov 30 00:57:14 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
IMP-00058: ORACLE error 1017 encountered
ORA-01017: invalid username/password; logon deniedUsername:
Password:
ORA-01017: invalid username/password; logon denied
IMP-00005: all allowable logon attempts failed
IMP-00000: Import terminated unsuccessfully
SQL> create user ch_china identified by system account unlock;
User created.
SQL> grant resource,connect to ch_china;
Grant succeeded.
[oracle@seconary admin]$ imp userid=system/system fromuser=zxy翟z touser=ch_china file=exp_user1.dmp
Import: Release 11.2.0.1.0 - Production on Mon Nov 30 01:00:43 2015
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 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
Warning: the objects were exported by ZXY翟z, not by you
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing ZXY翟z's objects into CH_CHINA
. . importing table "T_TESTV" 1 rows imported
Import terminated successfully without warnings.
[oracle@seconary admin]$ sqlplus ch_china/system
SQL*Plus: Release 11.2.0.1.0 Production on Mon Nov 30 01:01:04 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from t_testv;
A
----------
1
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9240380/viewspace-1850079/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle資料庫字符集characterset遷移及變更系列一Oracle資料庫
- oracle資料庫字符集characterset遷移及變更之csscan及csalter.plb系列三Oracle資料庫CSS
- oracle資料庫字符集characterset遷移及變更之csscan及csalter.plb系列四Oracle資料庫CSS
- 遷移資料時oracle字符集的轉換遷移資料時oracle字符集的轉換Oracle
- ORACLE資料庫遷移Oracle資料庫
- oracle10g_csscan_更變資料庫字符集及國家字符集的工具測試OracleCSS資料庫
- 資料庫-oracle-資料庫遷移資料庫Oracle
- Oracle資料庫資料遷移流程Oracle資料庫
- oracle RAC 更換儲存遷移資料Oracle
- oracle 遷移資料庫到asmOracle資料庫ASM
- OGG資料庫遷移方案（二）資料庫
- 資料庫的升級及遷移資料庫
- Oracle資料庫（資料泵）遷移方案（上）Oracle資料庫
- Oracle資料庫（資料泵）遷移方案（下）Oracle資料庫
- 無外網Oracle資料庫遷移Oracle資料庫
- Oracle資料庫（DataGuard）遷移方案(上)Oracle資料庫
- Oracle資料庫（DataGuard）遷移方案（中）Oracle資料庫
- Oracle資料庫（DataGuard）遷移方案（下）Oracle資料庫
- 【DATAPUMP】使用DataPump遷移Oracle資料庫Oracle資料庫
- Oracle資料庫遷移之一：RMANOracle資料庫
- Oracle資料庫遷移方案比較Oracle資料庫
- 【遷移】使用rman遷移資料庫資料庫
- 【資料遷移】RMAN遷移資料庫到ASM(二)切換資料檔案到ASM資料庫ASM
- 資料庫遷移資料庫
- ORACLE 資料遷移Oracle
- 使用RMAN進行Oracle資料庫遷移Oracle資料庫
- ORACLE資料庫切換和遷移方案Oracle資料庫
- Oracle遷移資料庫過程記錄Oracle資料庫
- 跨平臺遷移oracle資料庫指南Oracle資料庫
- 資料庫遷移 ：理解資料庫
- laravel資料庫遷移Laravel資料庫
- Odoo遷移資料庫Odoo資料庫
- redis資料庫遷移Redis資料庫
- Cacti 遷移資料庫資料庫
- 資料庫遷移方案資料庫
- 遷移資料庫成功！資料庫
- 建立資料庫遷移資料庫
- oracle資料庫遷移-文件 -LINUX_TO_WINDOWSOracle資料庫LinuxWindows