oracle資料庫字符集characterset遷移及變更之csscan及csalter.plb系列四

背景

  資料庫字符集涉及的概念與知識非常多，本文繼續學習；相關文章連結見下：


  oracle資料庫字符集characterset遷移及變更之csscan及csalter.plb系列三 
  http://blog.itpub.net/9240380/viewspace-1850149/

結論

1,al32utf8也可以透過csscan及csalter指令碼轉化為字符集zhs16gbk
  處理方法見下
2,如果僅要遷移資料庫中部分資料到另一種字符集，可以在csscan指定user,
  然後把這些資料匯入到目標資料庫中即可


3,遷移資料庫字符集要結合csscan及csalter指令碼，缺一不可
4,如果源資料庫中包括lengthb的儲存過程，遷移字符集後，其狀態仍是合理
  當然可能還是測試不夠充分導致


5，遷移字符集後，相關一些物件也要注意重建或重新啟用，以防影響業務

分析思路

  

測試

---字符集轉化或變更後，lengthb及nvarchar2相關的程式碼或儲存過程是否仍合理
-----源字符集是AL32UTF8
SQL> select parameter,value from nls_database_parameters where parameter='NLS_CHARACTERSET';


PARAMETER                      VALUE
------------------------------ ------------------------------
NLS_CHARACTERSET               AL32UTF8


SQL> conn scott/system
Connected.
SQL> create table t_migtest(a nvarchar2(10));


Table created.


SQL> insert into t_migtest values('我們');


1 row created.


SQL> commit;


Commit complete.


SQL> select a,dump(a) from t_migtest;


A                    DUMP(A)
-------------------- --------------------------------------------------
我們                 Typ=1 Len=6: 147,180,98,28,110,209


create or replace procedure proc_mig
as
v_length pls_integer;
begin
select lengthb(a) into v_length from t_migtest;
dbms_output.put_line(v_length);
end;
/


Procedure created.


SQL> exec proc_mig;
6


PL/SQL procedure successfully completed.


[oracle@seconary admin]$ csscan userid=\'sys/system as sysdba\' full=y tochar=ZHS16GBK array=1024000 process=10
中間略
. process 10 scanning CTXSYS.DR$PENDING
. process 1 scanning CTXSYS.DR$ONLINE_PENDING
. process 4 scanning CTXSYS.DR$PARALLEL
. process 3 scanning CTXSYS.DR$DBO
. process 9 scanning CTXSYS.DR$INDEX_CDI_COLUMN
. process 6 scanning EXFSYS.RLM$ERRCODE
. process 8 scanning CTXSYS.DR$SDATA_UPDATE
. process 1 scanning EXFSYS.RLM$RULESETSTCODE
. process 5 scanning EXFSYS.RLM$EVENTSTRUCT
. process 2 scanning EXFSYS.RLM$RULESET


Creating Database Scan Summary Report...


Creating Individual Exception Report...


Scanner terminated successfully.
[oracle@seconary admin]$ ll -l scan*
-rw-r--r-- 1 oracle oinstall  1357 Nov 30 05:36 scan.err
-rw-r--r-- 1 oracle oinstall 88850 Nov 30 05:36 scan.out
-rw-r--r-- 1 oracle oinstall  8693 Nov 30 05:36 scan.txt


[oracle@seconary admin]$ more scan.txt
Application Data:


USER.TABLE                                              Convertible       Truncation            Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
SCOTT.T_CHARSET                                             524,289                0                0
SCOTT.T_CHARSET_BAK                                         524,288                0                0
SCOTT.T_VARCHAR                                                   1                0                0
-------------------------------------------------- ---------------- ---------------- ----------------


[oracle@seconary admin]$ exp userid=scott/system  file=exp_scott_multi_table.dmp tables=t_charset,t_charset_bak,t_varchar


Export: Release 11.2.0.1.0 - Production on Mon Nov 30 05:40:41 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     524289 rows exported
. . exporting table                  T_CHARSET_BAK     524288 rows exported
. . exporting table                      T_VARCHAR          1 rows exported
Export terminated successfully without warnings.




SQL> conn scott/system
Connected.
SQL> drop table t_charset purge;


Table dropped.


SQL> drop table t_charset_bak purge;


Table dropped.


SQL> drop table t_varchar purge;


Table dropped.


[oracle@seconary admin]$ rm -rf scan*
[oracle@seconary admin]$ 


[oracle@seconary admin]$ csscan userid=\'sys/system as sysdba\' full=y tochar=ZHS16GBK array=1024000 process=10


中間略
. process 3 scanning CTXSYS.DR$INDEX_CDI_COLUMN
. process 4 scanning CTXSYS.DR$SDATA_UPDATE
. process 7 scanning EXFSYS.RLM$RULESETSTCODE
. process 5 scanning EXFSYS.RLM$ERRCODE
. process 10 scanning EXFSYS.RLM$RULESET
. process 6 scanning EXFSYS.RLM$EVENTSTRUCT


Creating Database Scan Summary Report...


Creating Individual Exception Report...


oracle@seconary admin]$ more scan.txt
Application Data:


USER.TABLE                                              Convertible       Truncation            Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
-------------------------------------------------- ---------------- ---------------- ----------------


[Distribution of Convertible, Truncated and Lossy Data by Column]


Data Dictionary Tables:


USER.TABLE|COLUMN                                       Convertible       Truncation            Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
-------------------------------------------------- ---------------- ---------------- ----------------


XML CSX Dictionary Tables:


USER.TABLE|COLUMN                                       Convertible       Truncation            Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
-------------------------------------------------- ---------------- ---------------- ----------------


Application Data:


USER.TABLE|COLUMN                                       Convertible       Truncation            Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
-------------------------------------------------- ---------------- ---------------- ----------------


[Indexes to be Rebuilt]




[oracle@seconary admin]$ cd /oracle/product/11.2.0/db_1/rdbms/admin
[oracle@seconary admin]$ sqlplus '/as sysdba'


SQL*Plus: Release 11.2.0.1.0 Production on Mon Nov 30 05:46:27 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> @@csalter.plb


0 rows created.




Function created.




Function created.




Procedure created.


This script will update the content of the Oracle Data Dictionary.
Please ensure you have a full backup before initiating this procedure.
Would you like to proceed (Y/N)?Y
old   6:     if (UPPER('&conf') <> 'Y') then
new   6:     if (UPPER('Y') <> 'Y') then
Checking data validity...
begin converting system objects


PL/SQL procedure successfully completed.


Alter the database character set...
CSALTER operation completed, please restart database


PL/SQL procedure successfully completed.




0 rows deleted.




Function dropped.




Function dropped.




Procedure dropped.


SQL> 




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             922748920 bytes
Database Buffers          721420288 bytes
Redo Buffers                7135232 bytes
Database mounted.
Database opened.


SQL> select parameter,value from nls_database_parameters where parameter='NLS_CHARACTERSET';


PARAMETER                      VALUE
------------------------------ ------------------------------
NLS_CHARACTERSET               ZHS16GBK


[oracle@seconary admin]$ imp userid=scott/system  file=exp_scott_multi_table.dmp tables=t_charset,t_charset_bak,t_varchar


Import: Release 11.2.0.1.0 - Production on Mon Nov 30 05:59: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
. importing SCOTT's objects into SCOTT
. importing SCOTT's objects into SCOTT
. . importing table                    "T_CHARSET"     524289 rows imported
. . importing table                "T_CHARSET_BAK"     524288 rows imported
. . importing table                    "T_VARCHAR"          1 rows imported
Import terminated successfully without warnings.
[oracle@seconary admin]$ 




SQL> select status,count(*) from user_objects group by status;


STATUS    COUNT(*)
------- ----------
VALID           12


SQL> col dump(a) for a50
SQL> select a,dump(a) from t_migtest;


A                    DUMP(A)
-------------------- --------------------------------------------------
我們                 Typ=1 Len=6: 147,180,98,28,110,209


SQL> set serveroutput on
SQL> exec proc_mig;
6


PL/SQL procedure successfully completed.