Oracle修改字符集前怎么找出可能出现问题的数据

2025-03-14 技术教程

本篇内容主要讲解“Oracle修改字符集前怎么找出可能出现问题的数据”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“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-helpCharacterSetScannerv2.2:Release11.2.0.4.0-ProductiononWedJun1713:46:292020Copyright(c)1982,2011,Oracleand/oritsaffiliates.Allrightsreserved.YoucanletScannerpromptyouforparametersbyenteringtheCSSCANcommandfollowedbyyourusername/password:Example:CSSCAN\"SYSTEM/MANAGERASSYSDBA\"Or,youcancontrolhowScannerrunsbyenteringtheCSSCANcommandfollowedbyvariousparameters.Tospecifyparameters,youusekeywords:Example:CSSCAN\"SYSTEM/MANAGERASSYSDBA\"FULL=yTOCHAR=utf8ARRAY=1024000PROCESS=3KeywordDefaultPromptDescription------------------------------------------------------------------------USERIDyesusername/passwordFULLNyesscanentiredatabaseUSERyesowneroftablestobescannedTABLEyeslistoftablestoscanCOLUMNyeslistofcolumnstoscanEXCLUDElistoftablestoexcludefromscanTOCHARyesnewdatabasecharactersetnameFROMCHARcurrentdatabasecharactersetnameTONCHARnewnationalcharactersetnameFROMNCHARcurrentnationalcharactersetnameARRAY1024000yessizeofarrayfetchbufferPROCESS1yesnumberofconcurrentscanprocessMAXBLOCKSsplittableifblocksizeexceedMAXBLOCKSCAPTURENcaptureconvertibledataSUPPRESSmaximumnumberofexceptionsloggedforeachtableFEEDBACKreportprogresseveryNrowsBOUNDARIESlistofcolumnsizeboundariesforsummaryreportLASTRPTNgeneratereportofthelastdatabasescanLOGscanbasefilenameofreportfilesPARFILEparameterfilenamePRESERVENpreserveexistingscanresultsLCSDNnoenablelanguageandcharactersetdetectionLCSDDATALOSSYnodefinethescopeofthedetectionHELPNshowhelpscreen(thisscreen)QUERYNselectclausetoscansubsetoftablesorcolumns------------------------------------------------------------------------Scannerterminatedsuccessfully.

三：使用csscan工具，生成scan.err等信息

[oracle@cjcos01~]$csscanCharacterSetScannerv2.2:Release11.2.0.4.0-ProductiononWedJun1711:47:212020Copyright(c)1982,2011,Oracleand/oritsaffiliates.Allrightsreserved.Username:sysassysdbaPassword:Connectedto:OracleDatabase11gEnterpriseEditionRelease11.2.0.4.0-64bitProductionWiththePartitioning,OLAP,DataMiningandRealApplicationTestingoptions(1)Fulldatabase,(2)User,(3)Table,(4)Column:1>2CurrentdatabasecharactersetisZHS16GBK.Enternewdatabasecharactersetname:>AL32UTF8Enterarrayfetchbuffersize:1024000>Enternumberofscanprocessestoutilize(1..):1>Enterusernametoscan:>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)Executethefollowingcommandsinthedatabasehomesqlplusconnectedas"/ASSYSDBA":Sqlplus/assysdbaSPOOLNswitch.logSHUTDOWNIMMEDIATE;STARTUPMOUNT;ALTERSYSTEMENABLERESTRICTEDSESSION;ALTERSYSTEMSETJOB_QUEUE_PROCESSES=0;ALTERSYSTEMSETAQ_TM_PROCESSES=0;ALTERDATABASEOPEN;--Donotdothesestepsona10gor11gsystemALTERDATABASECHARACTERSETINTERNAL_USEAL32UTF8;SHUTDOWNIMMEDIATE;--YouNEEDtorestartthedatabasebeforedoingANYoperationonthisdatabase--exitthissessionnowdonotusethesessionthatdidalterdatabaseforotheroperationsEXIT--reconnecttothedatabaseandstartthedatabaseSqlplus/assysdbaSTARTUP;--in8iyouneedtodothestartup/shutdown2timesSHUTDOWN;STARTUP;

Oracle RAC 修改数据库字符集

IfyouareusingRACyouwillneedtostartthedatabaseinsingleinstancewithCLUSTER_DATABASE=FALSERuninthedatabasehomesqlplusconnectedas"/ASSYSDBA":Sqlplus/assysdba--MakesuretheCLUSTER_DATABASEparameterisset--tofalseoritisnotsetatall.--IfyouareusingRACyouwillneedtostartthedatabaseinsingleinstance--withCLUSTER_DATABASE=FALSEshoparameterCLUSTER_DATABASE--ifyouareusingspfileSELECTDECODE(value,NULL,'PFILE','SPFILE')"InitFileType"FROMsys.v_$parameterWHEREname='spfile';--notethevaluesforshoparameterjob_queue_processesshoparameteraq_tm_processes--(thisisBug6005344fixedin11g)--thendoshutdownstartuprestrictSPOOLNswitch.logPURGEDBA_RECYCLEBIN/--nextselectshouldonlygiveONErow-yoursqlplusconnection--IfmorethenonesessionisconnectedCsalterwillfailandreport"Sorryonlyonesessionisallowedtorunthisscript".SELECTsid,serial#,username,status,osuser,machine,process,programFROMv$sessionWHEREusernameISNOTNULL;--dothisaltersystemoryoumightruninto"ORA-22839:DirectupdatesonSYS_NCcolumnsaredisallowed"--Thisisonlyneededin11.1.0.6,fixedin11.1.0.7,notapplicableto10.2orlower--ALTERSYSTEMSETEVENTS'22838TRACENAMECONTEXTLEVEL1,FOREVER';--thenrunCsalter.plb:@?/rdbms/admin/csalter.plb--Csalterwillaksconfirmation-donotcopypastethewholeactionsononetime--sampleCsalteroutput:--3rowscreated....--ThisscriptwillupdatethecontentoftheOracleDataDictionary.--Pleaseensureyouhaveafullbackupbeforeinitiatingthisprocedure.--Wouldyouliketoproceed(Y/N)?y--old6:if(UPPER('&conf')<>'Y')then--New6:if(UPPER('y')<>'Y')then--Checkingdatavalidility...--beginconvertingsystemobjects--PL/SQLproceduresuccessfullycompleted.--Alterthedatabasecharacterset...--CSALTERoperationcompleted,pleaserestartdatabase--PL/SQLproceduresuccessfullycompleted....--Proceduredropped.--ifyouareusingspfilethenyouneedtoalso--ALTERSYSTEMSETjob_queue_processes=<originalvalue>SCOPE=BOTH;--ALTERSYSTEMSETaq_tm_processes=<originalvalue>SCOPE=BOTH;SHUTDOWNIMMEDIATE;--YouNEEDtorestartthedatabasebeforedoingANYoperationonthisdatabase--exitthissessionnow.donotusethesessionwhereCsalterwasrunnedforotheroperations.EXIT--reconnecttothedatabaseandstartthedatabaseSqlplus/assysdbaSTARTUP;

到此，相信大家对“Oracle修改字符集前怎么找出可能出现问题的数据”有了更深的了解，不妨来实际操作一番吧！这里是亿速云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！