Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to find out the data that may have problems before Oracle modifies the character set". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to find out the data that may have problems before Oracle modifies the character set.

Scenario 1:

Migrate A library data to B library, where A library character set ZHS16GBK,B library character set AL32UTF8

Scenario 2:

Modify the character set of A library from ZHS16GBK to AL32UTF8.

Question:

How to know in advance which data problems will occur in the above two scenarios, such as field length overflow?

Solution:

Oracle has its own Character Set Scanner (CSSCAN) tool, which can collect data with errors in the converted character set in advance.

As follows:

Oracle uses the CSSCAN tool to check whether there are errors in the converted character set of a user or table.

One: execute the csminst.sql script

SQL > @? / rdbms/admin/csminst.sql

Second, csscan uses syntax:

[oracle@cjcos01] $csscan-helpCharacter Set Scanner v2.2: Release 11.2.0.4.0-Production on Wed Jun 17 13:46:29 2020Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.You can let Scanner prompt you for parameters by entering the CSSCAN command followed by your username/password: Example: CSSCAN\ "SYSTEM/MANAGER AS SYSDBA\" Or You can control how Scanner runs by entering the CSSCAN command followed by various parameters. To specify parameters You use keywords: Example: CSSCAN\ "SYSTEM/MANAGER AS SYSDBA\" FULL=y TOCHAR=utf8 ARRAY=1024000 PROCESS=3 Keyword Default Prompt Description-USERID Yes username/password FULL N yes scan entire database USER yes owner of tables to be scanned TABLE yes list of tables to scan COLUMN yes list of columns to scan EXCLUDE list of tables to exclude from scan TOCHAR yes new database character set name FROMCHAR current database character set name TONCHAR new national character set name FROMNCHAR current national character set name ARRAY 1024000 yes size of array fetch buffer PROCESS 1 yes number of concurrent scan process MAXBLOCKS split table if block size exceed MAXBLOCKS CAPTURE N capture convertible data SUPPRESS maximum number of exceptions logged for each tableFEEDBACK report progress every N rows BOUNDARIES list of column size boundaries for summary report LASTRPT N generate report of the last database scan LOG scan base file name of report files PARFILE parameter file name PRESERVE N preserve existing scan results LCSD N no enable language and character set detection LCSDDATA LOSSY no define the scope of the detection HELP N show help screen (this screen) QUERY N select clause to scan subset of tables or columns- -- Scanner terminated successfully.

Third, use csscan tools to generate scan.err and other information

[oracle@cjcos01] $csscan Character Set Scanner v2.2: Release 11.2.0.4.0-Production on Wed Jun 17 11:47:21 2020Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.Username: sys as sysdbaPassword: Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options (1) Full database, (2) User, (3) Table, (4) Column: 1 > 2Current database character set is ZHS16GBK.Enter new database character set name: > AL32UTF8Enter array fetch buffer size: 1024000 > Enter number of scan processes to utilize (1.): 1 > Enter user name to scan: > CJC.

Four: check which tables have errors after converting the character set, and deal with them separately

[oracle@cjcos01 ~] $ls

Scan.err scan.out scan.txt

Fifth, modify the database character set

First of all, make sure that the modified character set should be the superset before the modification.

Modify the database character set:

1: execute the csalter.plb script

2:ALTER DATABASE CHARACTER...

3: modify prop$ and other tables (this method is risky and is strictly prohibited)

Reference:

Changing the NLS_CHARACTERSET to AL32UTF8 / UTF8 (Unicode) in 8i, 9i, 10g and 11g (document ID 260192.1)

Oracle modifies the database character set on a single machine

10.a.2) Execute the following commands in the database home sqlplus connected as "/ AS SYSDBA": Sqlplus / as sysdbaSPOOL Nswitch.logSHUTDOWN IMMEDIATE;STARTUP MOUNT;ALTER SYSTEM ENABLE RESTRICTED SESSION;ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;ALTER SYSTEM SET AQ_TM_PROCESSES=0;ALTER DATABASE OPEN;-- Do not do these steps on a 10g or 11g systemALTER DATABASE CHARACTER SET INTERNAL_USE AL32UTF8;SHUTDOWN IMMEDIATE -- You NEED to restart the database before doing ANY operation on this database-- exit this session now do not use the session that did alter database for other operationsEXIT-- reconnect to the database and start the databaseSqlplus / as sysdbaSTARTUP;-- in 8i you need to do the startup/shutdown 2 timesSHUTDOWN;STARTUP

Oracle RAC modifies the database character set

If you are using RAC you will need to start the database in single instance with CLUSTER_DATABASE = FALSERun in the database home sqlplus connected as "/ AS SYSDBA": Sqlplus / as sysdba-- Make sure the CLUSTER_DATABASE parameter is set-- to false or it is not set at all.-- If you are using RAC you will need to start the database in single instance-- with CLUSTER_DATABASE = FALSEsho parameter CLUSTER_DATABASE-- if you are using spfileSELECT DECODE (value, NULL, 'PFILE',' SPFILE') "Init File Type" FROM sys.v_$parameter WHERE name = 'spfile' -- note the values forsho parameter job_queue_processessho parameter aq_tm_processes-- (this is Bug 6005344 fixed in 11g)-- then doshutdownstartup restrictSPOOL Nswitch.logPURGE DBA_RECYCLEBIN/-- next select should only give ONE row-your sqlplus connection-- If more then one session is connected Csalter will fail and report "Sorry only one session is allowed to run this script" .Select sid, serial#, username, status, osuser, machine, process, program FROM v$session WHERE username IS NOT NULL -- do this alter system or you might run into "ORA-22839: Direct updates on SYS_NC columns are disallowed"-- This is only needed in 11.1.0.6, fixed in 11.1.0.7, not applicable to 10.2 or lower-- ALTER SYSTEM SET EVENTS '22838 TRACE NAME CONTEXT LEVEL 1 -- then run Csalter.plb: @? / rdbms/admin/csalter.plb-- Csalter will aks confirmation-do not copy paste the whole actions on one time-- sample Csalter output:-- 3 rows 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 old N)? Ymuri-old 6: if (UPPER ('& conf')'Y') then-- New 6: if ( UPPER ('y')'Y') then-- Checking data validility...-- 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....-- Procedure dropped.-- if you are using spfile then you need to also-- ALTER SYSTEM SET job_queue_processes= SCOPE=BOTH -- ALTER SYSTEM SET aq_tm_processes= SCOPE=BOTH;SHUTDOWN IMMEDIATE;-- You NEED to restart the database before doing ANY operation on this database-- exit this session now. Do not use the session where Csalter was runned for other operations.EXIT-- reconnect to the database and start the databaseSqlplus / as sysdbaSTARTUP; so far, I believe you have a deeper understanding of "how to find out the data that may have problems before Oracle modifies the character set". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.