/* 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 '_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;