Oracle修改字符集前如何找出可能出現問題的資料?
場景1:
將A庫資料遷移到B庫,其中A庫字符集ZHS16GBK,B庫字符集AL32UTF8;
場景2:
修改A庫字符集,由ZHS16GBK改成AL32UTF8。
問題:
如何提前知道在以上兩個場景下,哪些資料會出現問題,例如欄位長度溢位等問題?
解決方案:
Oracle有自帶的Character Set Scanner(CSSCAN)工具,可以提前收集轉換字符集出現錯誤的資料。
如下:
Oracle使用CSSCAN工具,檢查某使用者或表字符集轉換後是否有錯誤
一:執行csminst.sql指令碼
SQL> @?/rdbms/admin/csminst.sql
二:csscan使用語法:
[oracle@cjcos01 ~]$ csscan -help Character Set Scanner v2.2 : Release 11.2.0.4.0 - Production on Wed Jun 17 13:46:29 2020 Copyright (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 table FEEDBACK 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.
三:使用csscan工具,生成scan.err等資訊
[oracle@cjcos01 ~]$ csscan Character Set Scanner v2.2 : Release 11.2.0.4.0 - Production on Wed Jun 17 11:47:21 2020 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Username: sys as sysdba Password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options (1)Full database, (2)User, (3)Table, (4)Column: 1 > 2 Current database character set is ZHS16GBK. Enter new database character set name: > AL32UTF8 Enter array fetch buffer size: 1024000 > Enter number of scan processes to utilize(1..): 1 > Enter user name to scan: > CJC ......
四:檢查轉換字符集後哪些表出現錯誤,單獨處理
[oracle@cjcos01 ~]$ ls
scan.err scan.out scan.txt
五:修改資料庫字符集
首先要確定修改後的字符集應該是修改前的超集。
修改資料庫字符集方式:
1:執行csalter.plb指令碼
2:ALTER DATABASE CHARACTER ...
3: 修改prop$等表(此方法有風險,嚴禁使用)
參考:
Changing the NLS_CHARACTERSET to AL32UTF8 / UTF8 (Unicode) in 8i, 9i , 10g and 11g (文件 ID 260192.1)
Oracle 單機修改資料庫字符集
10.a.2) Execute the following commands in the database home sqlplus connected as "/ AS SYSDBA": Sqlplus / as sysdba SPOOL Nswitch.log SHUTDOWN 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 system ALTER 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 operations EXIT -- reconnect to the database and start the database Sqlplus / as sysdba STARTUP; -- in 8i you need to do the startup/shutdown 2 times SHUTDOWN; STARTUP;
Oracle RAC 修改資料庫字符集
If you are using RAC you will need to start the database in single instance with CLUSTER_DATABASE = FALSE Run 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 = FALSE sho parameter CLUSTER_DATABASE -- if you are using spfile SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type" FROM sys.v_$parameter WHERE name = 'spfile'; -- note the values for sho parameter job_queue_processes sho parameter aq_tm_processes -- (this is Bug 6005344 fixed in 11g ) -- then do shutdown startup restrict SPOOL Nswitch.log PURGE 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,FOREVER'; -- 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/N)?y -- 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=<original value> SCOPE=BOTH; -- ALTER SYSTEM SET aq_tm_processes=<original value> 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 database Sqlplus / as sysdba STARTUP;
歡迎關注我的微信公眾號"IT小Chen",共同學習,共同成長!!
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29785807/viewspace-2698959/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 修改Oracle資料庫字符集(zt)Oracle資料庫
- 「Oracle」資料庫字符集編碼修改Oracle資料庫
- Oracle 字符集修改Oracle
- Oracle 19C上線後可能出現的問題彙總(全)Oracle
- 遷移ORACLE資料到MogDB/openGauss時的字符集問題Oracle
- 如何在10g中修改資料庫字符集資料庫
- 單例模式中的DDCP可能出現的問題單例模式
- 虛擬主機使用中可能出現的問題
- 關於CSS中的float可能出現的小問題CSS
- JAVA訪問雲資料mysql出現問題JavaMySql
- 如何解決各個行業出現的資料洩露問題?行業
- 如何解決Hive中經常出現的資料傾斜問題Hive
- a-textarea(textarea)出現模糊問題的可能解決方案
- 修改Oracle字符集為ZHS16GBKOracle
- Laravel 安裝 voyager 出現的資料庫問題Laravel資料庫
- 如何處理Oracle資料庫中的壞塊問題(轉)Oracle資料庫
- 第八篇:cx_Oracle出現的問題Oracle
- [TroubleShootting]Zabbix資料採集出現斷點的問題斷點
- Jtti:如何修復Oracle資料庫執行過程的問題JttiOracle資料庫
- 經典面試問題: Top K 之 ---- 海量資料找出現次數最多或,不重複的。面試
- 經典面試問題: Top K 之 —- 海量資料找出現次數最多或,不重複的。面試
- 如何快速定位線上出現的問題?
- Oracle資料庫——資料匯出時出現匯出成功終止, 但出現警告。Oracle資料庫
- 關於Oracle 9i字符集問題的解決辦法FCOracle
- ORACLE RAC TO RAC DG搭建過程中可能遇到的問題Oracle
- Redis作為快取可能會出現的問題及解決方案Redis快取
- 管理專案風險:考慮你的專案可能出現的問題
- mysql 字符集造成的效能問題MySql
- [Oracle]Oracle資料庫資料被修改或者刪除恢復資料Oracle資料庫
- 【硬貨】Oracle資料庫出現問題時,這十個指令碼幫你快速定位原因Oracle資料庫指令碼
- gulimall出現的問題
- 如何限制ip訪問Oracle資料庫Oracle資料庫
- 使用資料庫處理併發可能導致的問題資料庫
- 關於oracle資料庫訊號量的問題Oracle資料庫
- Oracle資料庫中的逐行處理問題NEOracle資料庫
- 系統投產前,Oracle資料庫最佳化思路和9個典型問題Oracle資料庫
- 【oracle 資料匯入匯出字元問題】Oracle字元
- 修改sqlserver字符集SQLServer