Characterset change 1

[spicysudhi]

hi

current national characterset is UTF8. I want to change that to AL16UTF16.

I tried
connect sys/syspassword as sysdba
SHUTDOWN IMMEDIATE
STARTUP RESTRICT
ALTER TABLE testdb NATIONAL CHARACTER SET AL16UTF16;

I got a message
ORA-12721: operation cannot execute when other sessions are active


can someone help me how to overcome this.

will there be any major impacts by changing this.

thanks in advance,
sudhi

 

[mike1953]

There is a very complete Oracle Note on this topic. The Note number is 66320.1. I have a printed copy right on my desk; we're converting our systems to Unicode (and what a joy this project is turning out to be!!)

In short, the procedure is:
1) Change INIT.ORA file; unset parallel_server parameter.
2) Execute these commands:
STARTUP MOUNT ;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
ALTER DATABASE OPEN;
<commands to change character set or national character set>
SHUTDOWN IMMEDIATE;
STARTUP RESTRICT;
SHUTDOWN IMMEDIATE ;
3) Restore INIT.ORA
4) STARTUP;

The double-shutdown in step 2 is required because of an SGA initialization bug. If you still get the 12721 error, then somebody is logging on who has the RESTRICTED SESSION privilege granted. This happened to me, too. I did:
1) stop the listener
2) Find out who has RESTRICTED SESSION. Copy down their password string from DBA_USERS, and then change their password(s). Or revoke RESTRICTED SESSION. Or lock their accounts(s). Basically, DENY THESE ACCOUNTS ACCESS!!
3) Change the character set.
4) Restore the user's privileges and/or passwords.

You're trying to change something that's important. Don't be nice about locking out users.

Good Luck!

Mike

 

[spicysudhi]

many thanks.

regards
sudhi