Partition table GL_JE_LINES

Summary 
Here are the steps to partition for example the table GL_JE_LINES 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 GL_JE_LINES is partitioned by period_name (monthly)

CREATE TABLE GL.GL_JE_LINES_P

(

  JE_HEADER_ID                  NUMBER(15)      NOT NULL,

  JE_LINE_NUM                   NUMBER(15)      NOT NULL,

  LAST_UPDATE_DATE              DATE            NOT NULL,

  LAST_UPDATED_BY               NUMBER(15)      NOT NULL,

  SET_OF_BOOKS_ID               NUMBER(15)      NOT NULL,

  CODE_COMBINATION_ID           NUMBER(15)      NOT NULL,

  PERIOD_NAME                   VARCHAR2(15 BYTE) NOT NULL,

  EFFECTIVE_DATE                DATE            NOT NULL,

  STATUS                        VARCHAR2(1 BYTE) NOT NULL,

  CREATION_DATE                 DATE,

  CREATED_BY                    NUMBER(15),

  LAST_UPDATE_LOGIN             NUMBER(15),

  ENTERED_DR                    NUMBER,

  ENTERED_CR                    NUMBER,

  ACCOUNTED_DR                  NUMBER,

  ACCOUNTED_CR                  NUMBER,

  DESCRIPTION                   VARCHAR2(240 BYTE),

  LINE_TYPE_CODE                VARCHAR2(20 BYTE),

  REFERENCE_1                   VARCHAR2(240 BYTE),

  REFERENCE_2                   VARCHAR2(240 BYTE),

  REFERENCE_3                   VARCHAR2(240 BYTE),

  REFERENCE_4                   VARCHAR2(240 BYTE),

  REFERENCE_5                   VARCHAR2(240 BYTE),

  ATTRIBUTE1                    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),

  ATTRIBUTE10                   VARCHAR2(150 BYTE),

  ATTRIBUTE11                   VARCHAR2(150 BYTE),

  ATTRIBUTE12                   VARCHAR2(150 BYTE),

  ATTRIBUTE13                   VARCHAR2(150 BYTE),

  ATTRIBUTE14                   VARCHAR2(150 BYTE),

  ATTRIBUTE15                   VARCHAR2(150 BYTE),

  ATTRIBUTE16                   VARCHAR2(150 BYTE),

  ATTRIBUTE17                   VARCHAR2(150 BYTE),

  ATTRIBUTE18                   VARCHAR2(150 BYTE),

  ATTRIBUTE19                   VARCHAR2(150 BYTE),

  ATTRIBUTE20                   VARCHAR2(150 BYTE),

  CONTEXT                       VARCHAR2(150 BYTE),

  CONTEXT2                      VARCHAR2(150 BYTE),

  INVOICE_DATE                  DATE,

  TAX_CODE                      VARCHAR2(15 BYTE),

  INVOICE_IDENTIFIER            VARCHAR2(20 BYTE),

  INVOICE_AMOUNT                NUMBER,

  NO1                           VARCHAR2(150 BYTE),

  STAT_AMOUNT                   NUMBER,

  IGNORE_RATE_FLAG              VARCHAR2(1 BYTE),

  CONTEXT3                      VARCHAR2(150 BYTE),

  USSGL_TRANSACTION_CODE        VARCHAR2(30 BYTE),

  SUBLEDGER_DOC_SEQUENCE_ID     NUMBER,

  CONTEXT4                      VARCHAR2(150 BYTE),

  SUBLEDGER_DOC_SEQUENCE_VALUE  NUMBER,

  REFERENCE_6                   VARCHAR2(240 BYTE),

  REFERENCE_7                   VARCHAR2(240 BYTE),

  GL_SL_LINK_ID                 NUMBER,

  GL_SL_LINK_TABLE              VARCHAR2(30 BYTE),

  REFERENCE_8                   VARCHAR2(240 BYTE),

  REFERENCE_9                   VARCHAR2(240 BYTE),

  REFERENCE_10                  VARCHAR2(240 BYTE),

  GLOBAL_ATTRIBUTE_CATEGORY     VARCHAR2(30 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),

  JGZZ_RECON_STATUS             VARCHAR2(1 BYTE),

  JGZZ_RECON_DATE               DATE,

  JGZZ_RECON_ID                 NUMBER,

  JGZZ_RECON_REF                VARCHAR2(240 BYTE),

  JGZZ_RECON_CONTEXT            VARCHAR2(30 BYTE),

  TAXABLE_LINE_FLAG             VARCHAR2(1 BYTE),

  TAX_TYPE_CODE                 VARCHAR2(1 BYTE),

  TAX_CODE_ID                   NUMBER(15),

  TAX_ROUNDING_RULE_CODE        VARCHAR2(1 BYTE),

  AMOUNT_INCLUDES_TAX_FLAG      VARCHAR2(1 BYTE),

  TAX_DOCUMENT_IDENTIFIER       VARCHAR2(50 BYTE),

  TAX_DOCUMENT_DATE             DATE,

  TAX_CUSTOMER_NAME             VARCHAR2(240 BYTE),

  TAX_CUSTOMER_REFERENCE        VARCHAR2(240 BYTE),

  TAX_REGISTRATION_NUMBER       VARCHAR2(50 BYTE),

  TAX_LINE_FLAG                 VARCHAR2(1 BYTE),

  TAX_GROUP_ID                  NUMBER(15)

)

PARTITION BY LIST (period_name) 

(  

  PARTITION GL_JE_LINES_01_2006 VALUES ('JAN-06'),

  PARTITION GL_JE_LINES_02_2006 VALUES ('FEB-06'),

  PARTITION GL_JE_LINES_03_2006 VALUES ('MAR-06'),

  PARTITION GL_JE_LINES_04_2006 VALUES ('APR-06'),

  PARTITION GL_JE_LINES_05_2006 VALUES ('MAY-06'),

  PARTITION GL_JE_LINES_06_2006 VALUES ('JUN-06'),

  PARTITION GL_JE_LINES_07_2006 VALUES ('JUL-06'),

  PARTITION GL_JE_LINES_08_2006 VALUES ('AUG-06'),

  PARTITION GL_JE_LINES_09_2006 VALUES ('SEP-06'),

  PARTITION GL_JE_LINES_10_2006 VALUES ('OCT-06'),

  PARTITION GL_JE_LINES_11_2006 VALUES ('NOV-06'),

  PARTITION GL_JE_LINES_12_2006 VALUES ('DEC-06'),

  PARTITION GL_JE_LINES_01_2007 VALUES ('JAN-07'),

  PARTITION GL_JE_LINES_02_2007 VALUES ('FEB-07'),

  PARTITION GL_JE_LINES_03_2007 VALUES ('MAR-07'),

  PARTITION GL_JE_LINES_04_2007 VALUES ('APR-07'),

  PARTITION GL_JE_LINES_05_2007 VALUES ('MAY-07'),

  PARTITION GL_JE_LINES_06_2007 VALUES ('JUN-07'),

  PARTITION GL_JE_LINES_07_2007 VALUES ('JUL-07'),

  PARTITION GL_JE_LINES_08_2007 VALUES ('AUG-07'),

  PARTITION GL_JE_LINES_09_2007 VALUES ('SEP-07'),

  PARTITION GL_JE_LINES_10_2007 VALUES ('OCT-07'),

  PARTITION GL_JE_LINES_11_2007 VALUES ('NOV-07'),

  PARTITION GL_JE_LINES_12_2007 VALUES ('DEC-07'),

  PARTITION GL_JE_LINES_01_2008 VALUES ('JAN-08'),

  PARTITION GL_JE_LINES_02_2008 VALUES ('FEB-08'),

  PARTITION GL_JE_LINES_03_2008 VALUES ('MAR-08'),

  PARTITION GL_JE_LINES_04_2008 VALUES ('APR-08'),

  PARTITION GL_JE_LINES_05_2008 VALUES ('MAY-08'),

  PARTITION GL_JE_LINES_06_2008 VALUES ('JUN-08'),

  PARTITION GL_JE_LINES_07_2008 VALUES ('JUL-08'),

  PARTITION GL_JE_LINES_08_2008 VALUES ('AUG-08'),

  PARTITION GL_JE_LINES_09_2008 VALUES ('SEP-08'),

  PARTITION GL_JE_LINES_10_2008 VALUES ('OCT-08'),

  PARTITION GL_JE_LINES_11_2008 VALUES ('NOV-08'),

  PARTITION GL_JE_LINES_12_2008 VALUES ('DEC-08'),

  PARTITION GL_JE_LINES_01_2009 VALUES ('JAN-09'),

  PARTITION GL_JE_LINES_02_2009 VALUES ('FEB-09'),

  PARTITION GL_JE_LINES_03_2009 VALUES ('MAR-09'),

  PARTITION GL_JE_LINES_04_2009 VALUES ('APR-09'),

  PARTITION GL_JE_LINES_05_2009 VALUES ('MAY-09'),

  PARTITION GL_JE_LINES_06_2009 VALUES ('JUN-09'),

  PARTITION GL_JE_LINES_07_2009 VALUES ('JUL-09'),

  PARTITION GL_JE_LINES_08_2009 VALUES ('AUG-09'),

  PARTITION GL_JE_LINES_09_2009 VALUES ('SEP-09'),

  PARTITION GL_JE_LINES_10_2009 VALUES ('OCT-09'),

  PARTITION GL_JE_LINES_11_2009 VALUES ('NOV-09'),

  PARTITION GL_JE_LINES_12_2009 VALUES ('DEC-09'),

  PARTITION GL_JE_LINES_01_2010 VALUES ('JAN-10'),

  PARTITION GL_JE_LINES_02_2010 VALUES ('FEB-10'),

  PARTITION GL_JE_LINES_03_2010 VALUES ('MAR-10'),

  PARTITION GL_JE_LINES_04_2010 VALUES ('APR-10'),

  PARTITION GL_JE_LINES_05_2010 VALUES ('MAY-10'),

  PARTITION GL_JE_LINES_06_2010 VALUES ('JUN-10'),

  PARTITION GL_JE_LINES_07_2010 VALUES ('JUL-10'),

  PARTITION GL_JE_LINES_08_2010 VALUES ('AUG-10'),

  PARTITION GL_JE_LINES_09_2010 VALUES ('SEP-10'),

  PARTITION GL_JE_LINES_10_2010 VALUES ('OCT-10'),

  PARTITION GL_JE_LINES_11_2010 VALUES ('NOV-10'),

  PARTITION GL_JE_LINES_12_2010 VALUES ('DEC-10')) 

  TABLESPACE APPS_TS_TX_DATA 

NOCOMPRESS 

NOCACHE

NOPARALLEL

MONITORING;

3. Insert data from the original table to the partitioned table. Use parallel DML.

ALTER SESSION ENABLE PARALLEL DML;

INSERT /*+ append parallel(A 8) */ INTO GL.GL_JE_LINES_P A 

SELECT /*+ parallel(B 8) */ * FROM GL.GL_JE_LINES 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 = 'GL_JE_LINES';

5. Create indexes to the partition table with the same columns as the original indexes. 

(Create new indexes as GL 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 = 'GL_JE_LINES';

6. GL_JE_LINES does not have any triggers so skip step 7 and go directly to step 8 

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 = 'GL_JE_LINES'

8. Do the table renaming. Rename original table to OLD and the partitioned table to original.

ALTER TABLE GL.GL_JE_LINES RENAME TO GL_JE_LINES_OLD;

ALTER TABLE GL.GL_JE_LINES_P RENAME TO GL_JE_LINES;

9. Drop the synonyms for the OLD table and recreate to point to the new partitioned (Run as APPS user)

DROP SYNONYM APPS.GL_JE_LINES FOR GL_JE_LINES;

CREATE SYNONYM APPS.GL_JE_LINES FOR GL.GL_JE_LINES;

10. Grant the appropriate privileges to new partitioned table(Run as GL user)

GRANT ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT, UPDATE ON  GL_JE_LINES TO APPS WITH GRANT OPTION;

GRANT SELECT ON  GL_JE_LINES TO APPS_READONLY;

11. No triggers to create on the new table 

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

Source: Internet