Partitioning of sj_arkiv

select max(doc_id) from sj_arkiv.archive_doc;

select max(doc_id) from sj_arkiv.archive_doc_info;

select segment_name, tablespace_name from sys.dba_segments where owner = 'SJ_ARKIV' and segment_type = 'TABLE';

select table_name, segment_name, tablespace_name from sys.dba_lobs where owner = 'SJ_ARKIV' and table_name = 'ARCHIVE_DOC';

select table_name, segment_name, tablespace_name, partitioned

from sys.dba_lobs where owner = 'SJ_ARKIV' and table_name = 'ARCHIVE_DOC';

select * from sys.dba_segments a, sys.dba_lobs b
where a.segment_name = b.segment_name
and a.owner = 'SJ_ARKIV'
and b.owner = 'SJ_ARKIV
				

-- Execute this as SJSYSADM
-- define must be corrected to what is desired
define new_arkiv_tb=?;
define new_arkiv_lob_tb=?;

set echo on
set trimspool on
set pagesize 0

spool alter_sj_arkiv.log

alter user sj_arkiv identified by sj_arkiv;
alter user sj_arkiv quota unlimited on &new_arkiv_tb;
alter user sj_arkiv quota unlimited on &new_arkiv_lob_tb;

spool off

-- Execute this as SJ_ARKIV
-- define must be corrected to what is desired

define old_arkiv_tb=?;
define new_arkiv_tb=?;
define p_doc_id=?;
-- p_doc_id2 must be assign to p_doc_id * 2
define p_doc_id2=?


set echo on
set trimspool on
set pagesize 0
set timing on

spool part_doc_info.log

rename archive_doc_info to archive_doc_info_org;
alter index P_ARCHIVE_DOC_INFO rename to P_ARCHIVE_DOC_INFO_ORG;

create table SJ_ARKIV.ARCHIVE_DOC_INFO(
		DOC_ID number,
		CREATED date,
		CREATOR varchar2(100 byte),
		EDITED date,
		EDITOR varchar2(100 byte),
		EDIT_LOCK number,
		SJ_DOC_TYPE varchar2(80 byte),
		SJ_ID number,
		SJ_VERSION_NO number,
		SJ_LOCK number,
		indexed char(1 byte),
		"EXTENSION" VARCHAR2(12 byte),
		"MIME_TYPE" VARCHAR2(128 byte),
		"PROG_ID" VARCHAR2(80 byte),
		"ORIGINAL_FILE_NAME" VARCHAR2(255 byte))
		partition by range (doc_id)
		(partition p1 values less than (&p_doc_id) tablespace &old_arkiv_tb,
		partition p2 values less than (&p_doc_id2) tablespace &new_arkiv_tb);

create unique index P_ARCHIVE_DOC_INFO on ARCHIVE_DOC_INFO(DOC_ID) tablespace &new_arkiv_tb;

alter table SJ_ARKIV.ARCHIVE_DOC_INFO exchange partition p1 with table SJ_ARKIV.ARCHIVE_DOC_INFO_ORG;

grant select, insert, update, delete on SJ_ARKIV.ARCHIVE_DOC_INFO to SJSYSADM;

drop index P_ARCHIVE_DOC_INFO;
create unique index P_ARCHIVE_DOC_INFO on ARCHIVE_DOC_INFO(DOC_ID) local
(partition p1 tablespace &old_arkiv_tb,
partition p2 tablespace &new_arkiv_tb);

spool off


				

-- Execute this as SJ_ARKIV
-- define must be corrected to what is desired

define old_arkiv_tb=?;
define new_arkiv_tb=?;
define old_arkiv_lob_tb=?;
define new_arkiv_lob_tb=?;
define p_doc_id=?;
-- p_doc_id2 must be assign to p_doc_id * 2
define p_doc_id2=?

set echo on
set trimspool on
set pagesize 0
set timing on

spool part_doc.log


rename archive_doc to archive_doc_org;
alter index P_ARCHIVE_DOC rename to P_ARCHIVE_DOC_ORG;

create table SJ_ARKIV.ARCHIVE_DOC(
	DOC_ID number,
	DOC_LENGTH number,
	DOCUMENT blob)
	lob (DOCUMENT) store as securefile SJ_ARKIV_CAPTIA_SEC_P
	partition by range (doc_id)
	(partition p1 values less than (&p_doc_id) tablespace &old_arkiv_tb lob(document) store as (tablespace &old_arkiv_lob_tb disable storage in row nocache logging),
	partition p2 values less than (&p_doc_id2) tablespace &new_arkiv_tb lob(document) store as (tablespace &new_arkiv_lob_tb disable storage in row nocache logging));

create unique index P_ARCHIVE_DOC on ARCHIVE_DOC(DOC_ID) tablespace &new_arkiv_tb;

alter table SJ_ARKIV.ARCHIVE_DOC exchange partition p1 with table SJ_ARKIV.ARCHIVE_DOC_ORG;

grant select, insert, update, delete on SJ_ARKIV.ARCHIVE_DOC to SJSYSADM;

drop index P_ARCHIVE_DOC;
create unique index P_ARCHIVE_DOC on ARCHIVE_DOC(DOC_ID) local
(partition p1 tablespace &old_arkiv_tb,
partition p2 tablespace &new_arkiv_tb);

spool off

				

		-- Execute this as sj_arkiv
set trimspool on
set echo on
set pagesize 0

spool sequence.log

declare
	droptext varchar2(100);
	maxdokid number(16);
	seq_max number(16);
	cursor c_seq is
		select null from all_sequences
		where sequence_name = 'ARCHIVE_DOC_INFO$DOC_ID'
		and sequence_owner = 'SJ_ARKIV';
begin

   droptext := null;

   for post in c_seq loop
      droptext := 'drop sequence SJ_ARKIV.ARCHIVE_DOC_INFO$DOC_ID';
   end loop;

   select max(mdokid) into maxdokid from
      (select nvl(max(DOC_ID),0)+1 mdokid from SJ_ARKIV.ARCHIVE_DOC
      union
      select nvl(max(DOC_ID),0)+1 mdokid from SJ_ARKIV.ARCHIVE_DOC_INFO);
   
   if droptext is not null then
      EXECUTE IMMEDIATE 'select SJ_ARKIV.ARCHIVE_DOC_INFO$DOC_ID.nextval from dual' into seq_max;
      if maxdokid > seq_max then
         EXECUTE IMMEDIATE droptext;
         droptext := null;
      end if;
   end if;

   if droptext is null then
      EXECUTE IMMEDIATE 'create sequence SJ_ARKIV.ARCHIVE_DOC_INFO$DOC_ID start with ' || to_char(maxdokid);
   end if;

end;
/

spool off

				

-- Execute this as sjsysadm
set echo on
set trimspool on
set pagesize 0

spool set_sj_arkiv_pwd.log

-- take the value from sys_user_guid and use that as parameter for the call to sjp_sys_users.set_all_pwd
-- select * from sys_user_guid;

execute sjp_sys_users.set_all_pwd('?');

spool off