January 10, 2012

Use online redefinition to change a table into a partitioned table

Created a test user hsun


drop table log purge;
drop table children purge;
drop table parent purge;
drop view children_less_10_v;

create table parent (id number);
alter table parent add constraint pk_parent_id primary key (id);

create table log (msg varchar2(50));

create table children (id number,pid number,  constraint  pk_id  primary key  (id));
alter table children add constraint fk_pid foreign key (pid) references parent (id);
create view children_less_10_v as select * from children where id < 10;

create or replace trigger children_tri 
before insert or update on children 
for each row 
begin
  insert into log values('new' || :new.id);
end;
/   

insert into parent  select rownum from dual connect by level < 15;
insert into children  select rownum, rownum from dual connect by level < 15;
COMMIT;
exit;

Run the script status.sql that contains the following SQL:


set lin 200;
col object_name format a20
select object_name, object_type, status from user_objects order by 2, 1 ;
select trigger_name , table_name from user_triggers order by 2;
select view_name, text from user_views;
select CONSTRAINT_NAME, table_name, status from user_constraints order by 2;
select table_name, PARTITION_NAME from user_tab_partitions order by 1;

SQL> @/tmp/status.sql
OBJECT_NAME          OBJECT_TYPE         STATUS
-------------------- ------------------- -------
PK_ID                INDEX               VALID
PK_PARENT_ID         INDEX               VALID
CHILDREN             TABLE               VALID
LOG                  TABLE               VALID
PARENT               TABLE               VALID
CHILDREN_TRI         TRIGGER             VALID
CHILDREN_LESS_10_V   VIEW                VALID

7 rows selected.

TRIGGER_NAME                   TABLE_NAME
------------------------------ ------------------------------
CHILDREN_TRI                   CHILDREN

VIEW_NAME                      TEXT
------------------------------ --------------------------------------------------------------------------------
CHILDREN_LESS_10_V             select "ID","PID" from children where id < 10

CONSTRAINT_NAME                TABLE_NAME                     STATUS
------------------------------ ------------------------------ --------
FK_PID                         CHILDREN                       ENABLED
PK_ID                          CHILDREN                       ENABLED
PK_PARENT_ID                   PARENT                         ENABLED

no rows selected


Run as SYSDBA in a different session:


-- -- Check table can be redefined
EXEC DBMS_REDEFINITION.can_redef_table('HSUN', 'CHILDREN');

drop table hsun.children_p purge;
create table hsun.children_p (id number,pid number) 
 partition by range (id)
 ( partition p1 values less than (5),  
   partition p2 values less than (10),   
   partition p3 values less than (maxvalue));

-- Start Redefinition
EXEC DBMS_REDEFINITION.start_redef_table('HSUN', 'CHILDREN', 'CHILDREN_P');

SET SERVEROUTPUT ON
DECLARE
  l_num_errors PLS_INTEGER;
BEGIN
 DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname => 'HSUN',
       orig_table => 'CHILDREN',
       int_table => 'CHILDREN_P', 
       copy_indexes  => DBMS_REDEFINITION.cons_orig_params,
       num_errors => l_num_errors);
 DBMS_OUTPUT.put_line('l_num_errors=' || l_num_errors);
END;
/

-- Optionally synchronize new table with interim data before index creation
EXEC DBMS_REDEFINITION.sync_interim_table('HSUN', 'CHILDREN', 'CHILDREN_P'); 

-- Complete redefinition
EXEC DBMS_REDEFINITION.finish_redef_table( 'HSUN',  'CHILDREN', 'CHILDREN_P');

drop table hsun.children_p purge;

Run status.sql again:


SQL> @/tmp/sta

OBJECT_NAME          OBJECT_TYPE         STATUS
-------------------- ------------------- -------
PK_ID                INDEX               VALID
PK_PARENT_ID         INDEX               VALID
CHILDREN             TABLE               VALID
LOG                  TABLE               VALID
PARENT               TABLE               VALID
CHILDREN             TABLE PARTITION     VALID
CHILDREN             TABLE PARTITION     VALID
CHILDREN             TABLE PARTITION     VALID
CHILDREN_TRI         TRIGGER             INVALID
CHILDREN_LESS_10_V   VIEW                INVALID

10 rows selected.

TRIGGER_NAME                   TABLE_NAME
------------------------------ ------------------------------
CHILDREN_TRI                   CHILDREN

VIEW_NAME                      TEXT
------------------------------ --------------------------------------------------------------------------------
CHILDREN_LESS_10_V             select "ID","PID" from children where id < 10

CONSTRAINT_NAME                TABLE_NAME                     STATUS
------------------------------ ------------------------------ --------
FK_PID                         CHILDREN                       ENABLED
PK_ID                          CHILDREN                       ENABLED
PK_PARENT_ID                   PARENT                         ENABLED

TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
CHILDREN                       P1
CHILDREN                       P3
CHILDREN                       P2

The Database versions

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Solaris: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
Observation:
  • The primary key, foreign key and triggers remains “ENABLED”
  • View and trigger are “INVALID”

- No comments Not publicly viewable


Add a comment

You are not allowed to comment on this entry as it has restricted commenting permissions.

Search this blog

Search Warwick Blogs

 

Tags

Most recent comments

  • Oracle 11g is the clear leader in this area. Oracle has invested heavily into self–tuning capabiliti… by shaane on this entry
  • All the features are very nice.I like Repair Advisors the most.Oracle 10G introduced some of the dat… by lucy on this entry
  • "logical standby failed to re–start, I am also getting the same error. I have search on internet and… by anemia on this entry
  • Thanks for the nice information because from last three days i am stuck on this problem…. by robot vacuum on this entry
  • So what happen City speed dating when you tried this coding once in your PC.. Please tell me so i am… by John Bergeron on this entry
Not signed in
Sign in

Powered by BlogBuilder
© MMXXI