Thinking Out Loud

October 29, 2012

dbms_redefinition and NOVALIDATE

Filed under: oracle — mdinh @ 4:52 am

Here are some references:

Master Note: An overview of online redefinition of tables (DBMS_REDEFINITION) [ID 1357825.1]
SYS.DBMS_REDEFINITION Fails With ORA-600 [17183] On Larger Table [ID 1458923.1]
WHY ARE ‘NOT NULL’ CONSTRAINTS NOT COPIED BY DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS? [ID 1089860.1]
AN EXAMPLE OF A COMPLEX ONLINE TABLE REDEFINITION (DBMS_REDEFINITION) [ID 1358236.1]
DBMS_REDEFINITION ONLINE REORGANIZATION OF TABLES [ID 149564.1]

Bug 13528814 – ORA-600 [17182] on redefinition with many referential constraints – superceded [ID 13528814.8]
Bug 13572659 – DBMS_REDEFINITION disables Foreign Keys used for REFERENCE partitioning [ID 13572659.8]
Bug 8507266 DBMS_REDEFINITION needs ANY privileges – cannot be used by normal users

Privileges Required
====================
Following privileges are needed to run this package:
- Execute privilege to DBMS_REDEFINITION
- Create any table
- Alter any table
- Drop any table
- Lock any table
- Select any table

If COMPATIBLE => 10.2 the constraints will be copied … but will be in NOVALIDATE mode

Here is the test case using SQL*Plus: Release 11.2.0.2.0

UPDATED: link to a nicer text format – dbms_redefinition.out

About these ads

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 272 other followers

%d bloggers like this: