create or replace PACKAGE BODY ckodsz as
PROCEDURE clear_tmp_tables is  
/* Delete rows from ck_ckdbsz_.. tmp tables */
begin
   delete from CK_CKDBSZ_ODB_TMP;
   delete from CK_CKDBSZ_OTB_TMP;
   delete from CK_CKDBSZ_DBU_TMP;
   delete from CK_CKDBSZ_TBI_TMP;
   delete from CK_CKDBSZ_KEY_TMP;
   commit;
end;


PROCEDURE setup_odb_tmp ( folderirid in number
			, SESSIONID in NUMBER
		        , RRI_DBNAME in VARCHAR2
			, RRI_CRSTATUS in VARCHAR2
			, RRI_DB_BLOCK_SIZE in NUMBER ) IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Session ID..... ' || SESSIONID);
DBMS_OUTPUT.PUT_LINE('Create Status.. ' || RRI_CRSTATUS);
DBMS_OUTPUT.PUT_LINE('Db Block Size.. ' || RRI_DB_BLOCK_SIZE);
/* get oracle databases with file usages */
insert into ck_ckdbsz_odb_tmp
(REP_SEQ
,odb_id
,odb_name
,odb_block_size
,odb_cr_status
,app_id)
SELECT  
SESSIONID
,D.ID
,D.NAME 
	,max(NVL(decode(DF.FILE_SIZE_UNIT,
                  'KILOBYTE', RRI_DB_BLOCK_SIZE*1000,
                  'MEGABYTE', RRI_DB_BLOCK_SIZE*1000000), 
                   RRI_DB_BLOCK_SIZE))
,D.COMPLETE_FLAG 
,FOLDERIRID
FROM	CI_ORACLE_DATABASES D
,       CI_DATAFILES DF
,       ci_folder_members AD
WHERE	D.ID = AD.member_object
AND     ad.folder_reference = FOLDERIRID
AND     D.NAME LIKE UPPER(RTRIM(RRI_DBNAME))
AND     DF.ORACLE_DATABASE_REFERENCE = D.ID
AND     ( RRI_CRSTATUS = 'Y'
          or D.COMPLETE_FLAG  = 'Y' )
GROUP BY D.ID, D.NAME, D.COMPLETE_FLAG
union all
/* get oracle databases with no file usages */
SELECT  SESSIONID
,       D.ID
,	D.NAME
,       RRI_DB_BLOCK_SIZE 
,	D.COMPLETE_FLAG
,      FOLDERIRID
FROM	CI_ORACLE_DATABASES D
,       ci_folder_members AD
WHERE	D.ID = AD.member_object
AND     ad.folder_reference = FOLDERIRID
AND     D.NAME LIKE UPPER(RTRIM(RRI_DBNAME))
AND     ( RRI_CRSTATUS = 'Y'
          or D.COMPLETE_FLAG = 'Y' )
AND     NOT EXISTS (select 1 from CI_DATAFILES DF where 
DF.ORACLE_DATABASE_REFERENCE = D.ID)
GROUP BY D.ID, D.NAME, D.COMPLETE_FLAG;  
commit;
DBMS_OUTPUT.PUT_LINE('Rows processed.. ' || to_char(sql%rowcount));
END;



PROCEDURE setup_otb_tmp(folderirid in number,SESSIONID in NUMBER, RRI_CRSTATUS in VARCHAR2, RRI_OTBNAME in VARCHAR2 ) IS
BEGIN
  /* Select all tablespace */
insert into ck_ckdbsz_otb_tmp
(      REP_SEQ
,      OTB_NAME
,      app_id
,      OTB_ID
,      ODB_ID
,      OTB_CR_STATUS)
select ODB.REP_SEQ
,      OTB.NAME
,      odb.app_id 
,      OTB.ID  
,      ODB.ODB_ID
,      OTB.COMPLETE_FLAG
from CI_TABLESPACES OTB,
     CK_CKDBSZ_ODB_TMP ODB
where OTB.DATABASE_REFERENCE = ODB.ODB_ID
and   odb.app_id = folderirid
AND   OTB.NAME LIKE UPPER(RTRIM(RRI_OTBNAME))
AND   ODB.REP_SEQ = SESSIONID
AND  (
      RRI_CRSTATUS = 'Y'
   or OTB.COMPLETE_FLAG = 'Y' )
union all
select ODB.REP_SEQ
,      'UNSPECIFIED'
,      odb.app_id
,      ODB.ODB_ID
,      ODB.ODB_ID
,      'Y'
from ck_ckdbsz_odb_tmp ODB
where ODB.REP_SEQ = SESSIONID
and  odb.app_id = folderirid;
commit;
DBMS_OUTPUT.PUT_LINE('Rows processed.. ' || to_char(sql%rowcount));
END;


PROCEDURE SETUP_DBU_TMP(folderirid in number, SESSIONID in NUMBER, RRI_USERNAME in VARCHAR2, RRI_CRSTATUS in VARCHAR2) IS
BEGIN
/* Select all database users */
insert into ck_ckdbsz_dbu_tmp
(      REP_SEQ
,      DBU_NAME
,      app_id
,      DBU_ID
,      ODB_ID
,      DBU_CR_STATUS)
select ODB.REP_SEQ
,      DBU.NAME 
,      odb.app_id
,      DBU.ID  
,      ODB.ODB_ID
,      DBU.COMPLETE_FLAG
from  CI_DATABASE_USERS DBU,
      CK_CKDBSZ_ODB_TMP ODB
WHERE DBU.NAME LIKE UPPER(RTRIM(RRI_USERNAME))
AND   DBU.DATABASE_REFERENCE = ODB.ODB_ID
and   odb.app_id = folderirid
AND   ODB.REP_SEQ = SESSIONID
AND  (
      RRI_CRSTATUS = 'Y'
   or DBU.COMPLETE_FLAG = 'Y' );
commit;
DBMS_OUTPUT.PUT_LINE('Rows processed.. ' || to_char(sql%rowcount));
END;


PROCEDURE setup_tbi_tmp (folderirid in number, SESSIONID in NUMBER
                       , RRI_CRSTATUS in VARCHAR2
                       , INIT_TRANS in NUMBER
                       , PCT_FREE in NUMBER
                       , RRI_TABNAME in VARCHAR2) IS
BEGIN
/* Select all table implementations that have no tablespace assigned for each database user*/
insert into 
ck_ckdbsz_tbi_tmp
(      REP_SEQ
,      tab_name
,      app_id
,      tbi_id
,      tab_id
,      otb_id
,      dbu_id
,      odb_id
,      TBI_INIT_TRANS
,      TBI_PCT_FREE
,      TAB_START_ROWS
,      TBI_MAX_TRANS
,      TAB_END_ROWS
,      TBI_PCT_USED
,      TBI_CR_STATUS)
select DBU.REP_SEQ
,      ELEM.NAME
,      dbu.app_id
,      DOI.ID   
,      ELEM.ID
,      OTB.OTB_ID 
,      DBU.DBU_ID
,      DBU.ODB_ID
,      nvl(DOI.INITIAL_TRANSACTION,INIT_TRANS)
,      nvl(DOI.PERCENT_FREE,PCT_FREE)
,      nvl(ELEM.INITIAL_NUMBER_OF_ROWS,0)
,      DOI.MAXIMUM_TRANSACTION
,      nvl(ELEM.MAXIMUM_NUMBER_OF_ROWS,0)
,      DOI.PERCENT_USED
,      DOI.COMPLETE_FLAG
FROM CK_CKDBSZ_DBU_TMP DBU
,    ck_ckdbsz_otb_tmp otb
,    CI_TABLE_IMPLEMENTATIONS DOI
,    CI_TABLE_DEFINITIONS ELEM
WHERE   DBU.REP_SEQ = SESSIONID
and     dbu.app_id = folderirid
AND     DOI.DATABASE_USER_REFERENCE = DBU.DBU_ID
AND     DOI.TABLESPACE_REFERENCE IS NULL
AND     DOI.TABLE_DEFINITION_REFERENCE = ELEM.ID
and     otb.otb_name = 'UNSPECIFIED'
AND     OTB.ODB_ID = DBU.ODB_ID
AND     OTB.APP_ID = DBU.APP_ID
AND     OTB.REP_SEQ = SESSIONID
AND     ELEM.NAME LIKE UPPER(RTRIM(RRI_TABNAME))
AND    (
        RRI_CRSTATUS = 'Y'
     or DOI.COMPLETE_FLAG = 'Y' 
        )
union all
/* select all table implementations that have a tablespace defined for each database user */
select DBU.REP_SEQ
,      ELEM.NAME
,      dbu.app_id
,      DOI.ID   
,      ELEM.ID
,      OTB.OTB_ID
,      DBU.DBU_ID
,      DBU.ODB_ID
,      nvl(DOI.INITIAL_TRANSACTION,INIT_TRANS)
,      nvl(DOI.PERCENT_FREE,PCT_FREE)
,      nvl(ELEM.INITIAL_NUMBER_OF_ROWS,0)
,      DOI.MAXIMUM_TRANSACTION
,      nvl(ELEM.MAXIMUM_NUMBER_OF_ROWS,0)
,      DOI.PERCENT_USED
,      DOI.COMPLETE_FLAG
from CK_CKDBSZ_DBU_TMP DBU
,    CK_CKDBSZ_OTB_TMP OTB
,    CI_TABLE_IMPLEMENTATIONS DOI
,    CI_TABLE_DEFINITIONS ELEM
WHERE   DBU.REP_SEQ = SESSIONID
AND     OTB.REP_SEQ = SESSIONID
and     dbu.app_id = folderirid
and     otb.app_id = folderirid
AND     DOI.DATABASE_USER_REFERENCE = DBU.DBU_ID
AND     DOI.TABLESPACE_REFERENCE = OTB.OTB_ID 
AND     DOI.TABLE_DEFINITION_REFERENCE = ELEM.ID
AND     ELEM.NAME LIKE UPPER(RTRIM(RRI_TABNAME))
AND  (
      RRI_CRSTATUS = 'Y'
   or DOI.COMPLETE_FLAG = 'Y' 
     );
commit;
DBMS_OUTPUT.PUT_LINE('Rows processed.. ' || to_char(sql%rowcount));
END;
PROCEDURE setup_key_tmp (folderirid in number, SESSIONID in NUMBER
                       , RRI_CRSTATUS in VARCHAR2
                       , PCT_FREE in NUMBER 
		       , IND_INIT_TRANS in NUMBER) IS
BEGIN
/* select index storages that have tablespaces assigned */
insert into ck_ckdbsz_key_tmp
(      REP_SEQ
,      KEY_NAME
,      app_id
,      OTB_ID
,      ODB_ID
,      KEY_ID
,      KEY_TYPE
,      TAB_ID
,      TAB_NAME
,      KEY_PCT_FREE
,      KEY_INIT_TRANS
,      KEY_START_ROWS
,      KEY_END_ROWS
,      KEY_CR_STATUS)
select SESSIONID
,      ind.name
,      tbi.app_id
,      OTB.otb_id
,      tbi.odb_id
,      ind.id
,      'INDEX'
,      tbi.tab_id
,      tbi.tab_name
,      nvl(indstor.percent_free,PCT_FREE)
,      nvl(indstor.initial_transaction,IND_INIT_TRANS)
,      tbi.tab_start_rows
,      tbi.tab_end_rows
,      ind.complete_flag
from ci_relation_indexes ind
,    ci_user_object_index_storages indstor
,    ck_ckdbsz_tbi_tmp tbi
,    CK_CKDBSZ_OTB_TMP OTB
where 
ind.table_definition_reference = tbi.tab_id 
and   indstor.table_implementation_reference = tbi.tbi_id
and   indstor.tablespace_reference = OTB.otb_id
and   indstor.index_reference = ind.id
and   tbi.rep_seq = SESSIONID 
and   tbi.app_id = folderirid
AND   OTB.REP_SEQ = SESSIONID 
AND   OTB.APP_ID = folderirid
AND   OTB.ODB_ID = TBI.ODB_ID
AND     ( RRI_CRSTATUS = 'Y' or
           IND.complete_flag  = 'Y' )
union all
/* index storages with no tablespace - use default tablespace */
select SESSIONID
,      ind.name
,      tbi.app_id
,      tbi.odb_id
,      tbi.odb_id
,      ind.id
,      'INDEX'
,      tbi.tab_id
,      tbi.tab_name
,      nvl(indstor.percent_free,PCT_FREE)
,      nvl(indstor.initial_transaction,IND_INIT_TRANS)
,      tbi.tab_start_rows
,      tbi.tab_end_rows
,      ind.complete_flag
from ci_relation_indexes ind
,    ci_user_object_index_storages indstor
,    ck_ckdbsz_tbi_tmp tbi
where ind.TABLE_DEFINITION_REFERENCE = tbi.tab_id
and   tbi.rep_seq = SESSIONID
and   tbi.app_id = folderirid
and   indstor.table_implementation_reference = tbi.tbi_id
and   indstor.index_reference = ind.id
and   indstor.tablespace_reference is null
AND     ( RRI_CRSTATUS = 'Y'
          or IND.complete_flag = 'Y' );  
commit;
DBMS_OUTPUT.PUT_LINE('Rows processed.. ' || to_char(sql%rowcount));
/* PK AND UK DETAILS */
/* Select keys that have index storages with a tablespace assigned */ 
insert into ck_ckdbsz_key_tmp
(      REP_SEQ
,      KEY_NAME
,      app_id
,      OTB_ID
,      ODB_ID
,      KEY_ID
,      KEY_TYPE
,      TAB_ID
,      TAB_NAME
,      KEY_PCT_FREE
,      KEY_INIT_TRANS
,      KEY_START_ROWS
,      KEY_END_ROWS
,      KEY_CR_STATUS)
/* keys with tablespaces */
select SESSIONID
,      key.name
,      tbi.app_id
,      otb.otb_id
,      tbi.odb_id
,      key.id
,      key.constraint_type
,      tbi.tab_id
,      tbi.tab_name
,      nvl(indstor.percent_free,PCT_FREE)
,      nvl(indstor.initial_transaction,IND_INIT_TRANS)
,      tbi.tab_start_rows
,      tbi.tab_end_rows
,      key.complete_flag
from ci_constraints key
,    ci_user_object_index_storages indstor
,    ck_ckdbsz_tbi_tmp tbi
,    ck_ckdbsz_otb_tmp otb
where key.table_reference = tbi.tab_id
and   tbi.rep_seq = SESSIONID
and   tbi.app_id = folderirid
and   indstor.table_implementation_reference = tbi.tbi_id
and   indstor.tablespace_reference = otb.otb_id
and   otb.rep_seq = sessionid
and   otb.app_id = folderirid
and otb.odb_id = tbi.odb_id
and   nvl(indstor.primary_key_reference,indstor.unique_key_reference) = key.id
and   key.constraint_type in ('UNIQUE','PRIMARY')
AND     ( RRI_CRSTATUS = 'Y'
          or KEY.complete_flag  = 'Y' )
union all
/* keys with no tablespace - use default tablespace */
select SESSIONID
,      key.name
,      tbi.app_id
,      tbi.odb_id
,      tbi.odb_id
,      key.id
,      key.constraint_type
,      tbi.tab_id
,      tbi.tab_name
,      nvl(indstor.percent_free,PCT_FREE)
,      nvl(indstor.initial_transaction,IND_INIT_TRANS)
,      tbi.tab_start_rows
,      tbi.tab_end_rows
,      key.complete_flag
from ci_constraints key
,    ci_user_object_index_storages indstor
,    ck_ckdbsz_tbi_tmp tbi
where key.table_reference = tbi.tab_id
and   tbi.rep_seq = SESSIONID
and   tbi.app_id = folderirid
and   key.constraint_type in ('UNIQUE','PRIMARY')
and   indstor.table_implementation_reference = tbi.tbi_id
and   nvl(indstor.primary_key_reference,indstor.unique_key_reference) = key.id
and   indstor.tablespace_reference is null
AND     ( RRI_CRSTATUS = 'Y'
          or KEY.complete_flag  = 'Y' );
commit;
DBMS_OUTPUT.PUT_LINE('Rows processed.. ' || to_char(sql%rowcount));
/* delete any 'DEFAULT' tablespaces which actually have no usage  
delete from ck_ckdbsz_otb_tmp otb
where not exists (select 1 from ck_ckdbsz_tbi_tmp tbi
                  where otb.otb_id = tbi.otb_id)
and not exists (select 1 from ck_ckdbsz_key_tmp key
                  where otb.otb_id = key.otb_id);
*/
commit;
END;
FUNCTION col_overhead      
            (col_avg_length in NUMBER,
             col_max_length in NUMBER,
             col_pct_used in NUMBER,
             col_datatype in VARCHAR2)
         RETURN NUMBER is
    l_length number;
    l_col_length number;
    begin
        l_col_length := nvl(col_avg_length,col_max_length);
        if   col_datatype = 'NUMBER' or
             col_datatype = 'REAL'   or
             col_datatype = 'SMALLINT' or
             col_datatype = 'DECIMAL' or
             col_datatype = 'DOUBLE PRECISION' or
             col_datatype = 'FLOAT' or
             col_datatype = 'INTEGER' or
             col_datatype = 'BINARY_INTEGER' then
           l_length:=ceil(l_col_length/2) + 1;
        elsif col_datatype = 'ROWID' then
           l_length := 6;
        elsif col_datatype = 'CHAR' then
           l_length := col_max_length;
        elsif col_datatype = 'DATE' or
              col_datatype = 'TIME' or
              col_datatype = 'TIMESTAMP' then 
           l_length := 7;
        else 
           l_length:= l_col_length;
        end if;
        l_length := ceil(nvl(col_pct_used,50)*nvl(l_length,10)/100);
        if l_length > 250 then
           l_length := l_length + 3;
        else
           l_length := l_length + 1;
        end if;
        return l_length;    
    end;
FUNCTION indcol_overhead      
            (col_avg_length in NUMBER,
             col_max_length in NUMBER,
             col_pct_used in NUMBER,
             col_datatype in VARCHAR2)
         RETURN NUMBER is
    l_length number;
    l_col_length number;
    begin
        l_col_length := nvl(col_avg_length,col_max_length);
        if   col_datatype = 'NUMBER' or
             col_datatype = 'REAL'   or
             col_datatype = 'SMALLINT' or
             col_datatype = 'DECIMAL' or
             col_datatype = 'DOUBLE PRECISION' or
             col_datatype = 'FLOAT' or
             col_datatype = 'INTEGER' or
             col_datatype = 'BINARY_INTEGER' then
           l_length:=ceil(l_col_length/2) + 1;
        elsif col_datatype = 'ROWID' then
           l_length := 6;
        elsif col_datatype = 'CHAR' then
           l_length := col_max_length;
        elsif col_datatype = 'DATE' or
              col_datatype = 'TIME' or
              col_datatype = 'TIMESTAMP' then 
           l_length := 7;
        else 
           l_length:= l_col_length;
        end if;
        l_length := ceil(nvl(col_pct_used,50)*nvl(l_length,10)/100);
        if l_length > 128 then
           l_length := l_length + 3;
        else
           l_length := l_length + 1;
        end if;
        return l_length;    
    end;
end ckodsz;
/