标签：name partition VARCHAR2 分区表 自动 Oracle table data id
原文-https://github.com/aws-samples/automate-table-partitioning-in-oracle-standard-edition/tree/main/partitions/scripts
需要调整的地方有：
1. drop table 未释放 垃圾空间
2. 未提及到收缩HWM释放空间
partitions/scripts/partition_mgmt_tables.sql
--- partitions/scripts/partition_mgmt_tables.sql
CREATE TABLE PARTITION_META_DATA
(
META_DATA_ID NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
TABLE_OWNER VARCHAR2(32) NOT NULL,
TABLE_NAME VARCHAR2(64) NOT NULL,
TABLE_ALIAS VARCHAR2(8) NOT NULL,
VIEW_NAME VARCHAR2(64) NOT NULL,
PARTITION_COLUMN_NAME VARCHAR2(64) NOT NULL,
PRIMARYKEY_COLUMN_NAME VARCHAR2(64) NOT NULL,
PARTITION_TYPE CHAR(1), /* D-DAILY, W-Weekly, M-MONTHLY, Y-YEARLY */
NUM_PRECREATE_PARTITIONS NUMBER(3) DEFAULT 3,
NUM_RETAIN_PARTITIONS NUMBER(3) DEFAULT 90,
AUTO_MANAGE_PARTITIONS CHAR(1) DEFAULT 'Y', /* Y-Yes, N-No */
DROP_OLD_PARTITIONS CHAR(1) DEFAULT 'N', /* Y-Yes, N-No */
ENABLE_LOGGING CHAR(1) DEFAULT 'Y' /* Y-Yes, N-No */
, CONSTRAINT PK_PARTMETADATA PRIMARY KEY (META_DATA_ID) ENABLE
, CONSTRAINT CK1_PMD_AUTOPART CHECK (AUTO_MANAGE_PARTITIONS IN ('Y', 'N')) ENABLE
, CONSTRAINT CK2_PMD_DROPPART CHECK (DROP_OLD_PARTITIONS IN ('Y', 'N')) ENABLE
, CONSTRAINT CK3_PMD_DEBUG CHECK (ENABLE_LOGGING IN ('Y', 'N')) ENABLE
, CONSTRAINT CK4_PMD_TYPE CHECK (PARTITION_TYPE IN ('D', 'W', 'M', 'Y')) ENABLE
);
CREATE TABLE PARTITION_TABLE_DATA
(
META_DATA_ID NUMBER NOT NULL,
PARTITION_TABLE_NAME VARCHAR2(64) NOT NULL,
PARTITION_DATE_VALUE DATE,
CRT_DT DATE,
STATUS CHAR(1) /* A-Active, D-Deleted */
);
CREATE TABLE PARTITION_LOG
(
LOG_ID NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
LOG_TYPE VARCHAR2(8) NOT NULL, /* DEBUG, ERROR */
META_DATA_ID NUMBER,
OBJECT_NAME VARCHAR2(64),
LOG_MESSAGE VARCHAR2(4000),
LOG_DATE DATE DEFAULT SYSDATE
);
---partitions/scripts/pkg_MANAGE_PARTITIONS.sql
CREATE OR REPLACE PACKAGE MANAGE_PARTITIONS AS
/*********************************************************************************
NAME: Package - MANAGE_PARTITIONS
PURPOSE: This package Automates the process for partition management
1. Creates new partitions
2. Grants all privileges on new partition tables created
3. Marks old partitions as 'D' Delete which are older than partitions to be retained
4. Re-creates the view to include new partition tables and exclude old partition tables
5. Re-compiles all the triggers created on view
6. Logs all the error into log table
Ver Date Author Description
----- ---------- ------------- -----------------------
1 07/07/2020 AWS Professional Services Initial Version
**********************************************************************************/
/*****
Type to hold the main table/view privileges
*****/
TYPE tab_privs IS TABLE OF ALL_TAB_PRIVS_MADE%rowtype INDEX BY PLS_INTEGER;
/*****
Type to hold the Synonym info on the view
*****/
TYPE tab_synonyms IS TABLE OF ALL_SYNONYMS%rowtype INDEX BY PLS_INTEGER;
/*****
Type to hold the code of existing triggers on the view
*****/
TYPE tab_trigs IS TABLE OF VARCHAR(8000) INDEX BY PLS_INTEGER;
/*********************************************************************************
NAME: Procedure - SETUP_PARTITIONS
PURPOSE: This procedure is to create necessary meta data entry and create partition tables
Ver Date Author Description
----- ---------- ------------- -----------------------
1 08/24/2020 AWS Professional Services Initial Version
**********************************************************************************/
PROCEDURE SETUP_PARTITIONS (
p_table_owner VARCHAR2
, p_table_name VARCHAR2
, p_table_alias VARCHAR2
, p_partition_column VARCHAR2
, p_pkey_column VARCHAR2
, p_view_name VARCHAR2
, p_partition_type VARCHAR2
, p_start_date VARCHAR2
, p_precreate_partitions NUMBER
, p_retain_partitions NUMBER
, p_manage_partitions VARCHAR2
, p_drop_old_partitions VARCHAR2
, p_enable_logging VARCHAR2
);
/*********************************************************************************
NAME: Procedure - MAIN_PROCESS
PURPOSE: This is the main procedure that is to be called to run the automation process
Ver Date Author Description
----- ---------- ------------- -----------------------
1 07/07/2020 AWS Professional Services Initial Version
**********************************************************************************/
PROCEDURE MAIN_PROCESS (p_meta_data_id NUMBER DEFAULT NULL);
END MANAGE_PARTITIONS;
/
--- partitions/scripts/pkg_body_MANAGE_PARTITIONS.sql
CREATE OR REPLACE PACKAGE BODY MANAGE_PARTITIONS AS
/*********************************************************************************
NAME: Package - MANAGE_PARTITIONS
PURPOSE: This package Automates the process for partition management
1. Creates new partitions
2. Grants all privileges on new partition tables created
3. Marks old partitions as 'D' Delete which are older than partitions to be retained
4. Re-creates the view to include new partition tables and exclude old partition tables
5. Re-compiles all the triggers created on view
6. Logs all the error into log table
Ver Date Author Description
----- ---------- ------------- -----------------------
1 07/07/2020 AWS Professional Services Initial Version
1.1 08/03/2020 AWS Professional Services Procedures to create the INSER/UPDATE/DELETE triggers on view dynamically
Procedures: (CREATE_INS_TRG, CREATE_UPD_TRG, CREATE_DEL_TRG)
1.2 08/28/2020 AWS Professional Services Procedure to execute all necessary pre-steps required for partition automation
Procedure: SETUP_PARTITIONS
1.3 12/13/2020 AWS Professional Services Modified for changing the column names as per review feedback
(PRIMARY_TABLE_OWNER -> TABLE_OWNER)
(PRIMARY_TABLE_NAME -> TABLE_NAME)
(PRIMARY_TABLE_SHORTNAME -> TABLE_ALIAS)
(PRIMARY_VIEW_NAME -> VIEW_NAME)
**********************************************************************************/
/*********************************************************************************
NAME: Procedure - WRITE_TO_LOG
PURPOSE: This procedure is used to log errors during the process
Also logs the events during the process as debug if the ENABLE_LOGGING is set to 'Y'
Parameters:
p_log_type Indicates ERROR or DEBUG
p_meta_data_id Partition main table unique identifier
p_object_name Object for which the log record is being created
p_is_log_enabled Flag to log the activity
Ver Date Author Description
----- ---------- ------------- -----------------------
1 07/07/2020 AWS Professional Services Initial Version
**********************************************************************************/
PROCEDURE WRITE_TO_LOG (
p_log_type VARCHAR2,
p_meta_data_id NUMBER,
p_object_name VARCHAR2,
p_log_msg VARCHAR2
)
AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO PARTITION_LOG (LOG_TYPE, META_DATA_ID, OBJECT_NAME, LOG_MESSAGE)
VALUES (p_log_type, p_meta_data_id, p_object_name, SUBSTR(p_log_msg, 1, 4000));
COMMIT;
END WRITE_TO_LOG;
/*********************************************************************************
NAME: Function - GET_OBJECT_PRIVILEGES
PURPOSE: This function retrieves all the privileges on the given object and
returns the same as collection
Parameters:
p_meta_data_id Partition main table unique identifier
p_table_owner Owner of the partition tables
p_object_name Object for which the existing privileges are to be retrieved
p_is_log_enabled Flag to log the activity
Ver Date Author Description
----- ---------- ------------- -----------------------
1 07/07/2020 AWS Professional Services Initial Version
**********************************************************************************/
FUNCTION GET_OBJECT_PRIVILEGES (
p_meta_data_id NUMBER,
p_table_owner VARCHAR2,
p_object_name VARCHAR2,
p_is_log_enabled BOOLEAN
)
RETURN tab_privs
AS
t_privs tab_privs;
BEGIN
DBMS_OUTPUT.PUT_LINE('----->>> START: GET_OBJECT_PRIVILEGES. meta_data_id: ' || p_meta_data_id || ', OWNER: ' || p_table_owner || ', OBJECT: ' || p_object_name);
IF p_is_log_enabled THEN
WRITE_TO_LOG ('DEBUG', p_meta_data_id, p_object_name, 'Getting privileges for ' || p_object_name);
END IF;
SELECT *
BULK COLLECT INTO t_privs
FROM ALL_TAB_PRIVS_MADE
WHERE OWNER = p_table_owner
AND TABLE_NAME = p_object_name;
DBMS_OUTPUT.PUT_LINE('----->>> END: GET_OBJECT_PRIVILEGES. meta_data_id: ' || p_meta_data_id || ', OWNER: ' || p_table_owner || ', OBJECT: ' || p_object_name);
RETURN t_privs;
END GET_OBJECT_PRIVILEGES;
/*********************************************************************************
NAME: Procedure - GRANT_OBJECT_PRIVILEGES
PURPOSE: This procedure grants all the privileges to the given object
Parameters:
p_meta_data_id Partition main table unique identifier
p_object_privs Collection of all privileges on main table or view got using GET_OBJECT_PRIVILEGES
p_object_name Object to which the privileges have to be granted
p_is_log_enabled Flag to log the activity
Ver Date Author Description
----- ---------- ------------- -----------------------
1 07/07/2020 AWS Professional Services Initial Version
**********************************************************************************/
PROCEDURE GRANT_OBJECT_PRIVILEGES (
p_meta_data_id NUMBER,
p_object_privs tab_privs,
p_object_name VARCHAR2,
p_is_log_enabled BOOLEAN
)
AS
v_sql VARCHAR2(2000);
BEGIN
DBMS_OUTPUT.PUT_LINE('----->>> START: GRANT_OBJECT_PRIVILEGES. meta_data_id: ' || p_meta_data_id || ', OBJECT: ' || p_object_name);
FOR i IN 1..p_object_privs.COUNT
LOOP
v_sql := 'GRANT ' || p_object_privs(i).PRIVILEGE || ' ON ' || p_object_privs(i).OWNER || '.' || p_object_name
|| ' TO ' || p_object_privs(i).GRANTEE;
IF p_is_log_enabled THEN
WRITE_TO_LOG ('DEBUG', p_meta_data_id, p_object_name, 'Granting privilege - SQL: ' || v_sql);
END IF;
EXECUTE IMMEDIATE v_sql;
END LOOP;
DBMS_OUTPUT.PUT_LINE('----->>> END: GRANT_OBJECT_PRIVILEGES. meta_data_id: ' || p_meta_data_id || ', OBJECT: ' || p_object_name);
END GRANT_OBJECT_PRIVILEGES;
/*********************************************************************************
NAME: Function - GET_SYNONYMS
PURPOSE: This function retrieves all the synonyms on the given object and returns the same as collection
Parameters:
p_meta_data_id Partition main table unique identifier
p_table_owner Owner of the object
p_object_name Object name for which the synonyms are to be retrieved
p_is_log_enabled Flag to log the activity
Ver Date Author Description
----- ---------- ------------- -----------------------
1 07/07/2020 AWS Professional Services Initial Version
**********************************************************************************/
FUNCTION GET_SYNONYMS (
p_meta_data_id NUMBER,
p_table_owner VARCHAR2,
p_object_name VARCHAR2,
p_is_log_enabled BOOLEAN
)
RETURN tab_synonyms
AS
t_synonyms tab_synonyms;
BEGIN
DBMS_OUTPUT.PUT_LINE('----->>> START: GET_SYNONYMS. meta_data_id: ' || p_meta_data_id || ', OWNER: ' || p_table_owner || ', OBJECT: ' || p_object_name);
IF p_is_log_enabled THEN
WRITE_TO_LOG ('DEBUG', p_meta_data_id, p_object_name, 'Getting Synonyms for: ' || p_object_name);
END IF;
SELECT *
BULK COLLECT INTO t_synonyms
FROM ALL_SYNONYMS
WHERE TABLE_OWNER = p_table_owner
AND TABLE_NAME = p_object_name;
DBMS_OUTPUT.PUT_LINE('----->>> END: GET_SYNONYMS. meta_data_id: ' || p_meta_data_id || ', OWNER: ' || p_table_owner || ', OBJECT: ' || p_object_name);
RETURN t_synonyms;
END GET_SYNONYMS;
/*********************************************************************************
NAME: Procedure - CREATE_SYNONYMS
PURPOSE: This procedure recreates all the synonyms using the data in p_object_synonyms collection
Parameters:
p_meta_data_id Partition main table unique identifier
p_object_synonyms Collection if synonyms
p_is_log_enabled Flag to log the activity
Ver Date Author Description
----- ---------- ------------- -----------------------
1 07/07/2020 AWS Professional Services Initial Version
**********************************************************************************/
PROCEDURE CREATE_SYNONYMS (
p_meta_data_id NUMBER,
p_object_synonyms tab_synonyms,
p_is_log_enabled BOOLEAN
)
AS
v_sql VARCHAR2(2000);
v_synonym VARCHAR2(64);
BEGIN
DBMS_OUTPUT.PUT_LINE('----->>> START: CREATE_SYNONYMS. meta_data_id: ' || p_meta_data_id);
FOR i IN 1..p_object_synonyms.COUNT
LOOP
v_synonym := p_object_synonyms(i).SYNONYM_NAME;
IF p_object_synonyms(i).OWNER = 'PUBLIC' THEN
v_sql := 'CREATE OR REPLACE PUBLIC SYNONYM ' || p_object_synonyms(i).SYNONYM_NAME
|| ' FOR ' || p_object_synonyms(i).TABLE_OWNER || '.' || p_object_synonyms(i).TABLE_NAME;
ELSE
v_sql := 'CREATE OR REPLACE SYNONYM ' || p_object_synonyms(i).SYNONYM_NAME
|| ' FOR ' || p_object_synonyms(i).TABLE_OWNER || '.' || p_object_synonyms(i).TABLE_NAME;
END IF;
IF p_is_log_enabled THEN
WRITE_TO_LOG ('DEBUG', p_meta_data_id, p_object_synonyms(i).TABLE_NAME, 'Creating Synonym: ' || v_sql);
END IF;
EXECUTE IMMEDIATE v_sql;
END LOOP;
DBMS_OUTPUT.PUT_LINE('----->>> END: CREATE_SYNONYMS. meta_data_id: ' || p_meta_data_id);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error while creating Synonym : ' || v_synonym);
DBMS_OUTPUT.PUT_LINE('SQLERRM : ' || SQLERRM);
WRITE_TO_LOG ('ERROR', p_meta_data_id, v_synonym, SQLERRM);
RAISE;
END CREATE_SYNONYMS;
/*********************************************************************************
NAME: Procedure - DISABLE_OLD_PARTITIONS
PURPOSE: This procedure updates the status to 'D' to all old partition tables
which are older than partitions to be retained
Parameters:
p_meta_data_id Partition main table unique identifier
p_table_owner Owner of the partition tables
p_partition_type Type of partition
p_num_retain_partitions Number of old partitions to be retained
p_is_log_enabled Flag to log the activity
Ver Date Author Description
----- ---------- ------------- -----------------------
1 07/07/2020 AWS Professional Services Initial Version
**********************************************************************************/
PROCEDURE DISABLE_OLD_PARTITIONS (
p_meta_data_id NUMBER,
p_table_owner VARCHAR2,
p_partition_type VARCHAR2,
p_num_retain_partitions NUMBER,
p_is_log_enabled BOOLEAN
)
AS
v_view_sql VARCHAR2(4000);
v_view_cnt NUMBER;
v_last_partition_dt DATE;
BEGIN
DBMS_OUTPUT.PUT_LINE('----->>> START: DISABLE_OLD_PARTITIONS. meta_data_id: ' || p_meta_data_id);
IF p_partition_type = 'Y' THEN
v_last_partition_dt := TRUNC(TO_DATE(EXTRACT(YEAR FROM SYSDATE) - p_num_retain_partitions, 'RRRR'), 'YEAR');
ELSIF p_partition_type = 'M' THEN
v_last_partition_dt := ADD_MONTHS(TRUNC(SYSDATE, 'MONTH'), -p_num_retain_partitions);
ELSIF p_partition_type = 'W' THEN
v_last_partition_dt := (TRUNC(SYSDATE, 'IW')-1) - (p_num_retain_partitions * 7);
ELSIF p_partition_type = 'D' THEN
v_last_partition_dt := TRUNC(SYSDATE) - p_num_retain_partitions;
END IF;
DBMS_OUTPUT.PUT_LINE('----->>> DISABLE_OLD_PARTITIONS. v_last_partition_dt: ' || v_last_partition_dt);
UPDATE PARTITION_TABLE_DATA
SET status = 'D'
WHERE meta_data_id = p_meta_data_id
AND status = 'A'
AND partition_date_value < v_last_partition_dt;
DBMS_OUTPUT.PUT_LINE('----->>> END: DISABLE_OLD_PARTITIONS. meta_data_id: ' || p_meta_data_id);
END DISABLE_OLD_PARTITIONS;
/*********************************************************************************
NAME: Procedure - DROP_OLD_PARTITIONS
PURPOSE: This procedure drops old partition tables with status = 'D'
and DROP_OLD_PARTITIONS flag is set to 'Y'
Status is updated with 'D' by function 'DISABLE_OLD_PARTITIONS'
for partitions which are older than partitions to be retained
Parameters:
p_meta_data_id Partition main table unique identifier
p_table_owner Owner of the partition tables
p_is_log_enabled Flag to log the activity
Ver Date Author Description
----- ---------- ------------- -----------------------
1 07/07/2020 AWS Professional Services Initial Version
**********************************************************************************/
PROCEDURE DROP_OLD_PARTITIONS (
p_meta_data_id NUMBER,
p_table_owner VARCHAR2,
p_is_log_enabled BOOLEAN
)
AS
v_drop_sql VARCHAR2(2000);
v_del_sql VARCHAR2(2000);
BEGIN
DBMS_OUTPUT.PUT_LINE('----->>> START: DROP_OLD_PARTITIONS. meta_data_id: ' || p_meta_data_id);
FOR rec IN (
SELECT partition_table_name
FROM PARTITION_TABLE_DATA
WHERE meta_data_id = p_meta_data_id
AND status = 'D'
)
LOOP
BEGIN
v_drop_sql := 'DROP TABLE ' || p_table_owner || '.' || rec.partition_table_name;
IF p_is_log_enabled THEN
WRITE_TO_LOG ('DEBUG', p_meta_data_id, rec.partition_table_name, 'Dropping partition table: ' || rec.partition_table_name);
END IF;
EXECUTE IMMEDIATE v_drop_sql;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error while dropping old partitions on table : ' || rec.partition_table_name);
DBMS_OUTPUT.PUT_LINE('SQLERRM : ' || SQLERRM);
WRITE_TO_LOG ('ERROR', p_meta_data_id, rec.partition_table_name, SQLERRM);
END;
BEGIN
v_del_sql := 'DELETE FROM PARTITION_TABLE_DATA WHERE meta_data_id = ' || p_meta_data_id || ' AND partition_table_name = ''' || rec.partition_table_name || '''';
IF p_is_log_enabled THEN
WRITE_TO_LOG ('DEBUG', p_meta_data_id, rec.partition_table_name, 'Delete record from PARTITION_TABLE_DATA for table: ' || rec.partition_table_name);
END IF;
EXECUTE IMMEDIATE v_del_sql;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error while dropping old partitions for META_DATA_ID: ' || p_meta_data_id);
DBMS_OUTPUT.PUT_LINE('SQLERRM : ' || SQLERRM);
WRITE_TO_LOG ('ERROR', p_meta_data_id, rec.partition_table_name, SQLERRM);
END;
END LOOP;
DBMS_OUTPUT.PUT_LINE('----->>> END: DROP_OLD_PARTITIONS. meta_data_id: ' || p_meta_data_id);
END DROP_OLD_PARTITIONS;
/*********************************************************************************
NAME: Procedure - CREATE_DEL_TRG
PURPOSE: This procedure creates INSTEAD OF DELETE trigger on the given view
Parameters:
p_meta_data_id Partition main table unique identifier
p_table_owner Owner of the partition table
p_table_name Partition main table name
p_partition_column The column used for partitioning
p_pkey_column The primary key column of the main partition table
p_view_name Name of the view for which the triggers are to be retrieved
p_partition_type Type of partition (Y/M/W/D)
p_is_log_enabled Flag to log the activity
Ver Date Author Description
----- ---------- ------------- -----------------------
1 08/03/2020 AWS Professional Services Initial Version
**********************************************************************************/
PROCEDURE CREATE_DEL_TRG (
p_meta_data_id NUMBER
, p_table_owner VARCHAR2
, p_table_name VARCHAR2
, p_partition_column VARCHAR2
, p_pkey_column VARCHAR2
, p_view_name VARCHAR2
, p_partition_type VARCHAR2
, p_is_log_enabled BOOLEAN
)
AS
trg_head VARCHAR2(2000);
del_stmt VARCHAR2(4000);
del_def_stmt VARCHAR2(4000);
stmt VARCHAR2(8000);
exp_sql VARCHAR2(8000);
part_dt_fmt VARCHAR2(10);
BEGIN
DBMS_OUTPUT.PUT_LINE('----->>> START: Creating INSTEAD OF DELETE trigger on: ' || p_view_name);
IF p_is_log_enabled THEN
WRITE_TO_LOG ('DEBUG', p_meta_data_id, p_view_name, 'Creating INSTEAD OF DELETE trigger');
END IF;
trg_head := 'CREATE OR REPLACE TRIGGER TRG_' || p_view_name || '_DEL INSTEAD OF DELETE ON ' || p_view_name || ' FOR EACH ROW ' || CHR(10) ||
' DECLARE ' || CHR(10) ||
' v_part_val VARCHAR2(32); ' || CHR(10) ||
' v_part_table VARCHAR2(64); ' || CHR(10) ||
' table_not_found EXCEPTION; ' || CHR(10) ||
' PRAGMA EXCEPTION_INIT (table_not_found, -942); ' || CHR(10) ||
' BEGIN ' || CHR(10) || CHR(10);
IF p_partition_type = 'Y' THEN
part_dt_fmt := 'RRRR';
ELSIF p_partition_type = 'M' THEN
part_dt_fmt := 'RRRRMM';
ELSIF p_partition_type = 'W' THEN
part_dt_fmt := 'RRRRMMDD';
ELSIF p_partition_type = 'D' THEN
part_dt_fmt := 'RRRRMMDD';
END IF;
IF p_partition_type = 'W' THEN
trg_head := trg_head ||
' v_part_val := TO_CHAR(TRUNC(:OLD.' || p_partition_column || ', ''IW'') - 1,''' || part_dt_fmt || '''); ' || CHR(10) ||
' v_part_table := ''' || p_table_name || '_'' || v_part_val; ' || CHR(10) || CHR(10);
ELSE
trg_head := trg_head ||
' v_part_val := TO_CHAR(:OLD.' || p_partition_column || ',''' || part_dt_fmt || '''); ' || CHR(10) ||
' v_part_table := ''' || p_table_name || '_'' || v_part_val; ' || CHR(10) || CHR(10);
END IF;
del_stmt := ' EXECUTE IMMEDIATE '' DELETE FROM ' || p_table_owner || '.'' || v_part_table || '' WHERE ' || p_pkey_column || ' = :' || p_pkey_column || '''' ||
' USING :OLD.' || p_pkey_column || ';';
del_def_stmt := ' EXECUTE IMMEDIATE '' DELETE FROM ' || p_table_owner || '.' || p_table_name || '_DEFAULT WHERE ' || p_pkey_column || ' = :' || p_pkey_column || '''' ||
' USING :OLD.' || p_pkey_column || ';';
exp_sql := CHR(10) || ' EXCEPTION ' || CHR(10) ||
' WHEN table_not_found THEN ' || CHR(10) ||
' ' || del_def_stmt;
stmt := trg_head || del_stmt || CHR(10) || exp_sql || CHR(10) || ' END;' ;
EXECUTE IMMEDIATE stmt;
-- DBMS_OUTPUT.PUT_LINE ( stmt);
IF p_is_log_enabled THEN
WRITE_TO_LOG ('DEBUG', p_meta_data_id, p_view_name, 'Creating INSTEAD OF DELETE trigger - Successful');
END IF;
DBMS_OUTPUT.PUT_LINE('----->>> END: Creating INSTEAD OF DELETE trigger on: ' || p_view_name);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error while creating DELETE trigger on: ' || p_view_name);
DBMS_OUTPUT.PUT_LINE('SQLERRM : ' || SQLERRM);
WRITE_TO_LOG ('ERROR', p_meta_data_id, p_view_name, SQLERRM);
END;
/*********************************************************************************
NAME: Procedure - CREATE_INS_TRG
PURPOSE: This procedure creates INSTEAD OF INSERT trigger on the given view
Parameters:
p_meta_data_id Partition main table unique identifier
p_table_owner Owner of the partition table
p_table_name Partition main table name
p_partition_column The column used for partitioning
p_view_name Name of the view for which the triggers are to be retrieved
p_partition_type Type of partition (Y/M/W/D)
p_is_log_enabled Flag to log the activity
Ver Date Author Description
----- ---------- ------------- -----------------------
1 08/03/2020 AWS Professional Services Initial Version
**********************************************************************************/
PROCEDURE CREATE_INS_TRG(
p_meta_data_id NUMBER
, p_table_owner VARCHAR2
, p_table_name VARCHAR2
, p_partition_column VARCHAR2
, p_view_name VARCHAR2
, p_partition_type VARCHAR2
, p_is_log_enabled BOOLEAN
)
AS
trg_head VARCHAR2(2000);
ins_cols_def VARCHAR2(4000);
ins_cols VARCHAR2(4000);
i_cols VARCHAR2(4000);
ins_vals VARCHAR2(4000);
exe_stmt VARCHAR2(4000);
stmt VARCHAR2(8000);
exp_sql VARCHAR2(8000);
part_dt_fmt VARCHAR2(10);
BEGIN
DBMS_OUTPUT.PUT_LINE('----->>> START: Creating INSTEAD OF INSERT trigger on: ' || p_view_name);
IF p_is_log_enabled THEN
WRITE_TO_LOG ('DEBUG', p_meta_data_id, p_view_name, 'Creating INSTEAD OF INSERT trigger');
END IF;
trg_head := 'CREATE OR REPLACE TRIGGER TRG_' || p_view_name || '_INS INSTEAD OF INSERT ON ' || p_view_name || ' FOR EACH ROW ' || CHR(10) ||
' DECLARE ' || CHR(10) ||
' v_part_val VARCHAR2(32); ' || CHR(10) ||
' v_part_table VARCHAR2(64); ' || CHR(10) ||
' table_not_found EXCEPTION; ' || CHR(10) ||
' PRAGMA EXCEPTION_INIT (table_not_found, -942); ' || CHR(10) ||
' BEGIN ' || CHR(10) || CHR(10);
IF p_partition_type = 'Y' THEN
part_dt_fmt := 'RRRR';
ELSIF p_partition_type = 'M' THEN
part_dt_fmt := 'RRRRMM';
ELSIF p_partition_type = 'W' THEN
part_dt_fmt := 'RRRRMMDD';
ELSIF p_partition_type = 'D' THEN
part_dt_fmt := 'RRRRMMDD';
END IF;
IF p_partition_type = 'W' THEN
trg_head := trg_head ||
' v_part_val := TO_CHAR(TRUNC(:NEW.' || p_partition_column || ', ''IW'') - 1,''' || part_dt_fmt || '''); ' || CHR(10) ||
' v_part_table := ''' || p_table_name || '_'' || v_part_val; ' || CHR(10) || CHR(10);
ELSE
trg_head := trg_head ||
' v_part_val := TO_CHAR(:NEW.' || p_partition_column || ',''' || part_dt_fmt || '''); ' || CHR(10) ||
' v_part_table := ''' || p_table_name || '_'' || v_part_val; ' || CHR(10) || CHR(10);
END IF;
ins_cols := ''' INSERT INTO ' || p_table_owner || '.'' || v_part_table || '' VALUES ( '|| CHR(10);
ins_cols_def := ''' INSERT INTO ' || p_table_owner || '.' || p_table_name || '_DEFAULT VALUES ( '|| CHR(10);
ins_vals := ' USING ';
i_cols := '';
FOR rec in ( SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS WHERE OWNER = UPPER(p_table_owner) and table_name = UPPER(p_table_name) ORDER BY COLUMN_ID )
LOOP
i_cols := i_cols || ' :' || rec.column_name || ' ,';
ins_vals := ins_vals || ' :NEW.' || rec.column_name || ' ,';
END LOOP;
i_cols := SUBSTR(i_cols, 0, length(i_cols)-2);
ins_cols := ins_cols || i_cols || ')''' || CHR(10);
-- DBMS_OUTPUT.PUT_LINE ('ins_cols: ' || ins_cols );
ins_cols_def := ins_cols_def || i_cols || ')''' || CHR(10);
-- DBMS_OUTPUT.PUT_LINE ('ins_cols_def: ' || ins_cols_def);
ins_vals := SUBSTR(ins_vals, 0, length(ins_vals)-2);
exe_stmt := CHR(10) || ' EXECUTE IMMEDIATE ' || ins_cols || ins_vals || ';';
exp_sql := CHR(10) || ' EXCEPTION ' || CHR(10) ||
' WHEN table_not_found THEN ' || CHR(10) ||
' EXECUTE IMMEDIATE ' || ins_cols_def || ins_vals || ';';
stmt := trg_head || exe_stmt || CHR(10) || exp_sql || CHR(10) || ' END;' ;
execute IMMEDIATE stmt;
-- DBMS_OUTPUT.PUT_LINE ( stmt);
IF p_is_log_enabled THEN
WRITE_TO_LOG ('DEBUG', p_meta_data_id, p_view_name, 'Creating INSTEAD OF INSERT trigger - Successful');
END IF;
DBMS_OUTPUT.PUT_LINE('----->>> END: Creating INSTEAD OF INSERT trigger on: ' || p_view_name);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error while creating INSERT trigger on: ' || p_view_name);
DBMS_OUTPUT.PUT_LINE('SQLERRM : ' || SQLERRM);
WRITE_TO_LOG ('ERROR', p_meta_data_id, p_view_name, SQLERRM);
END;
/*********************************************************************************
NAME: Procedure - CREATE_UPD_TRG
PURPOSE: This procedure creates INSTEAD OF UPDATE trigger on the given view
Parameters:
p_meta_data_id Partition main table unique identifier
p_table_owner Owner of the partition table
p_table_name Partition main table name
p_partition_column The column used for partitioning
p_pkey_column The primary key column of the main partition table
p_view_name Name of the view for which the triggers are to be retrieved
p_partition_type Type of partition (Y/M/W/D)
p_is_log_enabled Flag to log the activity
Ver Date Author Description
----- ---------- ------------- -----------------------
1 08/03/2020 AWS Professional Services Initial Version
**********************************************************************************/
PROCEDURE CREATE_UPD_TRG(
p_meta_data_id NUMBER
, p_table_owner VARCHAR2
, p_table_name VARCHAR2
, p_partition_column VARCHAR2
, p_pkey_column VARCHAR2
, p_view_name VARCHAR2
, p_partition_type VARCHAR2
, p_is_log_enabled BOOLEAN
)
AS
trg_head VARCHAR2(2000);
trg_code VARCHAR2(24000);
col_name VARCHAR2(64);
col_type VARCHAR2(32);
rid_sql VARCHAR2(4000);
exe_stmt VARCHAR2(4000);
stmt VARCHAR2(8000);
upd_cols VARCHAR2(30000);
v_upd_sql VARCHAR2(30000);
exp_sql VARCHAR2(8000);
part_dt_fmt VARCHAR2(10);
RID VARCHAR2(10) := 'RID';
BEGIN
DBMS_OUTPUT.PUT_LINE('----->>> START: Creating INSTEAD OF UPDATE trigger on: ' || p_view_name);
IF p_is_log_enabled THEN
WRITE_TO_LOG ('DEBUG', p_meta_data_id, p_view_name, 'Creating INSTEAD OF UPDATE trigger');
END IF;
trg_head := 'CREATE OR REPLACE TRIGGER TRG_' || p_view_name || '_UPD INSTEAD OF UPDATE ON ' || p_view_name || ' FOR EACH ROW ' || CHR(10) ||
' DECLARE ' || CHR(10) ||
' v_part_val VARCHAR2(32); ' || CHR(10) ||
' v_part_table VARCHAR2(64); ' || CHR(10) ||
' v_upd_sql VARCHAR2(8000); ' || CHR(10) ||
' v_rowid_sql VARCHAR2(2000); ' || CHR(10) ||
' rid ROWID; ' || CHR(10) ||
' table_not_found EXCEPTION; ' || CHR(10) ||
' PRAGMA EXCEPTION_INIT (table_not_found, -942); ' || CHR(10) ||
' BEGIN ' || CHR(10) || CHR(10);
IF p_partition_type = 'Y' THEN
part_dt_fmt := 'RRRR';
ELSIF p_partition_type = 'M' THEN
part_dt_fmt := 'RRRRMM';
ELSIF p_partition_type = 'W' THEN
part_dt_fmt := 'RRRRMMDD';
ELSIF p_partition_type = 'D' THEN
part_dt_fmt := 'RRRRMMDD';
END IF;
IF p_partition_type = 'W' THEN
trg_head := trg_head ||
' v_part_val := TO_CHAR(TRUNC(:OLD.' || p_partition_column || ', ''IW'') - 1,''' || part_dt_fmt || '''); ' || CHR(10) ||
' v_part_table := ''' || p_table_name || '_'' || v_part_val; ' || CHR(10) || CHR(10);
ELSE
trg_head := trg_head ||
' v_part_val := TO_CHAR(:OLD.' || p_partition_column || ',''' || part_dt_fmt || '''); ' || CHR(10) ||
' v_part_table := ''' || p_table_name || '_'' || v_part_val; ' || CHR(10) || CHR(10);
END IF;
rid_sql := ' BEGIN ' || CHR(10) || CHR(10);
rid_sql := rid_sql || ' v_rowid_sql := '' SELECT ROWID FROM '' || v_part_table || '' WHERE ' || p_pkey_column || ' = '' || :OLD.' || p_pkey_column || ';' || CHR(10);
rid_sql := rid_sql || ' EXECUTE IMMEDIATE v_rowid_sql INTO rid;' || CHR(10) || CHR(10);
rid_sql := rid_sql || ' EXCEPTION ' || CHR(10);
rid_sql := rid_sql || ' WHEN table_not_found THEN ' || CHR(10);
rid_sql := rid_sql || ' v_part_table := ''' || p_table_name || '_DEFAULT '';' || CHR(10);
rid_sql := rid_sql || ' v_rowid_sql := '' SELECT ROWID FROM '' || v_part_table || '' WHERE ' || p_pkey_column || ' = '' || :OLD.' || p_pkey_column || ';' || CHR(10);
rid_sql := rid_sql || ' EXECUTE IMMEDIATE v_rowid_sql INTO rid;' || CHR(10) || CHR(10);
rid_sql := rid_sql || ' END;' || CHR(10) || CHR(10);
-- DBMS_OUTPUT.PUT_LINE ('rid_sql: ' || rid_sql );
upd_cols := 'IF :OLD.' || p_partition_column || ' <> :NEW.' || p_partition_column || ' THEN '|| CHR(10);
upd_cols := upd_cols || ' RAISE_APPLICATION_ERROR(-20001, ''Partition key column ''''' || p_partition_column || ''''' cannot be modified'');'|| CHR(10);
upd_cols := upd_cols || ' END IF; ' || CHR(10) || CHR(10);
FOR rec in (
SELECT OWNER, TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM all_tab_columns
WHERE owner = UPPER(p_table_owner) and table_name = UPPER(p_table_name)
ORDER BY COLUMN_ID
)
LOOP
col_name := rec.COLUMN_NAME;
col_type := rec.DATA_TYPE;
upd_cols := upd_cols || 'IF :OLD.' || col_name || ' <> :NEW.' || col_name || ' THEN ' || CHR(10);
IF col_type = 'NUMBER' THEN
upd_cols := upd_cols || ' v_upd_sql := v_upd_sql || '', ' || col_name || ' = '' || :NEW.' || col_name || ';' || CHR(10);
ELSIF col_type IN ('VARCHAR2', 'VARCHAR', 'CHAR', 'NVARCHAR2', 'NCHAR') THEN
upd_cols := upd_cols || ' v_upd_sql := v_upd_sql || '', ' || col_name || ' = '''''' || :NEW.' || col_name || ' || '''''''';' || CHR(10);
ELSIF col_type IN ('DATE', 'TIMESTAMP') THEN
upd_cols := upd_cols || ' v_upd_sql := v_upd_sql || '', ' || col_name ||
' = TO_DATE('''''' || TO_CHAR(:NEW.' || col_name || ', ''YYYYMMDD HH24:MI:SS.SSSSS'') || '''''',''''YYYYMMDD HH24:MI:SS.SSSSS'''')'';' || CHR(10) ;
END IF;
upd_cols := upd_cols || ' END IF; ' || CHR(10) || CHR(10);
END LOOP;
-- DBMS_OUTPUT.PUT_LINE ('upd_cols: ' || upd_cols );
trg_code := ' v_upd_sql := '' UPDATE '' || v_part_table || '' SET '' || SUBSTR(v_upd_sql,2) || '' WHERE ROWID = '''''' || rid || ''''''''; ' || CHR(10);
trg_code := trg_code || ' EXECUTE IMMEDIATE v_upd_sql;' || CHR(10);
trg_code := rid_sql || upd_cols || trg_code || CHR(10);
-- DBMS_OUTPUT.PUT_LINE ('trg_code: ' || trg_code );
stmt := trg_head || CHR(10) || trg_code || CHR(10) || ' END;' ;
execute IMMEDIATE stmt;
-- DBMS_OUTPUT.PUT_LINE ( stmt);
IF p_is_log_enabled THEN
WRITE_TO_LOG ('DEBUG', p_meta_data_id, p_view_name, 'Creating INSTEAD OF UPDATE trigger - Successful');
END IF;
DBMS_OUTPUT.PUT_LINE('----->>> END: Creating INSTEAD OF UPDATE trigger on: ' || p_view_name);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error while creating UPDATE trigger on: ' || p_view_name);
DBMS_OUTPUT.PUT_LINE('SQLERRM : ' || SQLERRM);
WRITE_TO_LOG ('ERROR', p_meta_data_id, p_view_name, SQLERRM);
END;
/*********************************************************************************
NAME: Procedure - RECREATE_VIEW
PURPOSE: This procedure re-creates the view to include new partition tables and
exclude old partition tables
The activities performed:
1. Get all privileges on the view
2. Get synonyms on the view
3. Recreates the view to include new partition tables and exclude old partition tables
4. Grant all privileges on the view
5. Creates INSERT/UPDATE/DELETE triggers on the view
6. Create synonyms on the view
Parameters:
p_meta_data_id Partition main table unique identifier
p_table_owner Owner of the partition tables
p_default_table_name Default table used to have records that does not fall into any partition
p_view_name Name of the view to recreate
p_is_log_enabled Flag to log the activity
Ver Date Author Description
----- ---------- ------------- -----------------------
1 07/07/2020 AWS Professional Services Initial Version
**********************************************************************************/
PROCEDURE RECREATE_VIEW (
p_meta_data_id NUMBER,
p_table_owner VARCHAR2,
p_table_name VARCHAR2,
p_default_table_name VARCHAR2,
p_partition_column_name VARCHAR2,
p_primarykey_column_name VARCHAR2,
p_partition_type VARCHAR2,
p_view_name VARCHAR2,
p_is_log_enabled BOOLEAN
)
AS
v_view_sql VARCHAR2(8000);
t_vw_privs tab_privs;
t_trig_code tab_trigs;
t_synonyms tab_synonyms;
BEGIN
DBMS_OUTPUT.PUT_LINE('----->>> START: RECREATE_VIEW. meta_data_id: ' || p_meta_data_id || ', VIEW: ' || p_view_name);
IF p_is_log_enabled THEN
WRITE_TO_LOG ('DEBUG', p_meta_data_id, p_view_name, 'Recreating view: ' || p_view_name);
END IF;
/*** Get privileges on view before recreating ***/
t_vw_privs := GET_OBJECT_PRIVILEGES (p_meta_data_id, p_table_owner, p_view_name, p_is_log_enabled);
/*** Get Synonyms on view before recreating ***/
t_synonyms := GET_SYNONYMS (p_meta_data_id, p_table_owner, p_view_name, p_is_log_enabled);
/*** Build the view query for all partition tables that are in Active state ***/
SELECT listagg(pmd.TABLE_OWNER || '.' || ptd.PARTITION_TABLE_NAME,' UNION ALL SELECT * FROM ') within group(order by ptd.PARTITION_TABLE_NAME)
INTO v_view_sql
FROM PARTITION_META_DATA pmd
JOIN PARTITION_TABLE_DATA ptd ON ptd.META_DATA_ID = pmd.META_DATA_ID
WHERE pmd.META_DATA_ID = p_meta_data_id
AND ptd.STATUS = 'A';
v_view_sql := 'CREATE OR REPLACE VIEW ' || p_table_owner || '.' || p_view_name
|| ' AS '
|| 'SELECT * FROM ' || p_table_owner || '.' || p_default_table_name || ' UNION ALL '
|| 'SELECT * FROM ' || v_view_sql;
-- DBMS_OUTPUT.PUT_LINE(v_view_sql);
EXECUTE IMMEDIATE v_view_sql;
/*** Grant all privileges on view after recreating ***/
GRANT_OBJECT_PRIVILEGES (p_meta_data_id, t_vw_privs, p_view_name, p_is_log_enabled);
/*** Create insert trigger ***/
create_ins_trg(p_meta_data_id, p_table_owner, p_table_name, p_partition_column_name, p_view_name, p_partition_type, p_is_log_enabled);
/*** Create delete trigger ***/
create_del_trg(p_meta_data_id, p_table_owner, p_table_name, p_partition_column_name, p_primarykey_column_name, p_view_name, p_partition_type, p_is_log_enabled);
/*** Create update trigger ***/
create_upd_trg(p_meta_data_id, p_table_owner, p_table_name, p_partition_column_name, p_primarykey_column_name, p_view_name, p_partition_type, p_is_log_enabled);
/*** Create all Synonyms on view after recreating ***/
CREATE_SYNONYMS(p_meta_data_id, t_synonyms, p_is_log_enabled);
IF p_is_log_enabled THEN
WRITE_TO_LOG ('DEBUG', p_meta_data_id, p_view_name, 'Recreating view: ' || p_view_name || ' Completed');
END IF;
DBMS_OUTPUT.PUT_LINE('----->>> END: RECREATE_VIEW. meta_data_id: ' || p_meta_data_id || ', VIEW: ' || p_view_name);
END RECREATE_VIEW;
/*********************************************************************************
NAME: Procedure - MAIN_PROCESS
PURPOSE: This is the main procedure that is to be called to run the automation process
The activities performed:
1. Get info of all partition tables for which AUTO_MANAGE_PARTITIONS flag is set to 'Y'
2. For each of these partitions get the recent partition table date
3. Based on the partition type Y/M/W/D and NUM_PRECREATE_PARTITIONS identify new partition tables to be created
4. Get all the privileges on partition main table
5. Loop to create new partition tables
6. Identify the new partition table name
7. Get the partition main table DDL
8. Replace the main table name with new partition table name
9. Replace the constraints, indexes names using main table short name and partition date value
10. Create the new partition table
11. After all partition tables are created, disable old partitions which are older than partitions to be retained
12. Recreate the view with new partition tables
13. Drop old partition tables if DROP_OLD_PARTITIONS is set to 'Y'
Parameters:
p_meta_data_id Partition main table unique identifier,
If provided the automation is run only on the given table
If not provided (default NULL) the automation is run on all the partition tables
Ver Date Author Description
----- ---------- ------------- -----------------------
1 07/07/2020 AWS Professional Services Initial Version
**********************************************************************************/
PROCEDURE MAIN_PROCESS (p_meta_data_id NUMBER DEFAULT NULL)
AS
t_tab_privs tab_privs;
t_trig_code tab_trigs;
v_meta_data_id NUMBER;
v_table_owner VARCHAR2(32);
v_table_name VARCHAR2(64);
v_table_alias VARCHAR2(8);
v_view_name VARCHAR2(64);
v_partition_column_name VARCHAR2(64);
v_primaykey_column_name VARCHAR2(64);
v_partition_type CHAR(1);
v_num_partitions NUMBER;
v_num_retain_partitions NUMBER;
v_enable_logging CHAR(1);
v_is_log_enabled BOOLEAN;
v_prev_partition_date_val DATE;
v_prev_partition_num_val NUMBER;
v_new_partition_table_name VARCHAR2(64);
v_new_partition_short_name VARCHAR2(64);
v_new_partition_char_val VARCHAR2(32);
v_start_dt DATE;
v_end_dt DATE;
v_part_char_fmt VARCHAR2(8);
v_ddl_sql VARCHAR2(8000);
v_new_partition_created CHAR(1);
v_drop_old_partitions CHAR(1);
CURSOR c_part IS
SELECT META_DATA_ID, TABLE_OWNER, TABLE_NAME, TABLE_ALIAS
, PARTITION_COLUMN_NAME, PRIMARYKEY_COLUMN_NAME, VIEW_NAME
, PARTITION_TYPE, NUM_PRECREATE_PARTITIONS, DROP_OLD_PARTITIONS
, NUM_RETAIN_PARTITIONS, ENABLE_LOGGING
FROM PARTITION_META_DATA
WHERE AUTO_MANAGE_PARTITIONS = 'Y'
AND meta_data_id = NVL(p_meta_data_id, meta_data_id);
BEGIN
DBMS_OUTPUT.PUT_LINE('----->>> START: main_process');
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SEGMENT_ATTRIBUTES', false);
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'STORAGE', false);
OPEN c_part;
LOOP
BEGIN
FETCH c_part INTO
v_meta_data_id, v_table_owner, v_table_name, v_table_alias
, v_partition_column_name, v_primaykey_column_name, v_view_name
, v_partition_type, v_num_partitions, v_drop_old_partitions
, v_num_retain_partitions, v_enable_logging;
-- DBMS_OUTPUT.PUT_LINE('----->>> partition_table_name: ' || v_table_name);
EXIT WHEN c_part%NOTFOUND;
IF v_enable_logging = 'Y' THEN
v_is_log_enabled := TRUE;
ELSE
v_is_log_enabled := FALSE;
END IF;
IF v_is_log_enabled THEN
WRITE_TO_LOG ('DEBUG', v_meta_data_id, UPPER(v_table_name), 'START process for ' || v_table_owner || '.' || v_table_name);
END IF;
SELECT NVL(MAX(PARTITION_DATE_VALUE),
(CASE v_partition_type
WHEN 'Y' THEN TRUNC(TO_DATE(EXTRACT(YEAR FROM SYSDATE)-1, 'RRRR'), 'YEAR')
WHEN 'M' THEN ADD_MONTHS(TRUNC(SYSDATE, 'MONTH'), -1)
WHEN 'W' THEN TRUNC(SYSDATE, 'IW') - (7-1)
ELSE SYSDATE - 1
END)
)
INTO v_prev_partition_date_val
FROM PARTITION_TABLE_DATA
WHERE META_DATA_ID = v_meta_data_id;
DBMS_OUTPUT.PUT_LINE('----->>> prev_partition_date_val: ' || v_prev_partition_date_val);
IF v_partition_type = 'Y' THEN
v_part_char_fmt := 'RRRR';
v_start_dt := TRUNC(TO_DATE(EXTRACT(YEAR FROM v_prev_partition_date_val)+1, 'RRRR'), 'YEAR');
v_end_dt := TRUNC(TO_DATE(TO_NUMBER(TO_CHAR(SYSDATE, 'RRRR')) + v_num_partitions, 'RRRR'), 'YEAR');
ELSIF v_partition_type = 'M' THEN
v_part_char_fmt := 'RRRRMM';
v_start_dt := ADD_MONTHS(TRUNC(v_prev_partition_date_val, 'MONTH'), 1);
v_end_dt := ADD_MONTHS(TRUNC(SYSDATE, 'MONTH'), v_num_partitions);
ELSIF v_partition_type = 'W' THEN
v_part_char_fmt := 'RRRRMMDD';
v_start_dt := TRUNC(NEXT_DAY(v_prev_partition_date_val, 'SUN'));
v_end_dt := TRUNC(SYSDATE, 'IW') + (v_num_partitions * 7);
ELSIF v_partition_type = 'D' THEN
v_part_char_fmt := 'RRRRMMDD';
v_start_dt := TRUNC(v_prev_partition_date_val) + 1;
v_end_dt := TRUNC(SYSDATE + v_num_partitions);
END IF;
DBMS_OUTPUT.PUT_LINE('----->>> start_dt: ' || v_start_dt || ', end_dt: ' || v_end_dt);
v_new_partition_created := 'N';
/******
If new partition table is to be created get the privileges of main table
Grant the same privileges on the newly created partition table
******/
IF v_start_dt <= v_end_dt THEN
t_tab_privs := GET_OBJECT_PRIVILEGES (v_meta_data_id, UPPER(v_table_owner), UPPER(v_table_name), v_is_log_enabled);
END IF;
WHILE v_start_dt <= v_end_dt
LOOP
/*****
This flag is to identify if any new partition table is created.
This will be used to recreate the view to include new partition table
*****/
v_new_partition_created := 'Y';
v_new_partition_char_val := to_char(v_start_dt, v_part_char_fmt);
v_new_partition_table_name := UPPER(v_table_name || '_' || v_new_partition_char_val);
v_new_partition_short_name := UPPER(v_table_alias || '_' || v_new_partition_char_val);
SELECT DBMS_METADATA.GET_DDL('TABLE', UPPER(v_table_name), UPPER(v_table_owner))
INTO v_ddl_sql
FROM DUAL;
IF v_is_log_enabled THEN
WRITE_TO_LOG ('DEBUG', v_meta_data_id, v_new_partition_table_name, 'Creating partition table: ' || v_new_partition_table_name);
END IF;
v_ddl_sql := REGEXP_REPLACE (v_ddl_sql, v_table_name, v_new_partition_table_name, 1, 1, 'i');
v_ddl_sql := REPLACE (v_ddl_sql, v_table_alias, v_new_partition_short_name);
v_ddl_sql := substr(v_ddl_sql, 1, length(v_ddl_sql)-2);
EXECUTE IMMEDIATE v_ddl_sql;
-- DBMS_OUTPUT.PUT_LINE('----->>> creating: ' || new_partition_table_name);
INSERT INTO PARTITION_TABLE_DATA(META_DATA_ID, PARTITION_TABLE_NAME, PARTITION_DATE_VALUE, CRT_DT, STATUS)
VALUES (v_meta_data_id, UPPER(v_new_partition_table_name), v_start_dt, SYSDATE, 'A');
/******
Grant the main table privileges on the newly created partition table
******/
GRANT_OBJECT_PRIVILEGES (v_meta_data_id, t_tab_privs, v_new_partition_table_name, v_is_log_enabled);
IF v_partition_type = 'Y' THEN
v_start_dt := TRUNC(TO_DATE(EXTRACT(YEAR FROM v_start_dt) + 1, 'RRRR'), 'YEAR');
ELSIF v_partition_type = 'M' THEN
v_start_dt := ADD_MONTHS(v_start_dt, 1);
ELSIF v_partition_type = 'W' THEN
v_start_dt := NEXT_DAY(v_start_dt, 'SUN');
ELSIF v_partition_type = 'D' THEN
v_start_dt := TRUNC(v_start_dt) + 1;
END IF;
END LOOP;
/*****
If any new partition table is created the we have to recreate the view to include new partition table(s)
Get privileges and triggers on view before recreating
After recreating the view grant all privileges and create the triggers
*****/
IF v_new_partition_created = 'Y' THEN
/******
If the old partition tables are older than retain partitions count, then mark them as 'Delete'
******/
DISABLE_OLD_PARTITIONS (v_meta_data_id, v_table_owner, v_partition_type, v_num_retain_partitions, v_is_log_enabled);
/*** Recreate view with new partition table(s) ***/
RECREATE_VIEW (v_meta_data_id, v_table_owner, v_table_name, v_table_name || '_DEFAULT'
, v_partition_column_name, v_primaykey_column_name, v_partition_type
, v_view_name, v_is_log_enabled);
/******
If the drop old partitions flag is Yes, then drop old partitions where status is 'Delete'
******/
IF v_drop_old_partitions = 'Y' THEN
DROP_OLD_PARTITIONS (v_meta_data_id, v_table_owner, v_is_log_enabled);
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error in procedure MAIN_PROCESS while processing table: ' || v_table_name);
DBMS_OUTPUT.PUT_LINE('SQLERRM : ' || SQLERRM);
WRITE_TO_LOG ('ERROR', v_meta_data_id, v_table_name, SQLERRM);
END;
END LOOP;
CLOSE c_part;
DBMS_OUTPUT.PUT_LINE('----->>> END: main_process');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error in procedure MAIN_PROCESS while processing table: ' || v_table_name);
DBMS_OUTPUT.PUT_LINE('SQLERRM : ' || SQLERRM);
WRITE_TO_LOG ('ERROR', v_meta_data_id, v_table_name, SQLERRM);
RAISE;
END;
/*********************************************************************************
NAME: Procedure - SETUP_PARTITIONS
PURPOSE: This procedure creates the meta data entry and all required partitions management tasks.
This should be called only once when partition management is initialled.
The activities performed:
1. Checks if given table is already part of partition management. If yes, raises the error else continues with below activities
2. Insert record into PARTITION_META_DATA, which is required for managing the partitions
3. Create a default table '<main_table>_DEFAULT', this table used as overflow table to store records that do not fit into any partitions
4. Creates partition tables from the given start date (parameter: p_start_date)
5. Creates view with the given name (parameter: p_view_name) based on all required partition tables
6. Creates INSERT/UPDATE/DELETE triggers on view
Parameters:
p_table_owner Owner of the table
p_table_name Table to be partitioned
p_table_alias Short name of the partition table
p_partition_column Name of the column to be used for partitioning
p_pkey_column Name of primary key column
p_view_name Name of the view to be created
p_partition_type Type of partition (Y/M/W/D)
p_start_date Date from which partitions have to be created
p_precreate_partitions No. of future partitions to be created from current date
p_retain_partitions Number indicating how many old partitions to be retained
p_manage_partitions Flag to auto manage partitions
p_drop_old_partitions Flag to indicate if old partitions to be dropped
p_enable_logging Flag to log the activity
Ver Date Author Description
----- ---------- ------------- -----------------------
1 08/28/2020 AWS Professional Services Initial Version
**********************************************************************************/
PROCEDURE SETUP_PARTITIONS (
p_table_owner VARCHAR2
, p_table_name VARCHAR2
, p_table_alias VARCHAR2
, p_partition_column VARCHAR2
, p_pkey_column VARCHAR2
, p_view_name VARCHAR2
, p_partition_type VARCHAR2
, p_start_date VARCHAR2
, p_precreate_partitions NUMBER
, p_retain_partitions NUMBER
, p_manage_partitions VARCHAR2
, p_drop_old_partitions VARCHAR2
, p_enable_logging VARCHAR2
)
AS
v_meta_data_id NUMBER;
t_tab_privs tab_privs;
v_part_dt_fmt VARCHAR2(10);
v_is_log_enabled BOOLEAN;
v_table_name VARCHAR2(64);
v_table_alias VARCHAR2(32);
v_ddl_sql VARCHAR(8000);
v_start_dt DATE;
v_partition_char_val VARCHAR2(10);
v_part_char_fmt VARCHAR2(10);
v_cnt NUMBER;
v_start_date DATE;
BEGIN
DBMS_OUTPUT.PUT_LINE('----->>> START: setup_partitions p_table_name: ' || p_table_name);
SELECT COUNT(1) INTO v_cnt
FROM PARTITION_META_DATA
WHERE TABLE_OWNER = UPPER(p_table_owner)
AND TABLE_NAME = UPPER(p_table_name);
IF v_cnt > 0 THEN
RAISE_APPLICATION_ERROR(-20001, 'The table ' || p_table_owner || '.' || p_table_name || ' is already part of partition management');
END IF;
v_start_date := TO_DATE(p_start_date, 'YYYY-MM-DD');
INSERT INTO PARTITION_META_DATA(
TABLE_OWNER, TABLE_NAME, TABLE_ALIAS, VIEW_NAME
, PARTITION_COLUMN_NAME, PRIMARYKEY_COLUMN_NAME, PARTITION_TYPE
, NUM_PRECREATE_PARTITIONS, NUM_RETAIN_PARTITIONS, AUTO_MANAGE_PARTITIONS
, DROP_OLD_PARTITIONS, ENABLE_LOGGING)
VALUES (
UPPER(p_table_owner), UPPER(p_table_name), UPPER(p_table_alias), UPPER(p_view_name)
, UPPER(p_partition_column), UPPER(p_pkey_column), UPPER(p_partition_type)
, p_precreate_partitions, p_retain_partitions, UPPER(p_manage_partitions)
, UPPER(p_drop_old_partitions), UPPER(p_enable_logging))
RETURNING meta_data_id
INTO v_meta_data_id;
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SEGMENT_ATTRIBUTES', false);
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'STORAGE', false);
IF p_enable_logging = 'Y' THEN
v_is_log_enabled := TRUE;
ELSE
v_is_log_enabled := FALSE;
END IF;
t_tab_privs := GET_OBJECT_PRIVILEGES (v_meta_data_id, UPPER(p_table_owner), UPPER(p_table_name), v_is_log_enabled);
/* START - Create DRFAULT partition */
v_table_name := UPPER(p_table_name) || '_DEFAULT';
v_table_alias := UPPER(p_table_alias) || '_DFT';
SELECT DBMS_METADATA.GET_DDL('TABLE', UPPER(p_table_name), UPPER(p_table_owner))
INTO v_ddl_sql
FROM DUAL;
-- DBMS_OUTPUT.PUT_LINE('----->>> Original v_ddl_sql: ' || v_ddl_sql);
IF v_is_log_enabled THEN
WRITE_TO_LOG ('DEBUG', v_meta_data_id, UPPER(v_table_name), 'Creating default table: ' || v_table_name);
END IF;
v_ddl_sql := REGEXP_REPLACE (v_ddl_sql, p_table_name, v_table_name, 1, 1, 'i');
v_ddl_sql := REPLACE (v_ddl_sql, p_table_alias, v_table_alias);
v_ddl_sql := substr(v_ddl_sql, 1, length(v_ddl_sql)-2);
-- DBMS_OUTPUT.PUT_LINE('----->>> DEFAULT v_ddl_sql: ' || v_ddl_sql);
EXECUTE IMMEDIATE v_ddl_sql;
IF v_is_log_enabled THEN
WRITE_TO_LOG ('DEBUG', v_meta_data_id, UPPER(v_table_name), 'Creating default table: ' || v_table_name || ' - Successful');
END IF;
/******
Grant the main table privileges on the newly created partition table
******/
GRANT_OBJECT_PRIVILEGES (v_meta_data_id, t_tab_privs, UPPER(v_table_name), v_is_log_enabled);
/* END - Create DRFAULT partition */
IF p_partition_type = 'Y' THEN
v_part_char_fmt := 'RRRR';
v_start_dt := TRUNC(TO_DATE(EXTRACT(YEAR FROM v_start_date), 'RRRR'), 'YEAR');
ELSIF p_partition_type = 'M' THEN
v_part_char_fmt := 'RRRRMM';
v_start_dt := TRUNC(v_start_date, 'MONTH');
ELSIF p_partition_type = 'W' THEN
v_part_char_fmt := 'RRRRMMDD';
v_start_dt := TRUNC(v_start_date, 'IW') - 1;
ELSIF p_partition_type = 'D' THEN
v_part_char_fmt := 'RRRRMMDD';
v_start_dt := TRUNC(v_start_date);
END IF;
v_partition_char_val := to_char(v_start_dt, v_part_char_fmt);
v_table_name := UPPER(p_table_name || '_' || v_partition_char_val);
v_table_alias := UPPER(p_table_alias || '_' || v_partition_char_val);
SELECT DBMS_METADATA.GET_DDL('TABLE', UPPER(p_table_name), UPPER(p_table_owner))
INTO v_ddl_sql
FROM DUAL;
IF v_is_log_enabled THEN
WRITE_TO_LOG ('DEBUG', v_meta_data_id, UPPER(v_table_name), 'Creating partition table: ' || v_table_name);
END IF;
v_ddl_sql := REGEXP_REPLACE (v_ddl_sql, p_table_name, v_table_name, 1, 1, 'i');
v_ddl_sql := REPLACE (v_ddl_sql, p_table_alias, v_table_alias);
v_ddl_sql := substr(v_ddl_sql, 1, length(v_ddl_sql)-2);
-- DBMS_OUTPUT.PUT_LINE('----->>> 1ST PART v_ddl_sql: ' || v_ddl_sql);
EXECUTE IMMEDIATE v_ddl_sql;
INSERT INTO PARTITION_TABLE_DATA(META_DATA_ID, PARTITION_TABLE_NAME, PARTITION_DATE_VALUE, CRT_DT, STATUS)
VALUES (v_meta_data_id, UPPER(v_table_name), v_start_dt, SYSDATE, 'A');
/******
Grant the main table privileges on the newly created partition table
******/
GRANT_OBJECT_PRIVILEGES (v_meta_data_id, t_tab_privs, UPPER(v_table_name), v_is_log_enabled);
IF v_is_log_enabled THEN
WRITE_TO_LOG ('DEBUG', v_meta_data_id, UPPER(v_table_name), 'Creating partition table: ' || v_table_name || ' - Successful');
END IF;
MANAGE_PARTITIONS.main_process (v_meta_data_id);
DBMS_OUTPUT.PUT_LINE('----->>> END: setup_partitions p_table_name: ' || p_table_name);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error in procedure SETUP_PARTITIONS while processing table: ' || v_table_name);
DBMS_OUTPUT.PUT_LINE('SQLERRM : ' || SQLERRM);
WRITE_TO_LOG ('ERROR', v_meta_data_id, v_table_name, SQLERRM);
END;
END MANAGE_PARTITIONS;
/
partitions/Daily_Test_Scripts.txt
/* Create main table to be partitioned */
CREATE TABLE STUDENT_DLY
(STUDENT_ID NUMBER(8,0) NOT NULL
,SALUTATION VARCHAR2(5)
,FIRST_NAME VARCHAR2(25)
,LAST_NAME VARCHAR2(25) NOT NULL
,STREET_ADDRESS VARCHAR2(50)
,ZIP VARCHAR2(5) NOT NULL
,PHONE VARCHAR2(15)
,EMPLOYER VARCHAR2(50)
,REGISTRATION_DATE DATE NOT NULL
,CREATED_BY VARCHAR2(30) NOT NULL
,CREATED_DATE DATE NOT NULL
,MODIFIED_BY VARCHAR2(30)
,MODIFIED_DATE DATE
);
/***
Primay key on partition main table
***/
ALTER TABLE STUDENT_DLY ADD CONSTRAINT STDTD_PK PRIMARY KEY (STUDENT_ID);
/***
Index on partition main table
***/
CREATE INDEX STDTD_ZIP_IDX_I ON STUDENT_DLY (ZIP);
Execute the procedure:
EXEC MANAGE_PARTITIONS.setup_partitions ('PARTSRVC', 'STUDENT_DLY', 'STDTD', 'REGISTRATION_DATE', 'STUDENT_ID', 'STUDENT_DLY_VW', 'D', '2021-01-21', 30, 30, 'Y', 'N', 'Y');
SELECT * FROM STUDENT_DLY_VW;
INSERT INTO STUDENT_DLY_VW VALUES (20210105,'Mr.','TEST 111','Last name','101-09 120th St.','11419','718-555-5555','ABC Education Co.'
,SYSDATE-25,'ADMIN',SYSDATE,'ADMIN',SYSDATE);
INSERT INTO STUDENT_DLY_VW VALUES (20210111,'Mr.','TEST 111','Last name','101-09 120th St.','11419','718-555-5555','ABC Education Co.'
,SYSDATE-22,'ADMIN',SYSDATE,'ADMIN',SYSDATE);
INSERT INTO STUDENT_DLY_VW VALUES (20210124,'Mr.','TEST 111','Last name','101-09 120th St.','11419','718-555-5555','ABC Education Co.'
,SYSDATE-15,'ADMIN',SYSDATE,'ADMIN',SYSDATE);
INSERT INTO STUDENT_DLY_VW VALUES (20210126,'Mr.','TEST 111','Last name','101-09 120th St.','11419','718-555-5555','ABC Education Co.'
,SYSDATE-10,'ADMIN',SYSDATE,'ADMIN',SYSDATE);
INSERT INTO STUDENT_DLY_VW VALUES (20210128,'Mr.','TEST 111','Last name','101-09 120th St.','11419','718-555-5555','ABC Education Co.'
,SYSDATE-7,'ADMIN',SYSDATE,'ADMIN',SYSDATE);
INSERT INTO STUDENT_DLY_VW VALUES (20210130,'Mr.','TEST 111','Last name','101-09 120th St.','11419','718-555-5555','ABC Education Co.'
,SYSDATE-5,'ADMIN',SYSDATE,'ADMIN',SYSDATE);
INSERT INTO STUDENT_DLY_VW VALUES (20210202,'Mr.','TEST 111','Last name','101-09 120th St.','11419','718-555-5555','ABC Education Co.'
,SYSDATE,'ADMIN',SYSDATE,'ADMIN',SYSDATE);
INSERT INTO STUDENT_DLY_VW VALUES (20210203,'Mr.','TEST 111','Last name','101-09 120th St.','11419','718-555-5555','ABC Education Co.'
,SYSDATE+20,'ADMIN',SYSDATE,'ADMIN',SYSDATE);
INSERT INTO STUDENT_DLY_VW VALUES (20210214,'Mr.','TEST 111','Last name','101-09 120th St.','11419','718-555-5555','ABC Education Co.'
,SYSDATE+35,'ADMIN',SYSDATE,'ADMIN',SYSDATE);
SELECT * FROM STUDENT_DLY_VW;
update STUDENT_DLY_VW set first_name = first_name || ' - UPDATED' where student_id = 20210124;
SELECT * FROM STUDENT_DLY_VW;
delete FROM STUDENT_DLY_VW where student_id = 20210130;
SELECT * FROM STUDENT_DLY_VW;
partitions/Monthly_Test_Scripts.txt
/* Create main table to be partitioned */
CREATE TABLE STUDENT_MLY
(STUDENT_ID NUMBER(8,0) NOT NULL
,SALUTATION VARCHAR2(5)
,FIRST_NAME VARCHAR2(25)
,LAST_NAME VARCHAR2(25) NOT NULL
,STREET_ADDRESS VARCHAR2(50)
,ZIP VARCHAR2(5) NOT NULL
,PHONE VARCHAR2(15)
,EMPLOYER VARCHAR2(50)
,REGISTRATION_DATE DATE NOT NULL
,CREATED_BY VARCHAR2(30) NOT NULL
,CREATED_DATE DATE NOT NULL
,MODIFIED_BY VARCHAR2(30)
,MODIFIED_DATE DATE
);
/***
Primay key on partition main table
***/
ALTER TABLE STUDENT_MLY ADD CONSTRAINT STDTM_PK PRIMARY KEY (STUDENT_ID);
/***
Index on partition main table
***/
CREATE INDEX STDTM_ZIP_IDX_I ON STUDENT_MLY (ZIP);
Execute the procedure:
EXEC MANAGE_PARTITIONS.setup_partitions ('PARTSRVC', 'STUDENT_MLY', 'STDTM', 'REGISTRATION_DATE', 'STUDENT_ID', 'STUDENT_MLY_VW', 'M', '2020-10-01', 3, 6, 'Y', 'N', 'Y');
SELECT * FROM STUDENT_MLY_VW;
INSERT INTO STUDENT_MLY_VW VALUES (20201114,'Mr.','TEST 111','Last name','101-09 120th St.','11419','718-555-5555','ABC Education Co.'
,SYSDATE-150,'ADMIN',SYSDATE,'ADMIN',SYSDATE);
INSERT INTO STUDENT_MLY_VW VALUES (20201205,'Mr.','TEST 111','Last name','101-09 120th St.','11419','718-555-5555','ABC Education Co.'
,SYSDATE-120,'ADMIN',SYSDATE,'ADMIN',SYSDATE);
INSERT INTO STUDENT_MLY_VW VALUES (20210110,'Mr.','TEST 111','Last name','101-09 120th St.','11419','718-555-5555','ABC Education Co.'
,SYSDATE-90,'ADMIN',SYSDATE,'ADMIN',SYSDATE);
INSERT INTO STUDENT_MLY_VW VALUES (20210112,'Mr.','TEST 111','Last name','101-09 120th St.','11419','718-555-5555','ABC Education Co.'
,SYSDATE-50,'ADMIN',SYSDATE,'ADMIN',SYSDATE);
INSERT INTO STUDENT_MLY_VW VALUES (20210221,'Mr.','TEST 111','Last name','101-09 120th St.','11419','718-555-5555','ABC Education Co.'
,SYSDATE-20,'ADMIN',SYSDATE,'ADMIN',SYSDATE);
INSERT INTO STUDENT_MLY_VW VALUES (20210325,'Mr.','TEST 111','Last name','101-09 120th St.','11419','718-555-5555','ABC Education Co.'
,SYSDATE,'ADMIN',SYSDATE,'ADMIN',SYSDATE);
INSERT INTO STUDENT_MLY_VW VALUES (20210416,'Mr.','TEST 111','Last name','101-09 120th St.','11419','718-555-5555','ABC Education Co.'
,SYSDATE+50,'ADMIN',SYSDATE,'ADMIN',SYSDATE);
INSERT INTO STUDENT_MLY_VW VALUES (20210522,'Mr.','TEST 111','Last name','101-09 120th St.','11419','718-555-5555','ABC Education Co.'
,SYSDATE+75,'ADMIN',SYSDATE,'ADMIN',SYSDATE);
INSERT INTO STUDENT_MLY_VW VALUES (20210620,'Mr.','TEST 111','Last name','101-09 120th St.','11419','718-555-5555','ABC Education Co.'
,SYSDATE+100,'ADMIN',SYSDATE,'ADMIN',SYSDATE);
SELECT * FROM STUDENT_MLY_VW;
update STUDENT_MLY_VW set first_name = first_name || ' - UPDATED' where student_id = 20201205;
SELECT * FROM STUDENT_MLY_VW;
delete FROM STUDENT_MLY_VW where student_id = 20210112;
SELECT * FROM STUDENT_MLY_VW;
partitions/Weekly_Test_Scripts.txt
/* Create main table to be partitioned */
CREATE TABLE STUDENT_WKLY
(STUDENT_ID NUMBER(8,0) NOT NULL
,SALUTATION VARCHAR2(5)
,FIRST_NAME VARCHAR2(25)
,LAST_NAME VARCHAR2(25) NOT NULL
,STREET_ADDRESS VARCHAR2(50)
,ZIP VARCHAR2(5) NOT NULL
,PHONE VARCHAR2(15)
,EMPLOYER VARCHAR2(50)
,REGISTRATION_DATE DATE NOT NULL
,CREATED_BY VARCHAR2(30) NOT NULL
,CREATED_DATE DATE NOT NULL
,MODIFIED_BY VARCHAR2(30) NOT NULL
,MODIFIED_DATE DATE NOT NULL
);
/***
Primay key on partition main table
***/
ALTER TABLE STUDENT_WKLY ADD CONSTRAINT STDTW_PK PRIMARY KEY (STUDENT_ID);
/***
Index on partition main table
***/
CREATE INDEX STDTW_ZIP_IDX_I ON STUDENT_WKLY (ZIP);
Execute the procedure:
EXEC MANAGE_PARTITIONS.setup_partitions ('PARTSRVC', 'STUDENT_WKLY', 'STDTW', 'REGISTRATION_DATE', 'STUDENT_ID', 'STUDENT_WKLY_VW', 'W', '2020-12-01', 4, 8, 'Y', 'N', 'Y');
SELECT * FROM STUDENT_WKLY_VW;
INSERT INTO STUDENT_WKLY_VW VALUES (20201205,'Mr.','TEST 111','Last name','101-09 120th St.','11419','718-555-5555','ABC Education Co.'
,SYSDATE-50,'ADMIN',SYSDATE,'ADMIN',SYSDATE);
INSERT INTO STUDENT_WKLY_VW VALUES (20201215,'Mr.','TEST 111','Last name','101-09 120th St.','11419','718-555-5555','ABC Education Co.'
,SYSDATE-40,'ADMIN',SYSDATE,'ADMIN',SYSDATE);
INSERT INTO STUDENT_WKLY_VW VALUES (20210110,'Mr.','TEST 111','Last name','101-09 120th St.','11419','718-555-5555','ABC Education Co.'
,SYSDATE-30,'ADMIN',SYSDATE,'ADMIN',SYSDATE);
INSERT INTO STUDENT_WKLY_VW VALUES (20210112,'Mr.','TEST 111','Last name','101-09 120th St.','11419','718-555-5555','ABC Education Co.'
,SYSDATE-20,'ADMIN',SYSDATE,'ADMIN',SYSDATE);
INSERT INTO STUDENT_WKLY_VW VALUES (20210221,'Mr.','TEST 111','Last name','101-09 120th St.','11419','718-555-5555','ABC Education Co.'
,SYSDATE-10,'ADMIN',SYSDATE,'ADMIN',SYSDATE);
INSERT INTO STUDENT_WKLY_VW VALUES (20210216,'Mr.','TEST 111','Last name','101-09 120th St.','11419','718-555-5555','ABC Education Co.'
,SYSDATE,'ADMIN',SYSDATE,'ADMIN',SYSDATE);
INSERT INTO STUDENT_WKLY_VW VALUES (20210222,'Mr.','TEST 111','Last name','101-09 120th St.','11419','718-555-5555','ABC Education Co.'
,SYSDATE+20,'ADMIN',SYSDATE,'ADMIN',SYSDATE);
INSERT INTO STUDENT_WKLY_VW VALUES (20210228,'Mr.','TEST 111','Last name','101-09 120th St.','11419','718-555-5555','ABC Education Co.'
,SYSDATE+40,'ADMIN',SYSDATE,'ADMIN',SYSDATE);
INSERT INTO STUDENT_WKLY_VW VALUES (20200308,'Mr.','TEST 111','Last name','101-09 120th St.','11419','718-555-5555','ABC Education Co.'
,SYSDATE+50,'ADMIN',SYSDATE,'ADMIN',SYSDATE);
SELECT * FROM STUDENT_WKLY_VW;
update STUDENT_WKLY_VW set first_name = first_name || ' - UPDATED' where student_id = 20201205;
SELECT * FROM STUDENT_WKLY_VW;
delete FROM STUDENT_WKLY_VW where student_id = 20210221;
SELECT * FROM STUDENT_WKLY_VW;
partitions/Yearly_Test_Scripts.txt
/* Create main table to be partitioned */
CREATE TABLE STUDENT_YLY
(STUDENT_ID NUMBER(8,0) NOT NULL
,SALUTATION VARCHAR2(5)
,FIRST_NAME VARCHAR2(25)
,LAST_NAME VARCHAR2(25) NOT NULL
,STREET_ADDRESS VARCHAR2(50)
,ZIP VARCHAR2(5) NOT NULL
,PHONE VARCHAR2(15)
,EMPLOYER VARCHAR2(50)
,REGISTRATION_DATE DATE NOT NULL
,CREATED_BY VARCHAR2(30) NOT NULL
,CREATED_DATE DATE NOT NULL
,MODIFIED_BY VARCHAR2(30) NOT NULL
,MODIFIED_DATE DATE NOT NULL
);
/***
Primay key on partition main table
***/
ALTER TABLE STUDENT_YLY ADD CONSTRAINT STDTY_PK PRIMARY KEY (STUDENT_ID);
/***
Index on partition main table
***/
CREATE INDEX STDTY_ZIP_IDX_I ON STUDENT_YLY (ZIP);
/***
Create a user who needs access to the partitioned tables (Ex: MyUser1) and grant required privileges
Privileges on partition main table
***/
/*
GRANT SELECT ON STUDENT_YLY TO MyUser1;
GRANT INSERT ON STUDENT_YLY TO MyUser1;
GRANT UPDATE ON STUDENT_YLY TO MyUser1;
GRANT DELETE ON STUDENT_YLY TO MyUser1;
*/
6. Call below function to setup table partitioning, it does following tasks:
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
Execute the procedure:
EXEC MANAGE_PARTITIONS.setup_partitions ('PARTSRVC', 'STUDENT_YLY', 'STDTY', 'REGISTRATION_DATE', 'STUDENT_ID', 'STUDENT_YLY_VW', 'Y', '2015-01-01', 3, 3, 'Y', 'N', 'Y');
7. Check for errors
select * from partition_log where log_type = 'ERROR';
8. Scripts for verifying the functionality
SELECT * FROM STUDENT_YLY_VW;
INSERT INTO STUDENT_YLY_VW VALUES (20200120,'Mr.','TEST 111','Last name','101-09 120th St.','11419','718-555-5555','ABC Education Co.'
,TO_DATE('2020-JAN-20', 'YYYY-MON-DD'),'ADMIN',SYSDATE,'ADMIN',SYSDATE);
INSERT INTO STUDENT_YLY_VW VALUES (20200130,'Mr.','TEST 111','Last name','101-09 120th St.','11419','718-555-5555','ABC Education Co.'
,TO_DATE('2020-JAN-30', 'YYYY-MON-DD'),'ADMIN',SYSDATE,'ADMIN',SYSDATE);
INSERT INTO STUDENT_YLY_VW VALUES (20210204,'Mr.','TEST 111','Last name','101-09 120th St.','11419','718-555-5555','ABC Education Co.'
,TO_DATE('2021-FEB-04', 'YYYY-MON-DD'),'ADMIN',SYSDATE,'ADMIN',SYSDATE);
INSERT INTO STUDENT_YLY_VW VALUES (20210311,'Mr.','TEST 111','Last name','101-09 120th St.','11419','718-555-5555','ABC Education Co.'
,TO_DATE('2021-MAR-11', 'YYYY-MON-DD'),'ADMIN',SYSDATE,'ADMIN',SYSDATE);
INSERT INTO STUDENT_YLY_VW VALUES (20220606,'Mr.','TEST 111','Last name','101-09 120th St.','11419','718-555-5555','ABC Education Co.'
,TO_DATE('2022-JUN-06', 'YYYY-MON-DD'),'ADMIN',SYSDATE,'ADMIN',SYSDATE);
INSERT INTO STUDENT_YLY_VW VALUES (20220815,'Mr.','TEST 111','Last name','101-09 120th St.','11419','718-555-5555','ABC Education Co.'
,TO_DATE('2022-AUG-15', 'YYYY-MON-DD'),'ADMIN',SYSDATE,'ADMIN',SYSDATE);
INSERT INTO STUDENT_YLY_VW VALUES (20230921,'Mr.','TEST 111','Last name','101-09 120th St.','11419','718-555-5555','ABC Education Co.'
,TO_DATE('2023-SEP-21', 'YYYY-MON-DD'),'ADMIN',SYSDATE,'ADMIN',SYSDATE);
INSERT INTO STUDENT_YLY_VW VALUES (20231025,'Mr.','TEST 111','Last name','101-09 120th St.','11419','718-555-5555','ABC Education Co.'
,TO_DATE('2023-OCT-25', 'YYYY-MON-DD'),'ADMIN',SYSDATE,'ADMIN',SYSDATE);
INSERT INTO STUDENT_YLY_VW VALUES (20241126,'Mr.','TEST 111','Last name','101-09 120th St.','11419','718-555-5555','ABC Education Co.'
,TO_DATE('2024-NOV-26', 'YYYY-MON-DD'),'ADMIN',SYSDATE,'ADMIN',SYSDATE);
INSERT INTO STUDENT_YLY_VW VALUES (20250121,'Mr.','TEST 111','Last name','101-09 120th St.','11419','718-555-5555','ABC Education Co.'
,TO_DATE('2025-JAN-21', 'YYYY-MON-DD'),'ADMIN',SYSDATE,'ADMIN',SYSDATE);
INSERT INTO STUDENT_YLY_VW VALUES (20250610,'Mr.','TEST 111','Last name','101-09 120th St.','11419','718-555-5555','ABC Education Co.'
,TO_DATE('2025-JUN-10', 'YYYY-MON-DD'),'ADMIN',SYSDATE,'ADMIN',SYSDATE);
SELECT * FROM STUDENT_YLY_VW;
update STUDENT_YLY_VW set first_name = first_name || ' - UPDATED' where student_id = 20210204;
SELECT * FROM STUDENT_YLY_VW;
delete FROM STUDENT_YLY_VW where student_id = 20231025;
SELECT * FROM STUDENT_YLY_VW;
partitions/partition_steps.txt
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
Readme
# Implementing table partitioning in Oracle Standard Edition: Part 2
This repository is associated with the AWS Databases blog post titled "Implementing table partitioning in Oracle Standard Edition: Part 2". This post is a continuation Implementing table partitioning in Oracle Standard Edition: Part 1, which explains how to configure partitioning in Oracle SE. This post demonstrates techniques of automating date range partition management in Oracle SE.
Check out the blog post for details about the functionality, the setup instructions, and demo use case.
# Demo Instructions
## Configure partitioning environment and packages (*Listed in partition_steps.txt file*)
* Create the partition service account user
* Create the main partition table, primary key, and index
* Create the metadata tables to manage partitioning
* @partition_mgmt_tables.sql
* @pkg_MANAGE_PARTITIONS.sql
* @pkg_body_MANAGE_PARTITIONS
## Demo Use Case instruction
* Daily :- Daily_Test_Scripts.txt
* Weekly:- Weekly_Test_Scripts.txt
* Monthly:-Monthly_Test_Scripts.txt
* Yearly:-Yearly_Test_Scripts.txt
## Auto partioning management
* SQL> EXEC MANAGE_PARTITIONS.MAIN_PROCESS
标签：name,
partition,
VARCHAR2,
分区表,
自动,
Oracle,
table,
data,
id
From： https://www.cnblogs.com/Jeona/p/17644477.html