Pages

Tuesday, June 2, 2015

Partition an existing Table with DBMS_REDEFINITION, by Example.

=============================
General
=============================
Example of partitioning existing table using 
DBMS_REDEFINITION package.

Please see this post for general description of DBMS_REDEFINITION

=============================
General steps to partition existing table with DBMS_REDEFINITION
=============================

A. Check is the redefinition is possible 
DBMS_REDEFINITION.can_redef_table

B. Manual steps.
B-1. Check the space needed. 
You may need to create the partitioned table on a new Tablespace/Add datafile.

B-2. Manually create a new 
partitioned table
         This table would be manually dropped at the end of the process.

C. Start the redefinition 
DBMS_REDEFINITION.start_redef_table

D. Copy dependent objects
DBMS_REDEFINITION.copy_table_dependents
An alternative to this step is to manually create Constraints and Indexes on the new table.

E. Check for errors.
Query DBA_REDEFINITION_ERRORS

F. Synchronize data between old and new tables
DBMS_REDEFINITION.sync_interim_table

G. Complete the Redefinition Process
DBMS_REDEFINITION.finish_redef_table

At this point the partitioned table has become the "real" table and their names have been switched in the data dictionary.

H. Drop the interim table.
DROP TABLE ... PURGE;

J. Gather statistics on the new table.
EXEC DBMS_STATS.gather_table_stats

=============================
Example of using  DBMS_REDEFINITION to partition existing table step by step.
=============================

In this example, the table is quite large.
Approx 35,000,000 rows, 10Gb storage space.
It is holding audit data from DBA_FGA_AUDIT_TRAIL.
The idea is to partition the table by 
timestamp field.

Before starting the Partitioning of a table, need to make few checks
1. Check if the original table could be partitioned.
2. Check the space needed. During partitioning, a new temporary table would be created.
3. Manually create a new partitioned table.


The table before partitioning:

CREATE TABLE STATEMENTS_AUDIT
(
  session_id         NUMBER not null,
  timestamp          DATE,
  db_user            VARCHAR2(120),
  os_user            VARCHAR2(1020),
  userhost           VARCHAR2(512),
  client_id          VARCHAR2(256),
  ext_name           VARCHAR2(4000),
  object_schema      VARCHAR2(120),
  object_name        VARCHAR2(512),
  policy_name        VARCHAR2(120),
  scn                NUMBER,
  sql_text           VARCHAR2(4000),
  sql_bind           VARCHAR2(4000),
  comment$text       VARCHAR2(4000),
  statement_type     VARCHAR2(112),
  extended_timestamp TIMESTAMP(6) WITH TIME ZONE,
  proxy_sessionid    NUMBER,
  global_uid         VARCHAR2(128),
  instance_number    NUMBER,
  os_process         VARCHAR2(64),
  transactionid      RAW(8),
  statementid        NUMBER,
  entryid            NUMBER
)
tablespace COLLECT_TABLE;

CREATE INDEX STATEMENTS_AUDIT_IND1 on STATEMENTS_AUDIT (OBJECT_SCHEMA, OBJECT_NAME) tablespace COLLECT_INDEX;

There is no PK on this table.

A. Check if the Redefinition is possible.
Since there is no PK on the table, Oracle would need to work with ROWIDs.

BEGIN
  DBMS_REDEFINITION.can_redef_table ('MANAGER', 'STATEMENTS_AUDIT', DBMS_REDEFINITION.CONS_USE_ROWID);
END;
/
PL/SQL procedure successfully completed

B-1. Check the space needed. 

SELECT tablespace_name, segment_name, bytes/1024/1024 AS MB 
FROM DBA_SEGMENTS 
WHERE  segment_name='STATEMENTS_AUDIT';

TABLESPACE_NAME                SEGMENT_NAME                           MB
------------------------------ ------------------------------ ----------
COLLECT_TABLE                  STATEMENTS_AUDIT                    10128


SELECT tablespace_name, file_name, (maxbytes-user_bytes)/1024/1024 AS free_Mb  
FROM DBA_DATA_FILES 
WHERE  tablespace_name = 'COLLECT_TABLE';

TABLESPACE_NAME  FILE_NAME                                     FREE_MB
---------------- ------------------------------------------ ----------
COLLECT_TABLE    /oracle_db/db1/oraint/COLLECT_TABLE_1.dbf  3003.04687
COLLECT_TABLE    /oracle_db/db1/oraint/COLLECT_TABLE_2.dbf    580.0625

There is no additional 10Gb on the COLLECT_TABLE tablespace
The new partitioned table would be created in a new tablespace, AUDIT_TBS.

Lets create new Tablespace.
CREATE TABLESPACE AUDIT_TBS DATAFILE '/oracle_db/db1/orainst/AUDIT_TBS_1.dbf' SIZE 200M AUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
Tablespace created

B-2. Create new partitioned table on the new tablespace.

The table is partitioned by year range, on field timestamp.
The index is created with LOCAL clause.

CREATE TABLE STATEMENTS_AUDIT_PRT
(
  session_id         NUMBER not null,
  timestamp        DATE,
  db_user            VARCHAR2(120),
  os_user            VARCHAR2(1020),
  userhost           VARCHAR2(512),
  client_id          VARCHAR2(256),
  ext_name           VARCHAR2(4000),
  object_schema      VARCHAR2(120),
  object_name        VARCHAR2(512),
  policy_name        VARCHAR2(120),
  scn                NUMBER,
  sql_text           VARCHAR2(4000),
  sql_bind           VARCHAR2(4000),
  comment$text       VARCHAR2(4000),
  statement_type     VARCHAR2(112),
  extended_timestamp TIMESTAMP(6) WITH TIME ZONE,
  proxy_sessionid    NUMBER,
  global_uid         VARCHAR2(128),
  instance_number    NUMBER,
  os_process         VARCHAR2(64),
  transactionid      RAW(8),
  statementid        NUMBER,
  entryid            NUMBER
)
partition by range (timestamp)
(
  partition P_2010 values less than (TO_DATE('20110101','YYYYMMDD')) tablespace AUDIT_TBS
   storage (initial 64K minextents 1 maxextents unlimited  ),  
  partition P_2011 values less than (TO_DATE('20120101','YYYYMMDD')) tablespace AUDIT_TBS
   storage (initial 64K minextents 1 maxextents unlimited  ),     
  partition P_2012 values less than (TO_DATE('20130101','YYYYMMDD')) tablespace AUDIT_TBS
   storage (initial 64K minextents 1 maxextents unlimited  ),     
  partition P_2013 values less than (TO_DATE('20140101','YYYYMMDD')) tablespace AUDIT_TBS
   storage (initial 64K minextents 1 maxextents unlimited  ),     
  partition P_2014 values less than (TO_DATE('20150101','YYYYMMDD')) tablespace AUDIT_TBS
   storage (initial 64K minextents 1 maxextents unlimited  ),     
  partition P_2015 values less than (TO_DATE('20160101','YYYYMMDD')) tablespace AUDIT_TBS
   storage (initial 64K minextents 1 maxextents unlimited  ),     
  partition P_2016 values less than (TO_DATE('20170101','YYYYMMDD')) tablespace AUDIT_TBS
   storage (initial 64K minextents 1 maxextents unlimited  ),     
  partition P_2017 values less than (TO_DATE('20180101','YYYYMMDD')) tablespace AUDIT_TBS
   storage (initial 64K minextents 1 maxextents unlimited  ),     
  partition P_2018 values less than (TO_DATE('20190101','YYYYMMDD')) tablespace AUDIT_TBS
   storage (initial 64K minextents 1 maxextents unlimited  ),     
  partition P_2019 values less than (TO_DATE('20200101','YYYYMMDD')) tablespace AUDIT_TBS
   storage (initial 64K minextents 1 maxextents unlimited  ),     
  partition P_2020 values less than (TO_DATE('20210101','YYYYMMDD')) tablespace AUDIT_TBS
   storage (initial 64K minextents 1 maxextents unlimited  )
)

NOLOGGING;

CREATE INDEX STATEMENTS_AUDIT_IX01 ON STATEMENTS_AUDIT_PRT (OBJECT_SCHEMA, OBJECT_NAME) LOCAL;

Index created


C. Start the redefinition 
BEGIN
  DBMS_REDEFINITION.start_redef_table('MANAGER', 'STATEMENTS_AUDIT', 'STATEMENTS_AUDIT_PRT',OPTIONS_FLAG => DBMS_REDEFINITION.CONS_USE_ROWID);
END;
/
PL/SQL procedure successfully completed

This is the most heavy step.

D. Copy dependent objects
DECLARE
  num_errors PLS_INTEGER;
BEGIN
  DBMS_REDEFINITION.copy_table_dependents ('MANAGER', 'STATEMENTS_AUDIT', 'STATEMENTS_AUDIT_PRT', DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);
END;
/
PL/SQL procedure successfully completed

E. Check for errors.
SELECT object_name, base_table_name, ddl_txt 
FROM DBA_REDEFINITION_ERRORS;

F. Synchronize data between old and new tables
BEGIN
  DBMS_REDEFINITION.sync_interim_table ('MANAGER', 'STATEMENTS_AUDIT', 'STATEMENTS_AUDIT_PRT');
END;
/
PL/SQL procedure successfully completed

G. Complete the Redefintion Process
BEGIN
  DBMS_REDEFINITION.finish_redef_table ('MANAGER', 'STATEMENTS_AUDIT', 'STATEMENTS_AUDIT_PRT');
END;
/
PL/SQL procedure successfully completed


H. Drop the interim table.

Before dropping the inetrim table, check USER_TABLES, USER_SEGMENTS, USER_OBJECTS.

SELECT table_name, tablespace_name, logging, partitioned 
FROM USER_TABLES 
WHERE table_name LIKE 'STATEMENTS_AUDIT%';

TABLE_NAME                     TABLESPACE_NAME                LOGGING PARTITIONED
------------------------------ ------------------------------ ------- -----------
STATEMENTS_AUDIT                                                      YES
STATEMENTS_AUDIT_PRT           COLLECT_TABLE                  NO      NO

SELECT segment_name, partition_name, segment_type, tablespace_name 
FROM USER_SEGMENTS 
WHERE segment_name like 'STATEMENTS_AUDIT%'

SEGMENT_NAME                   PARTITION_NAME       SEGMENT_TYPE         TABLESPACE_NAME
------------------------------ -------------------- -------------------- ----------------
STATEMENTS_AUDIT_PRT                                TABLE                COLLECT_TABLE
STATEMENTS_AUDIT               P_2010               TABLE PARTITION      AUDIT_TBS
STATEMENTS_AUDIT               P_2011               TABLE PARTITION      AUDIT_TBS
STATEMENTS_AUDIT               P_2012               TABLE PARTITION      AUDIT_TBS
STATEMENTS_AUDIT               P_2013               TABLE PARTITION      AUDIT_TBS
STATEMENTS_AUDIT               P_2014               TABLE PARTITION      AUDIT_TBS
STATEMENTS_AUDIT               P_2015               TABLE PARTITION      AUDIT_TBS
STATEMENTS_AUDIT               P_2016               TABLE PARTITION      AUDIT_TBS
STATEMENTS_AUDIT               P_2017               TABLE PARTITION      AUDIT_TBS
STATEMENTS_AUDIT               P_2018               TABLE PARTITION      AUDIT_TBS
STATEMENTS_AUDIT               P_2019               TABLE PARTITION      AUDIT_TBS
STATEMENTS_AUDIT               P_2020               TABLE PARTITION      AUDIT_TBS
STATEMENTS_AUDIT_IND1                               INDEX                COLLECT_INDEX
STATEMENTS_AUDIT_IX01          P_2010               INDEX PARTITION      AUDIT_TBS
STATEMENTS_AUDIT_IX01          P_2011               INDEX PARTITION      AUDIT_TBS
STATEMENTS_AUDIT_IX01          P_2012               INDEX PARTITION      AUDIT_TBS
STATEMENTS_AUDIT_IX01          P_2013               INDEX PARTITION      AUDIT_TBS
STATEMENTS_AUDIT_IX01          P_2014               INDEX PARTITION      AUDIT_TBS
STATEMENTS_AUDIT_IX01          P_2015               INDEX PARTITION      AUDIT_TBS
STATEMENTS_AUDIT_IX01          P_2016               INDEX PARTITION      AUDIT_TBS
STATEMENTS_AUDIT_IX01          P_2017               INDEX PARTITION      AUDIT_TBS
STATEMENTS_AUDIT_IX01          P_2018               INDEX PARTITION      AUDIT_TBS
STATEMENTS_AUDIT_IX01          P_2019               INDEX PARTITION      AUDIT_TBS
STATEMENTS_AUDIT_IX01          P_2020               INDEX PARTITION      AUDIT_TBS

SELECT object_name, subobject_name, object_id, created, status 
FROM USER_OBJECTS 
WHERE object_name like 'STATEMENTS_AUDIT%';

OBJECT_NAME                    SUBOBJECT_NAME        OBJECT_ID CREATED     STATUS
------------------------------ -------------------- ---------- ----------- --------
STATEMENTS_AUDIT               P_2010                 32047566 01/06/2015  VALID
STATEMENTS_AUDIT               P_2011                 32047567 01/06/2015  VALID
STATEMENTS_AUDIT               P_2012                 32047568 01/06/2015  VALID
STATEMENTS_AUDIT               P_2013                 32047569 01/06/2015  VALID
STATEMENTS_AUDIT               P_2014                 32047570 01/06/2015  VALID
STATEMENTS_AUDIT               P_2015                 32047571 01/06/2015  VALID
STATEMENTS_AUDIT               P_2016                 32047572 01/06/2015  VALID
STATEMENTS_AUDIT               P_2017                 32047573 01/06/2015  VALID
STATEMENTS_AUDIT               P_2018                 32047574 01/06/2015  VALID
STATEMENTS_AUDIT               P_2019                 32047575 01/06/2015  VALID
STATEMENTS_AUDIT               P_2020                 32047576 01/06/2015  VALID
STATEMENTS_AUDIT                                      32047565 01/06/2015  VALID
STATEMENTS_AUDIT_IND1                                  7728253 24/03/2010  VALID
STATEMENTS_AUDIT_IX01          P_2010                 32062110 02/06/2015  VALID
STATEMENTS_AUDIT_IX01          P_2011                 32062111 02/06/2015  VALID
STATEMENTS_AUDIT_IX01          P_2012                 32062112 02/06/2015  VALID
STATEMENTS_AUDIT_IX01          P_2013                 32062113 02/06/2015  VALID
STATEMENTS_AUDIT_IX01          P_2014                 32062114 02/06/2015  VALID
STATEMENTS_AUDIT_IX01          P_2015                 32062115 02/06/2015  VALID
STATEMENTS_AUDIT_IX01          P_2016                 32062116 02/06/2015  VALID
STATEMENTS_AUDIT_IX01          P_2017                 32062117 02/06/2015  VALID
STATEMENTS_AUDIT_IX01          P_2018                 32062118 02/06/2015  VALID
STATEMENTS_AUDIT_IX01          P_2019                 32062119 02/06/2015  VALID
STATEMENTS_AUDIT_IX01          P_2020                 32062120 02/06/2015  VALID
STATEMENTS_AUDIT_IX01                                 32062109 02/06/2015  VALID

STATEMENTS_AUDIT_PRT                                   1825644 26/12/2007  VALID


DROP TABLE MANAGER.STATEMENTS_AUDIT_PRT CASCADE CONSTRAINTS PURGE;

Table dropped

J. Gather statistics on the new table.
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS (ownname =>  'MANAGER', tabname => 'STATEMENTS_AUDIT');
END;
/

PL/SQL procedure successfully completed

SELECT table_name, partition_name, num_rows, sample_size, last_analyzed   
FROM USER_TAB_PARTITIONS
WHERE table_name = 'STATEMENTS_AUDIT'

TABLE_NAME                     PARTITION_NAME         NUM_ROWS SAMPLE_SIZE LAST_ANALYZED
------------------------------ -------------------- ---------- ----------- -------------
STATEMENTS_AUDIT               P_2020                        0             02/06/2015 08
STATEMENTS_AUDIT               P_2019                        0             02/06/2015 08
STATEMENTS_AUDIT               P_2018                        0             02/06/2015 08
STATEMENTS_AUDIT               P_2017                        0             02/06/2015 08
STATEMENTS_AUDIT               P_2016                        0             02/06/2015 08
STATEMENTS_AUDIT               P_2015                 12236078      573669 02/06/2015 08
STATEMENTS_AUDIT               P_2014                 22430950      529563 02/06/2015 08
STATEMENTS_AUDIT               P_2013                    11103       11103 02/06/2015 08
STATEMENTS_AUDIT               P_2012                    41490       41490 02/06/2015 08
STATEMENTS_AUDIT               P_2011                    56042       56042 02/06/2015 08STATEMENTS_AUDIT               P_2010                   949638      949638 02/06/2015 08

=============================
Optionally - drop old partitions
=============================
ALTER TABLE STATEMENTS_AUDIT DROP PARTITION P_2010


No comments:

Post a Comment