Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

        
 

 Moving a Table into a Partitioned Tablespace

Oracle Tips by Burleson Consulting

Advanced Oracle Utilities: The Definitive Reference by Rampant TechPress is written by the top Oracle database experts (Bert Scalzo, Donald Burleson, and Steve Callan).  The following is an excerpt from the book.

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_redefition – 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 why dbms_metadata was designed as it is. So maybe the over-engineered comments referred to earlier by others on the web was premature or assumed through ignorance. Look how easy it is to copy all dependent objects with just a single call to dbms_redefition.

 

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 famished 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;

 

Finally, here is the screen for doing the same thing via OEM. As was said before, most folks will probably go with this. But at least it is now evident what is going on inside.

 

Figure 6.2:  OEM Schema Menu/Screen

 

Figure 6.3:  OEM Reorganize Object Screen

     


For more details on Oracle utilities, see the book "Advanced Oracle Utilities" by Bert Scalzo, Donald K. Burleson, and Steve Callan.

You can buy it direct from the publisher for 30% off directly from Rampant TechPress.

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.