In this post i will show you how to partition existing table for SIEBEL application which is my case holding huge number of records.
- Create the same structure for the original table but without any constraint just columns like the below with new name and sure choose the partition you want to use, in case any of the constraint has been created on the new table , error will be generated while the package running and you should recreate the table again without any constraint :-
CREATE TABLE SIEBEL.S_CASE_NEW
(
ROW_ID VARCHAR2(15 CHAR) ,
CREATED DATE DEFAULT sysdate ,
CREATED_BY VARCHAR2(15 CHAR) ,
LAST_UPD DATE DEFAULT sysdate ,
LAST_UPD_BY VARCHAR2(15 CHAR) ,
MODIFICATION_NUM NUMBER(10) DEFAULT 0 ,
CONFLICT_ID VARCHAR2(15 CHAR) DEFAULT ‘0’ ,
ASGN_USR_EXCLD_FLG CHAR(1 CHAR) DEFAULT ‘N’ ,
BU_ID VARCHAR2(15 CHAR) DEFAULT ‘0-R9NH’ ,
CASE_DT DATE ,
CASE_NUM VARCHAR2(100 CHAR) ,
CHGOFCCM_REQ_FLG CHAR(1 CHAR) DEFAULT ‘N’ ,
CLASS_CD VARCHAR2(30 CHAR) ,
LOCAL_SEQ_NUM NUMBER(10) DEFAULT 1 ,
NAME VARCHAR2(100 CHAR) ,
PR_REP_DNRM_FLG CHAR(1 CHAR) DEFAULT ‘N’ ,
PR_REP_MANL_FLG CHAR(1 CHAR) DEFAULT ‘N’ ,
PR_REP_SYS_FLG CHAR(1 CHAR) DEFAULT ‘N’ ,
STATUS_CD VARCHAR2(30 CHAR) ,
ASGN_DT DATE,
CLOSED_DT DATE,
CURR_APPR_SEQ_NUM NUMBER(10),
DB_LAST_UPD DATE,
REWARD_AMT NUMBER(22,7),
REWARD_EXCH_DATE DATE,
APPLICANT_ID VARCHAR2(15 CHAR),
APPR_TEMP_ID VARCHAR2(15 CHAR),
AUDIT_EMP_ID VARCHAR2(15 CHAR),
CATEGORY_TYPE_CD VARCHAR2(30 CHAR),
CITY VARCHAR2(50 CHAR),
COUNTRY VARCHAR2(30 CHAR),
CRIME_SUB_TYPE_CD VARCHAR2(30 CHAR),
CRIME_TYPE_CD VARCHAR2(30 CHAR),
DB_LAST_UPD_SRC VARCHAR2(50 CHAR),
DESC_TEXT VARCHAR2(2000 CHAR),
MSTR_CASE_ID VARCHAR2(15 CHAR),
ORG_GROUP_ID VARCHAR2(15 CHAR),
PAR_CASE_ID VARCHAR2(15 CHAR),
PRIORITY_TYPE_CD VARCHAR2(30 CHAR),
PR_AGENCY_ID VARCHAR2(15 CHAR),
PR_AGENT_ID VARCHAR2(15 CHAR),
PR_DISEASE_ID VARCHAR2(15 CHAR),
PR_POSTN_ID VARCHAR2(15 CHAR),
PR_PROD_INT_ID VARCHAR2(15 CHAR),
PR_PRTNR_ID VARCHAR2(15 CHAR),
PR_SGROUP_ID VARCHAR2(15 CHAR),
PR_SUBJECT_ID VARCHAR2(15 CHAR),
PR_SUSPCT_ID VARCHAR2(15 CHAR),
PS_APPL_ID VARCHAR2(15 CHAR),
REWARD_CURCY_CD VARCHAR2(20 CHAR),
SERIAL_NUM VARCHAR2(100 CHAR),
SOURCE_CD VARCHAR2(30 CHAR),
STAGE_CD VARCHAR2(30 CHAR),
STATE VARCHAR2(10 CHAR),
SUBJECT_NAME VARCHAR2(100 CHAR),
SUBJECT_PH_NUM VARCHAR2(40 CHAR),
SUB_STATUS_CD VARCHAR2(30 CHAR),
SUB_TYPE_CD VARCHAR2(30 CHAR),
TERRITORY_TYPE_CD VARCHAR2(30 CHAR),
THREAT_LVL_CD VARCHAR2(30 CHAR),
TYPE_CD VARCHAR2(30 CHAR),
X_APP_BIRTH_DATE DATE,
X_APP_BIRTH_DT_HIJRI VARCHAR2(10 CHAR),
X_APP_FATHER_NAME_A VARCHAR2(50 CHAR),
X_APP_FATHER_NAME_E VARCHAR2(50 CHAR),
X_APP_FAX VARCHAR2(15 CHAR),
X_APP_FIRST_NAME_A VARCHAR2(50 CHAR),
X_APP_FIRST_NAME_E VARCHAR2(50 CHAR),
X_APP_FULL_NAME VARCHAR2(100 CHAR),
X_APP_GENDER VARCHAR2(30 CHAR),
X_APP_GFATHER_NAME_A VARCHAR2(50 CHAR),
X_APP_GFATHER_NAME_E VARCHAR2(50 CHAR),
X_APP_LAST_NAME_A VARCHAR2(50 CHAR),
X_APP_LAST_NAME_E VARCHAR2(50 CHAR),
X_APP_MAIL VARCHAR2(50 CHAR),
X_APP_MOBILE VARCHAR2(15 CHAR),
X_APP_MOTHER_F_NAME_A VARCHAR2(50 CHAR),
X_APP_MOTHER_F_NAME_E VARCHAR2(50 CHAR),
X_APP_MOTHER_L_NAME_A VARCHAR2(50 CHAR),
X_APP_MOTHER_L_NAME_E VARCHAR2(50 CHAR),
X_APP_TYPE VARCHAR2(30 CHAR),
X_APPLICANT_CLASSIFICATION VARCHAR2(30 CHAR),
X_APPLICANT_NAT_ID_NO VARCHAR2(15 CHAR),
X_APPLICANT_TITLE VARCHAR2(30 CHAR),
X_APPLICANT_TYPE VARCHAR2(50 CHAR),
X_ATTACHMENT_FLG VARCHAR2(5 CHAR),
X_CANCEL_DESC VARCHAR2(300 CHAR),
X_CANCEL_REASON VARCHAR2(30 CHAR),
X_CASE_COPY_FLG VARCHAR2(30 CHAR),
X_CASE_HIJRI_DATE VARCHAR2(30 CHAR),
X_CHECK_EXISTS_FKG VARCHAR2(15 CHAR),
X_CHECK_EXISTS_FLG VARCHAR2(30 CHAR),
X_COMMERCIAL_NAME VARCHAR2(300 CHAR),
X_COMMERCIAL_REG_NO VARCHAR2(40 CHAR),
X_COPY_SERIAL_NUM VARCHAR2(100 CHAR),
X_CREATED_DATE_HEJRI VARCHAR2(30 CHAR),
X_CREATED_GRG VARCHAR2(30 CHAR),
X_CREATED_HIJRI VARCHAR2(10 CHAR),
X_CRED_EXP_DT_HIJRI VARCHAR2(10 CHAR),
X_CRED_EXPIRY_DATE DATE,
X_CRED_ISSUE_DATE DATE,
X_CRED_ISSUE_DT_HIJRI VARCHAR2(10 CHAR),
X_CRED_NO VARCHAR2(30 CHAR),
X_CRED_TYPE VARCHAR2(30 CHAR),
X_CRS_NO VARCHAR2(15 CHAR),
X_DLV_DATE DATE,
X_DLV_DATE_HIJRI VARCHAR2(10 CHAR),
X_DLV_USER_ID VARCHAR2(15 CHAR),
X_DOCUMENT_SORUCE VARCHAR2(30 CHAR),
X_EST_OWNERSHIP_TYPE VARCHAR2(30 CHAR),
X_EST_TYPE VARCHAR2(30 CHAR),
X_GIS_DATA_LOAD VARCHAR2(15 CHAR),
X_GIS_DATA_STATUS VARCHAR2(10 CHAR),
X_INV_TYPE VARCHAR2(30 CHAR),
X_IS_UPLOADED VARCHAR2(30 CHAR),
X_LAND_ORG_TYPE VARCHAR2(30 CHAR),
X_LAND_STATUS VARCHAR2(30 CHAR),
X_LAND_TYPE VARCHAR2(30 CHAR),
X_MUNICIPAL_NAME VARCHAR2(30 CHAR),
X_NATIONALITY VARCHAR2(30 CHAR),
X_ORG_END_REG_DATE DATE,
X_ORG_END_REG_HIJRI_DATE VARCHAR2(10 CHAR),
X_ORG_REGISTRATION_DATE DATE,
X_ORG_REGISTRATION_HIJRI_DATE VARCHAR2(10 CHAR),
X_ORGANIZATION_NAME VARCHAR2(200 CHAR),
X_ORIGINAL_ORG_ID VARCHAR2(15 CHAR),
X_PAPER_FLG CHAR(1 CHAR) DEFAULT ‘N’,
X_PAYMENT_DT DATE,
X_PAYMENT_FLAG VARCHAR2(30 CHAR),
X_PAYMENT_NO VARCHAR2(10 CHAR),
X_PR_EMP_ID VARCHAR2(15 CHAR),
X_PR_ENG_OFFICE_ID VARCHAR2(15 CHAR),
X_PROC_DESC VARCHAR2(100 CHAR),
X_PROC_FLG VARCHAR2(30 CHAR),
X_PROC_TYPE VARCHAR2(30 CHAR),
X_PROXY_ISSUE_AUTHORITY VARCHAR2(30 CHAR),
X_PROXY_NO VARCHAR2(10 CHAR),
X_QX_CRED_EXP_DT_HIJRI DATE,
X_REGISTRATION_DATE DATE,
X_REGISTRATION_HIJRI_DATE VARCHAR2(10 CHAR),
X_REGISTRATION_NO VARCHAR2(30 CHAR),
X_REJECT_DESC VARCHAR2(300 CHAR),
X_REJECT_REASON VARCHAR2(30 CHAR),
X_RELATION_TYPE VARCHAR2(30 CHAR),
X_RETURN_DATE DATE,
X_RETURN_DATE_HIJRI VARCHAR2(10 CHAR),
X_RETURN_NOTES VARCHAR2(100 CHAR),
X_RETURN_REASON VARCHAR2(30 CHAR),
X_SCHEMA_STATUS VARCHAR2(30 CHAR),
X_SECURITY_FLG VARCHAR2(30 CHAR),
X_SELECT_FLG CHAR(1 CHAR) DEFAULT ‘N’,
X_STRIPPED_FIRST_NAME VARCHAR2(50 CHAR),
X_STRIPPED_FULL_NAME VARCHAR2(200 CHAR),
X_STRIPPED_LAST_NAME VARCHAR2(50 CHAR),
X_STRIPPED_MOTHER_FIRST VARCHAR2(50 CHAR),
X_STRIPPED_MOTHER_FULLNAME VARCHAR2(50 CHAR),
X_STRIPPED_MOTHER_LASTNAME VARCHAR2(50 CHAR),
X_STRIPPED_SECOND_NAME VARCHAR2(50 CHAR),
X_STRIPPED_THIRD_NAME VARCHAR2(50 CHAR),
X_TO_BU_ID VARCHAR2(15 CHAR),
X_UPDATED_GRG VARCHAR2(30 CHAR),
X_UPDATED_HIJRI VARCHAR2(10 CHAR),
COR_TYPE VARCHAR2(30 CHAR),
CORR_CAS_CAT VARCHAR2(30 CHAR),
PRIMARY_EMPLOYEE VARCHAR2(30 CHAR),
SUBMIT_TO_STATUS VARCHAR2(15 CHAR),
X_DOCUMENT_TYPE VARCHAR2(30 CHAR),
X_SURVEYOR_NAME VARCHAR2(30 CHAR),
X_OLD_STATUS VARCHAR2(30 CHAR),
X_APPLICANT_ORG_ID VARCHAR2(15 CHAR),
X_APPLICANT_ROW_ID VARCHAR2(15 CHAR),
X_GIS_TOKEN VARCHAR2(100 CHAR),
X_NEW_PERMIT_FLG CHAR(1 CHAR) DEFAULT ‘Y’,
X_TRANSACTION_MOD VARCHAR2(30 CHAR),
X_TRANSACTION_STATUS VARCHAR2(30 CHAR),
X_CASE_CAT VARCHAR2(100 CHAR),
X_CASE_COPY_SERIAL NUMBER(10),
X_GIS_PARAMETER VARCHAR2(300 CHAR),
X_GIS_ROWIDS VARCHAR2(100 CHAR),
READING_FLAG CHAR(1 CHAR) DEFAULT ‘N’,
X_GIS_MUNICIPAL VARCHAR2(30 CHAR),
X_ORG_DELEGATE_NAME VARCHAR2(200 CHAR),
X_PR_POS_ORG_ID VARCHAR2(15 CHAR),
X_ORGANIZATION_STRIPPED_NAME VARCHAR2(200 CHAR),
X_CITIZEN_REVIEW CHAR(1 CHAR),
X_ALLOWED_USAGE VARCHAR2(50 CHAR),
X_AUTHORIZATION_DATE DATE,
X_AUTHORIZATION_HIJRI_DATE VARCHAR2(10 CHAR),
X_AUTHORIZATION_NO VARCHAR2(20 CHAR),
X_CIVIL_APPROVAL_DATE DATE,
X_CIVIL_APPROVAL_HIJRI_DATE VARCHAR2(10 CHAR),
X_CIVIL_APPROVAL_NO VARCHAR2(20 CHAR),
X_CIVIL_OFFICE VARCHAR2(25 CHAR),
X_NEW_MUNICIPAL_NAME VARCHAR2(30 CHAR),
X_OLD_MUNICIPAL_NAME VARCHAR2(30 CHAR),
X_OLD_STATUS_CD VARCHAR2(30 CHAR),
X_RESTRICT_NUM VARCHAR2(30 CHAR),
X_LAST_UPD_HIJRI VARCHAR2(10 CHAR),
X_APP_BIRTH_DATE_HIJRI VARCHAR2(10 CHAR),
X_FEES_EXCEPTION VARCHAR2(30 CHAR),
X_FINCL_NAME VARCHAR2(30 CHAR),
X_IS_OWNER VARCHAR2(15 CHAR),
X_MANAGER_ID VARCHAR2(15 CHAR),
X_OWNERSHIP_TYPE VARCHAR2(30 CHAR),
X_PRINT_FLG CHAR(1 CHAR) DEFAULT ‘N’,
X_PRNT_FLG VARCHAR2(5 CHAR),
X_SECRETARY_ID VARCHAR2(15 CHAR),
X_UNDER_SECRETARY_ID VARCHAR2(15 CHAR),
X_VIEW_SEQUENCE NUMBER(10) DEFAULT 0,
X_REGULATION_ACCPTNCE_FLG VARCHAR2(7 CHAR),
X_CONFIRM_FLAG VARCHAR2(7 CHAR),
X_OCCUPATION_IN_RESIDENCE VARCHAR2(30 CHAR),
X_ROWNUM NUMBER(10),
X_NUMBER_ARCHIVAL VARCHAR2(15 CHAR),
X_ATTACHMENT_PARAMETERS VARCHAR2(500 CHAR),
X_ATTACHMENT_ROW_IDS VARCHAR2(500 CHAR),
X_BP_ID VARCHAR2(15 CHAR),
X_CONTROL_SUB_TYPE VARCHAR2(30 CHAR),
X_CONTROL_TYPE VARCHAR2(30 CHAR),
X_DEPOSIT_ID VARCHAR2(15 CHAR),
X_MALL_ID VARCHAR2(15 CHAR),
X_NEW_DEPOSIT NUMBER(10),
X_SOURCE_ID VARCHAR2(15 CHAR),
X_STORE_ID VARCHAR2(15 CHAR),
APPEALED_FLG CHAR(1 CHAR) DEFAULT ‘N’ ,
CHANGED_FLG CHAR(1 CHAR) DEFAULT ‘N’ ,
EVAL_ASSESS_ID VARCHAR2(15 CHAR),
X_ARCHIEVING_TYPE VARCHAR2(30 CHAR),
X_ACTIVITY_ID VARCHAR2(15 CHAR),
X_OLD_SERIAL_NUM VARCHAR2(20 CHAR),
X_OWNER_ORG_POSTN_ID VARCHAR2(15 CHAR),
X_PR_CNTR_POSTN_ID VARCHAR2(15 CHAR),
X_REPORT_URL VARCHAR2(500 CHAR),
X_VIOLATION_ID VARCHAR2(15 CHAR),
X_APPLICANT_SOURCE VARCHAR2(50 CHAR)
)
PARTITION BY RANGE (created)(PARTITION S_CASE_2015 VALUES LESS THAN (TO_DATE(’01/01/2016′, ‘DD/MM/YYYY’)), PARTITION S_CASE_2016 VALUES LESS THAN (TO_DATE(’01/01/2017′, ‘DD/MM/YYYY’)), PARTITION S_CASE_2017 VALUES LESS THAN (MAXVALUE));
- Now we should start the redefinition by running the following package
BEGIN
DBMS_REDEFINITION.start_redef_table(
uname => ‘SIEBEL’,
orig_table => ‘S_CASE’,
int_table => ‘S_CASE_NEW’);
END;
/
- Sync the both tables together
BEGIN
dbms_redefinition.sync_interim_table(
uname => ‘SIEBEL’,
orig_table => ‘S_CASE’,
int_table => ‘S_CASE_NEW’);
END;
/
- After Running the both package above run the below scripts but run it from the server side, because it’s takes times & to avoid any interruption
SET SERVEROUTPUT ON
DECLARE
l_errors NUMBER;
BEGIN
DBMS_REDEFINITION.copy_table_dependents(
uname => ‘SIEBEL’,
orig_table => ‘S_CASE’,
int_table => ‘S_CASE_NEW’,
copy_indexes => DBMS_REDEFINITION.cons_orig_params,
copy_triggers => TRUE,
copy_constraints => TRUE,
copy_privileges => TRUE,
ignore_errors => FALSE,
num_errors => l_errors,
copy_statistics => FALSE,
copy_mvlog => FALSE);
DBMS_OUTPUT.put_line(‘Errors=’ || l_errors);
END;
/
- Finish the redefinition
BEGIN
dbms_redefinition.finish_redef_table(
uname => ‘SIEBEL’,
orig_table => ‘S_CASE’,
int_table => ‘S_CASE_NEW’);
END;
/
- After finishing everything successfully, just drop the new table because now it’s became the old table
DROP TABLE S_CASE_NEW;
- Run the below query to see if the partition has been successfully created
SELECT partitioned
FROM dba_tables
WHERE table_name = ‘S_CASE’;
Thanks
Osama
Technology Geek 