1. Create schema/user (Ex: PARTSRVC) where partition tables are to be created and provide necessary privileges

CREATE USER PARTSRVC IDENTIFIED BY part1234;

GRANT CONNECT, RESOURCE, DBA TO PARTSRVC;

GRANT CREATE SESSION, CREATE ANY TABLE, CREATE ANY VIEW TO PARTSRVC;

GRANT DROP ANY TABLE TO PARTSRVC;

GRANT CREATE ANY SYNONYM TO PARTSRVC;

GRANT CREATE PUBLIC SYNONYM TO PARTSRVC;

GRANT CREATE ANY TRIGGER TO PARTSRVC;



2. Login as PARTSRVC and 

a. Create the meta data tables required for partition management 
    Execute the scripts in file: partition_mgmt_tables.sql

b. Create the package 'MANAGE_PARTITIONS' (pkg_MANAGE_PARTITIONS_v7.sql)

c. Create the package body 'MANAGE_PARTITIONS' (pkg_body_MANAGE_PARTITIONS_v7.sql)


3. Create the table that requires partition management 
Follow the steps in given files for testing different partitions
Daily_Test_Scripts.txt
Monthly_Test_Scripts.txt
Weekly_Test_Scripts.txt
Yearly_Test_Scripts.txt


4. Partitions setup
  a. Insert record into PARTITION_META_DATA, which is required for managing the partitions
  b. Create a default table '<main_table>_DEFAULT', this table used as overflow table to store records that do not fit into any partitions
  c. Creates partition tables from the given start date (parameter: p_start_date)
  d. Creates view with the given name (parameter: p_view_name) based on all required partition tables
  e. Created INSERT/UPDATE/DELETE triggers on view


PROCEDURE MANAGE_PARTITIONS.setup_partitions
      p_table_owner                VARCHAR2    -- Owner of the table
    , p_table_name                VARCHAR2     -- Table to be partitioned
    , p_table_alias                  VARCHAR2     -- Short name of the partition table
    , p_partition_column         VARCHAR2     -- Name of the column used for partitioning
    , p_pkey_column              VARCHAR2     -- Name of primary key column
    , p_view_name                 VARCHAR2     -- Name of the view to be created
    , p_partition_type             VARCHAR2     -- Type of partition (Y/M/W/D)
    , p_start_date                   VARCHAR2     -- Date from which partitions have to be created, in 'YYYY-MM-DD' format
    , p_precreate_partitions     NUMBER        -- No. of future partitions to be created
    , p_retain_partitions          NUMBER        -- Number indicating how many old partitions to be retained 
    , p_manage_partitions       VARCHAR2     -- Flag to auto manage partitions
    , p_drop_old_partitions      VARCHAR2     -- Flag to indicate if old partitions to be dropped
    , p_enable_logging            VARCHAR2     -- Flag for logging



partition_steps.txt
partition_mgmt_tables.sql
pkg_body_MANAGE_PARTITIONS.sql
pkg_MANAGE_PARTITIONS.sql

Daily_Test_Scripts.txt
Monthly_Test_Scripts.txt
Weekly_Test_Scripts.txt
Yearly_Test_Scripts.txt