Skip to Main Content
  • Questions

  • truncate vs delete with constraints on tables

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Kelly.

Asked: July 07, 2000 - 5:43 pm UTC

Last updated: October 17, 2016 - 4:26 pm UTC

Version: 7.3.2

Viewed 50K+ times! This question is

You Asked

Tom,
I haven't used constraints as much as this new project that
I am on.
Usually I truncate tables to clear them out as
it frees up the space etc.
Yet when I tried to truncate the tables in RF order,
I received constraint errors and it took a long time
to come back with the error.
I then went through in the same order and used
delete from tableA. This went much quicker
and I didn't receive an error.
Do you know, why I received this behavior?

Thanks

and Tom said...

You cannot truncate objects that have child tables, you would have to first drop the foriegn key and then truncate the parent table and then put the foriegn key back in place.

I have a script I use to list the create foriegn key statements for a given parent. You would run it as such (assuming you wanted to truncate DEPT)


scott@8i> @cons dept

FKEY
----------------------------------------------------------
alter table "EMP"
add constraint "EMP_FK_TO_DEPT"
foreign key ( "DEPTNO" )
references "DEPT" ( "DEPTNO");


You would then DROP the constraint EMP_FK_TO_DEPT, truncate dept and then run the above command to put the fkey back. It should be fast since the EMP table must be emptied before you can empty the dept table anyway (so rebuilding the constraint is instantaneous).





column fkey format a80 word_wrapped
select
'alter table "' || child_tname || '"' || chr(10) ||
'add constraint "' || child_cons_name || '"' || chr(10) ||
'foreign key ( ' || child_columns || ' ) ' || chr(10) ||
'references "' || parent_tname || '" ( ' || parent_columns || ')
;' fkey
from
( select a.table_name child_tname, a.constraint_name child_cons_
name,
b.r_constraint_name parent_cons_name,
max(decode(position, 1, '"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 2,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 3,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 4,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 5,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 6,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 7,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 8,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 9,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,10,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,11,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,12,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,13,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,14,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,15,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,16,', '||'"'||
substr(column_name,1,30)||'"',NULL))
child_columns
from user_cons_columns a, user_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type = 'R'
group by a.table_name, a.constraint_name, b.r_constraint_name
) child,
( select a.constraint_name parent_cons_name, a.table_name parent
_tname,
max(decode(position, 1, '"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 2,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 3,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 4,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 5,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 6,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 7,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 8,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 9,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,10,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,11,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,12,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,13,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,14,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,15,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,16,', '||'"'||
substr(column_name,1,30)||'"',NULL))
parent_columns
from user_cons_columns a, user_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type in ( 'P', 'U' )
group by a.table_name, a.constraint_name ) parent
where child.parent_cons_name = parent.parent_cons_name
and parent.parent_tname = upper('&1')
/



Rating

  (30 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, September 25, 2001 - 2:31 pm UTC 

I save the script as dropandaddcons.sql and  

SQL> @DROPANDADDCONS DEPT
old  87:   and parent.parent_tname = upper('&1')
new  87:   and parent.parent_tname = upper('DEPT')

no rows selected

SQL> /
old  87:   and parent.parent_tname = upper('&1')
new  87:   and parent.parent_tname = upper('DEPT')

no rows selected

SQL> /
old  87:   and parent.parent_tname = upper('&1')
new  87:   and parent.parent_tname = upper('DEPT')

no rows selected

SQL> /
old  87:   and parent.parent_tname = upper('&1')
new  87:   and parent.parent_tname = upper('DEPT')

no rows selected

Firstly, Iam not getting any output as you are getting, 
and secondly

when I give @dropandaddcons at the sqlplus prompt, I expect it to prompt me to enter a table name, but that is not happening, why

I dont want be prompted for a variable every time I execute the script , I dont want to pass it like @scriptname <tablename>

Tom Kyte
September 25, 2001 - 7:56 pm UTC

Silly question for you -- does your emp and dept table have declaritive RI? I added it to mine, did you to yours?

It is not prompting you because you passed DEPT in on the command line there -- &1 is the first parameter. You can add:

undefine 1

to the end of the script to make it prompt you each time.

Reader

Reader, September 25, 2001 - 11:40 pm UTC

<quote>
alter table "EMP"
add constraint "EMP_FK_TO_DEPT"
foreign key ( "DEPTNO" )
references "DEPT" ( "DEPTNO");
<quote>

Is this not true that in this case, DEPT is the parent
table and EMP is the child table. If you truncate
DEPT first, will it not produce, orphan child rows
in EMP table.

Thanks.

Tom Kyte
September 26, 2001 - 7:54 am UTC

It is true that DEPT is the parent key and EMP is the child.

If you truncate DEPT with this constraint in place -- the truncate will FAIL (not proceed). The whole purpose of this script was to generate all of the ALTER commands to put the constraints BACK on th dept table -- so you CAN drop them and truncate tables. If upon putting the constraint back on you get an error -- it means you missed a table to truncate and need to truncate it.

At no time will you have orphan rows. You might not have a parent/child relationship anymore if you truncate DEPT and have rows in EMP -- but the constraint will not be in place (hence no orphans).

Q-why not just disable the FK's

A reader, May 04, 2003 - 11:23 pm UTC

Why do u have to drop them ?

Tom Kyte
May 05, 2003 - 7:09 am UTC

you can disable them as well.

truncate table in other schema.

Sean, October 22, 2004 - 12:24 pm UTC 

Hi Tom,

I have grant select, delete, insert, update on the table to a user, but he still can not truncate the table.

-----------------------------
SQL> select * from sean.t1;

        C1
----------
         1

SQL> truncate table sean.t1;
truncate table sean.t1
                    *
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> delete from sean.t1;

1 row deleted.
-------------------------------

Thanks so much for your help.


Sean

Tom Kyte
October 22, 2004 - 6:15 pm UTC

that is correct, that is the way it is documented to work.

</code> http://docs.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_107a.htm#2067604 <code>

So, whenver you find that a privilege doesn't seem to go "granular enough" for you (you don't want to grant 'user' "drop any table") -- you use a procedure. You would create:

create or replace procedure do_truncate
as
begin
execute immediate 'truncate table t1';
end;
/

grant execute on do_truncate to 'user';



Now, 'user' can

exec do_truncate;



truncate table in another schema.

Sean, October 22, 2004 - 12:51 pm UTC

Hi Tom,

I actually found the answer on your site (using procedure).

Thanks.

Sean

Alternate for Primary key at the time of insert

NLP, December 30, 2004 - 6:14 am UTC

Hi Tom,
I want uniqueness, at the time of insert record into a table with out primary key on that column of particular table.

Thanks

Tom Kyte
December 30, 2004 - 10:49 am UTC

we call that a unique constraint.

A reader, December 30, 2004 - 4:38 pm UTC

HA HA..

Alternate for Unique & Primary key at the time of insert

NLP, December 31, 2004 - 1:17 am UTC

Hi Tom,
I want uniqueness, at the time of insert record into a table with out primary key & Unique constraint on that column of particular table.

Thanks



Tom Kyte
December 31, 2004 - 11:04 am UTC

ummm, why.

the way to do it is called a "unique constraint" and there is no other realistic method.


Help me understand one thing here "why" -- why don't you want to do the right thing? why? what possible reason on this planet could there be?

"truncate table in another schema"

A reader, January 19, 2005 - 4:48 am UTC

Can you please provide me the link for "truncate table in another schema" ..there is a posting above says that he found in your site

Thanks a lot for your consideration

Tom Kyte
January 19, 2005 - 10:43 am UTC

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1458414958491 <code>

Logic behind the truncate?

JH Suryakanth, March 03, 2005 - 11:31 pm UTC

Why can't we truncate a master table even though there are no records in the child table.

Why do we need to disable the relation?

please detailed about it.

thanks in advance.
JH Suryakanth

Tom Kyte
March 04, 2005 - 7:56 am UTC

because truncate isn't going to verify the constraint, truncate is ddl.

given that disable/enable of the constraint is going to be "pretty darn fast" if there are no rows, this is not an excessive burden on the system.

Please explain

Abhijit Mallick, March 04, 2005 - 8:31 am UTC

SCOTT@MCS.US.ORACLE.COM>create table parent
2 as select * from user_objects
3 where rownum < 20;

Table created.

SCOTT@MCS.US.ORACLE.COM>alter table parent
2 add constraint parentpk primary key(object_id);

Table altered.

SCOTT@MCS.US.ORACLE.COM>create table child
2 as select * from user_objects
3 where 1=0;

Table created.

SCOTT@MCS.US.ORACLE.COM>alter table child
2 add constraint childfk foreign key(object_id) references parent(object_id);

Table altered.

SCOTT@MCS.US.ORACLE.COM>insert into child
2 select * from user_objects
3 where object_id in (select object_id from parent);

19 rows created.

SCOTT@MCS.US.ORACLE.COM>commit;

Commit complete.

SCOTT@MCS.US.ORACLE.COM>truncate table child;

Table truncated.

SCOTT@MCS.US.ORACLE.COM>truncate table parent;
truncate table parent
*
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys


Tom can you please explain me the reason for the above error. As per my knowledge the truncate is a DDL command, so it commits automatically, as well as it does not generate any redo entry, and the HWM is also reset. My question is if no data exists in the child table how come the error is given. Is there any hidden picture behind this..
The delete command never gives this type of error ....

SCOTT@MCS.US.ORACLE.COM>delete from child;

19 rows deleted.

SCOTT@MCS.US.ORACLE.COM>delete from parent;

19 rows deleted.

SCOTT@MCS.US.ORACLE.COM>

Please explain

Tom Kyte
March 04, 2005 - 8:33 am UTC

see right above??? i just answered that.

truncate and analyze

friend, March 04, 2005 - 2:41 pm UTC

Hi Tom,

we have bunch of tables that get truncate everuweek and we are facing a big perfomance issues while loading data into these table by sqlldr.

Their analyze info is very old .
what you comment on about follwoing statement ?
"
tables get truncated every week. So thie issue is not with data access it is with Data loads."


Tom Kyte
March 04, 2005 - 6:11 pm UTC

not sure what you mean or what that last statement means.

if you are facing a big performance issue with a load, you would need to tell us how you load, indexes on or off, conventional, direct path, etc etc etc.



truncate and analyze

friend, March 04, 2005 - 7:16 pm UTC

Tom
My concern is there any relation or dependacy of table stats with sqlldr?


Tom Kyte
March 04, 2005 - 8:18 pm UTC

on insert? no, not under normal circumstances.

I mean, ok, say you used the conventional path loader, had triggers, triggers do sql, sql runs back because of bad stats -- that would be an issue maybe.

But in general, sqlldr just inserts and there is but one plan for an "insert values" statement.

truncate and analyze

friend, March 04, 2005 - 11:29 pm UTC

COOL

Have great weekend Tom.....

The speed of truncate table

Sean, May 09, 2005 - 11:38 am UTC

I want to truncate all tables in one schema, so I generated a script
by

select 'truncate table '||table_name||';' from user_tables;

When I run the script, it is just very slow to truncate tables. It takes a few minutes just to truncate one table.

It is obviously not because of constraint. Otherwise, I should receive the error. I thought that truncate table should be very fast. How can I find the problem why it is so slow? By the way, the database itself is very quite and there are not many transactions.

Thanks so much for your help.



Tom Kyte
May 09, 2005 - 11:48 am UTC

you are waiting for the blocks to be flushed from the buffer cache to disk

OR

you are using dictionary managed tablespaces and these tables have hundreds/thousands of extents.


in order to truncate safely (make it so a failure doesn't leave us halfway), the buffer cache is flushed of affected blocks.

You could make the truncates faster by checkpointing manually -- but you just move the wait of course.

speed of truncate

Sean, May 09, 2005 - 12:20 pm UTC

Does it mean that it should be very fast if I run the script again in order to truncate the rest of unempty tables in the first run because of constraint, since most of tables are empty already?

Thanks so much for your help.

Tom Kyte
May 09, 2005 - 12:30 pm UTC

no, you need to wait for any dirty blocks of the table to be flushed to disk. you are just waiting for the checkpoint to complete.

Takes long time even to truncate empty table

Sean, May 09, 2005 - 11:54 pm UTC 

Here is the result:

SQL> set timing on
SQL> truncate table t1;    # this table has 800 records.

Table truncated.

Elapsed: 00:05:56.89
SQL> truncate table t1;   # this is empty table.

Table truncated.

Elapsed: 00:08:26.99
SQL>

Tom Kyte
May 10, 2005 - 7:58 am UTC

it is not related to the volume of data in the table.

it is related to the volume of dirty data in the buffer cache.

but, enable sql_trace and trace it, what do you see? could be self induced (triggers you were not aware of) for example.

reader

A reader, September 28, 2005 - 12:22 pm UTC

what is the broad definition of DDL commands
(alter, truncate, create, grant, revoke, drop ....)
to identify a DDL since DDL has an implicit commit

Tom Kyte
September 28, 2005 - 12:38 pm UTC

how about "anything other than select, insert, update, delete or merge" as a start. Some alters do and some do not

reader

A reader, September 28, 2005 - 3:31 pm UTC 

From the following test result can I conclude 
this "drop" statement and "alter" do not have a implicit 
commit;


SQL> select * from v$mystat where statistic# = 4;

       SID STATISTIC#      VALUE
---------- ---------- ----------
        16          4          1

SQL> drop table t5;

Table dropped.

SQL> select * from v$mystat where statistic# = 4;

       SID STATISTIC#      VALUE
---------- ---------- ----------
        16          4          1

SQL> create table t5 (x int);

Table created.

SQL> select * from v$mystat where statistic# = 4;

       SID STATISTIC#      VALUE
---------- ---------- ----------
        16          4          2

SQL> create trigger trg
  2  before insert on t5
  3  begin
  4  null;
  5  end;
  6  /

Trigger created.

SQL> select * from v$mystat where statistic# = 4;

       SID STATISTIC#      VALUE
---------- ---------- ----------
        16          4          3


SQL> alter trigger trg enable;

Trigger altered.

SQL> select * from v$mystat where statistic# = 4;

       SID STATISTIC#      VALUE
---------- ---------- ----------
        16          4          3

Tom Kyte
September 28, 2005 - 5:33 pm UTC 

No, I've no idea what statistic you might be looking at the numbers are port and version dependent.  

Here is how you can see - if the row we insert can be rolled back -- no commit took place:



ops$tkyte@ORA10GR1> create table t ( x varchar2(25) );

Table created.

ops$tkyte@ORA10GR1> create or replace trigger t_trigger
  2  before insert on t
  3  begin
  4          null;
  5  end;
  6  /

Trigger created.

ops$tkyte@ORA10GR1> alter trigger t_trigger disable;

Trigger altered.

ops$tkyte@ORA10GR1> create table t1 ( x int );

Table created.

ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> insert into t values ( 'implict commit' );

1 row created.

ops$tkyte@ORA10GR1> drop table t1;

Table dropped.

ops$tkyte@ORA10GR1> rollback;

Rollback complete.

ops$tkyte@ORA10GR1> select * from t;

X
-------------------------
implict commit

ops$tkyte@ORA10GR1> delete from t;

1 row deleted.

ops$tkyte@ORA10GR1> commit;

Commit complete.

ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> insert into t values ( 'implict commit' );

1 row created.

ops$tkyte@ORA10GR1> alter trigger t_trigger enable;

Trigger altered.

ops$tkyte@ORA10GR1> rollback;

Rollback complete.

ops$tkyte@ORA10GR1> select * from t;

X
-------------------------
implict commit

some problems with Truncate table

Vijay, January 23, 2006 - 7:59 am UTC

Hi Tom,
good day to you, we are facing a weird problem on one of our production boxes, there's a script which is like a batch job, it truncates the table and data is loaded after the truncate, from last few days we are observing that the table is not getting truncated,the data population is working fine due to this the extracts are populating the new data and old data is still there, can you point out some way to identify the problem so that we can work towards it's resolution, the env is 8.1.7.4 on Solaris box.

Kind Regards,
Vijay Sehgal

Tom Kyte
January 23, 2006 - 10:35 am UTC

is there a bug in your code whereby errors thrown from the truncate are being silently ignored?

I've never heard of a truncate "succeeding" yet not removing the data.

is truncate self commit

A reader, June 19, 2006 - 7:44 pm UTC

is truncate self commit ?

Tom Kyte
June 20, 2006 - 9:34 am UTC

truncate is consider DDL and as such is processed much like this:


begin
commit;
do the truncate;
commit;
exception
when others
then
rolllback;
raise;
end;

truncate helpful

Jason Moyer, September 06, 2006 - 12:38 pm UTC

as usual - very helpfu;

Is delete more powerful than truncate?

A reader, November 16, 2007 - 5:57 am UTC

Hi,
---------------------------------------------------
(Ref : 1st question in the thread)

Tom,
I haven't used constraints as much as this new project that
I am on.
Usually I truncate tables to clear them out as
it frees up the space etc.
Yet when I tried to truncate the tables in RF order,
I received constraint errors and it took a long time
to come back with the error.
I then went through in the same order and used
delete from tableA. This went much quicker
and I didn't receive an error.
Do you know, why I received this behavior?
---------------------------------------------------
Hi,
Apologies if I have not understood which you already have explained. But I still don't understand why a truncate should fail for any foreign key constraints where as a delete will work.

Could you please explain once more.




Tom Kyte
November 21, 2007 - 10:56 am UTC

because truncate on a table that is the parent table in a primary/foreign key constraint is just NOT PERMITTED. We don't do it.

We don't care if the child table appears empty to you - we just do not do it. You have to disable the constraint, truncate, then enable (and the enable is really fast right - the tables are empty)

Drop table instead of truncate

Lalitha, February 05, 2009 - 1:11 pm UTC

Hi Tom,

I wish to drop a parent table instead of truncating it and need to recreate the new parent table with additional columns. Can I drop/disable the foreign key constraints of the child tables and drop the parent table and enable foreign key constraints?
Tom Kyte
February 05, 2009 - 2:22 pm UTC

why do you want to drop it?

truncate it and add new columns to it.

truncate vs delete

Lalitha, February 05, 2009 - 2:49 pm UTC

I cannot truncate because I need the data back.

I developed a script that

1)Takes in the parent tablename, its corresponding "Create Table" statements with the new columns , default values for the new columns, if the new columns are NOT NULL columns (passed in an associative array)

2)Copies the data from the parent table into a temporary table

3) Disables the foreign key constraints

4) Drops the table

5)Creates the new parent table that is passed as a parameter and fills in the new column values with a default value if the columns are "NOT NULL" fields, moves the data back from the temporary table to the parent table, enables foreign key constraints for the child tables.
Tom Kyte
February 05, 2009 - 3:05 pm UTC

use dbms_redefinition instead. It'll be online and you won't have to deal with the constraints so much.



(and you know, if you can drop, you can CERTAINLY truncate - so there is a flaw in the logic there somewhere)



ops$tkyte%ORA10GR2> create table t1
  2  as
  3  select * from all_objects;

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> alter table t1 add constraint t1_pk primary key(object_id);

Table altered.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create table c
  2  as
  3  select object_id from t1;

Table created.

ops$tkyte%ORA10GR2> alter table c add constraint c_fk_t1 foreign key(object_id) references t1(object_id);

Table altered.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select 'indexes', index_name from user_indexes where table_name = 'T1'
  2  union all
  3  select 'constraints', constraint_name from user_constraints where table_name = 'T1'
  4  union all
  5  select 'triggers', trigger_name from user_triggers where table_name = 'T1';

'INDEXES'   INDEX_NAME
----------- ------------------------------
indexes     T1_PK
constraints SYS_C0034257
constraints SYS_C0034258
constraints SYS_C0034259
constraints SYS_C0034260
constraints SYS_C0034261
constraints T1_PK

7 rows selected.

ops$tkyte%ORA10GR2> create table t2 (
  2  OWNER                  VARCHAR2(30),
  3  OBJECT_NAME            VARCHAR2(30),
  4  SUBOBJECT_NAME         VARCHAR2(30),
  5  OBJECT_ID              NUMBER,
  6  DATA_OBJECT_ID         NUMBER,
  7  OBJECT_TYPE            VARCHAR2(19),
  8  CREATED                DATE,
  9  LAST_DDL_TIME          DATE,
 10  TIMESTAMP              VARCHAR2(19),
 11  STATUS                 VARCHAR2(7),
 12  TEMPORARY              VARCHAR2(1),
 13  GENERATED              VARCHAR2(1),
 14  SECONDARY              VARCHAR2(1),
 15  NAMESPACE              NUMBER default 42,
 16  EDITION_NAME           VARCHAR2(30) default 'xxxx'
 17  ) partition by hash(object_id) partitions 8;

Table created.

ops$tkyte%ORA10GR2> exec dbms_redefinition.can_redef_table( user, 'T1' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> exec dbms_redefinition.start_redef_table( user, 'T1', 'T2', col_mapping => 'OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY, GENERATED, SECONDARY' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> variable nerrors number
ops$tkyte%ORA10GR2> begin
  2          dbms_redefinition.copy_table_dependents
  3          ( user, 'T1', 'T2',
  4            copy_indexes => dbms_redefinition.cons_orig_params,
  5            num_errors => :nerrors );
  6  end;
  7  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> print nerrors

   NERRORS
----------
         0

ops$tkyte%ORA10GR2> select table_name, 'indexes', index_name from user_indexes where table_name in ('T1','T2')
  2  union all
  3  select table_name, 'constraints', constraint_name from user_constraints where table_name in ('T1','T2')
  4  union all
  5  select table_name, 'triggers', trigger_name from user_triggers where table_name in ('T1','T2');

TABLE_NAME                     'INDEXES'   INDEX_NAME
------------------------------ ----------- ------------------------------
T2                             indexes     TMP$$_T1_PK0
T1                             indexes     T1_PK
T1                             constraints SYS_C0034257
T1                             constraints SYS_C0034258
T1                             constraints SYS_C0034259
T1                             constraints SYS_C0034260
T1                             constraints SYS_C0034261
T1                             constraints T1_PK
T2                             constraints TMP$$_SYS_C00342570
T2                             constraints TMP$$_SYS_C00342580
T2                             constraints TMP$$_SYS_C00342590
T2                             constraints TMP$$_SYS_C00342600
T2                             constraints TMP$$_SYS_C00342610
T2                             constraints TMP$$_T1_PK0

14 rows selected.

ops$tkyte%ORA10GR2> exec dbms_redefinition.finish_redef_table( user, 'T1', 'T2' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> drop table t2 cascade constraints;

Table dropped.

ops$tkyte%ORA10GR2> select 'indexes', index_name from user_indexes where table_name = 'T1'
  2  union all
  3  select 'constraints', constraint_name from user_constraints where table_name = 'T1'
  4  union all
  5  select 'triggers', trigger_name from user_triggers where table_name = 'T1';

'INDEXES'   INDEX_NAME
----------- ------------------------------
indexes     T1_PK
constraints SYS_C0034257
constraints SYS_C0034258
constraints SYS_C0034259
constraints SYS_C0034260
constraints SYS_C0034261
constraints T1_PK

7 rows selected.

ops$tkyte%ORA10GR2> select dbms_metadata.get_ddl( 'TABLE', 'T1' ) from dual;

DBMS_METADATA.GET_DDL('TABLE','T1')
-------------------------------------------------------------------------------

  CREATE TABLE "OPS$TKYTE"."T1"
   (    "OWNER" VARCHAR2(30) CONSTRAINT "SYS_C0034257" NOT NULL ENABLE NOVALIDATE,

        "OBJECT_NAME" VARCHAR2(30) CONSTRAINT "SYS_C0034258" NOT NULL ENABLE NOVALIDAT
E,
        "SUBOBJECT_NAME" VARCHAR2(30),
        "OBJECT_ID" NUMBER CONSTRAINT "SYS_C0034259" NOT NULL ENABLE NOVALIDATE,
        "DATA_OBJECT_ID" NUMBER,
        "OBJECT_TYPE" VARCHAR2(19),
        "CREATED" DATE CONSTRAINT "SYS_C0034260" NOT NULL ENABLE NOVALIDATE,
        "LAST_DDL_TIME" DATE CONSTRAINT "SYS_C0034261" NOT NULL ENABLE NOVALIDATE,
        "TIMESTAMP" VARCHAR2(19),
        "STATUS" VARCHAR2(7),
        "TEMPORARY" VARCHAR2(1),
        "GENERATED" VARCHAR2(1),
        "SECONDARY" VARCHAR2(1),
        "NAMESPACE" NUMBER DEFAULT 42,
        "EDITION_NAME" VARCHAR2(30) DEFAULT 'xxxx',
         CONSTRAINT "T1_PK" PRIMARY KEY ("OBJECT_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"  ENABLE NOVALIDATE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"
  PARTITION BY HASH ("OBJECT_ID")
 (PARTITION "SYS_P1535"
   TABLESPACE "USERS",
 PARTITION "SYS_P1536"
   TABLESPACE "USERS",
 PARTITION "SYS_P1537"
   TABLESPACE "USERS",
 PARTITION "SYS_P1538"
   TABLESPACE "USERS",
 PARTITION "SYS_P1539"
   TABLESPACE "USERS",
 PARTITION "SYS_P1540"
   TABLESPACE "USERS",
 PARTITION "SYS_P1541"
   TABLESPACE "USERS",
 PARTITION "SYS_P1542"
   TABLESPACE "USERS")



ops$tkyte%ORA10GR2> select dbms_metadata.get_ddl( 'TABLE', 'C' ) from dual;

DBMS_METADATA.GET_DDL('TABLE','C')
-------------------------------------------------------------------------------

  CREATE TABLE "OPS$TKYTE"."C"
   (    "OBJECT_ID" NUMBER NOT NULL ENABLE,
         CONSTRAINT "C_FK_T1" FOREIGN KEY ("OBJECT_ID")
          REFERENCES "OPS$TKYTE"."T1" ("OBJECT_ID") ENABLE NOVALIDATE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"

database offline

Lalitha, February 05, 2009 - 3:13 pm UTC

Hi Tom,

Sorry that I did not tell you that the database would be offline when this script is executed.

Thanks,
Lalitha
Tom Kyte
February 05, 2009 - 4:03 pm UTC

so? same answer.

unless you want to micromanage all of the constraints, I'm lazy myself, this way you cannot mess it up and miss a grant, constraint, trigger, whatever.

Thanks

Lalitha, February 06, 2009 - 4:32 pm UTC

Hi Tom,

Thanks for the example for dbms_redefinition. Does it allow me to add the new columns in between the old columns instead of appending them at the end?

Thanks,
Lalitha
Tom Kyte
February 09, 2009 - 5:45 pm UTC

one word for you:

view

with a view you can put columns in any order you want - you should not in any way shape or form rely on this (columns don't have to have an order to them)

but, yes, you could, the column mapping could include constants, functions, whatever. It is just a select list

you can add columns "in between" using redefinition

Chinni, February 07, 2009 - 3:21 pm UTC

Yes, we can add columns in between using dbms_redefinition.

I am adding C2 between C1 and C3 in P1

SQL> DROP TABLE C;

Table dropped.

SQL> DROP TABLE P1;

Table dropped.

SQL> DROP TABLE P2;

Table dropped.

SQL> create table P1 ( c1 number PRIMARY KEY,c3 number);

Table created.

SQL> INSERT INTO P1 VALUES (100,300);

1 row created.

SQL> create table c(c1 number references p1);

Table created.

SQL> INSERT INTO C VALUES (100);

1 row created.

SQL> CREATE TABLE P2(C1 NUMBER,C2 NUMBER,C3 NUMBER);

Table created.

SQL> exec DBMS_REDEFINITION.CAN_REDEF_TABLE(USER,'P1');

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER,'P1','P2',COL_MAPPING=>'C1 C1,1 C2, C3 C3');

PL/SQL procedure successfully completed.

SQL> variable nerrors number;
SQL> EXEC DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(USER,'P1','P2',copy_indexes => dbms_redefinition.cons_orig_params,num_errors => :nerrors );

PL/SQL procedure successfully completed.

SQL> print :nerrors;

NERRORS
----------
0
SQL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(USER,'P1','P2');

PL/SQL procedure successfully completed.

SQL> select * from p1;

C1 C2 C3
---------- ---------- ----------
100 1 300



Note: I could not add column with default value NULL using this. It gives ORA-42016 error. i used 1 as default in my case

you can add columns "in between" using redefinition

Rajeshwaran, Jeyabal, October 01, 2014 - 9:04 am UTC

Just add to_number(null) in your column_mapping parameter. that helps out.
scott@ORCL> create table p1(c1 int primary key,c3 int);

Table created.

scott@ORCL> create table c(x references p1);

Table created.

scott@ORCL> create table p2(c1 int,c2 int,c3 int);

Table created.

scott@ORCL> insert into p1 values(100,300);

1 row created.

scott@ORCL> insert into c values(100);

1 row created.

scott@ORCL> variable x number
scott@ORCL> exec dbms_redefinition.can_redef_table(user,'P1');

PL/SQL procedure successfully completed.

scott@ORCL> exec dbms_redefinition.start_redef_table(user,'P1','P2',col_mapping => 'c1 c1,to_number(null) c2,c3 c3');

PL/SQL procedure successfully completed.

scott@ORCL> exec dbms_redefinition.copy_table_dependents(user,'P1','P2',num_errors=>:x);

PL/SQL procedure successfully completed.

scott@ORCL> print x

         X
----------
         0

scott@ORCL> exec dbms_redefinition.finish_redef_table(user,'P1','P2');

PL/SQL procedure successfully completed.

scott@ORCL> set linesize 31
scott@ORCL> desc p1
 Name              Null?    Type
 ----------------- -------- ------------
 C1                         NUMBER(38)
 C2                         NUMBER(38)
 C3                         NUMBER(38)

scott@ORCL>
scott@ORCL> select table_name ,
  2    column_name,nullable, data_default
  3  from user_tab_columns
  4  where table_name ='P1' ;

TABLE COLUM N DATA_DEFAU
----- ----- - ----------
P1    C1    Y
P1    C2    Y
P1    C3    Y

3 rows selected.

scott@ORCL> select * from p1;

        C1         C2         C3
---------- ---------- ----------
       100                   300

1 row selected.

scott@ORCL>

10g script, thise works too

Adam Fletcher, October 17, 2016 - 2:45 pm UTC

Something I knocked up when having to code back to 10g workk on 10g databases.Pass in the table owner and name of the table you are going to drop, then execute. save the output (works on versions 9i,10g,11g,12c database) code will do it all in one hit.

SELECT 'ALTER TABLE ' || fk.ref_owner || '.' || fk.ref_table_name ||
' ADD CONSTRAINT ' || fk.ref_constraint || ' FOREIGN KEY (' ||
ref_column_order || ')
REFERENCES ' || pk.pk_owner || '.' || pk.pk_table_name || '(' ||
pk_column_order || ');'
FROM (SELECT substr(sys_connect_by_path(column_name, ','), 2) ref_column_order,
constraint_name AS ref_constraint,
r_constraint_name AS CONSTRAINT,
owner AS ref_owner,
table_name AS ref_table_name
FROM (SELECT ic.owner,
c.table_name,
c.constraint_name constraint_name,
c.r_constraint_name,
ic.column_name,
dense_rank() over(PARTITION BY c.constraint_name ORDER BY ic.position) rn,
COUNT(*) over(PARTITION BY c.constraint_name) cnt
FROM dba_constraints c
JOIN dba_cons_columns ic
ON ic.owner = c.owner
AND ic.constraint_name = c.constraint_name
WHERE c.constraint_type IN ('R')
AND (r_constraint_name,r_owner) IN
(SELECT cols.constraint_name,cols.owner
FROM dba_constraints cons
JOIN dba_cons_columns cols
ON cons.constraint_name = cols.constraint_name
AND cols.owner = cons.owner
WHERE cols.table_name = '&2'
AND cols.owner = '&1'
AND constraint_type IN ('P', 'U')))
WHERE rn = cnt
START WITH rn = 1
CONNECT BY rn = PRIOR rn + 1
AND constraint_name = PRIOR constraint_name) fk,
(SELECT substr(sys_connect_by_path(column_name, ','), 2) pk_column_order,
constraint_name AS pk_constraint,
owner AS pk_owner,
table_name AS pk_table_name
FROM (SELECT ic.owner,
c.table_name,
c.constraint_name constraint_name,
ic.column_name,
dense_rank() over(PARTITION BY c.constraint_name ORDER BY ic.position) rn,
COUNT(*) over(PARTITION BY c.constraint_name) cnt
FROM dba_constraints c
JOIN dba_cons_columns ic
ON ic.owner = c.owner
AND ic.constraint_name = c.constraint_name
WHERE c.constraint_type IN ('P', 'U')
AND ic.table_name = '&2'
AND ic.owner = '&1')
WHERE rn = cnt
START WITH rn = 1
CONNECT BY rn = PRIOR rn + 1
AND constraint_name = PRIOR constraint_name) pk
WHERE pk.pk_constraint = fk.constraint;
Chris Saxon
October 17, 2016 - 4:26 pm UTC

Thanks for sharing. I'd be cautious about using this though. If you create the FKs as deferrable or any other "exotic" options, you'll lose this information!
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.