dbms_redefinition example
refference:
http://www.dba-oracle.com/t_dbms_redefinition_example.htm
http://www.dba-oracle.com/t_dbms_redefinition.htm
The Oracle online table reorganization package, (dbms_redefinition) is used to reorganize tables while they are accepting updates.
The online reorganization packages does this by creating a snapshot on the target table and applying all table changes after the table has been reorganized with the "Create table as select" command:
Here is a simple execution of an online table reorganization:
To solve the problem of doing table reorgs while the database accepts updates, Oracle9i has introduced Online Table Redefinitions using the DBMS_REDEFINITION package.
The dbms_redefinition package allows you to copy a table (using CTAS), create a snapshot on the
table, enqueue changes during the redefinition, and then re-synchronize the restructured table
with the changes that have accumulated during reorganization.
exec dbms_redefinition.abort_redef_table('PUBS','TITLES','TITLES2');
alter table titles add constraint pk_titles primary key (title_id);
exec dbms_redefinition.can_redef_table('PUBS','TITLES');
create table titles2
as
select * from titles;
exec dbms_redefinition.start_redef_table('PUBS','TITLES','TITLES2','title_id title_id,title
title,type type,pub_id pub_id,price price,advance advance,royalty*1.1 royalty,ytd_sales
ytd_sales,notes notes,pubdate pubdate');
exec dbms_redefinition.sync_interim_table('PUBS','TITLES','TITLES2');
exec dbms_redefinition.finish_redef_table('PUBS','TITLES','TITLES2');
If your reorganization fails, you must take special steps to make it re-start. Because the
redefinition requires creating a snapshot, you must call dbms_redefinition.abort_redef_table to
release the snapshot to re-start you procedure.
The ‘dbms_redefinition.abort_redef_table’ procedure which accepts 3 parameters (schema, original table name, holding table name), and which “pops the stack” and allows you to start over.
No database is 100% self-reliant or self-maintaining, which is a good thing for DBA job security. However, the last few major versions of Oracle have greatly increased its self-diagnostic and self-monitoring capabilities. Only database structural reorganization remains one of those tasks best left to the DBA to decide when it is appropriate to perform and when to schedule its execution. That is because data is the life blood of any modern organization, and while doing various database reorganizations, the following possibilities exist:
- The process could blow-up mid-stream, so data may be left offline
- The process is resource-intensive and takes significant time to execute
- Data could be momentarily inconsistent between key steps
- Probably advisable to consider doing a backup operation just prior to
The key point is that structural reorganizations are generally important events in any database?s life cycle. Even when a reorganization activity can theoretically be performed entirely online with little or no downtime, it is often a safer bet to perform any such activities in a controlled environment. Because the one time something that can not go wrong does, the DBA will be in a better situation to resume or recover if there are not frantic customers breathing down his neck. So schedule any reorganization event with extreme caution and over- compensation.
Now with all that said, Oracle provides a robust and reliable package for performing many common online table level reorganizations ?dbms_redefinition. Much like the dbms_metadata package, dbms_redefinitionprovides an almost limitless set of use cases or scenarios that it can address. Many people will probably just use the OEM graphical interface, but here is a very common example that should fulfill this key need as well as serve as a foundation for one?s own modifications. The following are the key basic steps:
1. Verify that the table is a candidate for online redefinition
2. Create an interim table
3. Enable parallel DML operations
4. Start the redefinition process (and do not stop until step 9 is done)
5. Copy dependent objects
6. Check for any errors
7. Synchronize the interim table (optional)
8. Complete the redefinition
9. Drop the interim table
A common question is what is happening behind the scenes here? In other words, how and what is Oracle doing? Essentially, the redefinition package is merely an API to an intelligent materialized view with a materialized view log. So a local replication of the object shows while the reorganization occurs. Then it refreshes to get up-to-date for any transaction that occurred during reorganization.
Partition a Table
One of the most common table reorganization tasks is to partition a table that is currently not partitioned but that could benefit in manageability and/or performance by becoming partitioned. It may be that this table is a throwback from an earlier Oracle database version like those that were created long ago before partitioning was available or that it simply has grown over time to the point where partitioning makes sense. Another example might be that it is partitioned, but it is so by an older partitioning method or scheme. So if one wants to rebuild a hash partitioned table using Oracle 11g?s new interval partitioning, there are many other partitioning scenarios, but the basic idea is this: the table is currently not partitioned or partitioned incorrectly, and this needs to be remedied.
Return once again to the MOVIES demo schema and partition the CUSTOMER table. And like any real world database, the rest of the database design depends on the customer, such as there are foreign keys to it. Not only that, but CUSTOMER has additional indexes and triggers. Here is the complete DDL for CUSTOMER. So as can be seen, it is much more than just a simple standalone table since there are also indexes and triggers that go with this table.
Complete CUSTOMER table DDL
CREATE TABLE "MOVIES"."CUSTOMER"
( "CUSTOMERID" NUMBER(10,0) NOT NULL ENABLE,
"FIRSTNAME" VARCHAR2(20) NOT NULL ENABLE,
"LASTNAME" VARCHAR2(30) NOT NULL ENABLE,
"PHONE" CHAR(10) NOT NULL ENABLE,
"ADDRESS" VARCHAR2(40) NOT NULL ENABLE,
"CITY" VARCHAR2(30) NOT NULL ENABLE,
"STATE" CHAR(2) NOT NULL ENABLE,
"ZIP" CHAR(5) NOT NULL ENABLE,
"BIRTHDATE" DATE,
"GENDER" CHAR(1),
CHECK (Gender in ('M','F')) ENABLE,
CHECK (CustomerId > 0) ENABLE,
CONSTRAINT "CUSTOMER_PK" PRIMARY KEY ("CUSTOMERID")
CONSTRAINT "CUSTOMER_UK" UNIQUE ("FIRSTNAME", "LASTNAME", "PHONE")
);
CREATE INDEX "MOVIES"."CUSTOMER_IE1" ON "MOVIES"."CUSTOMER" ("LASTNAME");
CREATE INDEX "MOVIES"."CUSTOMER_IE2" ON "MOVIES"."CUSTOMER" ("PHONE");
CREATE INDEX "MOVIES"."CUSTOMER_IE3" ON "MOVIES"."CUSTOMER" ("ZIP");
CREATE OR REPLACE TRIGGER "MOVIES"."CUSTOMER_CHECKS"
BEFORE INSERT OR UPDATE
ON customer
FOR EACH ROW
declare
-- Declare User Defined Exception
bad_length exception;
pragma exception_init(bad_length,-20001);
bad_date exception;
pragma exception_init(bad_date,-20002);
begin
-- Check Values for Correct Length
if (length(rtrim(:new.phone)) < 10 or
length(rtrim(:new.state)) < 2 or
length(rtrim(:new.zip)) < 5) then
raise bad_length;
end if;
-- Check Dates for Reasonableness
if (:new.birthdate > sysdate-18*365) then
raise bad_date;
end if;
-- Force Values to All Upper Case
:new.state := upper(:new.state);
:new.gender := upper(:new.gender);
exception
when bad_length then
raise_application_error(-20001, 'Illegal length: value shorter than required');
when bad_date then
raise_application_error(-20002, 'Illegal date: value fails reasonableness test');
end;
/
Step 1: Verify that the table is a candidate for online redefinition
This is a very easy step, but it is also a very critical step. If this step fails, then do not attempt to use dbms_redefinition to rebuild or redefine the table. Since it is known that customer has a primary key from reviewing the prior DD, then it can be verified that it can be used as the redefinition driver. Otherwise, redefinition must function utilizing the data?s ROWID. Remember, dbms_redefinition is simply using materialized views behind the scenes.
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE ('MOVIES', 'CUSTOMER', DBMS_REDEFINITION.CONS_USE_PK);
END;
/
Step 2: Create an interim table
Assuming that the table is a valid candidate, the interim table can then be created. This will be the partitioned table for the demonstration scenario. Note that the CREATE TABLE AS SELECT (CTAS) method is being used to save time here. The rows are not actually being copied because the SELECT WHERE clause evaluates to false. This is just a relatively easy shorthand method for the copy and, of course, adding the partitioning clause.
create table movies.customer_interim
partition by hash(zip) partitions 8
as
select * from movies.customer
where 1=0;
Step 3: Enable parallel DML operations
Now for those on multi-processor database servers, parallel operations can be enabled for the session to speed up the redefinition process. This is an optional step, but generally worth considering. Just make sure not to overdo using parallelization. If there is a very fast I/O subsystem and nothing else is really running, then consider up to two or four times of the actual CPU core count. It would also be good to check the db_writers init.ora parameter as well because it should be more than one if the choice is to force massive parallel operations that require extensive I/O. Here are the commands for this.
alter session force parallel dml parallel 4;
alter session force parallel query parallel 4;
Step 4: Start the redefinition process
From this step forward, watch the time between steps. This means that the following steps need to happen in sequence and without major delays between them. This is pointed out because some DBAs are hesitant to put these reorganization steps in a script as they want to manually monitor each step of the process. That is fine, just do not go to lunch or home between them. If everything is ready to proceed to completion, then start the redefinition process.
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE('MOVIES','CUSTOMER','CUSTOMER_INTERIM');
END;
/
Step 5: Copy dependent objects
This step performs one of the most critical and easily forgotten steps if this process was done without dbms_redefinition ? to automatically create any required triggers, indexes, materialized view logs, grants, and/or constraints on the table. If one refers back to the section about DDL extraction via dbms_metadata , it is easy to guess that Oracle is eating their own cooking internally here. Now it makes a little more sense as to whydbms_metadata was designed as it is. Look how easy it is to copy all dependent objects with just a single call to dbms_redefinition.
DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS ('MOVIES', 'CUSTOMER', 'CUSTOMER_INTERIM',
DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);
END;
/
Step 6: Check for any errors
It is advisable now to check that this last operation completed successfully. This is stated because remember that it is doing quite a few things in the background here. It is quite possible for some things to need reviewing and possibly fixed manually. In most cases there should be no rows returned, so proceed.
select object_name, base_table_name, ddl_txt from DBA_REDEFINITION_ERRORS;
Step 7: Synchronize the interim table (optional)
If there has been any activity or transaction between the start of the redefinition and now, it might be advisable to resynchronize the interim table one more time. When in doubt, it is very much like chicken soup here ? it may not help, but it will not hurt anything either.
BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE ('MOVIES', 'CUSTOMER', 'CUSTOMER_INTERIM');
END;
/
Step 8: Complete the redefinition
This step does two things: it severs the behind-the-scenes materialized view connection and swaps the data dictionary entries for the table and interim table. So now, what was the interim table is caught up on structural modifications and any data transactions. Thus, it is safe to make this data dictionary entry swap.
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE ('MOVIES', 'CUSTOMER', 'CUSTOMER_INTERIM');
END;
/
Step 9: Drop the interim table
The interim table is now finished which, as of the last step, is actually the original table via the dictionary entry swap done by the finish operation. So drop that table. And if there is a concern about the data, an option is to do a SELECT against the new original table to verify that nothing has been lost.
drop table movies.customer_interim cascade constraints purge;
浙公网安备 33010602011771号