Archiving: A generic solution - Part 2

In this part we will create the archive and index tablespaces, the archive user and the archive object types. 

Create the archive tablespace. 

This can be on a separate partition which can be backed to tape of any other backup mechanism on a regular basis

1_ddl_cre_archive_tablespace.sql

CREATE TABLESPACE HR_ARCHIVE 
DATAFILE 'D:\app\oracle\oradata\ALPHA\hr_archive.dbf' 
SIZE 50485760 AUTOEXTEND ON 
NEXT 50485760 MAXSIZE 309715200 
LOGGING ONLINE PERMANENT BLOCKSIZE 8192 
EXTENT MANAGEMENT LOCAL 
UNIFORM SIZE 524288 
SEGMENT SPACE MANAGEMENT AUTO;

Create the index tablespace 2_ddl_cre_indx_tablespace.sql

CREATE TABLESPACE INDX 
DATAFILE 'D:\app\oracle\oradata\XE\XEPDB2\INDX.dbf' 
SIZE 50485760 AUTOEXTEND ON 
NEXT 50485760 MAXSIZE 309715200 
LOGGING ONLINE PERMANENT BLOCKSIZE 8192 
EXTENT MANAGEMENT LOCAL 
UNIFORM SIZE 524288 
SEGMENT SPACE MANAGEMENT AUTO;

Create the archive user with the required rights 3_ddl_cre_archive_user.sql

--DROP USER HR_ARCHIVE CASCADE;
CREATE USER HR_ARCHIVE IDENTIFIED BY HR_ARCHIVE;
GRANT CREATE SESSION TO HR_ARCHIVE;
GRANT CONNECT TO HR_ARCHIVE;
GRANT CREATE TABLE TO HR_ARCHIVE;
GRANT CREATE PROCEDURE TO HR_ARCHIVE;
GRANT UNLIMITED TABLESPACE TO HR_ARCHIVE;
GRANT CREATE ANY TYPE TO HR_ARCHIVE;
GRANT CREATE ANY SEQUENCE TO HR_ARCHIVE;
ALTER USER HR_ARCHIVE DEFAULT TABLESPACE HR_ARCHIVE QUOTA UNLIMITED ON HR_ARCHIVE;
ALTER USER HR_ARCHIVE TEMPORARY TABLESPACE TEMP;

GRANT CREATE VIEW, ALTER SESSION, CREATE SEQUENCE TO HR_ARCHIVE;
GRANT CREATE SYNONYM, CREATE DATABASE LINK, RESOURCE , UNLIMITED TABLESPACE TO HR_ARCHIVE;

This archiving example is run for two tableS in HR schema. EMPLOYEES and JOB_HISTORY. So from HR schema we have to give the required grants on these tables to HR_ARCHIVE schema. hr_archive_grants.sql

GRANT SELECT, DELETE ON JOB_HISTORY TO HR_ARCHIVE;
GRANT SELECT, DELETE ON EMPLOYEES TO HR_ARCHIVE;

Create the required object types for this solution. 

-------------------------------------------------------- -- DDL for Type TYPE_REF_STR -------------------------------------------------------- 4_ddl_cre_archive_type.sql

--------------------------------------------------------
--  DDL for Type TAB_COL_DTLS
--------------------------------------------------------
CREATE OR REPLACE TYPE TAB_COL_DTLS AS TABLE OF VARCHAR2(100);
/
--------------------------------------------------------
--  DDL for Type TYPE_REF_STR
--------------------------------------------------------
CREATE OR REPLACE TYPE TYPE_REF_STR AS TABLE OF VARCHAR2(100);
/
--------------------------------------------------------
--  DDL for Type TYP_REC_TABLE_KEY_COLS
--------------------------------------------------------
CREATE OR REPLACE TYPE TYP_REC_TABLE_KEY_COLS 
AS OBJECT
 (
TABLE_NAME   VARCHAR2(30),
COLUMN_1     VARCHAR2(30),
COLUMN_2     VARCHAR2(30),
COLUMN_3     VARCHAR2(30),
COLUMN_4     VARCHAR2(30),
COLUMN_5     VARCHAR2(30)
 );
/
--------------------------------------------------------
--  DDL for Type TYP_REF_DATE
--------------------------------------------------------
CREATE OR REPLACE TYPE TYP_REF_DATE AS TABLE OF DATE;
/
--------------------------------------------------------
--  DDL for Type TYP_REF_NUMBER
--------------------------------------------------------
CREATE OR REPLACE TYPE TYP_REF_NUMBER AS TABLE OF NUMBER;
/
--------------------------------------------------------
--  DDL for Type TYP_REF_STR
--------------------------------------------------------
CREATE OR REPLACE TYPE TYP_REF_STR AS TABLE OF VARCHAR2(4000);
/
--------------------------------------------------------
--  DDL for Type TYP_TBL_TABLE_KEY_COLS
--------------------------------------------------------
CREATE OR REPLACE TYPE TYP_TBL_TABLE_KEY_COLS IS TABLE OF TYP_REC_TABLE_KEY_COLS;
/

-------------------------------------------------------- -- DDL to drop the Object Types --------------------------------------------------------

DROP TYPE TYP_TBL_TABLE_KEY_COLS;
/
DROP TYPE TYP_REF_STR;
/
DROP TYPE TYP_REF_NUMBER;
/
DROP TYPE TYP_REF_DATE;
/
DROP TYPE TYP_REC_TABLE_KEY_COLS;
/
DROP TYPE TYPE_REF_STR;
/