oracle数据库字符集characterset迁移及变更之csscan及csalter.plb系列四

最新推荐文章于 2021-04-04 06:48:20 发布

clg10051

最新推荐文章于 2021-04-04 06:48:20 发布

阅读量199

点赞数

文章标签：数据库

背景

数据库字符集涉及的概念与知识非常多，本文继续学习；相关文章链接见下：

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-1850408/