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;
/

God's Word for the day

Wisdom and Folly

Whoever teaches a fool is like one who glues potsherds together, 

  or who rouses a sleeper from deep slumber. 

Whoever tells a story to a fool tells it to a drowsy man; 

  and at the end he will say, "What is it?"

Sirach 22:1-2

Gospel teachings of Jesus

Jesus explains the parable of the weeds

Then he left the house and went into the house.

  And his disciples approached him saying,

"Explain to us the parable of the weeds of the field."

  He answered, "The one who sows the good seed is the Son of Man;

The field is the world, and the good seed are the children of the kingdom;

  The weeds are the children of the evil one, 

and the enemy who sowed them is the devil.

  The harvest is the end of the age, and the reapers are angels.

Just as the weeds are collected and burned up with fire,

  so will it be at the end of the age.

The Son of Man will send his angels, and they will collect out of his kingdom

  All causes of sin and evil doers.

And they will throw them into the furnace of fire,

  where there will be weeping and gnashing of teeth.

Then the righteous will shine like the sun in the kingdom of their Father.

  Let anyone with ears listen.

Mathew 13:36-43