A part of your job as a DBA is maintaining database availability to match your service level agreements (SLA) with your customers. But keep in mind that this SLA can also impact any required downtime for maintenance. Throughout the years, Oracle has introduced many tools and features to reduce this maintenance downtime. Today we are going to take a look at a cool feature that will help you when you have to perform maintenance on a large table, online table redefinition.
Introduced in Oracle 9i, online table redefinition allows you to modify a table without taking it offline or locking it for a long period of time. In order to do this, you will create a copy of the table with the modifications, then use the DBMS_REDEFINITION package to keep the tables in sync and to switch the tables when you are ready. There are restrictions, you can read them here.
That moment when you switch the tables, both tables will lock but in most cases, it will be very quick. Some of the simpler redefinitions (like the one we are going to see) takes less than a second. But if you are making complex modifications, it will take a longer. It all depends on the changes so I would definitely test this in a lab or sandbox environment before you try it in production.
Background
We are going to take a look at the testing that I did to work out the process to redefine a 3 tb table online. A few years ago, we had to work around a bug related to the timestamp datatype. The application vendor had to add an additional column to this table with a date datatype, change the partition key to this new column and add a trigger to populate this column. Fast forward to now, the bug was patched long ago and the vendor needs the partition key changed back to the original column so that one of the application components that we need to use can work.
Let’s do a step by step demo and I will explain as we go.
Demo
Create the table
Create a tablespace for the table
create tablespace redeftest_data datafile '+DATA01' size 4g autoextend on maxsize 10g;
Tablespace created.
Create the test user and grant privileges.
create user redeftest identified by oracletest default tablespace redeftest_data quota unlimited on redeftest_data; User created. grant connect, csl_schema_owner to redeftest; Grant succeeded.
Create the table with the required partitioning. Notice that the partitioning column is s_date_part column.
SQL> create table redeftest.emp_info(empl_id number,
2 dept_id number,
3 salary number,
4 gender varchar2(1),
5 yob date,
6 address varchar2(50),
7 s_date timestamp(6) with local time zone,
8 s_date_part date,
9 transaction_id varchar(20))
10 partition by range (s_date_part)
11 ( partition emp_2015_q1 values less than (to_date('04.01.2015','mm.dd.yyyy')) tablespace redeftest_data,
12 partition emp_2015_q2 values less than (to_date('07.01.2015','mm.dd.yyyy')) tablespace redeftest_data,
13 partition emp_2015_q3 values less than (to_date('10.01.2015','mm.dd.yyyy')) tablespace redeftest_data,
14 partition emp_2015_q4 values less than (to_date('01.01.2016','mm.dd.yyyy')) tablespace redeftest_data,
15 partition emp_2016_q1 values less than (to_date('04.01.2016','mm.dd.yyyy')) tablespace redeftest_data,
16 partition emp_2016_q2 values less than (to_date('07.01.2016','mm.dd.yyyy')) tablespace redeftest_data,
17 partition emp_2016_q3 values less than (to_date('10.01.2016','mm.dd.yyyy')) tablespace redeftest_data,
18 partition emp_2016_q4 values less than (to_date('01.01.2017','mm.dd.yyyy')) tablespace redeftest_data
19 ) logging;
Table created.
Create a trigger to update the second date column.
create or replace trigger redeftest.emp_info_s_date 2 before insert on redeftest.emp_info 3 for each row 4 begin 5 select :new.s_date 6 into :new.s_date_part 7 from dual; 8 end; 9 / Trigger created.
I needed unique transaction IDs for my test so I had to create a sequence and a trigger to sequentially assign an id to the user.
SQL> create sequence redeftest.emp_info_txn_seq; Sequence created. create or replace trigger redeftest.emp_info_txn_seq_tgr 2 before insert on redeftest.emp_info 3 for each row 4 begin 5 select emp_info_txn_seq.nextval 6 into :new.transaction_id 7 from dual; 8 end; 9 / Trigger created.
Now, let’s insert records into the table.
SQL> insert into redeftest.emp_info (
2 select level empl_id,
3 mod (rownum, 50000) dept_id,
4 trunc (dbms_random.value (1000, 500000), 2) salary,
5 decode (round (dbms_random.value (1, 2)), 1, 'M', 2, 'F') gender,
6 to_date(round(dbms_random.value(1900,extract(year from sysdate))),'YYYY') yob,
7 dbms_random.string ('x', dbms_random.value (20, 50)) address,
8 TO_DATE(TRUNC(DBMS_RANDOM.VALUE(TO_CHAR(DATE '2015-01-01','J'),TO_CHAR(DATE '2016-08-01','J'))),'J') s_date,
9 null,
10 null
11 from dual
12 connect by level < 1000001);
1000000 rows created.
SQL> commit;
Commit complete.
Gather statistics.
exec dbms_stats.gather_table_stats('REDEFTEST', 'EMP_INFO');
PL/SQL procedure successfully completed.
Begin the table redefinition
Create the interim table with the new partitioning scheme. Notice that the partitioning column is s_date, the vendor’s original partitioning column.
SQL> create table redeftest.emp_info_int(empl_id number, 2 dept_id number, 3 salary number, 4 gender varchar2(1), 5 yob date, 6 address varchar2(50), 7 s_date timestamp(6) with local time zone, 8 s_date_part date, 9 transaction_id varchar(20)) 10 partition by range (s_date) 11 ( partition emp_2015_q1 values less than (TIMESTAMP '2015-04-01 00:00:00 +00:00') tablespace redeftest_data, 12 partition emp_2015_q2 values less than (TIMESTAMP '2015-07-01 00:00:00 +00:00') tablespace redeftest_data, 13 partition emp_2015_q3 values less than (TIMESTAMP '2015-10-01 00:00:00 +00:00') tablespace redeftest_data, 14 partition emp_2015_q4 values less than (TIMESTAMP '2016-01-01 00:00:00 +00:00') tablespace redeftest_data, 15 partition emp_2016_q1 values less than (TIMESTAMP '2016-04-01 00:00:00 +00:00') tablespace redeftest_data, 16 partition emp_2016_q2 values less than (TIMESTAMP '2016-07-01 00:00:00 +00:00') tablespace redeftest_data, 17 partition emp_2016_q3 values less than (TIMESTAMP '2016-10-01 00:00:00 +00:00') tablespace redeftest_data, 18 partition emp_2016_q4 values less than (TIMESTAMP '2017-01-01 00:00:00 +00:00') tablespace redeftest_data 19 ) logging; Table created.
Show what column is used for the partition key.
col owner format a15
col name format a25 col column_name format a32 SQL> select owner, name, object_type, column_name from DBA_PART_KEY_COLUMNS where owner=’REDEFTEST’ and name like ‘EMP_INFO%’; OWNER NAME OBJECT_TYPE COLUMN_NAME ————— ————————- ——————– ——————————– REDEFTEST EMP_INFO TABLE S_DATE_PART REDEFTEST EMP_INFO_INT TABLE S_DATE 2 rows selected.
Check to see if the redefinition is possible. It fails because we have no primary key.
exec dbms_redefinition.can_redef_table('REDEFTEST', 'EMP_INFO');
SQL> exec dbms_redefinition.can_redef_table('REDEFTEST', 'EMP_INFO');
BEGIN dbms_redefinition.can_redef_table('REDEFTEST', 'EMP_INFO'); END;
*
ERROR at line 1:
ORA-12089: cannot online redefine table "REDEFTEST"."EMP_INFO" with no primary key
ORA-06512: at "SYS.DBMS_REDEFINITION", line 173
ORA-06512: at "SYS.DBMS_REDEFINITION", line 3664
ORA-06512: at line 1
Since the can_redef_table failed, we have a couple of ways that we can resolve it. We can add an option to can_redef_table and to the redef to have it use the rowid or we can create a unique index and add it as a primary key constraint on ID. For this one, I chose to create the index and constraint.
SQL> create unique index redeftest.emp_info_empl_id_idx on redeftest.emp_info (transaction_id) 2 tablespace redeftest_data nologging parallel 4; Index created. SQL> alter table redeftest.emp_info add constraint emp_info_pk primary key (transaction_id) 2 using index redeftest.emp_info_empl_id_idx; Table altered.
Show the constraints for the table.
SQL> column owner format a15; SQL> column constraint_name format a15; SQL> column constraint_type format a15; SQL> column table_name format a15; SQL> column status format a9; SQL> column index_owner format a15; SQL> column index_name format a25; SQL> column invalid format a15; SQL> SQL> select owner, constraint_name, constraint_type, table_name, 2 status, index_owner, index_name, invalid from dba_constraints 3 where owner = 'REDEFTEST' 4 and table_name like 'EMP_INFO%' 5 and constraint_name = 'EMP_INFO_PK'; OWNER CONSTRAINT_NAME CONSTRAINT_TYPE TABLE_NAME STATUS INDEX_OWNER INDEX_NAME INVALID --------------- --------------- --------------- --------------- --------- --------------- ------------------------- --------------- REDEFTEST EMP_INFO_PK P EMP_INFO ENABLED REDEFTEST EMP_INFO_EMPL_ID_IDX 1 row selected.
Check again to see if the redefinition is possible.
SQL> exec dbms_redefinition.can_redef_table('REDEFTEST', 'EMP_INFO');
PL/SQL procedure successfully completed.
Start the redefintion since there were no errors.
SQL> begin 2 dbms_redefinition.start_redef_table( 3 uname => 'REDEFTEST', 4 orig_table => 'EMP_INFO', 5 int_table => 'EMP_INFO_INT'); 6 end; 7 / PL/SQL procedure successfully completed.
Check the counts of the source and interim tables. They are in sync.
SQL> select count(*) from redeftest.emp_info; COUNT(*) ---------- 1000000 1 row selected. select count(*) from redeftest.emp_info_int; COUNT(*) ---------- 1000000 1 row selected.
Insert more records into original the table.
SQL> insert into redeftest.emp_info (
2 select level empl_id,
3 mod (rownum, 50000) dept_id,
4 trunc (dbms_random.value (1000, 500000), 2) salary,
5 decode (round (dbms_random.value (1, 2)), 1, 'M', 2, 'F') gender,
6 to_date(round(dbms_random.value(1900,extract(year from sysdate))),'YYYY') yob,
7 dbms_random.string ('x', dbms_random.value (20, 50)) address,
8 TO_DATE(TRUNC(DBMS_RANDOM.VALUE(TO_CHAR(DATE '2015-01-01','J'),TO_CHAR(DATE '2016-08-01','J'))),'J') s_date,
9 null,
10 null
11 from dual
12 connect by level < 1000001);
1000000 rows created.
SQL> commit;
Commit complete.
Check the counts of the source and interim tables. We can see now that the source table has all of our new records.
SQL> select count(*) from redeftest.emp_info; COUNT(*) ---------- 2000000 1 row selected. SQL> select count(*) from redeftest.emp_info_int; COUNT(*) ---------- 1000000 1 row selected.
Sync the tables to push the changes to interim table.
SQL> begin 2 dbms_redefinition.sync_interim_table( 3 uname => 'REDEFTEST', 4 orig_table => 'EMP_INFO', 5 int_table => 'EMP_INFO_INT'); 6 end; 7 / PL/SQL procedure successfully completed.
Check the counts of the source and interim tables again. We match!
SQL> select count(*) from redeftest.emp_info; COUNT(*) ---------- 2000000 1 row selected. SQL> select count(*) from redeftest.emp_info_int; COUNT(*) ---------- 2000000 1 row selected.
Gather statistics on the interim table again just before we complete the redefinition.
SQL> exec dbms_stats.gather_table_stats('REDEFTEST', 'EMP_INFO_INT');
PL/SQL procedure successfully completed.
Finish the redefinition. First, the final records are sent to the interim table, then the source table is renamed to a temporary name, the interim table is renamed to the source table name, the old source table is renamed to the interim table name and finally the table lock is released.
SQL> begin 2 dbms_redefinition.finish_redef_table( 3 uname => 'REDEFTEST', 4 orig_table => 'EMP_INFO', 5 int_table => 'EMP_INFO_INT'); 6 end; 7 / PL/SQL procedure successfully completed.
Show what column is used for the partition key. You can see that the table is now using the required partitioning column.
SQL> select owner, name, object_type, column_name from DBA_PART_KEY_COLUMNS where owner='REDEFTEST' and name like 'EMP_INFO%'; OWNER NAME OBJECT_TYPE COLUMN_NAME --------------- ------------------------- -------------------- -------------------------------- REDEFTEST EMP_INFO TABLE S_DATE REDEFTEST EMP_INFO_INT TABLE S_DATE_PART 2 rows selected.
Now that we are done, we can drop the interim (former source) table.
SQL> drop table redeftest.emp_info_int; Table Dropped
Online table redefinition is a simple and efficient way to make table modification without a heavy impact to your database availability. While it may not make you very popular with you storage team, it will keep your customers happy.
RSS - Posts
Leave a Reply