Dont Shut Down That Database Use Oracle 9i Online Object Redefinition Instead

1
Dont Shut Down That Database!Use Oracle 9i
Online Object Redefinition Instead
Chris Lawson Performance Solutions Roger Schra
g
Database Specialists, Inc.

2
Session Topics

• What is online object redefinition?
• Why is this feature important?
• The dbms_redefinition package
• The online redefinition process
• Examples
• Restrictions
• Lessons learned

3
Online Object Redefinition
The ability to change the definition of a
database object without restricting the ability
of users to query and update data

• Oracle 8i provided some online capabilities for
  IOTs.
  
• Oracle 9i enables a wider range of online
  maintenance for almost all types of tables.

4
Things You Can Do With Online Redefinition

• Move a table or index to a new tablespace
• Change a tables organization (partitioning,
  index-organized, etc.)
  
• Add, remove, or rename columns in a table
• Change the data type of a column in a table
• Add new indexes to a table
• Change constraint definitions on a table

5
Why Do We Need This Feature?

• Databases are getting larger and more complex.
• DBAs are being given less down time to perform
  maintenance.
  
• Companies often insist on no downtime.
• This feature makes it possible to perform many
  important maintenance tasks online.

6
The dbms_redefinition Package
Use the five procedures in this package to
redefine an object online.

• CAN_REDEF_TABLE
• START_REDEF_TABLE
• FINISH_REDEF_TABLE
• ABORT_REDEF_TABLE
• SYNC_INTERIM_TABLE

7
Permissions Required To Redefine A Table Online

• EXECUTE on the dbms_redefinition package
• CREATE ANY TABLE
• ALTER ANY TABLE
• DROP ANY TABLE
• LOCK ANY TABLE
• SELECT ANY TABLE

Typically, youll want to use a DBA account to
redefine objects online.
8
Overview Of The Online Redefinition Process

• Step 1 Verify the table is eligible for online
  redefinition.
  
• Step 2 Create an interim table.
• Step 3 Associate the interim table with the
  table to be redefined.
  
• Step 4 Add constraints, indexes, triggers, and
  grants to the interim table.
  
• Step 5 Complete the online redefinition.

9
Step 1 Verify The Table Is Eligible For Online
Redefinition

• Confirms table to be redefined meets requirements
  for online object redefinition
  
• Call the CAN_REDEF_TABLE procedure in the
  dbms_redefinition package
  
• EXECUTE dbms_redefinition.can_redef_table
  ('FINANCE', 'RATE')
  
• Table qualifies if no exception is raised
• Exception will be raised if table does not
  qualify
  
• ORA-12089 Cannot online redefine table with no
  primary key

10
Step 2 Create An Interim Table

• Online redefinition uses an interim table to
  change the object definition without restricting
  user access
  
• Create interim table using exact column names,
  data types, organization, tablespace assignment,
  and storage clause that the redefined table
  should have

11
Step 2 Create An Interim Table

• Declare primary key on interim table, but do not
  create any other indexes, constraints, or
  triggers
  
• Oracle will copy rows from the original table
  into the interim table during the redefinition
  process
  
• Oracle will swap the names of the two tables at
  the end so the interim table will become the
  production table and vice versa

12
Step 3 Associate The Interim Table With The
Table To Be Redefined

• Call the START_REDEF_TABLE procedure
• EXECUTE dbms_redefinition.start_redef_table
  ('FINANCE', 'RATE', 'STAGING')
  
• Oracle creates a materialized view and log in
  order to populate the interim table from the
  production table and track subsequent updates to
  the production table. All rows in the production
  table are copied to the interim table

13
Step 3 Associate The Interim Table With The
Table To Be Redefined

• A column mapping must be specified when calling
  START_REDEF_TABLE if any columns are being added
  or removed, if any data types are being changed,
  or if any data is being modified
• Could take a long time if production table has
  many rows
  
• Users have full query and update capability

14
Step 4 Add Constraints, Indexes, Triggers, And
Grants To Interim Table

• Create constraints, indexes, database triggers,
  and grants on interim table desired on the
  production table at the end of the online
  redefinition process
• Features on the production table, but not the
  interim table, will disappear from the redefined
  table in the next step
  
• Create foreign keys with the DISABLE keyword

15
Step 5 Complete The Online Redefinition

• Interim table has organization, column
  definitions, indexes, constraints, triggers, and
  grants desired on redefined table. Interim table
  also has all of the rows of the production table
  except for updates occurring since Step 3 began
• Call the FINISH_REDEF_TABLE procedure
• EXECUTE dbms_redefinition.finish_redef_table
  ('FINANCE', 'RATE', 'STAGING')

16
Step 5 Complete The Online Redefinition

• Oracle uses the materialized view log to
  propagate pending updates to interim table
  
• Oracle locks production and interim tables
  briefly and swaps their names in data dictionary
  
• Oracle drops the materialized view and log and
  enables disabled foreign keys
  
• Redefinition is complete. Interim table (which
  used to be the production table) may be dropped

17
Step 5 Complete The Online Redefinition

• Step usually runs quickly (unless users have
  updated a lot of rows in the production table
  between the time Step 3 began and the time this
  step began)
• Tables are locked only briefly. At all other
  times during the entire process, users have
  complete query and update ability on the table
  undergoing online redefinition

18
Aborting An Online Redefinition

• You may cancel an online redefinition process at
  any time before calling the FINISH_REDEF_TABLE
  procedure.
  
• Abort the online redefinition by calling the
  ABORT_TABLE_REDEF procedure. Oracle drops the
  materialized view and log definitions, if they
  had been created.
• You may drop the interim table if you wish.

19
Examples Of Online Object Redefinition

• Move a table to a new tablespace and add two new
  indexes.
  
• Massage values in one column of a table and
  change the data type of another column.

20
Example 1 Move A Table To A New Tablespace And
Add Two New Indexes
Move the accts_payable table in the finance
schema to a new tablespace. At the same time, add
indexes on the vendor_name and vendor_po columns.

• ALTER TABLE...MOVE locks the table.
• Export / Import makes the table read-only during
  export and inaccessible during import.
  
• CREATE INDEX locks the table.
• Online redefinition allows full query and update
  access.

21
Step 1 Verify The Table Is Eligible For Online
Redefinition

• SQL BEGIN
• 2 dbms_redefinition.can_redef_table
• 3 ('FINANCE', 'ACCTS_PAYABLE')
• 4 END
• 5 /
•  
• PL/SQL procedure successfully completed
•  
• SQL

22
Step 2 Create An Interim Table

• SQL CREATE TABLE accts_payable_stage
• 2 (
• 3 account_id VARCHAR2(20),
• 4 vendor_name VARCHAR2(50) NOT NULL,
• 5 vendor_address VARCHAR2(50),
• 6 vendor_po VARCHAR2(20),
• 7 invoice_date DATE NOT NULL,
• 8 invoice_amount NUMBER NOT NULL,
• 9 CONSTRAINT accts_payable_pk PRIMARY KEY
  (account_id)
  
• 10 USING INDEX TABLESPACE accts_pay_ind
• 11 )
• 12 TABLESPACE accts_pay_tab
•  
• Table created.
•  
• SQL

23
Step 2 Create An Interim Table

• Same column names and data types as production
  table
  
• Primary key declared, but no other indexes or
  constraints
  
• Desired tablespaces, storage clauses, and
  organization specified

24
Step 3 Associate The Interim Table With The
Table To Be Redefined

• SQL BEGIN
• 2 dbms_redefinition.start_redef_table
• 3 ('FINANCE', 'ACCTS_PAYABLE',
• 4 'ACCTS_PAYABLE_STAGE')
• 5 END
• 6 /
•  
• PL/SQL procedure successfully completed.
•  
• SQL

25
Step 3 Associate The Interim Table With The
Table To Be Redefined

• No column mapping provided because no column
  information changed in this example
  
• Step could take a long time if accts_payable
  table has many rows
  
• Users will have full query and update ability

26
Step 4 Add Constraints, Indexes, Triggers, And
Grants To Interim Table

• SQL CREATE INDEX accts_payable_n1 ON
  accts_payable_stage (vendor_name)
  
• 2 TABLESPACE accts_pay_ind
•  
• Index created.
•  
• SQL CREATE INDEX accts_payable_n2 ON
  accts_payable_stage (vendor_po)
  
• 2 TABLESPACE accts_pay_ind
•  
• Index created.
•  
• SQL CREATE INDEX accts_payable_n3 ON
  accts_payable_stage (invoice_date)
  
• 2 TABLESPACE accts_pay_ind
•  
• Index created.
•  
• SQL

27
Step 4 Add Constraints, Indexes, Triggers, And
Grants To Interim Table

• Add to interim table whatever features are
  desired on redefined table
  
• Could be quite different from what exists on
  original table (new indexes, different
  constraints, etc.)

28
Step 5 Complete The Online Redefinition

• SQL BEGIN
• 2 dbms_redefinition.finish_redef_table
• 3 ('FINANCE', 'ACCTS_PAYABLE',
• 4 'ACCTS_PAYABLE_STAGE')
• 5 END
• 6 /
•  
• PL/SQL procedure successfully completed.
•  
• SQL

29
Step 5 Complete The Online Redefinition

• Could take a long time if many updates occurred
  after Step 3 began
  
• Production table locked briefly near end of step
• Production and interim table names are swapped
• Drop interim table (old production table)
  manually after online redefinition is complete

30
Example 2 Massage Values In One Column Of A
Table And Change The Data Type Of Another Column
Strip trailing blanks from all values in the
description column of the invoices table. Also,
change the data type of the vendor_id column.

• SQL DESCRIBE invoices
•  
• Name Null? Type
• ---------------- -------- ----
• INVOICE_ID NOT NULL VARCHAR2(20)
• LINE_ITEM NOT NULL NUMBER
• DESCRIPTION VARCHAR2(200)
• VENDOR_ID NOT NULL NUMBER
• INVOICE_AMT NOT NULL NUMBER

31
Step 1 Verify The Table Is Eligible For Online
Redefinition

• SQL BEGIN
• 2 dbms_redefinition.can_redef_table
• 3 ('FINANCE', 'INVOICES')
• 4 END
• 5 /
•  
• PL/SQL procedure successfully completed
•  
• SQL

32
Step 2 Create An Interim Table

• SQL CREATE TABLE invoices_stage
• 2 (
• 3 invoice_id VARCHAR2(20),
• 4 line_item NUMBER NOT NULL,
• 5 description VARCHAR2(200),
• 6 vendor_id VARCHAR2(20) NOT NULL,
• 7 invoice_amt NUMBER NOT NULL,
• 8 CONSTRAINT invoices_pk PRIMARY KEY
  (invoice_id)
  
• 9 USING INDEX TABLESPACE ind
• 10 )
• 11 TABLESPACE tab
•  
• Table created.
•  
• SQL

33
Step 2 Create An Interim Table

• Data type of vendor_id column has changed from
  NUMBER to VARCHAR2(20)
  
• Tablespaces specified even though we are not
  moving table or index to new tablespace

34
Step 3 Associate The Interim Table With The
Table To Be Redefined

• SQL BEGIN
• 2 dbms_redefinition.start_redef_table
• 3 ('FINANCE', 'INVOICES',
  'INVOICES_STAGE',
  
• 4 'INVOICE_ID INVOICE_ID, LINE_ITEM
  LINE_ITEM, '
  
• 5 'RTRIM (DESCRIPTION) DESCRIPTION, '
• 6 'TO_CHAR (VENDOR_ID) VENDOR_ID, '
• 7 'INVOICE_AMT INVOICE_AMT')
• 8 END
• 9 /
•  
• PL/SQL procedure successfully completed.
•  
• SQL

35
Step 3 Associate The Interim Table With The
Table To Be Redefined

• Column mapping provided due to data massaging and
  type conversion
  
• RTRIM function to strip trailing blanks from
  description column
  
• TO_CHAR function to convert numeric vendor_id to
  VARCHAR2
  
• Invoice descriptions and vendor IDs are
  transformed as rows are copied to interim table

36
Step 4 Add Constraints, Indexes, Triggers, And
Grants To The Interim Table

• For simplicity, the invoices table in this
  example does not have any constraints, indexes,
  triggers, or grants other than the primary key
  declared when the interim table was created.

37
Step 5 Complete The Online Redefinition

• SQL BEGIN
• 2 dbms_redefinition.finish_redef_table
• 3 ('FINANCE', 'INVOICES',
  'INVOICES_STAGE')
  
• 4 END
• 5 /
•  
• PL/SQL procedure successfully completed.
•  
• SQL

38
Step 5 Complete The Online Redefinition

• At the end of the online redefinition, the
  description column is free of trailing blanks and
  the data type of the vendor_id column has been
  changed
• SQL DESCRIBE invoices
•  
• Name Null? Type
• ---------------- -------- ----
• INVOICE_ID NOT NULL VARCHAR2(20)
• LINE_ITEM NOT NULL NUMBER
• DESCRIPTION VARCHAR2(200)
• VENDOR_ID NOT NULL VARCHAR2(20)
• INVOICE_AMT NOT NULL NUMBER
•  
• SQL

39
Limitations Of The Online Redefinition Feature

• Table eligibility requirements
• Limitations of column mapping
• Row selectivity

40
Not Eligible For Online Redefinition

• Tables without a declared primary key
• Tables that are the basis of a materialized view
• Tables belonging to a cluster
• Temporary tables
• Tables in the SYS or SYSTEM schema
• Tables with FILE, LONG, or user-defined data types

41
Column Mapping Limitations

• Simple functions and expressions only
• Allowed Changing the case of a string of text or
  multiplying a numeric value by a constant
  
• Not allowed Subqueries or non-deterministic
  functions
  
• Mandatory columns must get values derived from
  existing columns

42
Row Selectivity Limitation

• Cannot eliminate rows from a table during online
  redefinition
  
• Redefined table will have all of the rows in the
  original table

43
Lessons Learned

• Testing with the interim table
• Constraint validation
• Handling foreign keys
• Column mapping
• Speeding up the FINISH_REDEF_TABLE call

44
Testing With The Interim Table
You can validate your object redefinition against
the interim table before calling the
FINISH_REDEF_TABLE procedure.

• Verify column mapping
• Validate data transformation
• Test new indexes
• Abort the redefinition if problems are
  discoveredwithout impacting production

45
Constraint Validation

• Foreign keys declared on the redefined table are
  enabled but not validated at end of redefinition
  
• Existing rows not tested for compliance
• Validating existing rows must be done manually if
  validation desired

The Oracle 9i documentation fails to mention this
point!
46
Handling Foreign Keys
Redefining a table that is a parent to foreign
keys declared on other tables presents a special
problem.

• Recall that at the end of an online object
  redefinition, the names of the original
  production table and the interim table are
  swapped.
• This means that after an online redefinition,
  foreign keys on other tables that used to
  reference the production table will now reference
  the interim table.

47
Redefining A Table That Is A Parent To Foreign
Keys Declared On Other Tables

• Declare new foreign keys in disabled state that
  reference the interim table.
  
• When redefinition is complete, the new foreign
  keys will reference the live table.
  
• Drop original foreign keys that now reference the
  interim table.
  
• Plan for locks and resources required if you wish
  new constraints to be validated.

48
Column Mapping

• Specify column map in call to START_REDEF_TABLE
  if any column names, data types, or values are
  changed
  
• Single string consisting of comma-delimited
  expression / name pairs
  
• An expression is a formula to derive column
  values from the existing production table.
  
• A name is the name of a column in the interim
  table to be loaded with the value.

49
SQL Syntax Refresher

• Strings are delimited by single quotes.
• Include a single quote in a string by specifying
  two single quotes in a row.
  
• Concatenate two strings with the operator.

50
Column Mapping Example

• Append an A to each invoice number while
  redefining the invoices table.
  
•  
• SQL BEGIN
• 2 dbms_redefinition.start_redef_table
• 3 ('FINANCE', 'INVOICES', 'INVOICES_INTERIM',
  
  
• 4 'INVOICE_ID INVOICE_ID, VENDOR_ID
  VENDOR_ID, '
  
• 5 'INVOICE_DATE INVOICE_DATE, AMOUNT
  AMOUNT, '
  
• 6 'INVOICE_NUMBER ''A''
  INVOICE_NUMBER')
  
• 7 END
• 8 /
•  
• PL/SQL procedure successfully completed.
•  
• SQL

51
Speeding Up The FINISH_REDEF_TABLE Call

• Materialized view log catches all updates to
  production table after START_REDEF_TABLE call
  
• All pending changes are propagated to interim
  table during FINISH_REDEF_TABLE call
  
• Can call SYNC_INTERIM_TABLE any time before
  FINISH_REDEF_TABLE to propagate all pending
  changes
  
• Does not speed up the overall redefinition process

52
Wrapping Up

• Oracle Corporation recognizes the need for high
  availability.
  
• By and large, the online redefinition feature of
  Oracle 9i is pretty easy to use.
  
• Tricky issues, such as foreign key handling, stem
  from the complexities of changing a table while
  users are querying and updating it.
  
• As with any new Oracle feature, test thoroughly
  before using!

53
Contact Information

• Chris Lawson
• Performance Solutions
• Tel [phone]
• Email chris_at_oraclemagician.com
• Web www.oraclemagician.com
• Roger Schrag
• Database Specialists, Inc.
• Tel [phone]
• Email rschrag_at_dbspecialists.com
• Web www.dbspecialists.com