These are the steps followed for changing the database characterset from WE8MSWIN1252 to AL32UTF8
I have to create a new DB with correct CHARACTERSET(AL32UTF8) & import the database dump to it, So in order to avoid losing data we have performed the below steps
We have followed document Changing the NLS_CHARACTERSET to AL32UTF8 / UTF8 (Unicode) [ID 260192.1] for it============================
START OF SCREENSHOT
============================
SQL> set TERMOUT ON
SQL> set ECHO ON
SQL> spool csminst.log
SQL> drop user csmig cascade;
drop user csmig cascade
*
ERROR at line 1:
ORA-01918: user 'CSMIG' does not exist
SQL> @?/rdbms/admin/csminst.sql
SQL> rem
SQL> rem $Header: csmig/sql/csminst.sql /main/29 2008/09/09 00:47:40 ssubrama Exp $ csminst.sql
SQL> rem
SQL> rem Copyright (c) 1988, 2008, Oracle. All rights reserved.
SQL> rem
SQL> rem NAME
SQL> rem csminst.sql
SQL> rem DESCRIPTION
SQL> rem Create tables for Database Character Set Migration Utility
SQL> rem NOTE
SQL> rem This script must be run while connected as SYS
SQL> rem MODIFIED
SQL> rem ssubrama 09/04/08 - bug 7047837 fix create user
SQL> rem nli 07/25/08 - fix bug 7256242, add the ORADATA user
SQL> rem ssubrama 06/09/08 - bug 7047837 cleanup csmig schema for security
SQL> rem xpeng 10/18/07 - fix bug 6460895
SQL> rem ssubrama 01/09/07 - bug 5738695 add copyright information
SQL> rem nli 08/02/06 - bug 5372557: CSX support. Add a CNVTYPE column into CSMIG.CSM$COLUMNS
SQL> rem ywu 07/14/04 - up version
SQL> rem fayang 04/23/04 - add column UNNESTED in CSM$TABLES
SQL> rem fayang 04/06/04 - add SCNCOL# in EXTABLES and add a view for EXTABLES
SQL> rem ywu 02/19/04 - fix bug 3434808, delete hard code passwd.
SQL> rem ywu 07/29/03 - grant privilege to system for external table
SQL> rem ywu 05/30/03 - add a table for data dictionary
SQL> rem ywu 05/09/03 - add led parameter to csm$errors
SQL> rem ywu 10/22/02 - add another error for codepoint exceed
SQL> rem ywu 08/30/02 - up version
SQL> rem ywu 07/01/02 - add size information
SQL> rem ywu 07/02/02 - add resumable
SQL> rem plinsley 01/24/02 - add include/exclude
SQL> rem plinsley 04/02/01 - up version
SQL> rem plinsley 03/23/01 - #1509940
SQL> rem plinsley 03/26/01 - update views
SQL> rem plinsley 12/15/00 - remove order by from view
SQL> rem plinsley 11/03/00 - Add converter process column
SQL> rem plinsley 10/04/00 - split tables that cross files
SQL> rem plinsley 09/21/00 - Long ROWIDs in id
SQL> rem plinsley 08/09/00 - Adding constraint dependency handling
SQL> rem mtozawa 06/29/00 - add csmv$ views
SQL> rem mtozawa 06/27/00 - add browid to csm$columns
SQL> rem mtozawa 06/02/00 - specify the storage clause for csm$errors
SQL> rem mtozawa 05/26/00 - bug1314547:optimize split mechanism, add blocks
SQL> rem mtozawa 05/19/00 - Change table names from SCN$* to CSM$*
SQL> rem mtozawa 03/08/00 - add SPLIT support
SQL> rem mtozawa 12/21/99 - add property column to SCN$TABLES for IOT
SQL> rem mtozawa 11/05/99 - remove storage params from SCN$ERRORS
SQL> rem mtozawa 11/04/99 - add maxsiz to SCN$COLUMNS
SQL> rem mtozawa 09/26/99 - Creation
SQL> rem
SQL>
SQL>
SQL> rem *****************************************************************
SQL> rem The user CSMIG owns tables and procedures of Database Scanner
SQL> rem *****************************************************************
SQL> WHENEVER SQLERROR EXIT
SQL>
SQL> create user csmig identified by csmig password expire account lock
2 /
User created.
SQL>
SQL> WHENEVER SQLERROR CONTINUE
SQL>
SQL> grant select on sys.obj$ to csmig
2 /
Grant succeeded.
SQL> grant select on sys.col$ to csmig
2 /
Grant succeeded.
SQL> grant select on sys.icol$ to csmig
2 /
Grant succeeded.
SQL> grant select on sys.ind$ to csmig
2 /
Grant succeeded.
SQL> grant select on sys.cdef$ to csmig
2 /
Grant succeeded.
SQL> grant select on sys.con$ to csmig
2 /
Grant succeeded.
SQL> grant select on sys.trigger$ to csmig
2 /
Grant succeeded.
SQL> rem *****************************************************************
SQL> rem DBA MUST ASSIGN PROPER TABLESPACE TO CSMIG
SQL> rem *****************************************************************
SQL> alter user csmig default tablespace SYSTEM quota unlimited on SYSTEM
2 /
User altered.
SQL>
SQL> rem *****************************************************************
SQL> rem Add version information for csm$* schema
SQL> rem The schema version must be bumped up as csm$* schema get updated.
SQL> rem VERSION HISTORY:
SQL> rem 1 ... 8.1.7
SQL> rem *****************************************************************
SQL> insert into sys.props$
2 select 'NLS_CSMIG_SCHEMA_VERSION', 'x',
3 'Character set migration utiltiy schema version #'
4 from dual
5 where not exists
6 (select 'x' from sys.props$ where name = 'NLS_CSMIG_SCHEMA_VERSION')
7 /
1 row created.
SQL> update sys.props$ set value$ = 5 where name = 'NLS_CSMIG_SCHEMA_VERSION'
2 /
1 row updated.
SQL> rem *****************************************************************
SQL> rem Database Scanner leaves the last scan parameters in CSM$PARAMETERS
SQL> rem Each background process will read scan parameters from here.
SQL> rem *****************************************************************
SQL> create table csmig.csm$parameters
2 ( name varchar2(30) not null, /* paraneter name */
3 value varchar2(80) not null /* parameter value */
4 )
5 /
Table created.
SQL> drop public synonym csm$parameters
2 /
drop public synonym csm$parameters
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
SQL> create public synonym csm$parameters for csmig.csm$parameters
2 /
Synonym created.
SQL> rem *****************************************************************
SQL> rem Database Scanner saves the query string in CSM$QUERY
SQL> rem *****************************************************************
SQL> create table csmig.csm$query
2 (
3 value clob not null /* query value */
4 )
5 /
Table created.
SQL> drop public synonym csm$query
2 /
drop public synonym csm$query
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
SQL> create public synonym csm$query for csmig.csm$query
2 /
Synonym created.
SQL> rem *****************************************************************
SQL> rem Database Scanner enumerate all tables need to be scanned
SQL> rem Each background process will pick up a row from here for table to scan
SQL> rem *****************************************************************
SQL> create table csmig.csm$tables
2 ( usr# number not null, /* user id of the table owner */
3 obj# number not null, /* object id of the table */
4 minrowid rowid, /* Minimum rowid of the split range of the table */
5 maxrowid rowid, /* Maximum rowid of the split range of the table */
6 property number, /* table property */
7 blocks number, /* number of blocks used by this table */
8 files number, /* number of files used by this table */
9 who number, /* internal thread id who scanned the table */
10 whoconv number, /* internal thread id who converted the table */
11 lngconv number, /* internal thread id who converted long col */
12 scnstart date, /* time table scan started */
13 scnend date, /* time table scan completed */
14 scncols number, /* number of columns to be scanned */
15 scnrows number, /* number of rows scanned */
16 cnvstart date, /* time table convert started */
17 cnvend date, /* time table convert completed */
18 lngstart date, /* time table convert long started */
19 lngend date, /* time table convert long completed */
20 cnvcols number, /* number of columns to be converted */
21 cnvrows number, /* number of rows to be converted */
22 lngrows number, /* number of rows of long to be converted */
23 addsize number,
24 lastupd rowid, /* ROWID lastupdated */
25 pstcvrows number, /* how many rows have been converted */
26 lastupdlg rowid, /* ROWID lastupdated */
27 pstcvrowslg number, /* how many rows have been converted */
28 unnested number /* if this table is unnested or not */
29 )
30 /
Table created.
SQL> drop public synonym csm$tables
2 /
drop public synonym csm$tables
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
SQL> create public synonym csm$tables for csmig.csm$tables
2 /
Synonym created.
SQL> rem *****************************************************************
SQL> rem CSM$COLUMNS contains statistic information of column data
SQL> rem *****************************************************************
SQL> create table csmig.csm$columns
2 ( usr# number not null, /* user id of the table owner */
3 obj# number not null, /* object id of the table */
4 browid rowid, /* rowid of the row in csm$tables */
5 col# number not null, /* column id */
6 intcol# number not null, /* internal column id (for ADT) */
7 dty# number not null, /* column data type */
8 frm# number not null, /* character set form */
9 numrows number not null, /* number of rows in this table */
10 nulcnt number not null, /* number of null cell data */
11 cnvcnt number not null, /* number of cell data that need to convert */
12 cnvtype number default 0 not null, /* convert type */
13 /* 1 = data in data dictionary, and can be converted by csconv*/
14 errcnt number not null, /* number of cell data that has exception */
15 sizerr number not null, /* number of cell data that exceed column size */
16 cnverr number not null, /* number of cell data that undergo lossy conv.*/
17 maxsiz number not null, /* max post conversion data size */
18 chrsiz number not null, /* truncation due to char semantics */
19 cnvsuc number, /* cells converted successfully */
20 cnvtrn number, /* cells converted with truncation */
21 cnvlos number, /* cells converted with lossy result */
22 cnvfai number /* cells failed to convert */
23 )
24 /
Table created.
SQL> drop public synonym csm$columns
2 /
drop public synonym csm$columns
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
SQL> create public synonym csm$columns for csmig.csm$columns
2 /
Synonym created.
SQL> rem *****************************************************************
SQL> rem CSM$EXTABLES contains exception tables
SQL> rem *****************************************************************
SQL> create table csmig.csm$extables
2 ( usr# number not null, /* user id of the table owner */
3 obj# number not null, /* object id of the table */
4 col# number, /* column id */
5 intcol# number, /* internal column id (for ADT) */
6 dty# number, /* column data type */
7 frm# number, /* character set form */
8 property number default 0 not null, /* property of row */
9 scncol# number /* column id to be scaned */
10 )
11 /
Table created.
SQL> drop public synonym csm$extables
2 /
drop public synonym csm$extables
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
SQL> create public synonym csm$extables for csmig.csm$extables
2 /
Synonym created.
SQL> rem *****************************************************************
SQL> rem CSM$ERRORS contains individual exception information
SQL> rem *****************************************************************
SQL> create table csmig.csm$errors
2 ( err# number not null, /* exception type */
3 usr# number not null, /* user id of the object/data owner */
4 obj# number not null, /* object id */
5 col# number, /* column id / position */
6 intcol# number, /* internal column id (for ADT) */
7 typ# number, /* column data type / object type */
8 frm# number, /* character set form */
9 cnvsize number, /* post conversion data size */
10 id$ varchar2(1000), /* rowid / name to identify object */
11 csidleds number, /* number of charset id from led */
12 csidled1 number, /* first charset id from led */
13 csidled2 number, /* second charset id from led */
14 csidled3 number, /* third charset id from led */
15 langidleds number, /* number of language id from led */
16 langidled1 number, /* first language id from led */
17 langidled2 number, /* second language id from led */
18 langidled3 number /* third language id from led */
19 )
20 pctfree 0 pctused 99
21 storage(next 100K maxextents unlimited pctincrease 0)
22 /
Table created.
SQL> drop public synonym csm$errors
2 /
drop public synonym csm$errors
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
SQL> create public synonym csm$errors for csmig.csm$errors
2 /
Synonym created.
SQL> rem *****************************************************************
SQL> rem CSM$LANGID contains summary information
SQL> rem *****************************************************************
SQL> create table csmig.csm$langid
2 ( obj# number not null, /* object id */
3 langid number, /* language id from led */
4 count number
5 )
6 /
Table created.
SQL> drop public synonym csm$langid
2 /
drop public synonym csm$langid
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
SQL> create public synonym csm$langid for csmig.csm$langid
2 /
Synonym created.
SQL> rem *****************************************************************
SQL> rem CSM$CHARSETID contains summary information
SQL> rem *****************************************************************
SQL> create table csmig.csm$charsetid
2 ( obj# number not null, /* object id */
3 csid number, /* language id from led */
4 count number
5 )
6 /
Table created.
SQL> drop public synonym csm$charsetid
2 /
drop public synonym csm$charsetid
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
SQL> create public synonym csm$charsetid for csmig.csm$charsetid
2 /
Synonym created.
SQL> rem *****************************************************************
SQL> rem CSM$INDEXES lists indexes to be disabled
SQL> rem *****************************************************************
SQL> create table csmig.csm$indexes
2 ( obj# number not null /* object id of the index */
3 )
4 /
Table created.
SQL> drop public synonym csm$indexes
2 /
drop public synonym csm$indexes
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
SQL> create public synonym csm$indexes for csmig.csm$indexes
2 /
Synonym created.
SQL> rem *****************************************************************
SQL> rem CSM$CONSTRAINTS lists constraints to be disabled
SQL> rem *****************************************************************
SQL> create table csmig.csm$constraints
2 (
3 rid number not null, /* root constraint id */
4 lvl number, /* constraint level */
5 con# number not null /* internal constraint number */
6 )
7 /
Table created.
SQL> drop public synonym csm$constraints
2 /
drop public synonym csm$constraints
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
SQL> create public synonym csm$constraints for csmig.csm$constraints
2 /
Synonym created.
SQL> rem *****************************************************************
SQL> rem CSM$TRIGGERS lists triggers to be disabled
SQL> rem *****************************************************************
SQL> create table csmig.csm$triggers
2 ( obj# number not null /* object id of the trigger */
3 )
4 /
Table created.
SQL> drop public synonym csm$triggers
2 /
drop public synonym csm$triggers
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
SQL> create public synonym csm$triggers for csmig.csm$triggers
2 /
Synonym created.
SQL> rem *****************************************************************
SQL> rem CSM$DICTUSERS lists triggers to be disabled
SQL> rem *****************************************************************
SQL> create table csmig.csm$dictusers
2 ( user# number not null, /* usre id for all data dictionary */
3 username varchar2(30)
4 )
5 /
Table created.
SQL> drop public synonym csm$dictusers
2 /
drop public synonym csm$dictusers
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
SQL> create public synonym csm$dictusers for csmig.csm$dictusers
2 /
Synonym created.
SQL> insert into csmig.csm$dictusers
2 select distinct u.user_id, u.username from all_users u, sys.ku_noexp_view k
3 where (k.OBJ_TYPE='USER' and k.name=u.username) or (u.username in ('SYSTEM', 'ORDDATA'))
4 /
17 rows created.
SQL> rem
SQL> rem define CSMV$ views
SQL> rem
SQL> rem *****************************************************************
SQL> rem CSMV$TABLES lists tables (to be) scanned
SQL> rem *****************************************************************
SQL> create or replace view csmig.csmv$tables
2 (owner_id, owner_name, table_id, table_name, MIN_ROWID, MAX_ROWID,
3 BLOCKS, SCAN_COLUMNS, SCAN_ROWS, SCAN_START, SCAN_END)
4 as
5 select c.usr#, u.username, c.obj#, o.name,
6 rowidtochar(c.minrowid), rowidtochar(c.maxrowid),
7 c.blocks, c.scncols, c.scnrows,
8 to_char(c.scnstart,'hh24:mi:ss'), to_char(c.scnend,'hh24:mi:ss')
9 from csm$tables c, all_users u, sys.obj$ o
10 where c.usr#=u.user_id and c.obj#=o.obj#
11 /
View created.
SQL> drop public synonym csmv$tables
2 /
drop public synonym csmv$tables
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
SQL> create public synonym csmv$tables for csmig.csmv$tables
2 /
Synonym created.
SQL> rem *****************************************************************
SQL> rem CSMV$COLUMNS lists columns scanned
SQL> rem *****************************************************************
SQL> create or replace view csmig.csmv$columns
2 (owner_id, owner_name, table_id, table_name, column_id, column_intid,
3 column_name, column_type, total_rows, null_rows, conv_rows, error_rows,
4 exceed_size_rows, data_loss_rows, cs_exceed_size_rows, max_post_convert_size)
5 as
6 select c.usr#, u.username, c.obj#, o.name, c.col#, c.intcol#, co.name,
7 decode(c.frm#, 2, 'N', '') ||
8 decode(c.dty#, 1, 'VARCHAR2', 8, 'LONG', 96, 'CHAR', 112, 'CLOB',''),
9 c.numrows, c.nulcnt, c.cnvcnt, c.errcnt, c.sizerr, c.cnverr, c.chrsiz, c.maxsiz
10 from csm$columns c, all_users u, sys.obj$ o, sys.col$ co
11 where c.usr#=u.user_id and c.obj#=o.obj# and c.obj#=co.obj#
12 and c.col#=co.col# and c.intcol#=co.intcol#
13 /
View created.
SQL> drop public synonym csmv$columns
2 /
drop public synonym csmv$columns
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
SQL> create public synonym csmv$columns for csmig.csmv$columns
2 /
Synonym created.
SQL> rem *****************************************************************
SQL> rem CSMV$ERRORS lists exceptional data cell information
SQL> rem *****************************************************************
SQL> create or replace view csmig.csmv$errors
2 (owner_id, owner_name, table_id, table_name,
3 column_id, column_intid, column_name, data_rowid,
4 column_type, error_type)
5 as
6 select e.usr#, u.username, e.obj#, o.name,
7 e.col#, e.intcol#, c.name, e.id$,
8 decode(e.frm#, 2, 'N', '') ||
9 decode(e.typ#, 1, 'VARCHAR2', 8, 'LONG', 96, 'CHAR', 112, 'CLOB'),
10 decode(e.err#, 0, 'CONVERTIBLE', 1, 'EXCEED_SIZE', 2, 'DATA_LOSS',
11 3, 'CS_EXCEED_SIZE')
12 from csm$errors e, all_users u, sys.obj$ o, sys.col$ c
13 where e.usr#=u.user_id and e.obj#=o.obj#
14 and e.obj#=c.obj# and e.col#=e.col# and e.intcol#=c.intcol#
15 /
View created.
SQL> drop public synonym csmv$errors
2 /
drop public synonym csmv$errors
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
SQL> create public synonym csmv$errors for csmig.csmv$errors
2 /
Synonym created.
SQL> rem *****************************************************************
SQL> rem CSMV$INDEXES lists all indexes to be disabled
SQL> rem *****************************************************************
SQL> create or replace view csmig.csmv$indexes
2 (index_owner_id, index_owner_name, index_id, index_name,
3 index_status#, index_status,
4 table_owner_id, table_owner_name, table_id, table_name,
5 column_id, column_intid, column_name)
6 as
7 select iu.user_id, iu.username, io.obj#, io.name, id.flags,
8 decode(bitand(id.flags,1), 1, 'UNUSABLE', 'VALID'),
9 bu.user_id, bu.username, bo.obj#, bo.name,
10 cl.col#, cl.intcol#, cl.name
11 from csm$indexes ci, sys.icol$ ic, sys.ind$ id, all_users iu,
12 sys.obj$ io, all_users bu, sys.obj$ bo, sys.col$ cl
13 where ci.obj#=ic.obj# and ci.obj#=id.obj#
14 and ci.obj#=io.obj# and io.owner#=iu.user_id
15 and ic.bo# =bo.obj# and bo.owner#=bu.user_id
16 and ic.bo#=cl.obj# and ic.col#=cl.col# and ic.intcol#=cl.intcol#
17 /
View created.
SQL> drop public synonym csmv$indexes
2 /
drop public synonym csmv$indexes
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
SQL> create public synonym csmv$indexes for csmig.csmv$indexes
2 /
Synonym created.
SQL> rem *****************************************************************
SQL> rem CSMV$CONSTRAINTS lists all constraints to be disabled
SQL> rem *****************************************************************
SQL> create or replace view csmig.csmv$constraints
2 (owner_id, owner_name, constraint_id, constraint_name,
3 constraint_type#, constraint_type, table_id, table_name,
4 constraint_rid, constraint_level)
5 as
6 select c.owner#, u.username, c.con#, c.name, cd.type#,
7 decode(cd.type#, 1, 'CHECK', 2, 'PRIMARY_KEY', 3, 'UNIQUE',
8 4, 'REFERENTIAL', 'UNKNOWN'),
9 o.obj#, o.name, cc.rid, cc.lvl
10 from csm$constraints cc, sys.cdef$ cd, sys.con$ c, all_users u, sys.obj$ o
11 where cc.con#=cd.con# and cc.con#=c.con#
12 and c.owner#=u.user_id and cd.obj#=o.obj#
13 /
View created.
SQL> drop public synonym csmv$constraints
2 /
drop public synonym csmv$constraints
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
SQL> create public synonym csmv$constraints for csmig.csmv$constraints
2 /
Synonym created.
SQL> rem *****************************************************************
SQL> rem CSMV$TRIGGERS lists all triggers to be disabled
SQL> rem *****************************************************************
SQL> create or replace view csmig.csmv$triggers
2 (trigger_owner_id, trigger_owner_name, trigger_id, trigger_name,
3 table_owner_id, table_owner_name, table_id, table_name)
4 as
5 select ru.user_id, ru.username, tr.obj#, ro.name, bu.user_id,
6 bu.username, tr.baseobject, bo.name
7 from csm$triggers ct, sys.trigger$ tr, all_users ru, sys.obj$ ro,
8 all_users bu, sys.obj$ bo
9 where ct.obj#=tr.obj# and ct.obj#=ro.obj# and ro.owner#=ru.user_id
10 and tr.baseobject=bo.obj# and bu.user_id=bo.owner#
11 /
View created.
SQL> drop public synonym csmv$triggers
2 /
drop public synonym csmv$triggers
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
SQL> create public synonym csmv$triggers for csmig.csmv$triggers
2 /
Synonym created.
SQL> rem *****************************************************************
SQL> rem CSMV$EXTABLES lists all distinct objects to be scaned
SQL> rem *****************************************************************
SQL> create or replace view csmig.csmv$extables
2 (obj#, usr#, property)
3 as
4 select distinct(obj#), usr#, property
5 from csm$extables where property=0;
View created.
SQL> /
View created.
SQL> rem *****************************************************************
SQL> rem SYS.CSMV$KTFBUE wraps the fixed table sys.x$ktfbue in data
SQL> rem dictionary for users only with DBA privilege to access
SQL> rem *****************************************************************
SQL> create or replace view sys.csmv$ktfbue as select * from sys.x$ktfbue;
View created.
SQL> /
View created.
SQL> grant select on sys.csmv$ktfbue to dba;
Grant succeeded.
SQL> /
Grant succeeded.
SQL> exit;
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[oracle@localhost ~]$ echo $ORACLE_SID
labdb
[oracle@localhost ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Mon Apr 23 19:04:02 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> conn csmig/csmig
ERROR:
ORA-28000: the account is locked
Warning: You are no longer connected to ORACLE.
SQL> show user
USER is ""
SQL> conn / as sysdba
Connected.
SQL> alter account csmig unlock;
alter account csmig unlock
*
ERROR at line 1:
ORA-00940: invalid ALTER command
SQL> alter user csmig account unlock;
User altered.
SQL> conn csmig/csmig
ERROR:
ORA-28001: the password has expired
Changing password for csmig
New password:
Retype new password:
ERROR:
ORA-01045: user CSMIG lacks CREATE SESSION privilege; logon denied
Password changed
Warning: You are no longer connected to ORACLE.
SQL> show user
USER is ""
SQL> conn / as sysdba
Connected.
SQL> alter user csmig identified by oracle;
User altered.
SQL> conn csmig/oracle
ERROR:
ORA-01045: user CSMIG lacks CREATE SESSION privilege; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn / as sysdba
Connected.
SQL> show user
USER is "SYS"
SQL> exit\
SP2-0584: EXIT variable "\" was non-numeric
Usage: { EXIT | QUIT } [ SUCCESS | FAILURE | WARNING | n |
<variable> | :<bindvariable> ] [ COMMIT | ROLLBACK ]
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[oracle@localhost ~]$ pwd
/home/oracle
[oracle@localhost ~]$ which csscaner
/usr/bin/which: no csscaner in (/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/oracle/bin:/u01/app/oracle/oracle11201/bin/)
[oracle@localhost ~]$ which csscan
/u01/app/oracle/oracle11201/bin/csscan
[oracle@localhost ~]$ csscan TABLE='(SYS.SQL_VERSION$)' FROMCHAR=US7ASCII TOCHAR=US7ASCII LOG=instchkc CAPTURE=N PROCESS=1 ARRAY=1024000
csscan: error while loading shared libraries: libclntsh.so.11.1: cannot open shared object file: No such file or directory
[oracle@localhost ~]$ csscan
csscan: error while loading shared libraries: libclntsh.so.11.1: cannot open shared object file: No such file or directory
[oracle@localhost ~]$ ldd `which sysresv`
linux-gate.so.1 => (0x00696000)
libclntsh.so.11.1 => /u01/app/oracle/oracle11201/lib/libclntsh.so.11.1 (0x00697000)
libnnz11.so => /u01/app/oracle/oracle11201/lib/libnnz11.so (0x00110000)
libdl.so.2 => /lib/libdl.so.2 (0x0035d000)
libm.so.6 => /lib/libm.so.6 (0x00361000)
libpthread.so.0 => /lib/libpthread.so.0 (0x00388000)
libnsl.so.1 => /lib/libnsl.so.1 (0x003a0000)
libc.so.6 => /lib/libc.so.6 (0x003b7000)
libaio.so.1 => /usr/lib/libaio.so.1 (0x00508000)
/lib/ld-linux.so.2 (0x00590000)
[oracle@localhost ~]$ echo $ORACLE_HOME
/u01/app/oracle/oracle11201
[oracle@localhost ~]$ cat /etc/oratab
#
# This file is used by ORACLE utilities. It is created by root.sh
# and updated by the Database Configuration Assistant when creating
# a database.
# A colon, ':', is used as the field terminator. A new line terminates
# the entry. Lines beginning with a pound sign, '#', are comments.
#
# Entries are of the form:
# $ORACLE_SID:$ORACLE_HOME:<N|Y>:
#
# The first and second fields are the system identifier and home
# directory of the database respectively. The third filed indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
+ASM:/u01/app/grid11201:N
labdb:/u01/app/oracle/oracle11201:N
[oracle@localhost ~]$ echo $PATH
/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/oracle/bin:/u01/app/oracle/oracle11201/bin/
[oracle@localhost ~]$ echo $LD_LIBRARY_PATH
[oracle@localhost ~]$ LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib; export LD_LIBRARY_PATH
[oracle@localhost ~]$ echo $LD_LIBRARY_PATH
/u01/app/oracle/oracle11201/lib:/usr/lib
[oracle@localhost ~]$ echo $CLASSPATH
[oracle@localhost ~]$ csscan
Character Set Scanner v2.2 : Release 11.2.0.1.0 - Production on Mon Apr 23 20:00:46 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username:
Password:
ORA-01017: invalid username/password; logon denied
Scanner terminated unsuccessfully.
[oracle@localhost ~]$
[oracle@localhost ~]$
[oracle@localhost ~]$
[oracle@localhost ~]$ csscan TABLE='(SYS.SQL_VERSION$)' FROMCHAR=US7ASCII TOCHAR=US7ASCII LOG=instchkc CAPTURE=N PROCESS=1 ARRAY=1024000
Character Set Scanner v2.2 : Release 11.2.0.1.0 - Production on Mon Apr 23 20:01:51 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Enumerating table to scan...
. process 1 scanning SYS.SQL_VERSION$[AAAACUAABAAAASYAAA]
Creating Database Scan Summary Report...
Creating Individual Exception Report...
Scanner terminated successfully.
[oracle@localhost ~]$ ls -ltr
total 80
drwxr-x--- 3 oracle oinstall 4096 Feb 25 19:55 oradiag_oracle
drwxr-xr-x 2 oracle oinstall 4096 Mar 21 00:02 backup
-rw-r--r-- 1 oracle oinstall 17710 Mar 29 18:38 dgdiag_phystby_labdb_Mar29_0638.out
-rw-r--r-- 1 oracle oinstall 69 Apr 22 08:42 afiedt.buf
-rw-r--r-- 1 oracle oinstall 25279 Apr 23 19:02 csminst.log
-rw-r--r-- 1 oracle oinstall 270 Apr 23 20:00 scan.out
-rw-r--r-- 1 oracle oinstall 7717 Apr 23 20:02 instchkc.txt
-rw-r--r-- 1 oracle oinstall 582 Apr 23 20:02 instchkc.out
-rw-r--r-- 1 oracle oinstall 1357 Apr 23 20:02 instchkc.err
[oracle@localhost ~]$ view instchkc.err
impdp full=Y directory=TEST_DIR dumpfile=Full_labdb.dmp logfile=impdpDB_testDB.log table_exists_action=replace
I have to create a new DB with correct CHARACTERSET(AL32UTF8) & import the database dump to it, So in order to avoid losing data we have performed the below steps
We have followed document Changing the NLS_CHARACTERSET to AL32UTF8 / UTF8 (Unicode) [ID 260192.1] for it============================
START OF SCREENSHOT
============================
SQL> set TERMOUT ON
SQL> set ECHO ON
SQL> spool csminst.log
SQL> drop user csmig cascade;
drop user csmig cascade
*
ERROR at line 1:
ORA-01918: user 'CSMIG' does not exist
SQL> @?/rdbms/admin/csminst.sql
SQL> rem
SQL> rem $Header: csmig/sql/csminst.sql /main/29 2008/09/09 00:47:40 ssubrama Exp $ csminst.sql
SQL> rem
SQL> rem Copyright (c) 1988, 2008, Oracle. All rights reserved.
SQL> rem
SQL> rem NAME
SQL> rem csminst.sql
SQL> rem DESCRIPTION
SQL> rem Create tables for Database Character Set Migration Utility
SQL> rem NOTE
SQL> rem This script must be run while connected as SYS
SQL> rem MODIFIED
SQL> rem ssubrama 09/04/08 - bug 7047837 fix create user
SQL> rem nli 07/25/08 - fix bug 7256242, add the ORADATA user
SQL> rem ssubrama 06/09/08 - bug 7047837 cleanup csmig schema for security
SQL> rem xpeng 10/18/07 - fix bug 6460895
SQL> rem ssubrama 01/09/07 - bug 5738695 add copyright information
SQL> rem nli 08/02/06 - bug 5372557: CSX support. Add a CNVTYPE column into CSMIG.CSM$COLUMNS
SQL> rem ywu 07/14/04 - up version
SQL> rem fayang 04/23/04 - add column UNNESTED in CSM$TABLES
SQL> rem fayang 04/06/04 - add SCNCOL# in EXTABLES and add a view for EXTABLES
SQL> rem ywu 02/19/04 - fix bug 3434808, delete hard code passwd.
SQL> rem ywu 07/29/03 - grant privilege to system for external table
SQL> rem ywu 05/30/03 - add a table for data dictionary
SQL> rem ywu 05/09/03 - add led parameter to csm$errors
SQL> rem ywu 10/22/02 - add another error for codepoint exceed
SQL> rem ywu 08/30/02 - up version
SQL> rem ywu 07/01/02 - add size information
SQL> rem ywu 07/02/02 - add resumable
SQL> rem plinsley 01/24/02 - add include/exclude
SQL> rem plinsley 04/02/01 - up version
SQL> rem plinsley 03/23/01 - #1509940
SQL> rem plinsley 03/26/01 - update views
SQL> rem plinsley 12/15/00 - remove order by from view
SQL> rem plinsley 11/03/00 - Add converter process column
SQL> rem plinsley 10/04/00 - split tables that cross files
SQL> rem plinsley 09/21/00 - Long ROWIDs in id
SQL> rem plinsley 08/09/00 - Adding constraint dependency handling
SQL> rem mtozawa 06/29/00 - add csmv$ views
SQL> rem mtozawa 06/27/00 - add browid to csm$columns
SQL> rem mtozawa 06/02/00 - specify the storage clause for csm$errors
SQL> rem mtozawa 05/26/00 - bug1314547:optimize split mechanism, add blocks
SQL> rem mtozawa 05/19/00 - Change table names from SCN$* to CSM$*
SQL> rem mtozawa 03/08/00 - add SPLIT support
SQL> rem mtozawa 12/21/99 - add property column to SCN$TABLES for IOT
SQL> rem mtozawa 11/05/99 - remove storage params from SCN$ERRORS
SQL> rem mtozawa 11/04/99 - add maxsiz to SCN$COLUMNS
SQL> rem mtozawa 09/26/99 - Creation
SQL> rem
SQL>
SQL>
SQL> rem *****************************************************************
SQL> rem The user CSMIG owns tables and procedures of Database Scanner
SQL> rem *****************************************************************
SQL> WHENEVER SQLERROR EXIT
SQL>
SQL> create user csmig identified by csmig password expire account lock
2 /
User created.
SQL>
SQL> WHENEVER SQLERROR CONTINUE
SQL>
SQL> grant select on sys.obj$ to csmig
2 /
Grant succeeded.
SQL> grant select on sys.col$ to csmig
2 /
Grant succeeded.
SQL> grant select on sys.icol$ to csmig
2 /
Grant succeeded.
SQL> grant select on sys.ind$ to csmig
2 /
Grant succeeded.
SQL> grant select on sys.cdef$ to csmig
2 /
Grant succeeded.
SQL> grant select on sys.con$ to csmig
2 /
Grant succeeded.
SQL> grant select on sys.trigger$ to csmig
2 /
Grant succeeded.
SQL> rem *****************************************************************
SQL> rem DBA MUST ASSIGN PROPER TABLESPACE TO CSMIG
SQL> rem *****************************************************************
SQL> alter user csmig default tablespace SYSTEM quota unlimited on SYSTEM
2 /
User altered.
SQL>
SQL> rem *****************************************************************
SQL> rem Add version information for csm$* schema
SQL> rem The schema version must be bumped up as csm$* schema get updated.
SQL> rem VERSION HISTORY:
SQL> rem 1 ... 8.1.7
SQL> rem *****************************************************************
SQL> insert into sys.props$
2 select 'NLS_CSMIG_SCHEMA_VERSION', 'x',
3 'Character set migration utiltiy schema version #'
4 from dual
5 where not exists
6 (select 'x' from sys.props$ where name = 'NLS_CSMIG_SCHEMA_VERSION')
7 /
1 row created.
SQL> update sys.props$ set value$ = 5 where name = 'NLS_CSMIG_SCHEMA_VERSION'
2 /
1 row updated.
SQL> rem *****************************************************************
SQL> rem Database Scanner leaves the last scan parameters in CSM$PARAMETERS
SQL> rem Each background process will read scan parameters from here.
SQL> rem *****************************************************************
SQL> create table csmig.csm$parameters
2 ( name varchar2(30) not null, /* paraneter name */
3 value varchar2(80) not null /* parameter value */
4 )
5 /
Table created.
SQL> drop public synonym csm$parameters
2 /
drop public synonym csm$parameters
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
SQL> create public synonym csm$parameters for csmig.csm$parameters
2 /
Synonym created.
SQL> rem *****************************************************************
SQL> rem Database Scanner saves the query string in CSM$QUERY
SQL> rem *****************************************************************
SQL> create table csmig.csm$query
2 (
3 value clob not null /* query value */
4 )
5 /
Table created.
SQL> drop public synonym csm$query
2 /
drop public synonym csm$query
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
SQL> create public synonym csm$query for csmig.csm$query
2 /
Synonym created.
SQL> rem *****************************************************************
SQL> rem Database Scanner enumerate all tables need to be scanned
SQL> rem Each background process will pick up a row from here for table to scan
SQL> rem *****************************************************************
SQL> create table csmig.csm$tables
2 ( usr# number not null, /* user id of the table owner */
3 obj# number not null, /* object id of the table */
4 minrowid rowid, /* Minimum rowid of the split range of the table */
5 maxrowid rowid, /* Maximum rowid of the split range of the table */
6 property number, /* table property */
7 blocks number, /* number of blocks used by this table */
8 files number, /* number of files used by this table */
9 who number, /* internal thread id who scanned the table */
10 whoconv number, /* internal thread id who converted the table */
11 lngconv number, /* internal thread id who converted long col */
12 scnstart date, /* time table scan started */
13 scnend date, /* time table scan completed */
14 scncols number, /* number of columns to be scanned */
15 scnrows number, /* number of rows scanned */
16 cnvstart date, /* time table convert started */
17 cnvend date, /* time table convert completed */
18 lngstart date, /* time table convert long started */
19 lngend date, /* time table convert long completed */
20 cnvcols number, /* number of columns to be converted */
21 cnvrows number, /* number of rows to be converted */
22 lngrows number, /* number of rows of long to be converted */
23 addsize number,
24 lastupd rowid, /* ROWID lastupdated */
25 pstcvrows number, /* how many rows have been converted */
26 lastupdlg rowid, /* ROWID lastupdated */
27 pstcvrowslg number, /* how many rows have been converted */
28 unnested number /* if this table is unnested or not */
29 )
30 /
Table created.
SQL> drop public synonym csm$tables
2 /
drop public synonym csm$tables
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
SQL> create public synonym csm$tables for csmig.csm$tables
2 /
Synonym created.
SQL> rem *****************************************************************
SQL> rem CSM$COLUMNS contains statistic information of column data
SQL> rem *****************************************************************
SQL> create table csmig.csm$columns
2 ( usr# number not null, /* user id of the table owner */
3 obj# number not null, /* object id of the table */
4 browid rowid, /* rowid of the row in csm$tables */
5 col# number not null, /* column id */
6 intcol# number not null, /* internal column id (for ADT) */
7 dty# number not null, /* column data type */
8 frm# number not null, /* character set form */
9 numrows number not null, /* number of rows in this table */
10 nulcnt number not null, /* number of null cell data */
11 cnvcnt number not null, /* number of cell data that need to convert */
12 cnvtype number default 0 not null, /* convert type */
13 /* 1 = data in data dictionary, and can be converted by csconv*/
14 errcnt number not null, /* number of cell data that has exception */
15 sizerr number not null, /* number of cell data that exceed column size */
16 cnverr number not null, /* number of cell data that undergo lossy conv.*/
17 maxsiz number not null, /* max post conversion data size */
18 chrsiz number not null, /* truncation due to char semantics */
19 cnvsuc number, /* cells converted successfully */
20 cnvtrn number, /* cells converted with truncation */
21 cnvlos number, /* cells converted with lossy result */
22 cnvfai number /* cells failed to convert */
23 )
24 /
Table created.
SQL> drop public synonym csm$columns
2 /
drop public synonym csm$columns
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
SQL> create public synonym csm$columns for csmig.csm$columns
2 /
Synonym created.
SQL> rem *****************************************************************
SQL> rem CSM$EXTABLES contains exception tables
SQL> rem *****************************************************************
SQL> create table csmig.csm$extables
2 ( usr# number not null, /* user id of the table owner */
3 obj# number not null, /* object id of the table */
4 col# number, /* column id */
5 intcol# number, /* internal column id (for ADT) */
6 dty# number, /* column data type */
7 frm# number, /* character set form */
8 property number default 0 not null, /* property of row */
9 scncol# number /* column id to be scaned */
10 )
11 /
Table created.
SQL> drop public synonym csm$extables
2 /
drop public synonym csm$extables
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
SQL> create public synonym csm$extables for csmig.csm$extables
2 /
Synonym created.
SQL> rem *****************************************************************
SQL> rem CSM$ERRORS contains individual exception information
SQL> rem *****************************************************************
SQL> create table csmig.csm$errors
2 ( err# number not null, /* exception type */
3 usr# number not null, /* user id of the object/data owner */
4 obj# number not null, /* object id */
5 col# number, /* column id / position */
6 intcol# number, /* internal column id (for ADT) */
7 typ# number, /* column data type / object type */
8 frm# number, /* character set form */
9 cnvsize number, /* post conversion data size */
10 id$ varchar2(1000), /* rowid / name to identify object */
11 csidleds number, /* number of charset id from led */
12 csidled1 number, /* first charset id from led */
13 csidled2 number, /* second charset id from led */
14 csidled3 number, /* third charset id from led */
15 langidleds number, /* number of language id from led */
16 langidled1 number, /* first language id from led */
17 langidled2 number, /* second language id from led */
18 langidled3 number /* third language id from led */
19 )
20 pctfree 0 pctused 99
21 storage(next 100K maxextents unlimited pctincrease 0)
22 /
Table created.
SQL> drop public synonym csm$errors
2 /
drop public synonym csm$errors
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
SQL> create public synonym csm$errors for csmig.csm$errors
2 /
Synonym created.
SQL> rem *****************************************************************
SQL> rem CSM$LANGID contains summary information
SQL> rem *****************************************************************
SQL> create table csmig.csm$langid
2 ( obj# number not null, /* object id */
3 langid number, /* language id from led */
4 count number
5 )
6 /
Table created.
SQL> drop public synonym csm$langid
2 /
drop public synonym csm$langid
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
SQL> create public synonym csm$langid for csmig.csm$langid
2 /
Synonym created.
SQL> rem *****************************************************************
SQL> rem CSM$CHARSETID contains summary information
SQL> rem *****************************************************************
SQL> create table csmig.csm$charsetid
2 ( obj# number not null, /* object id */
3 csid number, /* language id from led */
4 count number
5 )
6 /
Table created.
SQL> drop public synonym csm$charsetid
2 /
drop public synonym csm$charsetid
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
SQL> create public synonym csm$charsetid for csmig.csm$charsetid
2 /
Synonym created.
SQL> rem *****************************************************************
SQL> rem CSM$INDEXES lists indexes to be disabled
SQL> rem *****************************************************************
SQL> create table csmig.csm$indexes
2 ( obj# number not null /* object id of the index */
3 )
4 /
Table created.
SQL> drop public synonym csm$indexes
2 /
drop public synonym csm$indexes
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
SQL> create public synonym csm$indexes for csmig.csm$indexes
2 /
Synonym created.
SQL> rem *****************************************************************
SQL> rem CSM$CONSTRAINTS lists constraints to be disabled
SQL> rem *****************************************************************
SQL> create table csmig.csm$constraints
2 (
3 rid number not null, /* root constraint id */
4 lvl number, /* constraint level */
5 con# number not null /* internal constraint number */
6 )
7 /
Table created.
SQL> drop public synonym csm$constraints
2 /
drop public synonym csm$constraints
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
SQL> create public synonym csm$constraints for csmig.csm$constraints
2 /
Synonym created.
SQL> rem *****************************************************************
SQL> rem CSM$TRIGGERS lists triggers to be disabled
SQL> rem *****************************************************************
SQL> create table csmig.csm$triggers
2 ( obj# number not null /* object id of the trigger */
3 )
4 /
Table created.
SQL> drop public synonym csm$triggers
2 /
drop public synonym csm$triggers
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
SQL> create public synonym csm$triggers for csmig.csm$triggers
2 /
Synonym created.
SQL> rem *****************************************************************
SQL> rem CSM$DICTUSERS lists triggers to be disabled
SQL> rem *****************************************************************
SQL> create table csmig.csm$dictusers
2 ( user# number not null, /* usre id for all data dictionary */
3 username varchar2(30)
4 )
5 /
Table created.
SQL> drop public synonym csm$dictusers
2 /
drop public synonym csm$dictusers
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
SQL> create public synonym csm$dictusers for csmig.csm$dictusers
2 /
Synonym created.
SQL> insert into csmig.csm$dictusers
2 select distinct u.user_id, u.username from all_users u, sys.ku_noexp_view k
3 where (k.OBJ_TYPE='USER' and k.name=u.username) or (u.username in ('SYSTEM', 'ORDDATA'))
4 /
17 rows created.
SQL> rem
SQL> rem define CSMV$ views
SQL> rem
SQL> rem *****************************************************************
SQL> rem CSMV$TABLES lists tables (to be) scanned
SQL> rem *****************************************************************
SQL> create or replace view csmig.csmv$tables
2 (owner_id, owner_name, table_id, table_name, MIN_ROWID, MAX_ROWID,
3 BLOCKS, SCAN_COLUMNS, SCAN_ROWS, SCAN_START, SCAN_END)
4 as
5 select c.usr#, u.username, c.obj#, o.name,
6 rowidtochar(c.minrowid), rowidtochar(c.maxrowid),
7 c.blocks, c.scncols, c.scnrows,
8 to_char(c.scnstart,'hh24:mi:ss'), to_char(c.scnend,'hh24:mi:ss')
9 from csm$tables c, all_users u, sys.obj$ o
10 where c.usr#=u.user_id and c.obj#=o.obj#
11 /
View created.
SQL> drop public synonym csmv$tables
2 /
drop public synonym csmv$tables
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
SQL> create public synonym csmv$tables for csmig.csmv$tables
2 /
Synonym created.
SQL> rem *****************************************************************
SQL> rem CSMV$COLUMNS lists columns scanned
SQL> rem *****************************************************************
SQL> create or replace view csmig.csmv$columns
2 (owner_id, owner_name, table_id, table_name, column_id, column_intid,
3 column_name, column_type, total_rows, null_rows, conv_rows, error_rows,
4 exceed_size_rows, data_loss_rows, cs_exceed_size_rows, max_post_convert_size)
5 as
6 select c.usr#, u.username, c.obj#, o.name, c.col#, c.intcol#, co.name,
7 decode(c.frm#, 2, 'N', '') ||
8 decode(c.dty#, 1, 'VARCHAR2', 8, 'LONG', 96, 'CHAR', 112, 'CLOB',''),
9 c.numrows, c.nulcnt, c.cnvcnt, c.errcnt, c.sizerr, c.cnverr, c.chrsiz, c.maxsiz
10 from csm$columns c, all_users u, sys.obj$ o, sys.col$ co
11 where c.usr#=u.user_id and c.obj#=o.obj# and c.obj#=co.obj#
12 and c.col#=co.col# and c.intcol#=co.intcol#
13 /
View created.
SQL> drop public synonym csmv$columns
2 /
drop public synonym csmv$columns
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
SQL> create public synonym csmv$columns for csmig.csmv$columns
2 /
Synonym created.
SQL> rem *****************************************************************
SQL> rem CSMV$ERRORS lists exceptional data cell information
SQL> rem *****************************************************************
SQL> create or replace view csmig.csmv$errors
2 (owner_id, owner_name, table_id, table_name,
3 column_id, column_intid, column_name, data_rowid,
4 column_type, error_type)
5 as
6 select e.usr#, u.username, e.obj#, o.name,
7 e.col#, e.intcol#, c.name, e.id$,
8 decode(e.frm#, 2, 'N', '') ||
9 decode(e.typ#, 1, 'VARCHAR2', 8, 'LONG', 96, 'CHAR', 112, 'CLOB'),
10 decode(e.err#, 0, 'CONVERTIBLE', 1, 'EXCEED_SIZE', 2, 'DATA_LOSS',
11 3, 'CS_EXCEED_SIZE')
12 from csm$errors e, all_users u, sys.obj$ o, sys.col$ c
13 where e.usr#=u.user_id and e.obj#=o.obj#
14 and e.obj#=c.obj# and e.col#=e.col# and e.intcol#=c.intcol#
15 /
View created.
SQL> drop public synonym csmv$errors
2 /
drop public synonym csmv$errors
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
SQL> create public synonym csmv$errors for csmig.csmv$errors
2 /
Synonym created.
SQL> rem *****************************************************************
SQL> rem CSMV$INDEXES lists all indexes to be disabled
SQL> rem *****************************************************************
SQL> create or replace view csmig.csmv$indexes
2 (index_owner_id, index_owner_name, index_id, index_name,
3 index_status#, index_status,
4 table_owner_id, table_owner_name, table_id, table_name,
5 column_id, column_intid, column_name)
6 as
7 select iu.user_id, iu.username, io.obj#, io.name, id.flags,
8 decode(bitand(id.flags,1), 1, 'UNUSABLE', 'VALID'),
9 bu.user_id, bu.username, bo.obj#, bo.name,
10 cl.col#, cl.intcol#, cl.name
11 from csm$indexes ci, sys.icol$ ic, sys.ind$ id, all_users iu,
12 sys.obj$ io, all_users bu, sys.obj$ bo, sys.col$ cl
13 where ci.obj#=ic.obj# and ci.obj#=id.obj#
14 and ci.obj#=io.obj# and io.owner#=iu.user_id
15 and ic.bo# =bo.obj# and bo.owner#=bu.user_id
16 and ic.bo#=cl.obj# and ic.col#=cl.col# and ic.intcol#=cl.intcol#
17 /
View created.
SQL> drop public synonym csmv$indexes
2 /
drop public synonym csmv$indexes
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
SQL> create public synonym csmv$indexes for csmig.csmv$indexes
2 /
Synonym created.
SQL> rem *****************************************************************
SQL> rem CSMV$CONSTRAINTS lists all constraints to be disabled
SQL> rem *****************************************************************
SQL> create or replace view csmig.csmv$constraints
2 (owner_id, owner_name, constraint_id, constraint_name,
3 constraint_type#, constraint_type, table_id, table_name,
4 constraint_rid, constraint_level)
5 as
6 select c.owner#, u.username, c.con#, c.name, cd.type#,
7 decode(cd.type#, 1, 'CHECK', 2, 'PRIMARY_KEY', 3, 'UNIQUE',
8 4, 'REFERENTIAL', 'UNKNOWN'),
9 o.obj#, o.name, cc.rid, cc.lvl
10 from csm$constraints cc, sys.cdef$ cd, sys.con$ c, all_users u, sys.obj$ o
11 where cc.con#=cd.con# and cc.con#=c.con#
12 and c.owner#=u.user_id and cd.obj#=o.obj#
13 /
View created.
SQL> drop public synonym csmv$constraints
2 /
drop public synonym csmv$constraints
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
SQL> create public synonym csmv$constraints for csmig.csmv$constraints
2 /
Synonym created.
SQL> rem *****************************************************************
SQL> rem CSMV$TRIGGERS lists all triggers to be disabled
SQL> rem *****************************************************************
SQL> create or replace view csmig.csmv$triggers
2 (trigger_owner_id, trigger_owner_name, trigger_id, trigger_name,
3 table_owner_id, table_owner_name, table_id, table_name)
4 as
5 select ru.user_id, ru.username, tr.obj#, ro.name, bu.user_id,
6 bu.username, tr.baseobject, bo.name
7 from csm$triggers ct, sys.trigger$ tr, all_users ru, sys.obj$ ro,
8 all_users bu, sys.obj$ bo
9 where ct.obj#=tr.obj# and ct.obj#=ro.obj# and ro.owner#=ru.user_id
10 and tr.baseobject=bo.obj# and bu.user_id=bo.owner#
11 /
View created.
SQL> drop public synonym csmv$triggers
2 /
drop public synonym csmv$triggers
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
SQL> create public synonym csmv$triggers for csmig.csmv$triggers
2 /
Synonym created.
SQL> rem *****************************************************************
SQL> rem CSMV$EXTABLES lists all distinct objects to be scaned
SQL> rem *****************************************************************
SQL> create or replace view csmig.csmv$extables
2 (obj#, usr#, property)
3 as
4 select distinct(obj#), usr#, property
5 from csm$extables where property=0;
View created.
SQL> /
View created.
SQL> rem *****************************************************************
SQL> rem SYS.CSMV$KTFBUE wraps the fixed table sys.x$ktfbue in data
SQL> rem dictionary for users only with DBA privilege to access
SQL> rem *****************************************************************
SQL> create or replace view sys.csmv$ktfbue as select * from sys.x$ktfbue;
View created.
SQL> /
View created.
SQL> grant select on sys.csmv$ktfbue to dba;
Grant succeeded.
SQL> /
Grant succeeded.
SQL> exit;
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[oracle@localhost ~]$ echo $ORACLE_SID
labdb
[oracle@localhost ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Mon Apr 23 19:04:02 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> conn csmig/csmig
ERROR:
ORA-28000: the account is locked
Warning: You are no longer connected to ORACLE.
SQL> show user
USER is ""
SQL> conn / as sysdba
Connected.
SQL> alter account csmig unlock;
alter account csmig unlock
*
ERROR at line 1:
ORA-00940: invalid ALTER command
SQL> alter user csmig account unlock;
User altered.
SQL> conn csmig/csmig
ERROR:
ORA-28001: the password has expired
Changing password for csmig
New password:
Retype new password:
ERROR:
ORA-01045: user CSMIG lacks CREATE SESSION privilege; logon denied
Password changed
Warning: You are no longer connected to ORACLE.
SQL> show user
USER is ""
SQL> conn / as sysdba
Connected.
SQL> alter user csmig identified by oracle;
User altered.
SQL> conn csmig/oracle
ERROR:
ORA-01045: user CSMIG lacks CREATE SESSION privilege; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn / as sysdba
Connected.
SQL> show user
USER is "SYS"
SQL> exit\
SP2-0584: EXIT variable "\" was non-numeric
Usage: { EXIT | QUIT } [ SUCCESS | FAILURE | WARNING | n |
<variable> | :<bindvariable> ] [ COMMIT | ROLLBACK ]
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[oracle@localhost ~]$ pwd
/home/oracle
[oracle@localhost ~]$ which csscaner
/usr/bin/which: no csscaner in (/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/oracle/bin:/u01/app/oracle/oracle11201/bin/)
[oracle@localhost ~]$ which csscan
/u01/app/oracle/oracle11201/bin/csscan
[oracle@localhost ~]$ csscan TABLE='(SYS.SQL_VERSION$)' FROMCHAR=US7ASCII TOCHAR=US7ASCII LOG=instchkc CAPTURE=N PROCESS=1 ARRAY=1024000
csscan: error while loading shared libraries: libclntsh.so.11.1: cannot open shared object file: No such file or directory
[oracle@localhost ~]$ csscan
csscan: error while loading shared libraries: libclntsh.so.11.1: cannot open shared object file: No such file or directory
[oracle@localhost ~]$ ldd `which sysresv`
linux-gate.so.1 => (0x00696000)
libclntsh.so.11.1 => /u01/app/oracle/oracle11201/lib/libclntsh.so.11.1 (0x00697000)
libnnz11.so => /u01/app/oracle/oracle11201/lib/libnnz11.so (0x00110000)
libdl.so.2 => /lib/libdl.so.2 (0x0035d000)
libm.so.6 => /lib/libm.so.6 (0x00361000)
libpthread.so.0 => /lib/libpthread.so.0 (0x00388000)
libnsl.so.1 => /lib/libnsl.so.1 (0x003a0000)
libc.so.6 => /lib/libc.so.6 (0x003b7000)
libaio.so.1 => /usr/lib/libaio.so.1 (0x00508000)
/lib/ld-linux.so.2 (0x00590000)
[oracle@localhost ~]$ echo $ORACLE_HOME
/u01/app/oracle/oracle11201
[oracle@localhost ~]$ cat /etc/oratab
#
# This file is used by ORACLE utilities. It is created by root.sh
# and updated by the Database Configuration Assistant when creating
# a database.
# A colon, ':', is used as the field terminator. A new line terminates
# the entry. Lines beginning with a pound sign, '#', are comments.
#
# Entries are of the form:
# $ORACLE_SID:$ORACLE_HOME:<N|Y>:
#
# The first and second fields are the system identifier and home
# directory of the database respectively. The third filed indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
+ASM:/u01/app/grid11201:N
labdb:/u01/app/oracle/oracle11201:N
[oracle@localhost ~]$ echo $PATH
/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/oracle/bin:/u01/app/oracle/oracle11201/bin/
[oracle@localhost ~]$ echo $LD_LIBRARY_PATH
[oracle@localhost ~]$ LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib; export LD_LIBRARY_PATH
[oracle@localhost ~]$ echo $LD_LIBRARY_PATH
/u01/app/oracle/oracle11201/lib:/usr/lib
[oracle@localhost ~]$ echo $CLASSPATH
[oracle@localhost ~]$ csscan
Character Set Scanner v2.2 : Release 11.2.0.1.0 - Production on Mon Apr 23 20:00:46 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username:
Password:
ORA-01017: invalid username/password; logon denied
Scanner terminated unsuccessfully.
[oracle@localhost ~]$
[oracle@localhost ~]$
[oracle@localhost ~]$
[oracle@localhost ~]$ csscan TABLE='(SYS.SQL_VERSION$)' FROMCHAR=US7ASCII TOCHAR=US7ASCII LOG=instchkc CAPTURE=N PROCESS=1 ARRAY=1024000
Character Set Scanner v2.2 : Release 11.2.0.1.0 - Production on Mon Apr 23 20:01:51 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Enumerating table to scan...
. process 1 scanning SYS.SQL_VERSION$[AAAACUAABAAAASYAAA]
Creating Database Scan Summary Report...
Creating Individual Exception Report...
Scanner terminated successfully.
[oracle@localhost ~]$ ls -ltr
total 80
drwxr-x--- 3 oracle oinstall 4096 Feb 25 19:55 oradiag_oracle
drwxr-xr-x 2 oracle oinstall 4096 Mar 21 00:02 backup
-rw-r--r-- 1 oracle oinstall 17710 Mar 29 18:38 dgdiag_phystby_labdb_Mar29_0638.out
-rw-r--r-- 1 oracle oinstall 69 Apr 22 08:42 afiedt.buf
-rw-r--r-- 1 oracle oinstall 25279 Apr 23 19:02 csminst.log
-rw-r--r-- 1 oracle oinstall 270 Apr 23 20:00 scan.out
-rw-r--r-- 1 oracle oinstall 7717 Apr 23 20:02 instchkc.txt
-rw-r--r-- 1 oracle oinstall 582 Apr 23 20:02 instchkc.out
-rw-r--r-- 1 oracle oinstall 1357 Apr 23 20:02 instchkc.err
[oracle@localhost ~]$ view instchkc.err
12.d) When using (full) export/import to go to a new/other AL32UTF8 database and there was NO "Truncation" data, only
"Convertible" and "Changeless" in the csscan done in point 4: expdp full=Y directory=TEST_DIR dumpfile=Full_labdb.dmp logfile=expdpFull.logimpdp full=Y directory=TEST_DIR dumpfile=Full_labdb.dmp logfile=impdpDB_testDB.log table_exists_action=replace
total bakwaas
ReplyDelete