Partitioning “SCOTT”.” CREDIT_CARD” Heap Table

Given that this table is frequently written to, it is envisaged that the Partitioning will be done with Oracle 11g redefinition.

A rough first plan is detailed below about how I think we should prepare and carry out this process.

Create new Partition Tablespace to hold Interim Table

Ensure that new Tablespace has enough space for interim table and indexes
Extend datafiles or add new datafiles if required
Double check that space is free and available

Determine and retain Object Status

Table row count

Run query to get total count per month

Select to_date(call_date,'DD-MON-YYYY'), COUNT(*) from SCOTT.CREDIT_CARD group by to_date(call_date,'DD-MON-YYYY') order by to_date(call_date,'DD-MON-YYYY');

Group by month as use as a check later on

Table column listing

Table sum bytes

Table Primary Keys

Names
Columns
Order

Table Indexes

Names
Status
Columns
Order
Creation date

Invalid Object check

No Invalid Objects in schema

Table constraints

Names
Types of constraints
Columns

Table Statistics

Gather statistics

AWR report

Take AWR Report prior to start

Query Timings

Obtain timings from queries

Full table scan, no where clause
Select within 1 month only

April 2013 (start of inserts)
August 2012 (average month increase)
October 2012 (significant jump)

Select crossing 4 months above
Select with columns other than datelogged

Backup table and indexes

Export Table and Indexes, constraints and statistics

EXPDP

Start Partitioning

Confirm table can be redefined using Primary Key

BEGIN

DBMS_REDEFINITION.CAN_REDEF_TABLE('SCOTT','CREDIT_CARD',

DBMS_REDEFINITION.CONS_USE_PK);

END;

Create New Interim Interval composite Partition Table (Range+Hash)

CREATE TABLE "SCOTT"."CREDIT_CARD_INTERIM"

    (    "USERNAME" VARCHAR2(256) NOT NULL ENABLE,

        "CALL_DATE" TIMESTAMP (6) NOT NULL ENABLE,

        "METHOD_NAME" VARCHAR2(256) NOT NULL ENABLE,

        "PARAMETERS" VARCHAR2(4000),

        "DURATION" NUMBER(*,0) NOT NULL ENABLE,

        "STATUS" NUMBER(*,0),

        "BLS_PROPID" NUMBER,

        "STATUS_MSG" VARCHAR2(4000),

        "RESPONSE_VALUES" VARCHAR2(4000),

        "REMOTE_ADDRESS" VARCHAR2(100),

        "ID" NUMBER NOT NULL ENABLE,

        "TRANSACTION_ID" NUMBER,

         CONSTRAINT "CREDIT_CARD_PRIMARY_KEY" PRIMARY KEY ("ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)TABLESPACE "DEV_INDEX"  ENABLE)

PARTITION BY RANGE(CALL_DATE)

INTERVAL(NUMTOYMINTERVAL(1, 'MONTH')) STORE IN (FLOW_1)

subpartition by hash(ID)

subpartition template(

subpartition clock_sp1,

subpartition clock_sp2,

subpartition clock_sp3,

subpartition clock_sp4,

subpartition clock_sp5,

subpartition clock_sp6,

subpartition clock_sp7,

subpartition clock_sp8)

(PARTITION "CREDIT_CARD_CALL_P1"  VALUES LESS THAN (TO_DATE(' 2010-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED   PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255  NOCOMPRESS LOGGING) ENABLE ROW MOVEMENT;

Start redefinition – using Primary Key

SQL> exec DBMS_REDEFINITION.START_REDEF_TABLE (uname =>'SCOTT',orig_table =>'CREDIT_CARD',int_table => 'CREDIT_CARD_INTERIM',col_mapping => '', options_flag => DBMS_REDEFINITION.CONS_USE_PK);

Perform a count to check status

After this both table should have near about same amount of records

SQL>select count(1) from SCOTT.CREDIT_CARD_INTRIM;

  COUNT(1)

----------

 SQL>select count(1) from SCOTT.CREDIT_CARD;

  COUNT(1)

----------

Note: There will likely be continuous inserts into the original table so there might be a few more records in original table than interim table. This Sync can be done frequently without any effect.

Sync new table on regular basis till cut off time

BEGIN

DBMS_REDEFINITION.SYNC_INTERIM_TABLE(

   uname      => SCOTT,

   orig_table => 'CREDIT_CARD',

   int_table  => 'CREDIT_CARD_INTRIM'

);

END;

Copy Dependent Objects

DECLARE

num_errors PLS_INTEGER;

BEGIN

DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(

    uname               => SCOTT,

    orig_table          => 'CREDIT_CARD',

    int_table           => 'CREDIT_CARD_INTRIM',

    copy_indexes        => DBMS_REDEFINITION.CONS_ORIG_PARAMS,

    copy_triggers       => TRUE,

    copy_constraints    => TRUE,

    copy_privileges     => TRUE,

    ignore_errors       => TRUE,

    num_errors          => num_errors);

END;

Confirm that Interim table is Partitioned before completing

SQL>select table_name, partitioned from user_tables where table_name in ('CREDIT_CARD',' CREDIT_CARD_INTRIM’);

TABLE_NAME                   PAR

------------------------------ ---

CREDIT_CARD                 NO

CRDET_CARD_INTRIM           YES

Run same query as before but against new table

Select to_date(call_date,'DD-MON-YYYY'), COUNT(*) from SCOTT.CREDIT_CARD group by to_date(call_date,'DD-MON-YYYY') order by to_date(call_date,'DD-MON-YYYY');

Check monthly counts match original query.

Complete the redefinition( Transaction may interrupt here due to exclusive put on table while ending the redefinition, better to get outage for few minutes)

BEGIN

DBMS_REDEFINITION.FINISH_REDEF_TABLE(SCOTT, ' CREDIT_CARD ', ' CREDIT_CARD_INTRIM’);

END;

Confirm that original table is now Partitioned and Interim is not

SQL>select table_name, partitioned from user_tables where table_name in ('CREDIT_CARD',' CREDIT_CARD_INTRIM’);

TABLE_NAME                   PAR

------------------------------ ---

CREDIT_CARD                 YES

CREDIT_CARD_INTRIM          NO

Now drop the interim table

Drop table CREDIT_CARD_INTRIM;

Vishwanath Nayak

Saturday, September 3, 2016

Online Redefinition - Converting Reguler (Heap) table into Partitioned table