Summary
Here are the steps to partition for example the table AP_INVOICE_DISTRIBUTIONS_ALL in Oracle E-Business Suite.
1. Shutdown the Apps tier
2. Create the new partitioned table with the same column structure as the original and with the partitions.
The table is partitioned by
accounting_date (monthly)
CREATE TABLE AP.AP_INVOICE_DISTRIBUTIONS_ALL_P
(
ACCOUNTING_DATE DATE NOT NULL,
ACCRUAL_POSTED_FLAG VARCHAR2(1 BYTE) NOT NULL,
ASSETS_ADDITION_FLAG VARCHAR2(1 BYTE) NOT NULL,
ASSETS_TRACKING_FLAG VARCHAR2(1 BYTE) NOT NULL,
CASH_POSTED_FLAG VARCHAR2(1 BYTE) NOT NULL,
DISTRIBUTION_LINE_NUMBER NUMBER(15) NOT NULL,
DIST_CODE_COMBINATION_ID NUMBER(15) NOT NULL,
INVOICE_ID NUMBER(15) NOT NULL,
LAST_UPDATED_BY NUMBER(15) NOT NULL,
LAST_UPDATE_DATE DATE NOT NULL,
LINE_TYPE_LOOKUP_CODE VARCHAR2(25 BYTE) NOT NULL,
PERIOD_NAME VARCHAR2(15 BYTE) NOT NULL,
SET_OF_BOOKS_ID NUMBER(15) NOT NULL,
ACCTS_PAY_CODE_COMBINATION_ID NUMBER(15),
AMOUNT NUMBER,
BASE_AMOUNT NUMBER,
BASE_INVOICE_PRICE_VARIANCE NUMBER,
BATCH_ID NUMBER(15),
CREATED_BY NUMBER(15),
CREATION_DATE DATE,
DESCRIPTION VARCHAR2(240 BYTE),
EXCHANGE_RATE_VARIANCE NUMBER,
FINAL_MATCH_FLAG VARCHAR2(1 BYTE),
INCOME_TAX_REGION VARCHAR2(10 BYTE),
INVOICE_PRICE_VARIANCE NUMBER,
LAST_UPDATE_LOGIN NUMBER(15),
MATCH_STATUS_FLAG VARCHAR2(1 BYTE),
POSTED_FLAG VARCHAR2(1 BYTE),
PO_DISTRIBUTION_ID NUMBER(15),
PROGRAM_APPLICATION_ID NUMBER(15),
PROGRAM_ID NUMBER(15),
PROGRAM_UPDATE_DATE DATE,
QUANTITY_INVOICED NUMBER,
RATE_VAR_CODE_COMBINATION_ID NUMBER(15),
REQUEST_ID NUMBER(15),
REVERSAL_FLAG VARCHAR2(1 BYTE),
TYPE_1099 VARCHAR2(10 BYTE),
UNIT_PRICE NUMBER,
VAT_CODE VARCHAR2(15 BYTE),
AMOUNT_ENCUMBERED NUMBER,
BASE_AMOUNT_ENCUMBERED NUMBER,
ENCUMBERED_FLAG VARCHAR2(1 BYTE),
EXCHANGE_DATE DATE,
EXCHANGE_RATE NUMBER,
EXCHANGE_RATE_TYPE VARCHAR2(30 BYTE),
PRICE_ADJUSTMENT_FLAG VARCHAR2(1 BYTE),
PRICE_VAR_CODE_COMBINATION_ID NUMBER(15),
QUANTITY_UNENCUMBERED NUMBER,
STAT_AMOUNT NUMBER,
AMOUNT_TO_POST NUMBER,
ATTRIBUTE1 VARCHAR2(150 BYTE),
ATTRIBUTE10 VARCHAR2(150 BYTE),
ATTRIBUTE11 VARCHAR2(150 BYTE),
ATTRIBUTE12 VARCHAR2(150 BYTE),
ATTRIBUTE13 VARCHAR2(150 BYTE),
ATTRIBUTE14 VARCHAR2(150 BYTE),
ATTRIBUTE15 VARCHAR2(150 BYTE),
ATTRIBUTE2 VARCHAR2(150 BYTE),
ATTRIBUTE3 VARCHAR2(150 BYTE),
ATTRIBUTE4 VARCHAR2(150 BYTE),
ATTRIBUTE5 VARCHAR2(150 BYTE),
ATTRIBUTE6 VARCHAR2(150 BYTE),
ATTRIBUTE7 VARCHAR2(150 BYTE),
ATTRIBUTE8 VARCHAR2(150 BYTE),
ATTRIBUTE9 VARCHAR2(150 BYTE),
ATTRIBUTE_CATEGORY VARCHAR2(150 BYTE),
BASE_AMOUNT_TO_POST NUMBER,
CASH_JE_BATCH_ID NUMBER(15),
EXPENDITURE_ITEM_DATE DATE,
EXPENDITURE_ORGANIZATION_ID NUMBER(15),
EXPENDITURE_TYPE VARCHAR2(30 BYTE),
JE_BATCH_ID NUMBER(15),
PARENT_INVOICE_ID NUMBER(15),
PA_ADDITION_FLAG VARCHAR2(1 BYTE),
PA_QUANTITY NUMBER(22,5),
POSTED_AMOUNT NUMBER,
POSTED_BASE_AMOUNT NUMBER,
PREPAY_AMOUNT_REMAINING NUMBER,
PROJECT_ACCOUNTING_CONTEXT VARCHAR2(30 BYTE),
PROJECT_ID NUMBER(15),
TASK_ID NUMBER(15),
USSGL_TRANSACTION_CODE VARCHAR2(30 BYTE),
USSGL_TRX_CODE_CONTEXT VARCHAR2(30 BYTE),
EARLIEST_SETTLEMENT_DATE DATE,
REQ_DISTRIBUTION_ID NUMBER(15),
QUANTITY_VARIANCE NUMBER,
BASE_QUANTITY_VARIANCE NUMBER,
PACKET_ID NUMBER(15),
AWT_FLAG VARCHAR2(1 BYTE),
AWT_GROUP_ID NUMBER(15),
AWT_TAX_RATE_ID NUMBER(15),
AWT_GROSS_AMOUNT NUMBER,
AWT_INVOICE_ID NUMBER(15),
AWT_ORIGIN_GROUP_ID NUMBER(15),
REFERENCE_1 VARCHAR2(30 BYTE),
REFERENCE_2 VARCHAR2(30 BYTE),
ORG_ID NUMBER(15) DEFAULT TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),
OTHER_INVOICE_ID NUMBER(15),
AWT_INVOICE_PAYMENT_ID NUMBER(15),
GLOBAL_ATTRIBUTE_CATEGORY VARCHAR2(150 BYTE),
GLOBAL_ATTRIBUTE1 VARCHAR2(150 BYTE),
GLOBAL_ATTRIBUTE2 VARCHAR2(150 BYTE),
GLOBAL_ATTRIBUTE3 VARCHAR2(150 BYTE),
GLOBAL_ATTRIBUTE4 VARCHAR2(150 BYTE),
GLOBAL_ATTRIBUTE5 VARCHAR2(150 BYTE),
GLOBAL_ATTRIBUTE6 VARCHAR2(150 BYTE),
GLOBAL_ATTRIBUTE7 VARCHAR2(150 BYTE),
GLOBAL_ATTRIBUTE8 VARCHAR2(150 BYTE),
GLOBAL_ATTRIBUTE9 VARCHAR2(150 BYTE),
GLOBAL_ATTRIBUTE10 VARCHAR2(150 BYTE),
GLOBAL_ATTRIBUTE11 VARCHAR2(150 BYTE),
GLOBAL_ATTRIBUTE12 VARCHAR2(150 BYTE),
GLOBAL_ATTRIBUTE13 VARCHAR2(150 BYTE),
GLOBAL_ATTRIBUTE14 VARCHAR2(150 BYTE),
GLOBAL_ATTRIBUTE15 VARCHAR2(150 BYTE),
GLOBAL_ATTRIBUTE16 VARCHAR2(150 BYTE),
GLOBAL_ATTRIBUTE17 VARCHAR2(150 BYTE),
GLOBAL_ATTRIBUTE18 VARCHAR2(150 BYTE),
GLOBAL_ATTRIBUTE19 VARCHAR2(150 BYTE),
GLOBAL_ATTRIBUTE20 VARCHAR2(150 BYTE),
AMOUNT_INCLUDES_TAX_FLAG VARCHAR2(1 BYTE),
TAX_CALCULATED_FLAG VARCHAR2(1 BYTE),
LINE_GROUP_NUMBER NUMBER(15),
RECEIPT_VERIFIED_FLAG VARCHAR2(1 BYTE),
RECEIPT_REQUIRED_FLAG VARCHAR2(1 BYTE),
RECEIPT_MISSING_FLAG VARCHAR2(1 BYTE),
JUSTIFICATION VARCHAR2(240 BYTE),
EXPENSE_GROUP VARCHAR2(80 BYTE),
START_EXPENSE_DATE DATE,
END_EXPENSE_DATE DATE,
RECEIPT_CURRENCY_CODE VARCHAR2(15 BYTE),
RECEIPT_CONVERSION_RATE NUMBER,
RECEIPT_CURRENCY_AMOUNT NUMBER,
DAILY_AMOUNT NUMBER,
WEB_PARAMETER_ID NUMBER,
ADJUSTMENT_REASON VARCHAR2(240 BYTE),
AWARD_ID NUMBER(15),
MRC_DIST_CODE_COMBINATION_ID VARCHAR2(2000 BYTE),
MRC_BASE_AMOUNT VARCHAR2(2000 BYTE),
MRC_BASE_INV_PRICE_VARIANCE VARCHAR2(2000 BYTE),
MRC_EXCHANGE_RATE_VARIANCE VARCHAR2(2000 BYTE),
MRC_RATE_VAR_CCID VARCHAR2(2000 BYTE),
MRC_EXCHANGE_DATE VARCHAR2(2000 BYTE),
MRC_EXCHANGE_RATE VARCHAR2(2000 BYTE),
MRC_EXCHANGE_RATE_TYPE VARCHAR2(2000 BYTE),
MRC_RECEIPT_CONVERSION_RATE VARCHAR2(2000 BYTE),
DIST_MATCH_TYPE VARCHAR2(25 BYTE),
RCV_TRANSACTION_ID NUMBER(15),
INVOICE_DISTRIBUTION_ID NUMBER(15) NOT NULL,
PARENT_REVERSAL_ID NUMBER(15),
TAX_RECOVERY_RATE NUMBER,
TAX_RECOVERY_OVERRIDE_FLAG VARCHAR2(1 BYTE),
TAX_RECOVERABLE_FLAG VARCHAR2(1 BYTE),
TAX_CODE_OVERRIDE_FLAG VARCHAR2(1 BYTE),
TAX_CODE_ID NUMBER(15),
PA_CC_AR_INVOICE_ID NUMBER(15),
PA_CC_AR_INVOICE_LINE_NUM NUMBER(15),
PA_CC_PROCESSED_CODE VARCHAR2(1 BYTE),
MERCHANT_DOCUMENT_NUMBER VARCHAR2(80 BYTE),
MERCHANT_NAME VARCHAR2(80 BYTE),
MERCHANT_REFERENCE VARCHAR2(240 BYTE),
MERCHANT_TAX_REG_NUMBER VARCHAR2(80 BYTE),
MERCHANT_TAXPAYER_ID VARCHAR2(80 BYTE),
COUNTRY_OF_SUPPLY VARCHAR2(5 BYTE),
MATCHED_UOM_LOOKUP_CODE VARCHAR2(25 BYTE),
GMS_BURDENABLE_RAW_COST NUMBER(22,5),
ACCOUNTING_EVENT_ID NUMBER(15),
PREPAY_DISTRIBUTION_ID NUMBER(15),
CREDIT_CARD_TRX_ID NUMBER(15),
UPGRADE_POSTED_AMT NUMBER,
UPGRADE_BASE_POSTED_AMT NUMBER,
INVENTORY_TRANSFER_STATUS VARCHAR2(1 BYTE) DEFAULT 'N',
COMPANY_PREPAID_INVOICE_ID NUMBER(15),
CC_REVERSAL_FLAG VARCHAR2(1 BYTE),
PREPAY_TAX_PARENT_ID NUMBER(15),
AWT_WITHHELD_AMT NUMBER,
INVOICE_INCLUDES_PREPAY_FLAG VARCHAR2(1 BYTE),
PRICE_CORRECT_INV_ID NUMBER(15),
PRICE_CORRECT_QTY NUMBER,
PA_CMT_XFACE_FLAG VARCHAR2(1 BYTE),
CANCELLATION_FLAG VARCHAR2(1 BYTE),
FULLY_PAID_ACCTD_FLAG VARCHAR2(1 BYTE),
ROOT_DISTRIBUTION_ID NUMBER(15),
XINV_PARENT_REVERSAL_ID NUMBER(15),
AMOUNT_VARIANCE NUMBER,
BASE_AMOUNT_VARIANCE NUMBER,
RECURRING_PAYMENT_ID NUMBER(15)
)PARTITION BY RANGE (accounting_date)
(PARTITION AP_INVOICE_DISTR01_2006 VALUES LESS THAN (TO_DATE('2006-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION AP_INVOICE_DISTR02_2006 VALUES LESS THAN (TO_DATE('2006-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION AP_INVOICE_DISTR03_2006 VALUES LESS THAN (TO_DATE('2006-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION AP_INVOICE_DISTR04_2006 VALUES LESS THAN (TO_DATE('2006-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION AP_INVOICE_DISTR05_2006 VALUES LESS THAN (TO_DATE('2006-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION AP_INVOICE_DISTR06_2006 VALUES LESS THAN (TO_DATE('2006-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION AP_INVOICE_DISTR07_2006 VALUES LESS THAN (TO_DATE('2006-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION AP_INVOICE_DISTR08_2006 VALUES LESS THAN (TO_DATE('2006-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION AP_INVOICE_DISTR09_2006 VALUES LESS THAN (TO_DATE('2006-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION AP_INVOICE_DISTR10_2006 VALUES LESS THAN (TO_DATE('2006-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION AP_INVOICE_DISTR11_2006 VALUES LESS THAN (TO_DATE('2006-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION AP_INVOICE_DISTR12_2006 VALUES LESS THAN (TO_DATE('2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION AP_INVOICE_DISTR01_2007 VALUES LESS THAN (TO_DATE('2007-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION AP_INVOICE_DISTR02_2007 VALUES LESS THAN (TO_DATE('2007-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION AP_INVOICE_DISTR03_2007 VALUES LESS THAN (TO_DATE('2007-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION AP_INVOICE_DISTR04_2007 VALUES LESS THAN (TO_DATE('2007-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION AP_INVOICE_DISTR05_2007 VALUES LESS THAN (TO_DATE('2007-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION AP_INVOICE_DISTR06_2007 VALUES LESS THAN (TO_DATE('2007-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION AP_INVOICE_DISTR07_2007 VALUES LESS THAN (TO_DATE('2007-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION AP_INVOICE_DISTR08_2007 VALUES LESS THAN (TO_DATE('2007-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION AP_INVOICE_DISTR09_2007 VALUES LESS THAN (TO_DATE('2007-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION AP_INVOICE_DISTR10_2007 VALUES LESS THAN (TO_DATE('2007-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION AP_INVOICE_DISTR11_2007 VALUES LESS THAN (TO_DATE('2007-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION AP_INVOICE_DISTR12_2007 VALUES LESS THAN (TO_DATE('2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION AP_INVOICE_DISTR01_2008 VALUES LESS THAN (TO_DATE('2008-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION AP_INVOICE_DISTR02_2008 VALUES LESS THAN (TO_DATE('2008-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION AP_INVOICE_DISTR03_2008 VALUES LESS THAN (TO_DATE('2008-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION AP_INVOICE_DISTR04_2008 VALUES LESS THAN (TO_DATE('2008-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION AP_INVOICE_DISTR05_2008 VALUES LESS THAN (TO_DATE('2008-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION AP_INVOICE_DISTR06_2008 VALUES LESS THAN (TO_DATE('2008-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION AP_INVOICE_DISTR07_2008 VALUES LESS THAN (TO_DATE('2008-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION AP_INVOICE_DISTR08_2008 VALUES LESS THAN (TO_DATE('2008-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION AP_INVOICE_DISTR09_2008 VALUES LESS THAN (TO_DATE('2008-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION AP_INVOICE_DISTR10_2008 VALUES LESS THAN (TO_DATE('2008-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION AP_INVOICE_DISTR11_2008 VALUES LESS THAN (TO_DATE('2008-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION AP_INVOICE_DISTR12_2008 VALUES LESS THAN (TO_DATE('2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION AP_INVOICE_DISTR01_2009 VALUES LESS THAN (TO_DATE('2009-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION AP_INVOICE_DISTR02_2009 VALUES LESS THAN (TO_DATE('2009-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION AP_INVOICE_DISTR03_2009 VALUES LESS THAN (TO_DATE('2009-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION AP_INVOICE_DISTR04_2009 VALUES LESS THAN (TO_DATE('2009-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION AP_INVOICE_DISTR05_2009 VALUES LESS THAN (TO_DATE('2009-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION AP_INVOICE_DISTR06_2009 VALUES LESS THAN (TO_DATE('2009-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION AP_INVOICE_DISTR07_2009 VALUES LESS THAN (TO_DATE('2009-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION AP_INVOICE_DISTR08_2009 VALUES LESS THAN (TO_DATE('2009-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION AP_INVOICE_DISTR09_2009 VALUES LESS THAN (TO_DATE('2009-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION AP_INVOICE_DISTR10_2009 VALUES LESS THAN (TO_DATE('2009-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION AP_INVOICE_DISTR11_2009 VALUES LESS THAN (TO_DATE('2009-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION AP_INVOICE_DISTR12_2009 VALUES LESS THAN (TO_DATE('2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION AP_INVOICE_DISTR01_2010 VALUES LESS THAN (TO_DATE('2010-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION AP_INVOICE_DISTR02_2010 VALUES LESS THAN (TO_DATE('2010-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION AP_INVOICE_DISTR03_2010 VALUES LESS THAN (TO_DATE('2010-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION AP_INVOICE_DISTR04_2010 VALUES LESS THAN (TO_DATE('2010-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION AP_INVOICE_DISTR05_2010 VALUES LESS THAN (TO_DATE('2010-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION AP_INVOICE_DISTR06_2010 VALUES LESS THAN (TO_DATE('2010-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION AP_INVOICE_DISTR07_2010 VALUES LESS THAN (TO_DATE('2010-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION AP_INVOICE_DISTR08_2010 VALUES LESS THAN (TO_DATE('2010-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION AP_INVOICE_DISTR09_2010 VALUES LESS THAN (TO_DATE('2010-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION AP_INVOICE_DISTR10_2010 VALUES LESS THAN (TO_DATE('2010-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION AP_INVOICE_DISTR11_2010 VALUES LESS THAN (TO_DATE('2010-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION AP_INVOICE_DISTR12_2010 VALUES LESS THAN (TO_DATE('2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION AP_INVOICE_DISTR_MAX VALUES LESS THAN (MAXVALUE)
)
TABLESPACE APPS_TS_TX_DATA
NOCOMPRESS
NOCACHE
MONITORING;
3. Insert data from the original table to the partitioned. Use parallel DML.
ALTER SESSION ENABLE PARALLEL DML;
INSERT /*+ append parallel(A 8) */ INTO AP.AP_INVOICE_DISTRIBUTIONS_ALL_P A
SELECT /*+ parallel(B 8) */ * FROM AP.AP_INVOICE_DISTRIBUTIONS_ALL B;
COMMIT;
4. Rename the indexes of the original table(Execute the output of the script)
SELECT 'alter index ' || owner || '.' || index_name || ' rename to ' || index_name || '_O;' SQL
FROM dba_indexes WHERE table_name = 'AP_INVOICE_DISTRIBUTIONS_ALL';
5. Create indexes to the partition table with the same columns as the original indexes.
(Create new indexes as AP user with parallel option and after creation remove the parallel degree)
SELECT 'ALTER INDEX ' || owner || '.' || index_name || ' noparallel;' SQL
FROM dba_indexes WHERE table_name = 'AP_INVOICE_DISTRIBUTIONS_ALL_P';
6. Save the source code of the original table triggers
7. Rename the triggers of the original table to OLD (Run as APPS)
SELECT 'alter trigger ' || trigger_name || ' rename to ' || trigger_name || '_O;' SQL
FROM dba_triggers WHERE table_name = 'AP_INVOICE_DISTRIBUTIONS_ALL'
8. Do the table renaming. Rename original table to OLD and the partitioned table to original.
ALTER TABLE AP.AP_INVOICE_DISTRIBUTIONS_ALL RENAME TO AP_INVOICE_DISTRIBUTIONS_ALL_O;
ALTER TABLE AP.AP_INVOICE_DISTRIBUTIONS_ALL_P RENAME TO AP_INVOICE_DISTRIBUTIONS_ALL;
9. Drop the synonyms for the OLD table and recreate to point to the new partitioned
(Run as APPS user)
DROP SYNONYM APPS.AP_INVOICE_DISTRIBUTIONS_ALL;
CREATE SYNONYM APPS.AP_INVOICE_DISTRIBUTIONS_ALL FOR AP.AP_INVOICE_DISTRIBUTIONS_ALL;
10. Grant the appropriate privileges to new partitioned table(Run as AP user)
GRANT ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT, UPDATE ON AP_INVOICE_DISTRIBUTIONS_ALL TO APPS WITH GRANT OPTION;
GRANT SELECT ON AP_INVOICE_DISTRIBUTIONS_ALL TO APPS_READONLY;
11. Create the triggers to the partitioned table(Run as APPS)
12. Calculate new statistics for the table, indexes and partitions.
13. Run Adadmin to Compile the Apps Schema, recreating the table makes many invalid objects.
14. Start the Apps tier
