Stack Overflow
Stack Overflow

Questions tagged [dbms-redefinition]

An Oracle package that enables the online redefinition of objects.

An Oracle package that enables the online redefinition of objects.

14 questions
5
votes
4 answers

Insufficient privilege error with DBMS_REDEFINITION.start_redef_table

I am trying to partition existing tables without dropping and recreating it using Oracle. DBMS_REDEFINITION package in my Oracle 10g supporting application I have have given all the necessary permission to the user as per mentioned in the oracle…
Nitin_Sen
  • 331
  • 3
  • 5
  • 10
3
votes
2 answers

ORACLE: Error on Drop an interim table after dbms_redefinition

I'm working on Oracle11g, and trying to redefine a table with dbms_redefinition. It works ok but when trying to drop the interim table it throws an ORA-02449: unique/primary keys in table referenced by foreign keys error. I found a query to look for…
Keber
  • 61
  • 2
  • 9
3
votes
1 answer

TMP$$_ prefix in ORACLE's dbms_redefinition.copy_table_dependents

ORACLE's dbms_redefinition.copy_table_dependents copies indexes/triggers/constraints and renames them to TMP$$_[original object name] Is it possible to change the string "TMP$$_" , so that copy_table_dependents uses a different prefix? I know I can…
lxxxvi
  • 549
  • 6
  • 15
3
votes
2 answers

Oracle: How do I reorder columns using the DBMS_REDEFINITION package?

I need to reorder some columns in a table using the DBMS_REDEFINITION package. How do I do this for a table such as create table a (z number, x number); such that the reordered table has x as the first column? (context: I'm writing some utilities…
Mark Harrison
  • 297,451
  • 125
  • 333
  • 465
2
votes
0 answers

Auto partition the existing tables that we used to manually add the partitions by splitting the max partition in Oracle

I need suggestions on adding partitions on to the existing manually partitioned tables. My case: version : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Existing table data type: total rows: 13495522 CREATE TABLE…
B Red
  • 33
  • 4
2
votes
3 answers

Oracle: what does Dbms_Redefinition mean?

What is Dbms_Redefinition? Oracle docs says that it is a package which provides interface for objects redefinition. I want to understand what exactly does this "redefinition" means? How is this redefinition different from DDL and DML commands? And…
user2488578
  • 896
  • 4
  • 21
  • 40
2
votes
1 answer

Redefining a table with a user-defined type using DBMS_REDEFINITION

The application which we developed has one schema, USER1, which has tables, types, packages etc. I have to move some of the user-defined types to a different schema, USER2. USER1 has few tables which have a dependency on these user-defined types. We…
Raghavendra Acharya n
  • 93
  • 2
  • 7
1
vote
0 answers

How to convert a partitioned table into a non-partitioned table by using Oracle online redefinition with the object id kept

It is not difficult to convert a partitioned table into a non-partitioned table if we use a Oracle online redefinition and the object id of the source table is changed as a result of the operation because the interim table (non-partitioned) becomes…
Sigularity
  • 917
  • 2
  • 12
  • 28
1
vote
1 answer

mlog$_ objects without mviews after dmbs_redefinition

I'm not a DBA, but let's hope I'll be able to describe our current Problem. We have a rather large partitioned table that we reorganize regularly via dbms_redefinition.start_redef_table(..) etc. . We had collision with other stuff going on in our…
EasterBunnyBugSmasher
  • 1,507
  • 2
  • 15
  • 34
1
vote
1 answer

Space utilization in DBMS_REDEFINITION package in oracle

I am trying to create partitions on pre-existing tables in my application. While doing same, I am facing space issues in the schema's default tablespaces. Could someone please explain how the DBMS_REDEFINITION package works in terms of space…
Nitin_Sen
  • 331
  • 3
  • 5
  • 10
0
votes
1 answer

Oracle sql - Redefined table using DBMS_REDEFINITION package to enable ROWDEPENDENCIES for a table

we have tables that were created with "NOROWDEPENDENCIES" (the default). I've read in oracle documentation that the only two ways to do so are: redefine the table using the DBMS_REDEFINITION package or recreate the table. I can not recreate the…
Izik
  • 746
  • 1
  • 9
  • 25
0
votes
0 answers

oracle prevent select on table

i have a pl\sql process that run an alter table exchange partition operation. another sessions makes selects on this table, and if the select started before the alter but doesn't finished yet (alter table start during the select) - the select throws…
user2671057
  • 1,411
  • 2
  • 25
  • 43
0
votes
0 answers

Oracle select failes because of redefinition

I have a plsql code in session1 that makes redefinishion using dbms_redefinition. Another session that running a select query on the original table just failes when the finish procedure of the redefinition invoke.. with exception of…
user2671057
  • 1,411
  • 2
  • 25
  • 43
0
votes
1 answer

how to create interim table from (select * ) code in DBMS_REDEFINITION

I want to create Interim table from main table for DBMS_Redefinition Process. but the requirement is not to write entire create syntax because there are n number of tables and i cant take each value every time. INTERIM TABLE = table_INTER MAIN_TABLE…
Mayur Sawant
  • 51
  • 1
  • 7