rem
rem NAME
rem   csminst.sql
rem DESCRIPTION
rem   Create tables for Database Character Set Migration Utility
rem NOTE
rem   This script must be run while connected as SYSTEM
rem MODIFIED
rem   mtozawa   06/02/00 - specify the storage clause for csm$errors
rem   mtozawa   05/26/00 - bug1314547:optimize split mechanism, add blocks
rem   mtozawa   05/19/00 - Change table names from SCN$* to CSM$*
rem   mtozawa   03/08/00 - add SPLIT support
rem   mtozawa   12/21/99 - add property column to SCN$TABLES for IOT
rem   mtozawa   11/05/99 - remove storage params from SCN$ERRORS
rem   mtozawa   11/04/99 - add maxsiz to SCN$COLUMNS
rem   mtozawa   09/26/99 - Creation
rem

rem *****************************************************************
rem  The user CSMIG owns tables and procedures of Database Scanner
rem *****************************************************************
create user csmig identified by csmig;
grant connect, resource, dba to csmig;
grant select any table to csmig;

rem *****************************************************************
rem  DBA MUST ASSIGN PROPER TABLESPACE TO CSMIG
rem *****************************************************************
alter user csmig default tablespace SYSTEM;

rem *****************************************************************
rem  connect as CSMIG, all tables and packages belong to CSMIG
rem *****************************************************************
connect csmig/csmig

drop table csm$parameters;
drop table csm$tables;
drop table csm$columns;
drop table csm$errors;

rem *****************************************************************
rem  Database Scanner leaves the last scan parameters in CSM$PARAMETERS
rem  Each background process will read scan parameters from here.
rem *****************************************************************
create table csm$parameters
( name      varchar2(30) not null,                         /* paraneter name */
  value     varchar2(80) not null                         /* parameter value */
);

rem *****************************************************************
rem  Database Scanner enumerate all tables need to be scanned
rem  Each background process will pick up a row from here for table to scan
rem *****************************************************************
create table csm$tables
(  usr#      number not null,                  /* user id of the table owner */
   obj#      number not null,                      /* object id of the table */
   minrowid  rowid,         /* Minimum rowid of the split range of the table */
   maxrowid  rowid,         /* Maximum rowid of the split range of the table */
   property  number,                                       /* table property */
   blocks    number,                  /* number of blocks used by this table */
   who       number,             /* internal thread id who scanned the table */
   scnstart  date,                                /* time table scan started */
   scnend    date,                              /* time table scan completed */
   scncols   number,                      /* number of columns to be scanned */
   scnrows   number,                               /* number of rows scanned */
   cnvstart  date,                             /* time table convert started */
   cnvend    date,                           /* time table convert completed */
   cnvcols   number,                    /* number of columns to be converted */
   cnvrows   number                        /* number of rows to be converted */
);

rem *****************************************************************
rem  CSM$COLUMNS contains statistic information of column data
rem *****************************************************************
create table csm$columns
( usr#       number not null,                  /* user id of the table owner */
  obj#       number not null,                      /* object id of the table */
  col#       number not null,                                   /* column id */
  intcol#    number not null,                /* internal column id (for ADT) */
  dty#       number not null,                            /* column data type */
  frm#       number not null,                          /* character set form */
  numrows    number not null,                /* number of rows in this table */
  nulcnt     number not null,                    /* number of null cell data */
  cnvcnt     number not null,    /* number of cell data that need to convert */
  errcnt     number not null,      /* number of cell data that has exception */
  sizerr     number not null, /* number of cell data that exceed column size */
  cnverr     number not null, /* number of cell data that undergo lossy conv.*/
  maxsiz     number not null                /* max post conversion data size */
);

rem *****************************************************************
rem  CSM$ERRORS contains individual exception information
rem *****************************************************************
create table csm$errors
( err#       number not null,                              /* exception type */
  usr#       number not null,            /* user id of the object/data owner */
  obj#       number not null,                                  /* object id  */
  col#       number,                                 /* column id / position */
  intcol#    number,                         /* internal column id (for ADT) */
  typ#       number,                       /* column data type / object type */
  frm#       number,                                   /* character set form */
  cnvsize    number,                            /* post conversion data size */
  id$        varchar2(41)                 /* rowid / name to identify object */
)
pctfree 0 pctused 99
storage(next 100K maxextents unlimited pctincrease 0);

rem *****************************************************************
rem create public synonyms for csm$* tables
rem *****************************************************************
drop public synonym csm$parameters;
drop public synonym csm$tables;
drop public synonym csm$columns;
drop public synonym csm$errors;

create public synonym csm$parameters  for csmig.csm$parameters;
create public synonym csm$tables      for csmig.csm$tables;
create public synonym csm$columns     for csmig.csm$columns;
create public synonym csm$errors      for csmig.csm$errors;

grant select on csmig.csm$parameters  to public;
grant select on csmig.csm$tables      to public;
grant select on csmig.csm$columns     to public;
grant select on csmig.csm$errors      to public;

rem *****************************************************************
rem  Add version information for csm$* schema
rem  The schema version must be bumped up as csm$* schema get updated.
rem  VERSION HISTORY:
rem    1 ... 8.1.7
rem *****************************************************************
insert into sys.props$
select 'NLS_CSMIG_SCHEMA_VERSION', 'x',
       'Character set migration utiltiy schema version #'
  from dual 
 where not exists
       (select 'x' from sys.props$ where name = 'NLS_CSMIG_SCHEMA_VERSION')
/
update sys.props$ set value$ = 1 where name = 'NLS_CSMIG_SCHEMA_VERSION'
/
commit
/

exit;

