OTools--如何使用Oracle提供的字符扫描工具csscan

1.以sys用户身份创建相应数据字典对象:
$sqlplus /nolog
SQL>conn / as sysdba
SQL>@@?/rdbms/admin/csminst.sql
SQL>exit
2.执行csscan符集扫描工具：
详细的帮助说明可以执行 csscan help=y 来查看。
$csscan SYSTEM/Password FULL=y TOCHAR=utf8 ARRAY=1024000 PROCESS=3
3.检查扫描后的出错信息。
$ls -al |grep scan
$cat scan.err|more

详细的执行记录如下。

[ora10g]~$ csscan help=y

Character Set Scanner v2.1 : Release 10.2.0.0.0 - Production on Wed Mar 14 13:50:43 2007

Copyright (c) 1982, 2005, Oracle. 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

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 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.
[oracle@szvsx017:/opt/oracle/db920/bin]
[ora10g]~$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Mar 14 13:51:00 2007

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

SQL> !
$ csscan SYSTEM FULL=y TOCHAR=utf8 ARRAY=1024000 PROCESS=3

Character Set Scanner v2.1 : Release 10.2.0.0.0 - Production on Wed Mar 14 13:51:33 2007

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

Password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
CSS-00107: Character set migration utility schema not installed

Scanner terminated unsuccessfully.
$ exit

SQL> conn / as sysdba
Connected.
SQL> @?/rdbms/admin/csminst.sql
grant READ on directory log_file_dir to system
*
ERROR at line 1:
ORA-22930: directory does not exist

grant READ on directory data_file_dir to system
*
ERROR at line 1:
ORA-22930: directory does not exist

drop user csmig cascade
*
ERROR at line 1:
ORA-01918: user 'CSMIG' does not exist

Please create password for user CSMIG:
Enter value for csmig_passwd: p2ssw0rd
User created.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

User altered.

1 row created.

1 row updated.

Table created.

drop public synonym csm$parameters
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

Synonym created.

Grant succeeded.

Table created.

drop public synonym csm$query
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

Synonym created.

Grant succeeded.

Table created.

drop public synonym csm$tables
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

Synonym created.

Grant succeeded.

Table created.

drop public synonym csm$columns
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

Synonym created.

Grant succeeded.

Table created.

drop public synonym csm$extables
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

Synonym created.

Grant succeeded.

Table created.

drop public synonym csm$errors
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

Synonym created.

Grant succeeded.

Table created.

drop public synonym csm$langid
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

Synonym created.

Grant succeeded.

Table created.

drop public synonym csm$charsetid
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

Synonym created.

Grant succeeded.

Table created.

drop public synonym csm$indexes
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

Synonym created.

Grant succeeded.

Table created.

drop public synonym csm$constraints
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

Synonym created.

Grant succeeded.

Table created.

drop public synonym csm$triggers
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

Synonym created.

Grant succeeded.

Table created.

drop public synonym csm$dictusers
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

Synonym created.

Grant succeeded.

13 rows created.

View created.

drop public synonym csmv$tables
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

Synonym created.

View created.

drop public synonym csmv$columns
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

Synonym created.

View created.

drop public synonym csmv$errors
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

Synonym created.

View created.

drop public synonym csmv$indexes
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

Synonym created.

View created.

drop public synonym csmv$constraints
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

Synonym created.

View created.

drop public synonym csmv$triggers
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

Synonym created.

View created.

View created.

Commit complete.

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
[oracle@szvsx017:/opt/oracle/db920/bin]
[ora10g]~$ csscan SYSTEM FULL=y TOCHAR=utf8 ARRAY=1024000 PROCESS=3

Character Set Scanner v2.1 : Release 10.2.0.0.0 - Production on Wed Mar 14 13:51:33 2007

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

Password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

Enumerating tables to scan...
. process 3 scanning SYS.TRUSTED_LIST$[AAAABfAABAAAALRAAA]
. process 1 scanning SYS._default_auditing_options_[AAAACPAABAAAAQxAAA]
. process 3 scanning SYS.TABPART$[AAAAEKAABAAAAhRAAA]
. process 1 scanning SYS.INDOP$[AAAAF/AABAAAAuhAAA]
. process 1 scanning SYS.RLS_SC$[AAAAG5AABAAAA1xAAA]
. process 3 scanning SYS.CDC_PROPAGATED_SETS$[AAAAIaAABAAABC5AAA]
. process 1 scanning SYS.AW$[AAAAJCAABAAABHpAAA]
. process 3 scanning SYS.STREAMS$_RULES[AAAAJ9AABAAABM5AAA]
. process 1 scanning SYS.DIR$INSTANCE_ACTIONS[AAAAL/AADAAAAFxAAA]
. process 3 scanning SYS.RULE_MAP$[AAABFlAABAAACRpAAA]
. process 1 scanning SYS.SCHEDULER$_JOBQTAB[AAABPLAABAAACtRAAA]
. process 3 scanning SYS.LOGMNRG_TS$[AAABXYAABAAADJxAAA]

. process 3 scanning SYS.TRUSTED_LIST$[AAAABfAABAAAALRAAA]
. process 1 scanning SYS._default_auditing_options_[AAAACPAABAAAAQxAAA]
. process 3 scanning SYS.TABPART$[AAAAEKAABAAAAhRAAA]
. process 1 scanning SYS.INDOP$[AAAAF/AABAAAAuhAAA]
. process 1 scanning SYS.RLS_SC$[AAAAG5AABAAAA1xAAA]
. process 3 scanning SYS.CDC_PROPAGATED_SETS$[AAAAIaAABAAABC5AAA]
. process 1 scanning SYS.AW$[AAAAJCAABAAABHpAAA]
. process 3 scanning SYS.STREAMS$_RULES[AAAAJ9AABAAABM5AAA]
. process 1 scanning SYS.DIR$INSTANCE_ACTIONS[AAAAL/AADAAAAFxAAA]
. process 3 scanning SYS.RULE_MAP$[AAABFlAABAAACRpAAA]
. process 1 scanning SYS.SCHEDULER$_JOBQTAB[AAABPLAABAAACtRAAA]
. process 3 scanning SYS.LOGMNRG_TS$[AAABXYAABAAADJxAAA]
. process 3 scanning SYSMAN.MGMT_CREATED_USERS[AAAMrVAADAAAGppAAA]
. process 1 scanning SYSMAN.MGMT_POLICY_PARAMS[AAAMn2AADAAAGPBAAA]
. process 1 scanning SYSMAN.MGMT_CREDENTIAL_TYPES[AAAMlLAADAAAF6hAAA]
. process 3 scanning SYSMAN.MGMT_BCN_AVAIL_LOG[AAAMkjAADAAAF1xAAA]
. process 1 scanning SYSMAN.MGMT_HC_FS_MOUNT_DETAILS[AAAMnZAADAAAGLZAAA]
. process 3 scanning SYSMAN.MGMT_HC_CPU_DETAILS[AAAMnRAADAAAGKZAAA]
. process 1 scanning SYSMAN.MGMT_INV_PATCH[AAAMmSAADAAAGChAAA]
. process 3 scanning SYSMAN.MGMT_JOB_OUTPUT[AAAMpxAADAAAGeJAAA]
. process 1 scanning SYSMAN.MGMT_ECM_CSA_SNAPSHOT_INFO[AAAMoHAADAAAGRJAAA]
. process 3 scanning SYSMAN.MGMT_DB_LATEST_HDM_FINDINGS[AAAM0TAADAAAHHxAAA]
. process 1 scanning SYSMAN.MGMT_PRIVS[AAAMrGAADAAAGoZAAA]
. process 3 scanning SYSTEM.LOGMNR_TS$[AAABcFAADAAAAfpAAA]
. process 1 scanning SYS.WRH$_TABLESPACE_STAT[AAACVYAADAAAA/5AAA]
. process 3 scanning SYSTEM.LOGMNR_USER$[AAABcLAADAAAAYRAAA]
. process 1 scanning SYS.WRH$_PARAMETER[AAACVFAADAAAA5RAAA]
. process 3 scanning SYS.WRH$_SGASTAT[AAACU9AADAAAA1RAAA]
ORA-00904: "SYS_NC00009$": invalid identifier
CSS-00144: failed to scan table XDB.XDB$ACL

Creating Database Scan Summary Report...

Creating Individual Exception Report...

Scanner terminated successfully.
[oracle@szvsx017:/opt/oracle/db920/bin]
[ora10g]~$

然后我们可以检查输出的日志来查看数据库扫描情况:

[ora10g]~$ ll -st|grep scan
total 1066404
4 -rw-rw-r-- 1 oracle dba 1357 Mar 14 13:55 scan.err
148 -rw-rw-r-- 1 oracle dba 74427 Mar 14 13:55 scan.out
12 -rw-rw-r-- 1 oracle dba 5700 Mar 14 13:55 scan.txt

[ora10g]~$ cat scan.err
Database Scan Individual Exception Report

[Database Scan Parameters]

Parameter Value
------------------------------ ------------------------------------------------
CSSCAN Version v2.1
Instance Name ora10g
Database Version 10.2.0.1.0
Scan type Full database
Scan CHAR data? YES
Database character set AL32UTF8
FROMCHAR AL32UTF8
TOCHAR utf8
Scan NCHAR data? NO
Array fetch buffer size 1024000
Number of processes 3
Capture convertible data? NO
------------------------------ ------------------------------------------------

[Data Dictionary individual exceptions]

[Application data individual exceptions]

[ora10g]~$

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29987/viewspace-51984/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29987/viewspace-51984/