set echo on
SPOOL ITIS-.LOG
/** DATABASE itis  delimiter | **/
DROP USER ITIS CASCADE;
DROP ROLE ITIS_USER;

CREATE ROLE ITIS_USER;
CREATE USER "ITIS"  PROFILE "DEFAULT" IDENTIFIED BY "ITIS" 
    DEFAULT 
    TABLESPACE "STORET1_TABS" TEMPORARY 
    TABLESPACE "TEMPORARY_DATA" QUOTA UNLIMITED 
    ON STORET1_INDS QUOTA UNLIMITED 
    ON STORET1_TABS ACCOUNT UNLOCK;
GRANT "EXP_FULL_DATABASE" TO "ITIS";
GRANT "IMP_FULL_DATABASE" TO "ITIS";
--GRANT "STORET1_ADMIN" TO "ITIS";
GRANT "STORET1_USER" TO "ITIS" WITH ADMIN OPTION;
--GRANT "STORET_ADMIN" TO "ITIS";
GRANT "STORET_USER" TO "ITIS";


--grant dba to "informix";
--grant dba to "ITIS";
--grant dba to "public";
--grant dba to "root";
--grant connect to "http";
GRANT CONNECT TO ITIS;

CONNECT ITIS/ITIS@ZION

/** TABLE "ITIS".change_comments row size = 263 number of columns = 4 index size = 
              22 **/
/** unload file name = chang00100.unl number of rows = 0 **/

create table "ITIS".change_comments 
  (
    change_track_id integer not null ,
    chg_cmt_id NUMBER not null ,
    change_detail varchar(251) not null ,
    update_date   varchar(10)   not null 
  )
    PCTFREE 5 PCTUSED 90
    tablespace STORET1_TABS
    STORAGE ( INITIAL 25M NEXT 25M )
   ;
--revoke all on "ITIS".change_comments from "public";
 HOST  sqlldr ITIS/ITIS@ZION chang00100.ctl

/** TABLE "ITIS".change_operations row size = 12 number of columns = 3 index size = 
              31 **/
/** unload file name = chang00101.unl number of rows = 0 **/

create table "ITIS".change_operations 
  (
    change_track_id integer not null ,
    chg_op_id integer not null ,
    update_date   varchar(10)   not null 
  )
    PCTFREE 5 PCTUSED 90
    tablespace STORET1_TABS
    STORAGE ( INITIAL 25M NEXT 25M )
   ;
--revoke all on "ITIS".change_operations from "public";
 HOST  sqlldr ITIS/ITIS@ZION chang00101.ctl

/** TABLE "ITIS".chg_operation_lkp row size = 34 number of columns = 3 index size = 
              9 **/
/** unload file name = chg_o00102.unl number of rows = 0 **/

create table "ITIS".chg_operation_lkp 
  (
    chg_op_id NUMBER not null ,
    change_operation varchar(30) not null ,
    update_date   varchar(10)   not null 
  )
    PCTFREE 5 PCTUSED 90
    tablespace STORET1_TABS
    STORAGE ( INITIAL 25M NEXT 25M )
   ;
--revoke all on "ITIS".chg_operation_lkp from "public";
 HOST  sqlldr ITIS/ITIS@ZION chg_o00102.ctl

/** TABLE "ITIS".change_tracks row size = 367 number of columns = 9 index size = 9 
              **/
/** unload file name = chang00103.unl number of rows = 0 **/

create table "ITIS".change_tracks 
  (
    change_track_id NUMBER not null ,
    old_tsn integer,
    change_reason varchar(58) not null ,
    change_initiator varchar(101) not null ,
    change_reviewer varchar(101) not null ,
    change_certifier varchar(101) not null ,
    change_time_stamp   varchar(10)   not null ,
    tsn integer not null ,
    update_date   varchar(10)   not null 
  )
    PCTFREE 5 PCTUSED 90
    tablespace STORET1_TABS
    STORAGE ( INITIAL 25M NEXT 25M )
   ;
--revoke all on "ITIS".change_tracks from "public";
 HOST  sqlldr ITIS/ITIS@ZION chang00103.ctl

/** TABLE "ITIS".kingdoms row size = 18 number of columns = 3 index size = 9 **/
/** unload file name = kingd00104.unl number of rows = 5 **/

create table "ITIS".kingdoms 
  (
    kingdom_id NUMBER not null ,
    kingdom_name char(10) not null ,
    update_date   varchar(10)   not null 
  )
    PCTFREE 5 PCTUSED 90
    tablespace STORET1_TABS
    STORAGE ( INITIAL 25M NEXT 25M )
   ;
--revoke all on "ITIS".kingdoms from "public";
 HOST  sqlldr ITIS/ITIS@ZION kingd00104.ctl

/** TABLE "ITIS".taxon_unit_types row size = 29 number of columns = 6 index size = 
              11 **/
/** unload file name = taxon00105.unl number of rows = 87 **/

create table "ITIS".taxon_unit_types 
  (
    kingdom_id integer not null ,
    rank_id integer not null ,
    rank_name char(15) not null ,
    dir_parent_rank_id integer not null ,
    req_parent_rank_id integer not null ,
    update_date   varchar(10)   not null 
  )
    PCTFREE 5 PCTUSED 90
    tablespace STORET1_TABS
    STORAGE ( INITIAL 25M NEXT 25M )
   ;
--revoke all on "ITIS".taxon_unit_types from "public";
 HOST  sqlldr ITIS/ITIS@ZION taxon00105.ctl

/** TABLE "ITIS".geographic_div row size = 54 number of columns = 3 index size = 64 
              **/
/** unload file name = geogr00106.unl number of rows = 85049 **/

create table "ITIS".geographic_div 
  (
    tsn integer not null ,
    geographic_value varchar(51) not null ,
    update_date   varchar(10)   not null 
  )
    PCTFREE 5 PCTUSED 90
    tablespace STORET1_TABS
    STORAGE ( INITIAL 25M NEXT 25M )
   ;
--revoke all on "ITIS".geographic_div from "public";
 HOST  sqlldr ITIS/ITIS@ZION geogr00106.ctl

/** TABLE "ITIS".jurisdiction row size = 59 number of columns = 4 index size = 49 **/
/** unload file name = juris00107.unl number of rows = 43966 **/

create table "ITIS".jurisdiction 
  (
    tsn integer not null ,
    jurisdiction_value varchar(36) not null ,
    origin varchar(25) not null ,
    update_date   varchar(10)   not null 
  )
    PCTFREE 5 PCTUSED 90
    tablespace STORET1_TABS
    STORAGE ( INITIAL 25M NEXT 25M )
   ;
--revoke all on "ITIS".jurisdiction from "public";
 HOST  sqlldr ITIS/ITIS@ZION juris00107.ctl

/** TABLE "ITIS".nodc_ids row size = 20 number of columns = 3 index size = 30 **/
/** unload file name = nodc_00108.unl number of rows = 209565 **/

create table "ITIS".nodc_ids 
  (
    nodc_id char(12) not null ,
    update_date   varchar(10)   not null ,
    tsn integer not null ,
    CONSTRAINT  u833_145 PRIMARY KEY (
         nodc_id,
         tsn )
    USING INDEX  tablespace STORET1_INDS
    STORAGE ( INITIAL 2560000 NEXT 2560000 )  )
    PCTFREE 5 PCTUSED 90
    tablespace STORET1_TABS
    STORAGE ( INITIAL 5M NEXT 5M )
   ;
--revoke all on "ITIS".nodc_ids from "public";
 HOST  sqlldr ITIS/ITIS@ZION nodc_00108.ctl

/** TABLE "ITIS".taxon_authors_lkp row size = 111 number of columns = 4 index size 
              = 11 **/
/** unload file name = taxon00109.unl number of rows = 80709 **/

create table "ITIS".taxon_authors_lkp 
  (
    taxon_author_id NUMBER not null ,
    taxon_author varchar(130) not null ,
    update_date   varchar(10)   not null ,
    kingdom_id integer not null 
  )
    PCTFREE 5 PCTUSED 90
    tablespace STORET1_TABS
    STORAGE ( INITIAL 25M NEXT 25M )
   ;
--revoke all on "ITIS".taxon_authors_lkp from "public";
 HOST  sqlldr ITIS/ITIS@ZION taxon00109.ctl

/** TABLE "ITIS".synonym_links row size = 12 number of columns = 3 index size = 22 
              **/
/** unload file name = synon00111.unl number of rows = 77405 **/

create table "ITIS".synonym_links 
  (
    tsn integer not null ,
    tsn_accepted integer not null ,
    update_date   varchar(10)   not null 
  )
    PCTFREE 5 PCTUSED 90
    tablespace STORET1_TABS
    STORAGE ( INITIAL 25M NEXT 25M )
   ;
--revoke all on "ITIS".synonym_links from "public";
 HOST  sqlldr ITIS/ITIS@ZION synon00111.ctl

/** TABLE "ITIS".reference_links row size = 102 number of columns = 8 index size = 
              34 **/
/** unload file name = refer00112.unl number of rows = 655132 **/

create table "ITIS".reference_links 
  (
    tsn integer not null ,
    doc_id_prefix char(3) not null ,
    documentation_id integer not null ,
    original_desc_ind char(1),
    init_itis_desc_ind char(1),
    change_track_id integer,
    vernacular_name varchar(85),
    update_date   varchar(10)   not null 
  )
    PCTFREE 5 PCTUSED 90
    tablespace STORET1_TABS
    STORAGE ( INITIAL 25M NEXT 25M )
   ;
--revoke all on "ITIS".reference_links from "public";
 HOST  sqlldr ITIS/ITIS@ZION refer00112.ctl

/** TABLE "ITIS".experts row size = 368 number of columns = 5 index size = 12 **/
/** unload file name = exper00113.unl number of rows = 77 **/

create table "ITIS".experts 
  (
    expert_id_prefix char(3) not null ,
    expert_id NUMBER not null ,
    expert varchar(101) not null ,
    exp_comment varchar(255),
    update_date   varchar(10)   not null 
  )
    PCTFREE 5 PCTUSED 90
    tablespace STORET1_TABS
    STORAGE ( INITIAL 25M NEXT 25M )
   ;
--revoke all on "ITIS".experts from "public";
 HOST  sqlldr ITIS/ITIS@ZION exper00113.ctl

/** TABLE "ITIS".other_sources row size = 356 number of columns = 8 index size = 12 
              **/
/** unload file name = other00114.unl number of rows = 144 **/

create table "ITIS".other_sources 
  (
    source_id_prefix char(3) not null ,
    source_id NUMBER not null ,
    source_type char(10) not null ,
    source varchar(68) not null ,
    version char(10) not null ,
    acquisition_date   varchar(10)   not null ,
    source_comment varchar(255),
    update_date   varchar(10)   not null 
  )
    PCTFREE 5 PCTUSED 90
    tablespace STORET1_TABS
    STORAGE ( INITIAL 25M NEXT 25M )
   ;
--revoke all on "ITIS".other_sources from "public";
 HOST  sqlldr ITIS/ITIS@ZION other00114.ctl

/** TABLE "ITIS".publications row size = 1060 number of columns = 14 index size = 12 
              **/
/** unload file name = publi00115.unl number of rows = 1556 **/

create table "ITIS".publications 
  (
    pub_id_prefix char(3) not null ,
    publication_id NUMBER not null ,
    reference_author varchar(101) not null ,
    title varchar(265),
    publication_name varchar(256) not null ,
    listed_pub_date    varchar(10)   ,
    actual_pub_date   varchar(10)   not null ,
    publisher varchar(90),
    pub_place varchar(50),
    isbn varchar(16),
    issn varchar(16),
    pages varchar(15),
    pub_comment varchar(255),
    update_date   varchar(10)   not null 
  )
    PCTFREE 5 PCTUSED 90
    tablespace STORET1_TABS
    STORAGE ( INITIAL 25M NEXT 25M )
   ;
--revoke all on "ITIS".publications from "public";
 HOST  sqlldr ITIS/ITIS@ZION publi00115.ctl

/** TABLE "ITIS".reviews row size = 399 number of columns = 7 index size = 22 **/
/** unload file name = revie00116.unl number of rows = 0 **/

create table "ITIS".reviews 
  (
    tsn integer not null ,
    review_start_date   varchar(10)   not null ,
    review_end_date    varchar(10)   ,
    review_reason varchar(33) not null ,
    reviewer varchar(125) not null ,
    review_comment varchar(255),
    update_date   varchar(10)   not null 
  )
    PCTFREE 5 PCTUSED 90
    tablespace STORET1_TABS
    STORAGE ( INITIAL 25M NEXT 25M )
   ;
--revoke all on "ITIS".reviews from "public";
 HOST  sqlldr ITIS/ITIS@ZION revie00116.ctl

/** TABLE "ITIS".tu_comments_links row size = 12 number of columns = 3 index size = 
              31 **/
/** unload file name = tu_co00117.unl number of rows = 51973 **/

create table "ITIS".tu_comments_links 
  (
    tsn integer not null ,
    comment_id integer not null ,
    update_date   varchar(10)   not null 
  )
    PCTFREE 5 PCTUSED 90
    tablespace STORET1_TABS
    STORAGE ( INITIAL 25M NEXT 25M )
   ;
--revoke all on "ITIS".tu_comments_links from "public";
 HOST  sqlldr ITIS/ITIS@ZION tu_co00117.ctl

/** TABLE "ITIS".comments row size = 2117 number of columns = 5 index size = 9 **/
/** unload file name = comme00118.unl number of rows = 11241 **/

create table "ITIS".comments 
  (
    comment_id NUMBER not null ,
    commentator varchar(100),
    comment_detail char(4000) not null ,
    comment_time_stamp   varchar(19)   not null ,
    update_date   varchar(10)   not null 
  )
    PCTFREE 5 PCTUSED 90
    tablespace STORET1_TABS
    STORAGE ( INITIAL 25M NEXT 25M )
   ;
--revoke all on "ITIS".comments from "public";
 HOST  sqlldr ITIS/ITIS@ZION comme00118.ctl

/** TABLE "ITIS".taxonomic_units row size = 321 number of columns = 24 index size = 76 **/
/** unload file name = taxon00119.unl number of rows = 340708 **/

create table "ITIS".taxonomic_units 
  (
    tsn NUMBER not null ,
    unit_ind1 char(1),
    unit_name1 char(35) not null, --constraint "ITIS".nsue_53,
    unit_ind2 char(1),
    unit_name2 varchar(35),
    unit_ind3 varchar(7),
    unit_name3 varchar(35),
    unit_ind4 varchar(7),
    unit_name4 varchar(35),
    unnamed_taxon_ind char(1),
    usage varchar(17) not null, --constraint "ITIS".nsue_54,
    unaccept_reason varchar(59),
    credibility_rtng varchar(57) not null, --constraint "ITIS".nsue_55,
    completeness_rtng char(10),
    currency_rating char(7),
    phylo_sort_seq smallint,
    initial_time_stamp   varchar(19)   not null, --constraint "ITIS".nsue_56,
    parent_tsn integer,
    taxon_author_id integer,
    hybrid_author_id integer,
    kingdom_id integer not null, --constraint "ITIS".nsue_57,
    rank_id integer not null, --constraint "ITIS".nsue_58,
    update_date   varchar(10)   not null, --constraint "ITIS".nsue_59,
    uncertain_prnt_ind char(3)
  )
    PCTFREE 5 PCTUSED 90
    tablespace STORET1_TABS
    STORAGE ( INITIAL 25M NEXT 25M )
   ;
--revoke all on "ITIS".taxonomic_units from "public";
 HOST  sqlldr ITIS/ITIS@ZION taxon00119.ctl

/** TABLE "ITIS".vernaculars row size = 110 number of columns = 6 index size = 115 
              **/
/** unload file name = verna00579.unl number of rows = 86962 **/

create table "ITIS".vernaculars 
  (
    tsn integer not null ,
    vernacular_name varchar(85) not null ,
    language varchar(15) not null ,
    approved_ind char(1),
    update_date   varchar(10)   not null ,
    vern_id NUMBER not null ,
--    unique (tsn,vernacular_name,language)  constraint "ITIS".u_vern,
    CONSTRAINT  vern_key PRIMARY KEY (
         vern_id )
    USING INDEX  tablespace STORET1_INDS
    STORAGE ( INITIAL 2560000 NEXT 2560000 )  )
    PCTFREE 5 PCTUSED 90
    tablespace STORET1_TABS
    STORAGE ( INITIAL 5M NEXT 5M )
   ;
--revoke all on "ITIS".vernaculars from "public";
 HOST  sqlldr ITIS/ITIS@ZION verna00579.ctl

/** TABLE "ITIS".vern_ref_links row size = 19 number of columns = 5 index size = 20 
              **/
/** unload file name = vern_00580.unl number of rows = 16990 **/

--create table "ITIS".vern_ref_links 
--  (
--    tsn integer not null ,
--    doc_id_prefix char(3) not null ,
--    documentation_id varchar2(10) not null ,
--    update_date   varchar(10)   not null ,
--    vern_id varchar2(10) not null ,
----    primary key (tsn,doc_id_prefix,documentation_id,vern_id)  constraint "ITIS".vern_rf_lnks_key
--    CONSTRAINT  vern_rf_lnks_key PRIMARY KEY (
--         tsn,
--         doc_id_prefix,
--         documentation_id,
--         vern_id )
--    USING INDEX  tablespace STORET1_INDS
--    STORAGE ( INITIAL 2560000 NEXT 2560000 )  )
--    PCTFREE 5 PCTUSED 90
--    tablespace STORET1_TABS
--    STORAGE ( INITIAL 5M NEXT 5M )
--   ;
create table "ITIS".vern_ref_links 
  (
    tsn integer not null ,
    doc_id_prefix char(3) ,
    documentation_id varchar2(10) not null ,
    update_date   varchar(10)   not null ,
    vern_id varchar2(10) not null 
  )
    PCTFREE 5 PCTUSED 90
    tablespace STORET1_TABS
    STORAGE ( INITIAL 5M NEXT 5M )
   ;
--revoke all on "ITIS".vern_ref_links from "public";
 HOST  sqlldr ITIS/ITIS@ZION vern_00580.ctl

alter table "ITIS".synonym_links add constraint u843_165  
primary key (tsn,
         doc_id_prefix,
         documentation_id,
         vern_id ) 
         using index tablespace STORET1_INDS
         storage ( INITIAL 2560000 NEXT 2560000 );

grant select on "ITIS".change_comments to "ITIS_USER";
grant update on "ITIS".change_comments to "ITIS_USER";
grant insert on "ITIS".change_comments to "ITIS_USER";
grant delete on "ITIS".change_comments to "ITIS_USER";
--grant index on "ITIS".change_comments to "ITIS_USER";
grant select on "ITIS".change_operations to "ITIS_USER";
grant update on "ITIS".change_operations to "ITIS_USER";
grant insert on "ITIS".change_operations to "ITIS_USER";
grant delete on "ITIS".change_operations to "ITIS_USER";
--grant index on "ITIS".change_operations to "ITIS_USER";
grant select on "ITIS".chg_operation_lkp to "ITIS_USER";
grant update on "ITIS".chg_operation_lkp to "ITIS_USER";
grant insert on "ITIS".chg_operation_lkp to "ITIS_USER";
grant delete on "ITIS".chg_operation_lkp to "ITIS_USER";
--grant index on "ITIS".chg_operation_lkp to "ITIS_USER";
grant select on "ITIS".change_tracks to "ITIS_USER";
grant update on "ITIS".change_tracks to "ITIS_USER";
grant insert on "ITIS".change_tracks to "ITIS_USER";
grant delete on "ITIS".change_tracks to "ITIS_USER";
--grant index on "ITIS".change_tracks to "ITIS_USER";
grant select on "ITIS".kingdoms to "ITIS_USER";
grant update on "ITIS".kingdoms to "ITIS_USER";
grant insert on "ITIS".kingdoms to "ITIS_USER";
grant delete on "ITIS".kingdoms to "ITIS_USER";
--grant index on "ITIS".kingdoms to "ITIS_USER";
grant select on "ITIS".taxon_unit_types to "ITIS_USER";
grant update on "ITIS".taxon_unit_types to "ITIS_USER";
grant insert on "ITIS".taxon_unit_types to "ITIS_USER";
grant delete on "ITIS".taxon_unit_types to "ITIS_USER";
--grant index on "ITIS".taxon_unit_types to "ITIS_USER";
grant select on "ITIS".geographic_div to "ITIS_USER";
grant update on "ITIS".geographic_div to "ITIS_USER";
grant insert on "ITIS".geographic_div to "ITIS_USER";
grant delete on "ITIS".geographic_div to "ITIS_USER";
--grant index on "ITIS".geographic_div to "ITIS_USER";
grant select on "ITIS".jurisdiction to "ITIS_USER";
grant update on "ITIS".jurisdiction to "ITIS_USER";
grant insert on "ITIS".jurisdiction to "ITIS_USER";
grant delete on "ITIS".jurisdiction to "ITIS_USER";
--grant index on "ITIS".jurisdiction to "ITIS_USER";
grant select on "ITIS".nodc_ids to "ITIS_USER";
grant update on "ITIS".nodc_ids to "ITIS_USER";
grant insert on "ITIS".nodc_ids to "ITIS_USER";
grant delete on "ITIS".nodc_ids to "ITIS_USER";
--grant index on "ITIS".nodc_ids to "ITIS_USER";
grant select on "ITIS".taxon_authors_lkp to "ITIS_USER";
grant update on "ITIS".taxon_authors_lkp to "ITIS_USER";
grant insert on "ITIS".taxon_authors_lkp to "ITIS_USER";
grant delete on "ITIS".taxon_authors_lkp to "ITIS_USER";
--grant index on "ITIS".taxon_authors_lkp to "ITIS_USER";
grant select on "ITIS".synonym_links to "ITIS_USER";
grant update on "ITIS".synonym_links to "ITIS_USER";
grant insert on "ITIS".synonym_links to "ITIS_USER";
grant delete on "ITIS".synonym_links to "ITIS_USER";
--grant index on "ITIS".synonym_links to "ITIS_USER";
grant select on "ITIS".reference_links to "ITIS_USER";
grant update on "ITIS".reference_links to "ITIS_USER";
grant insert on "ITIS".reference_links to "ITIS_USER";
grant delete on "ITIS".reference_links to "ITIS_USER";
--grant index on "ITIS".reference_links to "ITIS_USER";
grant select on "ITIS".experts to "ITIS_USER";
grant update on "ITIS".experts to "ITIS_USER";
grant insert on "ITIS".experts to "ITIS_USER";
grant delete on "ITIS".experts to "ITIS_USER";
--grant index on "ITIS".experts to "ITIS_USER";
grant select on "ITIS".other_sources to "ITIS_USER";
grant update on "ITIS".other_sources to "ITIS_USER";
grant insert on "ITIS".other_sources to "ITIS_USER";
grant delete on "ITIS".other_sources to "ITIS_USER";
--grant index on "ITIS".other_sources to "ITIS_USER";
grant select on "ITIS".publications to "ITIS_USER";
grant update on "ITIS".publications to "ITIS_USER";
grant insert on "ITIS".publications to "ITIS_USER";
grant delete on "ITIS".publications to "ITIS_USER";
--grant index on "ITIS".publications to "ITIS_USER";
grant select on "ITIS".reviews to "ITIS_USER";
grant update on "ITIS".reviews to "ITIS_USER";
grant insert on "ITIS".reviews to "ITIS_USER";
grant delete on "ITIS".reviews to "ITIS_USER";
--grant index on "ITIS".reviews to "ITIS_USER";
grant select on "ITIS".tu_comments_links to "ITIS_USER";
grant update on "ITIS".tu_comments_links to "ITIS_USER";
grant insert on "ITIS".tu_comments_links to "ITIS_USER";
grant delete on "ITIS".tu_comments_links to "ITIS_USER";
--grant index on "ITIS".tu_comments_links to "ITIS_USER";
grant select on "ITIS".comments to "ITIS_USER";
grant update on "ITIS".comments to "ITIS_USER";
grant insert on "ITIS".comments to "ITIS_USER";
grant delete on "ITIS".comments to "ITIS_USER";
--grant index on "ITIS".comments to "ITIS_USER";
grant select on "ITIS".taxonomic_units to "ITIS_USER";
grant update on "ITIS".taxonomic_units to "ITIS_USER";
grant insert on "ITIS".taxonomic_units to "ITIS_USER";
grant delete on "ITIS".taxonomic_units to "ITIS_USER";
--grant index on "ITIS".taxonomic_units to "ITIS_USER";
grant select on "ITIS".vernaculars to "ITIS_USER";
grant update on "ITIS".vernaculars to "ITIS_USER";
grant insert on "ITIS".vernaculars to "ITIS_USER";
grant delete on "ITIS".vernaculars to "ITIS_USER";
--grant index on "ITIS".vernaculars to "ITIS_USER";
grant select on "ITIS".vern_ref_links to "ITIS_USER";
grant update on "ITIS".vern_ref_links to "ITIS_USER";
grant insert on "ITIS".vern_ref_links to "ITIS_USER";
grant delete on "ITIS".vern_ref_links to "ITIS_USER";
--grant index on "ITIS".vern_ref_links to "ITIS_USER";





----revoke usage on language SPL from public ;

--grant usage on language SPL to public ;

--alter table fa_station
--add constraint fk_sd_org foreign key (fk_org) references di_org(pk_isn);
--alter table di_org
--add constraint pk_org primary key (pk_isn)
--using index
--tablespace STORET1_INDS
--storage (initial 300k next 300k);
--create unique index db_cat02 on DI_DB_cat(hydrologic_unit_code)
--tablespace STORET1_INDS
--storage(initial 100K next 100K)
--nologging;



create unique index "ITIS".change_comments_ky on "ITIS".change_comments 
    (change_track_id,chg_cmt_id) tablespace STORET1_INDS ;
alter table "ITIS".change_comments add constraint u111_1  primary key 
    (change_track_id,chg_cmt_id) using index tablespace STORET1_INDS;
create unique index "ITIS".chg_operations_key on "ITIS".change_operations 
    (change_track_id,chg_op_id) tablespace STORET1_INDS ;
alter table "ITIS".change_operations add constraint u112_2  primary key 
    (change_track_id,chg_op_id) using index tablespace STORET1_INDS;
create unique index "ITIS".chg_oper_lkp_ky on "ITIS".chg_operation_lkp 
    (chg_op_id) tablespace STORET1_INDS ;
alter table "ITIS".chg_operation_lkp add constraint u113_3  primary key 
    (chg_op_id) using index tablespace STORET1_INDS;
create unique index "ITIS".change_tracks_key on "ITIS".change_tracks 
    (change_track_id) tablespace STORET1_INDS ;
alter table "ITIS".change_tracks add constraint u114_4  primary key (change_track_id) 
    using index tablespace STORET1_INDS;
create unique index "ITIS".kingdoms_key on "ITIS".kingdoms (kingdom_id) 
    tablespace STORET1_INDS ;
alter table "ITIS".kingdoms add constraint u119_9  primary key (kingdom_id) 
    using index tablespace STORET1_INDS;
create unique index "ITIS".taxon_unit_type_ky on "ITIS".taxon_unit_types 
    (kingdom_id,rank_id) tablespace STORET1_INDS ;
alter table "ITIS".taxon_unit_types add constraint u571_81  primary key 
    (kingdom_id,rank_id) using index tablespace STORET1_INDS;
create unique index "ITIS".geographic_div_key on "ITIS".geographic_div 
    (tsn,geographic_value) tablespace STORET1_INDS ;
alter table "ITIS".geographic_div add constraint u831_143  primary key 
    (tsn,geographic_value) using index tablespace STORET1_INDS;
create unique index "ITIS".jurisdiction_key on "ITIS".jurisdiction 
    (tsn,jurisdiction_value) tablespace STORET1_INDS ;
alter table "ITIS".jurisdiction add constraint u832_144  primary key (tsn,
    jurisdiction_value) using index tablespace STORET1_INDS;
create index "ITIS".nodc_ids1 on "ITIS".nodc_ids (tsn)  
    tablespace STORET1_INDS ;
create unique index "ITIS".txn_authors_lkp_ky on "ITIS".taxon_authors_lkp 
    (taxon_author_id,kingdom_id) tablespace STORET1_INDS ;
alter table "ITIS".taxon_authors_lkp add constraint u839_151  primary key 
    (taxon_author_id,kingdom_id) using index tablespace STORET1_INDS;
    
create index "ITIS".synonym_links1 on "ITIS".synonym_links (tsn_accepted) 
    tablespace STORET1_INDS ;
create unique index "ITIS".synonym_links_key on "ITIS".synonym_links 
    (tsn,tsn_accepted) tablespace STORET1_INDS ;
alter table "ITIS".synonym_links add constraint u843_165  primary key (tsn,
    tsn_accepted) using index tablespace STORET1_INDS;
create unique index "ITIS".reference_links_ky on "ITIS".reference_links 
    (tsn,doc_id_prefix,documentation_id) tablespace STORET1_INDS ;
alter table "ITIS".reference_links add constraint u844_167  primary key 
    (tsn,doc_id_prefix,documentation_id)  using index tablespace STORET1_INDS;
create unique index "ITIS".experts_key on "ITIS".experts (expert_id_prefix,
    expert_id) tablespace STORET1_INDS ;
alter table "ITIS".experts add constraint u859_192  primary key (expert_id_prefix,
    expert_id) using index tablespace STORET1_INDS;
create unique index "ITIS".other_sources_key on "ITIS".other_sources 
    (source_id_prefix,source_id) tablespace STORET1_INDS ;
alter table "ITIS".other_sources add constraint u860_193  primary key (source_id_prefix,
    source_id) using index tablespace STORET1_INDS;
create unique index "ITIS".publications_key on "ITIS".publications 
    (pub_id_prefix,publication_id) tablespace STORET1_INDS ;
alter table "ITIS".publications add constraint u861_194  primary key (pub_id_prefix,
    publication_id) using index tablespace STORET1_INDS;
create unique index "ITIS".reviews_key on "ITIS".reviews (tsn,
    review_start_date) tablespace STORET1_INDS ;
alter table "ITIS".reviews add constraint u862_195  primary key (tsn,review_start_date) 
    using index tablespace STORET1_INDS;
create unique index "ITIS".tu_comments_lnk_ky on "ITIS".tu_comments_links 
    (tsn,comment_id) tablespace STORET1_INDS ;
alter table "ITIS".tu_comments_links add constraint u864_198  primary key 
    (tsn,comment_id) using index tablespace STORET1_INDS;
create unique index "ITIS".comments_key on "ITIS".comments (comment_id) 
    tablespace STORET1_INDS ;
alter table "ITIS".comments add constraint u863_197  primary key (comment_id) 
    using index tablespace STORET1_INDS;
create index "ITIS".taxon_unit1 on "ITIS".taxonomic_units (parent_tsn) 
    tablespace STORET1_INDS ;
create index "ITIS".taxon_unit2 on "ITIS".taxonomic_units (unit_name1) 
    tablespace STORET1_INDS ;
create index "ITIS".taxon_unit3 on "ITIS".taxonomic_units (kingdom_id,
    rank_id) tablespace STORET1_INDS ;
create index "ITIS".taxon_unit4 on "ITIS".taxonomic_units (taxon_author_id) 
    tablespace STORET1_INDS ;
create unique index "ITIS".taxonomic_units_ky on "ITIS".taxonomic_units 
    (tsn) tablespace STORET1_INDS ;
alter table "ITIS".taxonomic_units add constraint tu_const1  primary key 
    (tsn) using index tablespace STORET1_INDS;

--alter table fa_station
--add constraint fk_sd_org foreign key (fk_org) references di_org(pk_isn);

alter table "ITIS".change_comments add constraint further_described foreign key 
    (change_track_id) references "ITIS".change_tracks(change_track_id)  ;

alter table "ITIS".change_operations add constraint detail foreign 
    key (chg_op_id) references "ITIS".chg_operation_lkp(chg_op_id)  ;

alter table "ITIS".change_operations add constraint are_typed_by foreign 
    key (change_track_id) references "ITIS".change_tracks(change_track_id)  ;

alter table "ITIS".geographic_div add constraint are_located_within foreign key 
    (tsn) references "ITIS".taxonomic_units(tsn)  ;
    
alter table "ITIS".jurisdiction add constraint id_us_interest_via foreign key (tsn) 
    references "ITIS".taxonomic_units(tsn)  ;
    
alter table "ITIS".synonym_links add constraint supercede foreign key 
    (tsn_accepted) references "ITIS".taxonomic_units(tsn_accepted)  ;

alter table "ITIS".reference_links add constraint are_documented_by1 foreign key 
    (change_track_id) references "ITIS".change_tracks(change_track_id)  ;

alter table "ITIS".reference_links add constraint are_documented_by3 foreign key 
    (tsn) references "ITIS".taxonomic_units(tsn)  ;
    
alter table "ITIS".reviews add constraint are_validated_by foreign key (tsn) 
    references "ITIS".taxonomic_units(tsn)  ;
    
alter table "ITIS".tu_comments_links add constraint are_assoc_with_tus foreign 
    key (comment_id) references "ITIS".comments(comment_id)  ;

alter table "ITIS".tu_comments_links add constraint are_assoc_with_cmt foreign 
    key (tsn) references "ITIS".taxonomic_units(tsn)  ;


--ANALYZE TABLE storetw.DI_ACTIVITY_MEDIUM COMPUTE STATISTICS ;
--ANALYZE TABLE storetw.DI_ACTIVITY_MEDIUM COMPUTE STATISTICS for all indexes;


analyze table comments 
--(     comment_detail, comment_id, comment_time_stamp, commentator, update_date) 
     COMPUTE STATISTICS ;
analyze table experts 
--(     exp_comment, expert, expert_id, expert_id_prefix, update_date) 
     COMPUTE STATISTICS ;
analyze table geographic_div 
--(     geographic_value, tsn, update_date) 
     COMPUTE STATISTICS ;
analyze table jurisdiction 
--(     jurisdiction_value, origin, tsn, update_date) 
     COMPUTE STATISTICS ;
analyze table kingdoms 
--(     kingdom_id, kingdom_name, update_date) 
     COMPUTE STATISTICS ;
analyze table nodc_ids 
--(     nodc_id, tsn, update_date) 
     COMPUTE STATISTICS ;
analyze table other_sources 
--(     acquisition_date, source, source_comment, source_id, source_id_prefix,      source_type, update_date, version) 
     COMPUTE STATISTICS ;
analyze table publications 
--(     actual_pub_date, isbn, issn, listed_pub_date, pages,      pub_comment, pub_id_prefix, pub_place, publication_id, publication_name,      publisher, reference_author, title, update_date) 
     COMPUTE STATISTICS ;
analyze table reference_links 
--(     change_track_id, doc_id_prefix, documentation_id, init_itis_desc_ind, original_desc_ind,      tsn, update_date, vernacular_name) 
     COMPUTE STATISTICS ;
analyze table synonym_links 
--(     tsn, tsn_accepted, update_date) 
     COMPUTE STATISTICS ;
analyze table taxon_authors_lkp 
--(     kingdom_id, taxon_author, taxon_author_id, update_date) 
     COMPUTE STATISTICS ;
analyze table taxon_unit_types 
--(     dir_parent_rank_id, kingdom_id, rank_id, rank_name, req_parent_rank_id,      update_date) 
     COMPUTE STATISTICS ;
analyze table taxonomic_units 
--(     completeness_rtng, credibility_rtng, currency_rating, hybrid_author_id, initial_time_stamp,      kingdom_id, parent_tsn, phylo_sort_seq, rank_id, taxon_author_id,      tsn, unaccept_reason, uncertain_prnt_ind, unit_ind1, unit_ind2,      unit_ind3, unit_ind4, unit_name1, unit_name2, unit_name3,      unit_name4, unnamed_taxon_ind, update_date, usage) 
     COMPUTE STATISTICS ;
analyze table tu_comments_links 
--(     comment_id, tsn, update_date) 
     COMPUTE STATISTICS ;
analyze table vern_ref_links 
--(     doc_id_prefix, documentation_id, tsn, update_date, vern_id) 
     COMPUTE STATISTICS ;
analyze table vernaculars 
--(     approved_ind, language, tsn, update_date, vern_id,      vernacular_name) 
     COMPUTE STATISTICS ;

analyze table comments 
--(     comment_detail, comment_id, comment_time_stamp, commentator, update_date) 
     COMPUTE STATISTICS for all indexes;
analyze table experts 
--(     exp_comment, expert, expert_id, expert_id_prefix, update_date) 
     COMPUTE STATISTICS for all indexes;
analyze table geographic_div 
--(     geographic_value, tsn, update_date) 
     COMPUTE STATISTICS for all indexes;
analyze table jurisdiction 
--(     jurisdiction_value, origin, tsn, update_date) 
     COMPUTE STATISTICS for all indexes;
analyze table kingdoms 
--(     kingdom_id, kingdom_name, update_date) 
     COMPUTE STATISTICS for all indexes;
analyze table nodc_ids 
--(     nodc_id, tsn, update_date) 
     COMPUTE STATISTICS for all indexes;
analyze table other_sources 
--(     acquisition_date, source, source_comment, source_id, source_id_prefix,      source_type, update_date, version) 
     COMPUTE STATISTICS for all indexes;
analyze table publications 
--(     actual_pub_date, isbn, issn, listed_pub_date, pages,      pub_comment, pub_id_prefix, pub_place, publication_id, publication_name,      publisher, reference_author, title, update_date) 
     COMPUTE STATISTICS for all indexes;
analyze table reference_links 
--(     change_track_id, doc_id_prefix, documentation_id, init_itis_desc_ind, original_desc_ind,      tsn, update_date, vernacular_name) 
     COMPUTE STATISTICS for all indexes;
analyze table synonym_links 
--(     tsn, tsn_accepted, update_date) 
     COMPUTE STATISTICS for all indexes;
analyze table taxon_authors_lkp 
--(     kingdom_id, taxon_author, taxon_author_id, update_date) 
     COMPUTE STATISTICS for all indexes;
analyze table taxon_unit_types 
--(     dir_parent_rank_id, kingdom_id, rank_id, rank_name, req_parent_rank_id,      update_date) 
     COMPUTE STATISTICS for all indexes;
analyze table taxonomic_units 
--(     completeness_rtng, credibility_rtng, currency_rating, hybrid_author_id, initial_time_stamp,      kingdom_id, parent_tsn, phylo_sort_seq, rank_id, taxon_author_id,      tsn, unaccept_reason, uncertain_prnt_ind, unit_ind1, unit_ind2,      unit_ind3, unit_ind4, unit_name1, unit_name2, unit_name3,      unit_name4, unnamed_taxon_ind, update_date, usage) 
     COMPUTE STATISTICS for all indexes;
analyze table tu_comments_links 
--(     comment_id, tsn, update_date) 
     COMPUTE STATISTICS for all indexes;
analyze table vern_ref_links 
--(     doc_id_prefix, documentation_id, tsn, update_date, vern_id) 
     COMPUTE STATISTICS for all indexes;
analyze table vernaculars 
--(     approved_ind, language, tsn, update_date, vern_id,      vernacular_name) 
     COMPUTE STATISTICS for all indexes;

-- *****************************************************************************************************
-- *****************************************************************************************************
-- *****************************************************************************************************
 
SPOOL OFF
set echo off