Oracle DBA by Example. Code samples, Scripts, Reference.: Partition an existing Table with DBMS

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

Oracle DBA by Example. Code samples, Scripts, Reference.

Pages

Tuesday, June 2, 2015

Partition an existing Table with DBMS_REDEFINITION, by Example.

No comments:

Post a Comment