DBMS_REDEFINITION package.
Create and populate a small lookup table.
CREATE TABLE lookup ( id NUMBER(10), description VARCHAR2(50));
ALTER TABLE lookup ADD ( CONSTRAINT lookup_pk PRIMARY KEY (id));
INSERT INTO lookup (id, description) VALUES (1, 'ONE');
INSERT INTO lookup (id, description) VALUES (2, 'TWO');
INSERT INTO lookup (id, description) VALUES (3, 'THREE');
COMMIT;
CREATE TABLE big_table ( id NUMBER(10), created_date DATE, lookup_id NUMBER(10), data VARCHAR2(50));
DECLARE
l_lookup_id lookup.id%TYPE;
l_create_date DATE;
BEGIN
FOR i IN 1 .. 1000000 LOOP
IF MOD(i, 3) = 0 THEN
l_create_date := ADD_MONTHS(SYSDATE, -24);
l_lookup_id := 2;
ELSIF MOD(i, 2) = 0 THEN
l_create_date := ADD_MONTHS(SYSDATE, -12);
l_lookup_id := 1;
ELSE
l_create_date := SYSDATE;
l_lookup_id := 3;
END IF;
INSERT INTO big_table (id, created_date, lookup_id, data)
VALUES (i, l_create_date, l_lookup_id, 'This is some data for ' i);
END LOOP;
COMMIT;
END;
/
-- Apply some constraints to the table.
ALTER TABLE big_table ADD (
CONSTRAINT big_table_pk PRIMARY KEY (id)
);
CREATE INDEX bita_created_date_i ON big_table(created_date);
CREATE INDEX bita_look_fk_i ON big_table(lookup_id);
ALTER TABLE big_table ADD (
CONSTRAINT bita_look_fk
FOREIGN KEY (lookup_id)
REFERENCES lookup(id)
);
-- Gather statistics on the schema objects
EXEC DBMS_STATS.gather_table_stats(USER, 'LOOKUP', cascade => TRUE);
EXEC DBMS_STATS.gather_table_stats(USER, 'BIG_TABLE', cascade => TRUE);
-- Create partitioned table.
CREATE TABLE big_table2 (
id NUMBER(10),
created_date DATE,
lookup_id NUMBER(10),
data VARCHAR2(50)
)
PARTITION BY RANGE (created_date)
(PARTITION big_table_2003 VALUES LESS THAN (TO_DATE('01/01/2004', 'DD/MM/YYYY')),
PARTITION big_table_2004 VALUES LESS THAN (TO_DATE('01/01/2005', 'DD/MM/YYYY')),
PARTITION big_table_2005 VALUES LESS THAN (MAXVALUE));
With this interim table in place we can start the online redefinition.EXEC Dbms_Redefinition.Can_Redef_Table(USER, 'BIG_TABLE');
If no errors are reported it is safe to start the redefintion using the following command.
BEGIN
DBMS_REDEFINITION.start_redef_table(
uname => USER,
orig_table => 'BIG_TABLE',
int_table => 'BIG_TABLE2');
END;
/
resynchronization of the interim table is initiated using the following command.
-- Optionally synchronize new table with interim data before index creation
BEGIN
dbms_redefinition.sync_interim_table(
uname => USER,
orig_table => 'BIG_TABLE',
int_table => 'BIG_TABLE2');
END;
/
-- Add new keys, FKs and triggers.
ALTER TABLE big_table2 ADD (
CONSTRAINT big_table_pk2 PRIMARY KEY (id)
);
CREATE INDEX bita_created_date_i2 ON big_table2(created_date) LOCAL;
CREATE INDEX bita_look_fk_i2 ON big_table2(lookup_id) LOCAL;
ALTER TABLE big_table2 ADD (
CONSTRAINT bita_look_fk2
FOREIGN KEY (lookup_id)
REFERENCES lookup(id)
);
-- Gather statistics on the new table.
EXEC DBMS_STATS.gather_table_stats(USER, 'BIG_TABLE2', cascade => TRUE);
BEGIN
dbms_redefinition.finish_redef_table(
uname => USER,
orig_table => 'BIG_TABLE',
int_table => 'BIG_TABLE2');
END;
/
-- Remove original table which now has the name of the interim table.
DROP TABLE big_table2;
-- Rename all the constraints and indexes to match the original names.
ALTER TABLE big_table RENAME CONSTRAINT big_table_pk2 TO big_table_pk;
ALTER TABLE big_table RENAME CONSTRAINT bita_look_fk2 TO bita_look_fk;
ALTER INDEX big_table_pk2 RENAME TO big_table_pk;
ALTER INDEX bita_look_fk_i2 RENAME TO bita_look_fk_i;
ALTER INDEX bita_created_date_i2 RENAME TO bita_created_date_i;
SELECT partitioned
FROM user_tables
WHERE table_name = 'BIG_TABLE';
PAR
---
YES
1 row selected.
SELECT partition_name
FROM user_tab_partitions
WHERE table_name = 'BIG_TABLE';
PARTITION_NAME
------------------------------
BIG_TABLE_2003
BIG_TABLE_2004
BIG_TABLE_2005
3 rows selected.
2 EJEMPLO
CREATE TABLE "DBO"."TEM_SEG_OFERTA" ("HGO_ID" NUMBER NOT NULL,
"OFT_ID" NUMBER(9) NOT NULL, "FSO_ID" NUMBER(2) NOT NULL,
"HGO_FCH_REGISTRO" DATE NOT NULL, "FUN_ID" NUMBER(4),
"HGO_DESCRIPCION" VARCHAR2(300 byte)
)
PARTITION BY RANGE (HGO_FCH_REGISTRO)
(PARTITION HIS_SEG_OFERTA_2003 VALUES LESS THAN (TO_DATE('01/01/2004', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2004 VALUES LESS THAN (TO_DATE('01/01/2005', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2005 VALUES LESS THAN (TO_DATE('01/01/2006', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2006 VALUES LESS THAN (TO_DATE('01/01/2007', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2007 VALUES LESS THAN (TO_DATE('01/01/2008', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2008 VALUES LESS THAN (TO_DATE('01/01/2009', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2009 VALUES LESS THAN (TO_DATE('01/01/2010', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2010 VALUES LESS THAN (TO_DATE('01/01/2011', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2011 VALUES LESS THAN (TO_DATE('01/01/2012', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2012 VALUES LESS THAN (TO_DATE('01/01/2013', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2013 VALUES LESS THAN (TO_DATE('01/01/2014', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2014 VALUES LESS THAN (TO_DATE('01/01/2015', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2015 VALUES LESS THAN (TO_DATE('01/01/2016', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2016 VALUES LESS THAN (TO_DATE('01/01/2017', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2017 VALUES LESS THAN (TO_DATE('01/01/2018', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2018 VALUES LESS THAN (TO_DATE('01/01/2019', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2019 VALUES LESS THAN (TO_DATE('01/01/2020', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2020 VALUES LESS THAN (TO_DATE('01/01/2021', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2021 VALUES LESS THAN (MAXVALUE)
TABLESPACE "USERS")
/
SQL> EXEC Dbms_Redefinition.can_Redef_Table(USER, 'HIS_SEG_OFERTA');
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_REDEFINITION.start_redef_table(uname => USER,orig_table => 'HIS_SEG_OFERTA',int_table => 'TEM_SEG_OFERTA');
PL/SQL procedure successfully completed.
SQL> EXEC dbms_redefinition.sync_interim_table(uname => USER,orig_table => 'HIS_SEG_OFERTA',int_table => 'TEM_SEG_OFERTA');
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'TEM_SEG_OFERTA', cascade => TRUE);
PL/SQL procedure successfully completed.
SQL> exec dbms_redefinition.finish_redef_table(uname => USER,orig_table => 'HIS_SEG_OFERTA',int_table => 'TEM_SEG_OFERTA');
PL/SQL procedure successfully completed.
SQL>
ORA-12091: cannot online redefine table with materialized views
Problem Description
While I attempt to reorganize a table online whenever I do with DBMS_REDEFINITION.can_redef_table or DBMS_REDEFINITION.START_REDEF_TABLE procedure on the table it fails with error ORA-12091 as below.
SQL> exec dbms_redefinition.can_redef_table('CR_2', 'OUT_CDR');
BEGIN dbms_redefinition.can_redef_table('CR_2', 'OUT_CDR'); END;
*
ERROR at line 1:
ORA-12091: cannot online redefine table "CR_2"."OUT_CDR" with materialized views
ORA-06512: at "SYS.DBMS_REDEFINITION", line 137
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1478
ORA-06512: at line 1
Cause of the Problem
If you unsuccessfully launched the DBMS_REDEFINITION.START_REDEF_TABLE procedure
once, the materialized view would be created on the interim table and still be remained there. So subsequent run of the DBMS_REDEFINITION.CAN_REDEF_TABLE BMS_REDEFINITION.START_REDEF_TABLE will return error until you remove the materalized view against the table.
Solution of the Problem
Solution 01:
Remove the snapshot log and materialized view created during the unsuccessful
first execution of the DBMS_REDEFINITION.START_REDEF_TABLE procedure.
You can define the snapshot log and materialized view log of current table by,
SQL> select log_table from user_snapshot_logs;
LOG_TABLE
------------------------------
MLOG$_IN_CDR
MLOG$_OUT_CDR
SQL> select master,log_table from user_mview_logs;MASTER LOG_TABLE------------------------------ ------------------------------IN_CDR MLOG$_IN_CDROUT_CDR MLOG$_OUT_CDR
Remove it by,
SQL> DROP MATERIALIZED VIEW LOG ON IN_CDR;
Materialized view log dropped.
SQL> DROP MATERIALIZED VIEW LOG ON OUT_CDR;
Materialized view log dropped.
You can use the keyword SNAPSHOT in place of MATERIALIZED VIEW. The keyword SNAPSHOT is supported in place of MATERIALIZED VIEW for backward compatibility.
Now execute your statement like,
SQL> exec dbms_redefinition.can_redef_table('CR_2', 'OUT_CDR');
PL/SQL procedure successfully completed.
And it works.
Solution 02:
Run the dbms_redefinition.abort_redef_table procedure which will automatically do the clean up task. This procedure will remove the temporary objects that are created by the redefinition process such as materialized view logs.
SQL> exec dbms_redefinition.abort_redef_table('CR_2', 'IN_CDR', 'IN_CDR_');
PL/SQL procedure successfully completed.
No hay comentarios:
Publicar un comentario