Rem $Header: wfupart.sql 26.2 2001/11/07 21:26:53 mcraig ship $
Rem
Rem wfupart.sql
Rem
Rem  Copyright (c) Oracle Corporation 1999. All Rights Reserved.
Rem
Rem    NAME
Rem      wfupart.sql - This (Upgrade) script partitions the
Rem      WF tables by item type. 
Rem
Rem    DESCRIPTION
Rem      This SQL script migrates existing non-partitioned WF
Rem      tables to partitioned tables. You should make sure
Rem      your database is backed up. Also you should backup the
Rem      the following tables: WF_ITEMS, WF_ITEM_ACTIVITY_STATUSES,
Rem      WF_ITEM_ACTIVITY_STATUSES_H, WF_ITEM_ATTRIBUTE_VALUES. 
Rem
Rem    NOTES
Rem      This is a sample script to partition WF tables by
Rem      Item Type, with a Hash Key on Item Key
Rem      !!!For 8.1.7 Database Only with Partition Option!!!
Rem      Make sure you run this script on a test instance first.
Rem     
Rem
Rem
Rem    MODIFIED   (MM/DD/YYYY)
Rem
Rem    aalomari     11/14/1999 - Creation
Rem    dlam         12/04/1999 - Modified as a post-installed script
Rem    mcraig       26/10/2001 - Modified Apps script for standalone
Rem

-- add this to exit at error
-- at the connection testing stage, we cannot use exit failure rollback
whenever sqlerror exit failure;

set serveroutput on size 1000000

prompt Please review this script properly and ensure you have enough space
prompt in your tablespace, as well as enqueue_resources in your init.ora.
prompt Usage:
prompt   sqlplus <wf_user>/<wf_passwd> @wfupart <wf_user> <wf_passwd>

def u_app = &1
def p_app = &2

-- Testing connection to Workflow Account
prompt Test connection to &u_app
connect &u_app/&p_app;

-- All connected properly, now we can proceed with package creation.
whenever sqlerror exit failure rollback;

-- Create Packages
prompt Creating WF_INSTALL package ...

create or replace package Wf_Install 
authid current_user as
g_partition_ct  number := 0;
Procedure CreateTable (
  tblname    in varchar2,
  tblspcname in varchar2 default null
);

Procedure CreateIndex (
  idxname    in varchar2,
  tblspcname in varchar2 default null
);

-- objtype are like 'VIEW' and 'PACKAGE BODY'
-- objname can have wild card like 'WF%'
Procedure CompileObject (
  objtype    in varchar2,
  objname    in varchar2 default '%'
);

end Wf_Install;
/

create or replace package body Wf_Install as
Procedure CreateTable (
  tblname    in varchar2,
  tblspcname in varchar2 default null
)
is
  v_tablespace_name  varchar2(30);
  v_initial          number;
  v_next             number;
  v_pctinc           number;
  v_partitioned      varchar2(3);
  v_new_table        varchar2(30);
  v_old_table        varchar2(30);
  v_sql              varchar2(32000);
  l_sql              varchar2(32000);
  v_dummy            varchar2(1);
  i		     pls_integer := 0;

  cursor c is
  select name
  from wf_item_types
  order by 1 asc;

begin
  begin

    for c_rec in c loop
      i := i+1;
      l_sql := l_sql||
         ' partition wf_item'||to_char(i)||' values less than ('''||c_rec.name||'''),'; 
    end loop;

    -- Set the Global Partition Count
    g_partition_ct := i;
 
    dbms_output.put_line('Number of Partitions to be created:'||to_char(i+1));

    select TABLESPACE_NAME, INITIAL_EXTENT, nvl(NEXT_EXTENT,10), nvl(PCT_INCREASE,0),
           PARTITIONED
    into   v_tablespace_name, v_initial, v_next, v_pctinc, v_partitioned
    from   USER_TABLES
    where  TABLE_NAME = tblname;
    dbms_output.put_line('tablespace name:'||v_tablespace_name);
    dbms_output.put_line('initial:'||v_initial);
    dbms_output.put_line('next:'||v_next);
    dbms_output.put_line('pctinc:'||v_pctinc);
    dbms_output.put_line('partitioned:'||v_partitioned);
  exception
    when NO_DATA_FOUND then
      dbms_output.put_line('--Error while querying storage parameters.');
      dbms_output.put_line('-- Table '||tblname||' does not exist.');
      dbms_output.put_line('-- Please check if you login to the correct user, database,');
      dbms_output.put_line('-- and that application installation has been completed successfully.');
      raise_application_error(-20001,'Missing table '||tblname);

    when OTHERS then
      dbms_output.put_line('--Error while querying storage parameters.');
      raise;
  end;

  if (v_partitioned = 'YES') then
    -- already partitioned.  Just exit.
    dbms_output.put_line('Table '||tblname||' already partitioned ... skipped.');
    return;
  end if;

  if (tblspcname is not null) then
    v_tablespace_name := tblspcname;
  end if;

  v_new_table := substr('WFN_'||tblname, 1, 30);

  -- check if new table name exists, if so, may ask to drop it.
  begin
    select null into v_dummy from sys.dual where exists (
      select 1
      from   USER_OBJECTS
      where  OBJECT_NAME = v_new_table
    );
    dbms_output.put_line('Name conflict.  Please first drop '||v_new_table);
    raise_application_error(-20002, v_new_table||' already exists.');
  exception
    when NO_DATA_FOUND then
      null;
    when OTHERS then
      dbms_output.put_line('--Error while checking the new table name.');
      raise;
  end;

  v_initial := 16384;  -- force it to 16k

  dbms_output.put_line('Creating new table '||v_new_table);  
  v_sql :='create table '||v_new_table||
          ' pctfree 40'||
          ' pctused 60'||
          ' initrans 10'||
          ' tablespace '||v_tablespace_name||
          ' storage (initial '||to_char(v_initial)||' next '||to_char(v_next)
	  ||'M'||
          ' freelists 32 freelist groups 10'||
          ' pctincrease '||to_char(v_pctinc)||')' ||
          ' parallel (degree 1)'||
          ' nologging'||
          ' partition by range (item_type)' ||
          ' subpartition by hash (item_key) '||
          ' subpartitions 32 ('||l_sql||
          ' partition wf_item'||to_char(i+1)||' values less than (MAXVALUE))' ||
          ' as select * from '||tblname;

  begin
    execute immediate v_sql;
  exception
    when OTHERS then
      dbms_output.put_line('Error in SQL: '||substr(v_sql,1,200));
      dbms_output.put_line('Error in SQL: '||substr(v_sql,201,200));
      dbms_output.put_line('Error in SQL: '||substr(v_sql,401,200));
      raise;
  end;

  -- rename orig table to old table
  v_old_table := substr('WFO_'||tblname, 1, 30);
  dbms_output.put_line('Rename '||tblname||' to '||v_old_table);
  v_sql := 'alter table '||tblname||' rename to '||v_old_table;
  begin
    execute immediate v_sql;
  exception
    when OTHERS then
      dbms_output.put_line('Error in: '||substr(v_sql,1,220));
      raise;
  end;

  -- rename new table to orig table
  dbms_output.put_line('Rename '||v_new_table||' to '||tblname);
  v_sql := 'alter table '||v_new_table||' rename to '||tblname;
  begin
    execute immediate v_sql;
  exception
    when OTHERS then
      raise_application_error(-20005,'Error in SQL: '||substr(v_sql,1,3000));
  end;

exception
  when OTHERS then
    dbms_output.put_line('--Error in CreateTable '||tblname);
    raise;
end CreateTable;

Procedure CreateIndex (
  idxname    in varchar2,
  tblspcname in varchar2 default null
)
is
  v_tablespace_name  varchar2(30);
  v_initial          number;
  v_next             number;
  v_pctinc           number;
  v_tblpartitioned   varchar2(3);
  v_idxpartitioned   varchar2(3);
  v_prefix           varchar2(8);
  v_new_index        varchar2(30);
  v_old_index        varchar2(30);
  v_sql              varchar2(32000);
  l_sql              varchar2(32000);
  v_dummy            varchar2(1);
  partition_index    boolean := false;
  drop_index         boolean := false;
  i		     pls_integer := 0;

begin
  begin

    --for c_rec in c loop
    for x in 1..g_partition_ct loop
      i := i+1;
      if l_sql is null then 
        l_sql :=
         ' partition wf_item'||to_char(i);
      else
        l_sql := l_sql||
         ' ,partition wf_item'||to_char(i);
      end if;
    end loop;

    l_sql := l_sql||' ,partition wf_item'||to_char(i+1);

    select I.TABLESPACE_NAME, I.INITIAL_EXTENT, nvl(I.NEXT_EXTENT,1), nvl(I.PCT_INCREASE,0),
           I.PARTITIONED, T.PARTITIONED
    into   v_tablespace_name, v_initial, v_next, v_pctinc,
           v_idxpartitioned, v_tblpartitioned
    from   USER_INDEXES I, USER_TABLES T
    where  I.INDEX_NAME = idxname
    and    I.TABLE_NAME = T.TABLE_NAME;

  exception
    when NO_DATA_FOUND then
      dbms_output.put_line('--Error while querying storage parameters.');
      dbms_output.put_line('-- Index '||idxname||' does not exist.');
      dbms_output.put_line('-- Please check if you login to the correct user, database,');
      dbms_output.put_line('-- and that application installation has been completed successfully.');
      raise_application_error(-20011,'Missing index '||idxname);

    when OTHERS then
      dbms_output.put_line('--Error while querying storage parameters.');
      raise;
  end;

  if (tblspcname is not null) then
    v_tablespace_name := tblspcname;
  end if;

  v_new_index := substr('WFN_'||idxname, 1, 30);

  -- check if new index name exists, if so, may ask to drop it.
  begin
    select null into v_dummy from sys.dual where exists (
      select 1
      from   USER_OBJECTS
      where  OBJECT_NAME = v_new_index
    );
    dbms_output.put_line('Name conflict.  Please first drop '||v_new_index);
    raise_application_error(-20012, v_new_index||' already exists.');
  exception
    when NO_DATA_FOUND then
      null;
    when OTHERS then
      dbms_output.put_line('--Error while checking the new index name.');
      raise;
  end;

  dbms_output.put_line('Recreating index '||idxname);

  v_prefix := 'WFO_';
  v_initial := 16384; -- force it to 16k
  if (idxname = 'WF_ITEM_ACTIVITY_STATUSES_PK') then
    partition_index := true;
    v_prefix := 'WFP_';
    v_sql := 'create unique index '||v_new_index||
             ' on WF_ITEM_ACTIVITY_STATUSES (ITEM_TYPE, ITEM_KEY, PROCESS_ACTIVITY)'||
             ' pctfree 40'||
             ' initrans 10'||
             ' tablespace '||v_tablespace_name||
             ' storage (initial '||to_char(v_initial)||' next '||to_char(v_next)||'M'||
             ' freelists 32 freelist groups 10'||
             ' pctincrease '||to_char(v_pctinc)||')' ||
             ' nologging compute statistics'||
             ' local ('||l_sql||')';

  elsif (idxname = 'WF_ITEM_ACTIVITY_STATUSES_N1') then
    v_prefix := 'WF1_';
    v_sql := 'create index '||v_new_index||
             ' on WF_ITEM_ACTIVITY_STATUSES (ACTIVITY_STATUS, ITEM_TYPE)'||
             ' pctfree 40'||
             ' initrans 10'||
             ' tablespace '||v_tablespace_name||
             ' storage (initial '||to_char(v_initial)||' next '||to_char(v_next)||
             ' freelists 32 freelist groups 10'||
             ' pctincrease '||to_char(v_pctinc)||')' ||
             ' nologging compute statistics';
  elsif (idxname = 'WF_ITEM_ACTIVITY_STATUSES_N2') then
    v_prefix := 'WF2_';
    v_sql := 'create index '||v_new_index||
             ' on WF_ITEM_ACTIVITY_STATUSES (NOTIFICATION_ID)'||
             ' pctfree 40'||
             ' initrans 10'||
             ' tablespace '||v_tablespace_name||
             ' storage (initial '||to_char(v_initial)||' next '||to_char(v_next)||
             ' freelists 32 freelist groups 10'||
             ' pctincrease '||to_char(v_pctinc)||')' ||
             ' nologging compute statistics';
  elsif (idxname = 'WF_ITEM_ACTIVITY_STATUSES_N3') then
    v_prefix := 'WF3_';
    v_sql := 'create index '||v_new_index||
             ' on WF_ITEM_ACTIVITY_STATUSES (DUE_DATE, ITEM_TYPE)'||
             ' pctfree 40'||
             ' initrans 10'||
             ' tablespace '||v_tablespace_name||
             ' storage (initial '||to_char(v_initial)||' next '||to_char(v_next)||
             ' freelists 32 freelist groups 10'||
             ' pctincrease '||to_char(v_pctinc)||')' ||
             ' nologging compute statistics';
  elsif (idxname = 'WF_ITEM_ACTIVITY_STATUSES_H_N1') then
    v_prefix := 'WFH1_';
    v_sql := 'create index '||v_new_index||
             ' on WF_ITEM_ACTIVITY_STATUSES_H (ACTIVITY_STATUS, ITEM_TYPE)'||
             ' pctfree 40'||
             ' initrans 10'||
             ' tablespace '||v_tablespace_name||
             ' storage (initial '||to_char(v_initial)||' next '||to_char(v_next)||
             ' freelists 32 freelist groups 10'||
             ' pctincrease '||to_char(v_pctinc)||')' ||
             ' nologging compute statistics';
  elsif (idxname = 'WF_ITEM_ACTIVITY_STATUSES_H_N2') then
    v_prefix := 'WFH2_';
    v_sql := 'create index '||v_new_index||
             ' on WF_ITEM_ACTIVITY_STATUSES_H (NOTIFICATION_ID)'||
             ' pctfree 40'||
             ' initrans 10'||
             ' tablespace '||v_tablespace_name||
             ' storage (initial '||to_char(v_initial)||' next '||to_char(v_next)||
             ' freelists 32 freelist groups 10'||
             ' pctincrease '||to_char(v_pctinc)||')' ||
             ' nologging compute statistics';
  elsif (idxname = 'WF_ITEM_ATTRIBUTE_VALUES_PK') then
    partition_index := true;
    v_sql := 'create index '||v_new_index||
             ' on WF_ITEM_ATTRIBUTE_VALUES (ITEM_TYPE, ITEM_KEY, NAME)'||
             ' pctfree 40'||
             ' initrans 10'||
             ' tablespace '||v_tablespace_name||
             ' storage (initial '||to_char(v_initial)||' next '||to_char(v_next)||
             ' freelists 32 freelist groups 10'||
             ' pctincrease '||to_char(v_pctinc)||')' ||
             ' nologging compute statistics'||
             ' local ('||l_sql||')';
  elsif (idxname = 'WF_ITEMS_PK') then
    partition_index := true;
    v_sql := 'create index '||v_new_index||
             ' on WF_ITEMS (ITEM_TYPE, ITEM_KEY)'||
             ' pctfree 40'||
             ' initrans 10'||
             ' tablespace '||v_tablespace_name||
             ' storage (initial '||to_char(v_initial)||' next '||to_char(v_next)||
             ' freelists 32 freelist groups 10'||
             ' pctincrease '||to_char(v_pctinc)||')' ||
             ' nologging compute statistics'||
             ' local ('||l_sql||')';
  elsif (idxname = 'WF_ITEMS_N1') then
    v_sql := 'create index '||v_new_index||
             ' on WF_ITEMS (PARENT_ITEM_TYPE, PARENT_ITEM_KEY)'||
             ' pctfree 40'||
             ' initrans 10'||
             ' tablespace '||v_tablespace_name||
             ' storage (initial '||to_char(v_initial)||' next '||to_char(v_next)||
             ' freelists 32 freelist groups 10'||
             ' pctincrease '||to_char(v_pctinc)||')' ||
             ' nologging compute statistics';
  elsif (idxname = 'WF_ITEMS_N2') then
    v_sql := 'create index '||v_new_index||
             ' on WF_ITEMS (BEGIN_DATE)'||
             ' pctfree 40'||
             ' initrans 10'||
             ' tablespace '||v_tablespace_name||
             ' storage (initial '||to_char(v_initial)||' next '||to_char(v_next)||
             ' freelists 32 freelist groups 10'||
             ' pctincrease '||to_char(v_pctinc)||')' ||
             ' nologging compute statistics';
  elsif (idxname = 'WF_ITEMS_N3') then
    v_sql := 'create index '||v_new_index||
             ' on WF_ITEMS (END_DATE)'||
             ' pctfree 40'||
             ' initrans 10'||
             ' tablespace '||v_tablespace_name||
             ' storage (initial '||to_char(v_initial)||' next '||to_char(v_next)||
             ' freelists 32 freelist groups 10'||
             ' pctincrease '||to_char(v_pctinc)||')' ||
             ' nologging compute statistics';
  end if;

  -- now check if we need to proceed
  if (partition_index) then
    if (v_idxpartitioned = 'YES') then
      -- already partitioned.  Just exit.
      dbms_output.put_line('Index '||idxname||' already partitioned ... skipped.');
      return;
    elsif (v_tblpartitioned = 'YES') then
      -- first drop the index
      drop_index := true;
      begin
        dbms_output.put_line('Drop index '||idxname);
        execute immediate 'drop index '||idxname;
      exception
        when OTHERS then
          dbms_output.put_line('Error in SQL: '||substr('drop index '||
                               idxname,1,200));
          raise;
      end;

    end if;
  else
    if (v_tblpartitioned = 'YES') then
      -- Detected table partitioned meant this script had already been run.
      -- Just exit.
      dbms_output.put_line('Table for index '||idxname||' already partitioned ... skipped.');
      return;
    end if;
  end if;

  begin
    dbms_output.put_line('Create index '||v_new_index);
    execute immediate v_sql;
  exception
    when OTHERS then
      dbms_output.put_line('Error in SQL: '||substr(v_sql,1,220));
      raise;
  end;

  -- Rename only if we did not drop index before
  if (not drop_index) then
    -- rename orig index to old index
    v_old_index := substr(v_prefix||idxname, 1, 30);
    dbms_output.put_line('Rename '||idxname||' to '||v_old_index);
    v_sql := 'alter index '||idxname||' rename to '||v_old_index;
    begin
      execute immediate v_sql;
    exception
      when OTHERS then
        dbms_output.put_line('Error in SQL: '||substr(v_sql,1,220));
        raise;
    end;
  end if;

  -- rename new index to orig index
  dbms_output.put_line('Rename '||v_new_index||' to '||idxname);
  v_sql := 'alter index '||v_new_index||' rename to '||idxname;
  begin
    execute immediate v_sql;
  exception
    when OTHERS then
      dbms_output.put_line('Error in SQL: '||substr(v_sql,1,220));
      raise;
  end;

exception
  when OTHERS then
    dbms_output.put_line('--Error in CreateIndex '||idxname);
    raise;
end CreateIndex;

Procedure CompileObject (
  objtype    in varchar2,
  objname    in varchar2 default '%'
)
is
  cursor objcurs(objt varchar2, objn varchar2) is
    select OBJECT_NAME
    from   ALL_OBJECTS
    where  OBJECT_TYPE = objt
    and    OBJECT_NAME like objn
    and    STATUS = 'INVALID';

  v_sql varchar2(4000);

begin
  for obj in objcurs(objtype, objname) loop
    if (objtype = 'PACKAGE BODY') then
      v_sql := 'alter package '||obj.OBJECT_NAME||' compile body';
    else
      v_sql := 'alter '||objtype||' '||obj.OBJECT_NAME||' compile';
    end if;

    dbms_output.put_line('Running: '||v_sql);

    begin
      execute immediate v_sql;
    exception
      when OTHERS then
        -- continues when error is caught.
        dbms_output.put_line('--Error in SQL: '||v_sql);
    end;
  end loop;
exception
  when OTHERS then
    dbms_output.put_line('--Error in CompileObject '||objname);
    raise;
end CompileObject;

end Wf_Install;
/

-- show errors package body WF_INSTALL;

whenever sqlerror exit failure rollback;
set serveroutput on

prompt Recreating tables.  May take a long time...

prompt Recreating table WF_ITEM_ACTIVITY_STATUSES
begin
  dbms_output.enable(1000000);

  -- create table
  Wf_Install.CreateTable('WF_ITEM_ACTIVITY_STATUSES');

  -- create index
  Wf_Install.CreateIndex('WF_ITEM_ACTIVITY_STATUSES_PK');
  Wf_Install.CreateIndex('WF_ITEM_ACTIVITY_STATUSES_N1');
  Wf_Install.CreateIndex('WF_ITEM_ACTIVITY_STATUSES_N2');
  Wf_Install.CreateIndex('WF_ITEM_ACTIVITY_STATUSES_N3');
end;
/

-- drop old table
whenever sqlerror continue;
prompt drop table WFO_WF_ITEM_ACTIVITY_STATUSES;
drop table WFO_WF_ITEM_ACTIVITY_STATUSES;

whenever sqlerror exit failure rollback;

prompt Recreating table WF_ITEM_ACTIVITY_STATUSES_H
begin
  dbms_output.enable(1000000);
  Wf_Install.CreateTable('WF_ITEM_ACTIVITY_STATUSES_H');

  Wf_Install.CreateIndex('WF_ITEM_ACTIVITY_STATUSES_H_N1');
  Wf_Install.CreateIndex('WF_ITEM_ACTIVITY_STATUSES_H_N2');
end;
/

-- drop old table
whenever sqlerror continue;
prompt drop table WFO_WF_ITEM_ACTIVITY_STATUSES_;
drop table WFO_WF_ITEM_ACTIVITY_STATUSES_;
whenever sqlerror exit failure rollback;

prompt Recreating table WF_ITEM_ATTRIBUTE_VALUES
begin
  dbms_output.enable(1000000);
  -- create table
  Wf_Install.CreateTable('WF_ITEM_ATTRIBUTE_VALUES');

  -- create index
  Wf_Install.CreateIndex('WF_ITEM_ATTRIBUTE_VALUES_PK');
end;
/

-- drop old table
whenever sqlerror continue;
prompt drop table WFO_WF_ITEM_ATTRIBUTE_VALUES
drop table WFO_WF_ITEM_ATTRIBUTE_VALUES;
whenever sqlerror exit failure rollback;

prompt Recreating table WF_ITEMS
begin
  dbms_output.enable(1000000);

  -- create table
  Wf_Install.CreateTable('WF_ITEMS');

  -- create index
  Wf_Install.CreateIndex('WF_ITEMS_PK');
  Wf_Install.CreateIndex('WF_ITEMS_N1');
  Wf_Install.CreateIndex('WF_ITEMS_N2');
  Wf_Install.CreateIndex('WF_ITEMS_N3');
end;
/

-- drop old table
whenever sqlerror continue;
prompt drop table WFO_WF_ITEMS
drop table WFO_WF_ITEMS;

-- As Workflow Schema owner
whenever sqlerror exit failure rollback;
set serveroutput on

prompt Recompiling views
-- recompile views
begin
  dbms_output.enable(1000000);
  Wf_Install.CompileObject('VIEW');
end;
/

set serveroutput on
prompt Recompiling package bodies
-- recompile packages
begin
  dbms_output.enable(1000000);
  Wf_Install.CompileObject('PACKAGE BODY');
end;
/

-- clean up
prompt Drop package WF_INSTALL in &u_app schema
drop package WF_INSTALL;


prompt Post-install script completed, you may ignore error during create
prompt synonyms.
prompt

commit;
exit;