- in Source Database alter system set job_queue_processes=0 scope=both;
- in Source Database alter tables to CHAR (change the tables to CHAR before taking the export (-> point "E.2 Steps" )
- utlrp.sql
- create database in CHARACTER_SET=UTF8 with NLS_LENGTH_SEMANTICS=BYTE ! (or run startup migrate + catpatch.sql after creating database)
- Issue an ALTER SYSTEM SET NLS_LENGTH_SEMANTICS=CHAR SCOPE=BOTH command on the target database.
- Stop and restart the instance so that the parameter change takes effect.
- export database after change tables to CHAR (NLS_LANG=KOI8) можно exclude tables TRAFFIC_DAILY (RLS)
- import database to target в shell установить старый NLS_LANG=KOI8R (NLS_LENGTH_SEMANTICS=CHAR,NLS_LANG=KOI8R)
- run startup migrate + catpatch.sql + utlrp.sql
- alter system set job_queue_processes=8 scope=both;
- set NLS_LANG in oraenv :
NLS_LANG=american_america.utf8
NLS_SORT=russian
LC_ALL=C
LANG=C
- SET NLS_LENGTH_SEMANTICS=CHAR
*********************************************************
- BACKUP DATABASE !!! , BACKUP DATABASE !!!, BACKUP DATABASE !!!
- stop all jobs,sessions
- in Source Database alter tables to CHAR (change the tables to CHAR before taking the export (-> point "E.2 Steps" )
- in Source Database set NLS_LENGTH_SEMANTICS=CHAR
- utlrp.sql
- Issue an ALTER SYSTEM SET NLS_LENGTH_SEMANTICS=CHAR SCOPE=BOTH command on the target database.
- RESTART DATABASE;
- check convertible data:
csscan ......
- export tables (TRIGGERS=N CONSTRAINTS=N INDEXES=N) after change tables to CHAR (NLS_LANG=CL8KOI8R) можно exclude tables TRAFFIC (+ RLS)
- truncate all reloading tables (convertible data)
- disable all triggers,constraints !!!!!!!!!
- check,check and check all triggers,constraints !
- import database to target в shell установить старый NLS_LANG=....
******** по другому ************
Drop the original schema.
Recreate the original schema and its tables (you can use import's show=Y option to get the CREATE TABLE statements). Columns in the recreated tables will use character semantics, because that's now the default.
Import the schema into the target database using the IGNORE=Y import option.
**********
http://www.oracle.com/technology/oramag/oracle/03-mar/o23sql.html
Note:144808.1
Note:313175.1
E.2 Steps:
=============================================================================================================
=================================== A) check if you have partitions who are affected:
select C.owner, C.table_name, C.column_name, C.data_type, C.char_length
from all_tab_columns C, all_tables T
where C.owner = T.owner
and T.owner not in ('SYS', 'SYSTEM', 'CTXSYS', 'DBSNMP', 'DMSYS',
'EXFSYS', 'HR', 'IX', 'MDSYS', 'OE', 'OLAPSYS',
'ORDSYS', 'OUTLN', 'SH', 'SYSMAN', 'WKSYS',
'WK_TEST', 'WMSYS', 'XDB')
and C.table_name = T.table_name
and C.char_used = 'B'
and T.PARTITIONED='YES'
and C.table_name not in (select table_name from all_external_tables)
and C.data_type in ('VARCHAR2', 'CHAR');
=================================== A-2) check IOT overflow
select 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME || ' ADD OVERFLOW;' from dba_tables where IOT_TYPE is not null order by OWNER;
=============================================================================================================
==================================== B) check if you have functional indexes on affected columns:
select OWNER, INDEX_NAME , INDEX_TYPE, TABLE_OWNER, TABLE_NAME, STATUS, FUNCIDX_STATUS from ALL_INDEXES
where INDEX_TYPE not in ('NORMAL', 'BITMAP','IOT - TOP')
and TABLE_OWNER not in ('SYS', 'SYSTEM', 'CTXSYS', 'DBSNMP', 'DMSYS', 'EXFSYS', 'HR', 'IX',
'MDSYS', 'OE', 'OLAPSYS', 'ORDSYS', 'OUTLN', 'SH', 'SYSMAN', 'WKSYS', 'WK_TEST', 'WMSYS', 'XDB')
and TABLE_NAME in (select unique (table_name) from dba_tab_columns where char_used ='B');
The existence of functional indexes on BYTE columns will give "ORA-30556: functional index is defined on the
column to be modified" when going to CHAR semantics, you will need to drop them before changing to CHAR semantics
and then recreate them afterwards.
=============================================================================================================
==================================== C) then run the script to do the actual change of BYTE columns to CHAR:
set feedback off
set verify off
set serveroutput on
set serveroutput on size 200000
set termout on
exec dbms_output.put_line('Starting build select of columns to be altered');
drop table semantics$
/
create table semantics$(s_owner varchar2(40),
s_table_name varchar2(40),
s_column_name varchar2(40),
s_data_type varchar2(40),
s_char_length number)
/
insert into semantics$
select C.owner, C.table_name, C.column_name, C.data_type, C.char_length
from all_tab_columns C, all_tables T
where C.owner = T.owner
and T.owner not in ('SYS', 'SYSTEM', 'CTXSYS', 'DBSNMP', 'DMSYS',
'EXFSYS', 'HR', 'IX', 'MDSYS', 'OE', 'OLAPSYS',
'ORDSYS', 'OUTLN', 'SH', 'SYSMAN', 'WKSYS',
'WK_TEST', 'WMSYS', 'XDB')
and C.table_name = T.table_name
and C.char_used = 'B'
-- only need to look for tables who are not yet CHAR semantics.
-- and T.partitioned != 'YES'
-- exclude partitioned tables
and C.table_name not in (select table_name from all_external_tables)
and C.data_type in ('VARCHAR2', 'CHAR')
-- You can exclude or include tables or schemas as you wish, by adjusting
-- "and T.owner not in" as per your requirements
/
commit
/
declare
cursor c1 is select * from semantics$;
v_statement varchar2(255);
v_nc number(10);
v_nt number(10);
begin
execute immediate
'select count(*) from semantics$' into v_nc;
execute immediate
'select count(distinct s_table_name) from semantics$' into v_nt;
dbms_output.put_line
('ALTERing ' || v_nc || ' columns in ' || v_nt || ' tables');
for r1 in c1 loop
v_statement := 'ALTER TABLE ' || r1.s_owner || '.' || r1.s_table_name;
v_statement := v_statement || ' modify (' || r1.s_column_name || ' ';
v_statement := v_statement || r1.s_data_type || '(' || r1.s_char_length;
v_statement := v_statement || ' CHAR))';
dbms_output.put_line(r1.s_owner || '.' || r1.s_table_name);
execute immediate v_statement;
end loop;
dbms_output.put_line('Done');
end;
/
=============================================================================================
D) After running the script it's a good idea to run UTL_RECOMP to validate dependent objects.
==============================================================================================
alter table ...... MODIFY (VEC_KEY VARCHAR2(1000));
run utlrp.sql
