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.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9240380/viewspace-1850408/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle資料庫字符集characterset遷移及變更之csscan及csalter.plb系列三Oracle資料庫CSS
- oracle資料庫字符集characterset遷移及變更系列一Oracle資料庫
- oracle資料庫字符集characterset遷移及變更系列二Oracle資料庫
- oracle10g_csscan_更變資料庫字符集及國家字符集的工具測試OracleCSS資料庫
- 資料庫的升級及遷移資料庫
- 遷移資料時oracle字符集的轉換遷移資料時oracle字符集的轉換Oracle
- ORACLE資料庫遷移Oracle資料庫
- 資料庫-oracle-資料庫遷移資料庫Oracle
- Oracle資料庫資料遷移流程Oracle資料庫
- db2匯出資料庫定義及遷移資料DB2資料庫
- OGG資料庫遷移方案（四）資料庫
- oracle RAC 更換儲存遷移資料Oracle
- sqoop安裝及資料遷移OOP
- oracle 遷移資料庫到asmOracle資料庫ASM
- Oracle資料庫（資料泵）遷移方案（上）Oracle資料庫
- Oracle資料庫（資料泵）遷移方案（下）Oracle資料庫
- 資料庫遷移之資料泵實驗資料庫
- 無外網Oracle資料庫遷移Oracle資料庫
- Oracle資料庫（DataGuard）遷移方案(上)Oracle資料庫
- Oracle資料庫（DataGuard）遷移方案（中）Oracle資料庫
- Oracle資料庫（DataGuard）遷移方案（下）Oracle資料庫
- 【DATAPUMP】使用DataPump遷移Oracle資料庫Oracle資料庫
- Oracle資料庫遷移之一：RMANOracle資料庫
- Oracle資料庫遷移方案比較Oracle資料庫
- Laravel 學習之資料庫遷移Laravel資料庫
- ORM實操之資料庫遷移ORM資料庫
- 【遷移】使用rman遷移資料庫資料庫
- 資料庫遷移資料庫
- ORACLE 資料遷移Oracle
- Core Data資料遷移及單元測試
- ODI的安裝及後設資料遷移
- 使用RMAN進行Oracle資料庫遷移Oracle資料庫
- ORACLE資料庫切換和遷移方案Oracle資料庫
- Oracle遷移資料庫過程記錄Oracle資料庫
- 跨平臺遷移oracle資料庫指南Oracle資料庫
- 異構資料庫資料遷移 oracle to mysql之oracle sqlloader和mysql load data資料庫OracleMySql
- 資料庫遷移 ：理解資料庫
- laravel資料庫遷移Laravel資料庫