Tales From A Lazy Fat DBA

—————————–
CSSCAN & CSALTER
—————————–
The character set migration utility schema is installed by running the “$ORACLE_HOME/rdbms/admin/csminst.sql” script in SQL*Plus as the SYS user. Once the schema is present, the character set scanner should work normally.

[oracle@localhost admin]$ csscan
Character Set Scanner v2.1 : Release 10.2.0.0.0 – Production on Tue Aug 21 09:43:59 2012
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Username: sys as sysdba

Password:

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

(1)Full database, (2)User, (3)Table, (4)Column: 1 > 2

Current database character set is JA16SJIS.

Enter new database character set name: > WE8ISO8859P1

Enter array fetch buffer size: 1024000 >
Enter number of scan processes to utilize(1..32): 1 > 32

Enter user name to scan: > larry tonchar
CSS-00124: user larry tonchar not found

Enter user name to scan: > larry

Enumerating tables to scan…

. process 2 scanning LARRY.SYS_EXPORT_FULL_01[AAANXxAAHAAAAMJAAA]
. process 1 scanning LARRY.SYS_EXPORT_FULL_01[AAANXxAAHAAAAAJAAA]
. process 3 scanning LARRY.SYS_EXPORT_FULL_01[AAANXxAAEAAAAIxAAA]
. process 3 scanning LARRY.SYS_EXPORT_FULL_02[AAANgbAAEAAAAWJAAA]
. process 2 scanning LARRY.SYS_EXPORT_FULL_02[AAANgbAAEAAAATpAAA]
. process 4 scanning LARRY.SYS_EXPORT_FULL_02[AAANgbAAEAAAASpAAA]
. process 2 scanning LARRY.SYS_EXPORT_FULL_02[AAANgbAAEAAAAMBAAA]
. process 2 scanning LARRY.SYS_EXPORT_FULL_01[AAANXxAAEAAAAKZAAA]
. process 5 scanning LARRY.SYS_EXPORT_FULL_01[AAANXxAAEAAAAJxAAA]
. process 4 scanning LARRY.SYS_EXPORT_FULL_02[AAANgbAAEAAAAWpAAA]
. process 6 scanning LARRY.SYS_EXPORT_FULL_01[AAANXxAAEAAAAIJAAA]
. process 2 scanning LARRY.SYS_EXPORT_FULL_01[AAANXxAAEAAAAK5AAA]
. process 7 scanning LARRY.SYS_EXPORT_SCHEMA_01[AAANSoAAEAAAAGJAAA]
. process 6 scanning LARRY.SYS_EXPORT_SCHEMA_01[AAANSoAAEAAAAHxAAA]
. process 8 scanning LARRY.PLAN_TABLE[AAANieAAEAAAAXBAAA]
. process 2 scanning LARRY.SYS_EXPORT_FULL_02[AAANgbAAHAAAAPJAAA]
. process 4 scanning LARRY.DEPT1[AAANQuAAEAAAAIBAAA]
. process 9 scanning LARRY.ETR[AAAM/uAAEAAAAGBAAA]
. process 7 scanning LARRY.SALARY[AAAM75AAEAAAAHpAAA]
. process 4 scanning LARRY.EMP1[AAAM9RAAEAAAAH5AAA]
. process 5 scanning LARRY.PERSON[AAAM/xAAEAAAAGhAAA]

Creating Database Scan Summary Report…

Creating Individual Exception Report…

Scanner terminated successfully.
[oracle@localhost admin]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 – Production on Tue Aug 21 09:47:54 2012
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

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

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

SQL> startup restrict
ORACLE instance started.

Total System Global Area  306184192 bytes
Fixed Size                  1219112 bytes
Variable Size             113247704 bytes
Database Buffers          188743680 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
SQL>

SQL> @csalter.plb
4 rows created.

Function created.

Function created.

Procedure 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…
Exceptional data found in scanner result

PL/SQL procedure successfully completed.

Checking or Converting phrase finished successfully
Database (national) character set will be altered
CSALTER finished successfully.

PL/SQL procedure successfully completed.

4 rows deleted.

Function dropped.

Function dropped.

Procedure dropped.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area  306184192 bytes
Fixed Size                  1219112 bytes
Variable Size             113247704 bytes
Database Buffers          188743680 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
SQL>