Shulou(Shulou.com)06/01 Report--

Reproduced from: http://blog.itpub.net/4227/viewspace-706635/

The tool csscan is used to check whether the data in the database will be lost or truncated when converting from one character set to another.

This tool has been around for a long time, but since there is not much work on character set conversion for a long time, there is little research on this tool. This time I need to convert ZHS16GBK to AL32UTF8. I tried the function of csscan and found that this tool is still very convenient.

[oracle@dbserver1 bin] $csscan userid=thams/thams table=libfile722 tochar=AL32UTF8 log=/home/oracle/scan_722

Character Set Scanner v2.2: Release 11.2.0.2.0-Production on Wed Aug 31 10:54:15 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

CSS-00127: user thams does not have DBA privilege

Scanner terminated unsuccessfully.

The error message is clear that the connection user is not a DBA role, if you use the system user connection to make this command:

[oracle@dbserver1 bin] $csscan userid=system/oracle table=thams.libfile722 tochar=AL32UTF8 log=/home/oracle/scan_722

Character Set Scanner v2.2: Release 11.2.0.2.0-Production on Wed Aug 31 10:54:45 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0-64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

And Real Application Testing options

CSS-00107: Character set migration utility schema not installed

Scanner terminated unsuccessfully.

This error is caused by the need for the CSSCAN tool to create a CSMIG user in the database:

[oracle@dbserver1 bin] $sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Wed Aug 31 10:57:28 2011

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0-64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

And Real Application Testing options

SQL > @? / rdbms/admin/csminst

User created.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

User altered.

1 row created.

1 row updated.

Table created.

.

.

.

View created.

View created.

View created.

View created.

Grant succeeded.

Grant succeeded.

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0-64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

And Real Application Testing options

[oracle@dbserver1 bin] $csscan userid=system/oracle table=thams.libfile722 tochar=AL32UTF8 log=/home/oracle/scan_722

Character Set Scanner v2.2: Release 11.2.0.2.0-Production on Wed Aug 31 10:59:35 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0-64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

And Real Application Testing options

Enter array fetch buffer size: 1024000 > 4096000

Enter number of scan processes to utilize (1.. 64): 1 >

Enumerating table to scan...

. Process 1 scanning THAMS.LIBFILE722 [AAAP2KAAAAAB7XYAAA]

Creating Database Scan Summary Report...

Creating Individual Exception Report...

Scanner terminated successfully.

After calling the csminst.sql script to create auxiliary users and objects, run the csscan tool again to scan the data in the table before conversion.

The tool csscan can be called on the command line and interactively. When the command line does not provide enough parameters, the tool will get other parameters interactively.

After the operation is completed, you can check the corresponding log information:

[oracle@dbserver1 bin] $more / home/oracle/scan_722.err

Database Scan Individual Exception Report

[Database Scan Parameters]

Parameter Value

-

CSSCAN Version v2.1

Instance Name fhacdb1

Database Version 11.2.0.2.0

Scan type Selective tables

Scan CHAR data? YES

Database character set ZHS16GBK

FROMCHAR ZHS16GBK

TOCHAR AL32UTF8

Scan NCHAR data? NO

Array fetch buffer size 4096000

Number of processes 1

Capture convertible data? NO

-

[Data Dictionary individual exceptions]

[Application data individual exceptions]

User: THAMS

Table: LIBFILE722

Column: F4

Type: VARCHAR2 (42)

Number of Exceptions: 1

Max Post Conversion Data Size: 45

ROWID Exception Type Size Cell Data (first 30 bytes)

AAAP2KAAAAAB+u0AAE exceed column size 45 inspects the imperial history of Taiwan, science, government, Shaanxi and western regions

[oracle@dbserver1 bin] $more / home/oracle/scan_722.out

Character Set Scanner v2.2: Release 11.2.0.2.0-Production on Wed Aug 31 10:59:35 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0-64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

And Real Application Testing options

Enter array fetch buffer size: 1024000 >

Enter number of scan processes to utilize (1.. 64): 1 >

Enumerating table to scan...

. Process 1 scanning THAMS.LIBFILE722 [AAAP2KAAAAAB7XYAAA]

Creating Database Scan Summary Report...

Creating Individual Exception Report...

Scanner terminated successfully.

[oracle@dbserver1 bin] $more / home/oracle/scan_722.txt

Database Scan Summary Report

Time Started: 2011-08-31 10:59:48

Time Completed: 2011-08-31 10:59:50

Process ID Time Started Time Completed

1 2011-08-31 10:59:49 2011-08-31 10:59:49

[Database Size]

Tablespace Used Free Total Expansion

--

SYSTEM 581.81M 118.19M 700.00M .00K

SYSAUX 521.38M 78.63M 600.00M .00K

UNDOTBS1 7.44M 20699.56M 20707.00M .00K

TEMP .00K .00K .00K

USERS 1930.38M 139.88M 2070.25M .00K

LOB_AU2M 8176.13M 1937423.88M 1945600.00M 212.00K

LOB_AU8M 10074.69M 501925.31M 512000.00M .00K

DATA_ARCHIVE 5332.06M 1018667.94M 1024000.00M .00K

LOB_AU32M 4772.38M 507227.63M 512000.00M .00K

LOB_AU64M 4548.38M 507451.63M 512000.00M .00K

--

Total 35944.63M 4493732.63M 4529677.25M 212.00K

[Database Scan Parameters]

Parameter Value

-

CSSCAN Version v2.1

Instance Name fhacdb1

Database Version 11.2.0.2.0

Scan type Selective tables

Scan CHAR data? YES

Database character set ZHS16GBK

FROMCHAR ZHS16GBK

TOCHAR AL32UTF8

Scan NCHAR data? NO

Array fetch buffer size 4096000

Number of processes 1

Capture convertible data? NO

-

[Scan Summary]

Some character type application data are not convertible to the new character set

[Data Dictionary Conversion Summary]

Data Dictionary Tables:

Datatype Changeless Convertible Truncation Lossy

--

VARCHAR2 0 0 0

CHAR 0 0 0

LONG 0 0 0

VARRAY 0 0 0

--

Total 0 0 0

Total in percentage 0.000% 0.000% 0.000% 0.000%

XML CSX Dictionary Tables:

Datatype Changeless Convertible Truncation Lossy

--

VARCHAR2 0 0 0

CHAR 0 0 0

LONG 0 0 0

VARRAY 0 0 0

--

Total 0 0 0

Total in percentage 0.000% 0.000% 0.000% 0.000%

[Application Data Conversion Summary]

Datatype Changeless Convertible Truncation Lossy

--

VARCHAR2 222683 28308 1 0

CHAR 0 0 0

LONG 0 0 0

VARRAY 0 0 0

--

Total 222683 28308 1 0

Total in percentage 88.721% 11.278% 0.000% 0.000%

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

Data Dictionary Tables:

USER.TABLE Convertible Truncation Lossy

XML CSX Dictionary Tables:

USER.TABLE Convertible Truncation Lossy

Application Data:

USER.TABLE Convertible Truncation Lossy

THAMS.LIBFILE722 28308 1 0

[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

THAMS.LIBFILE722 | F11 4648 0

THAMS.LIBFILE722 | F2 4643

THAMS.LIBFILE722 | F3 4648 0

THAMS.LIBFILE722 | F46 222000

THAMS.LIBFILE722 | F6 4312

THAMS.LIBFILE722 | F7 565 00

THAMS.LIBFILE722 | TITLE 4642 0

[Indexes to be Rebuilt]

USER.INDEX on USER.TABLE (COLUMN)

-

-

The tool csscan generates three logs, one err records the error message, for example, in this case, the length of a column needs to be longer, otherwise it cannot accommodate the extension of the data length; a log records the operation steps; and txt is the final summary information.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.