Remove Oracle Partitioning

This document is a run through to removing the Partitioning DB option and moving any partitioned tables to normal tables. The procedure works against standalone databases, standby dataguard databases and those databases replicated using Golden Gate. 

Scenarios:

1. Standalone Database - Run scripts in Order
   
   
2. Dataguard Standby Database - Run scripts on Primary instance. Changes will be replicated to standby. Remove Partitioning from Oracle binaries on primary & standby and restart both the primary & standby databases.
   
   
3. Golden Gate Replication - Stop replication on all instances. Run the scripts on each database. Start replication.

Procedure:

• Login to database server as oracle user
• Set your environment to the database i.e oraenv
• If you have GG running, stop replication on all databases
• Connect as sysdba
• ON EACH DATABASE, run following SQL to confirm partitioning is enabled:
  
  

SELECT * FROM v$option WHERE parameter = 'Partitioning';

PARAMETER              VALUE
-------------------- --------------------
Partitioning           TRUE

• Check the schema objects which have partitioned tables:

select table_owner, table_name from ALL_TAB_PARTITIONS where table_owner in ('USER1','USER2','USER3','USER4') group by table_owner, table_name;

TABLE_OWNER            TABLE_NAME
-------------------- ------------------------------
USER1                  TABLEA
USER4                  TABLEB
USER2                  TABLEC

• Check invalid object count e.g

select owner, count(1) from dba_objects where status != 'VALID' and owner in ('USER2','USER1','USER3','USER4') group by owner;

OWNER                           COUNT(1)
------------------------------ ----------
USER2                              64
USER1                              26
USER4                              11
USER3                               1

• ON EACH DATABASE (not standby):
  • Generate 'Metadata DDL' scripts to re-create the table, indexes, triggers etc..
  • Run scripts as schema i.e
    • create table tablea_new as select * from tablea;
    • alter table tablea rename to tablea_old;
    • alter table tablea_new rename to tablea;
    • drop trigger tablea_trg;
    • CREATE OR REPLACE TRIGGER user1.tablea_trg BEFORE INSERT OR UPDATE ON user1.tablea FOR EACH ROW BEGIN :new.tstamp := sys_extract_utc(CURRENT_TIMESTAMP); END; /
    • drop table tablea_OLD;
    • purge recyclebin;
  • Connect as sysdba and recompile objects:
    
    

@?/rdbms/admin/utlrp.sql

• Check invalid object count again e.g

select owner, count(1) from dba_objects where status != 'VALID' and owner in ('USER2','USER1','USER3','USER4') group by owner;

OWNER                           COUNT(1)
------------------------------ ----------
USER2                              64
USER1                              26
USER4                              11
USER3                               1

oracle RDBMS server1:> chopt disable partitioning

Writing to /u01/app/oracle/product/11.2.0/db_1/install/disable_partitioning.log...
/usr/bin/make -f /u01/app/oracle/product/11.2.0/db_1/rdbms/lib/ins_rdbms.mk part_off ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
/usr/bin/make -f /u01/app/oracle/product/11.2.0/db_1/rdbms/lib/ins_rdbms.mk ioracle ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1

server2:oracle>. oraenv
ORACLE_SID = [USER2DB] ? USER2DB

server2:oracle>cd $ORACLE_HOME/oui/bin
server2:oracle>./runInstaller

SELECT * FROM v$option WHERE parameter = 'Partitioning';

PARAMETER              VALUE
-------------------- --------------------
Partitioning           FALSE

select table_owner, table_name from ALL_TAB_PARTITIONS where table_owner in ('USER2','USER1','USER3','USER4');

no rows selected