This discussion is archived
1 2 Previous Next 23 Replies Latest reply: May 9, 2013 12:50 PM by Alvaro Go to original post RSS
  • 15. Re: TruncateTable
    Fran Guru
    Currently Being Moderated
    well, it is just my opinion. Frustration can lead to demotivation.
  • 16. Re: TruncateTable
    yxes2013 Newbie
    Currently Being Moderated
    Hi Sunny & Alvaro,
    select 'alter table 'a.owner'.'a.table_name' disable constraint 'a.constraint_name';'
    from all_constraints a, all_constraints b
    where a.constraint_type = 'R'
    and a.r_constraint_name = b.constraint_name
    and a.r_owner = b.owner
    and b.table_name = 'APPLICATION';
    The sample script above take from the link you gave me does not work :(

    Can you try at your end if what is lacking? I suspect there should by ||' '|| ?


    Thanks
  • 17. Re: TruncateTable
    John Stegeman Oracle ACE
    Currently Being Moderated
    If you have a simple script that you cannot understand, I, for one, would certainly not be using it.

    And if, on the other hand, you're too lazy to make the simple and obvious fix and test it, then why should we?
  • 18. Re: TruncateTable
    Justin_Mungal Journeyer
    Currently Being Moderated
    John Stegeman wrote:
    If you have a simple script that you cannot understand, I, for one, would certainly not be using it.

    And if, on the other hand, you're too lazy to make the simple and obvious fix and test it, then why should we?
    Why are you still feeding the troll?
  • 19. Re: TruncateTable
    John Stegeman Oracle ACE
    Currently Being Moderated
    <zips lips>
  • 20. Re: TruncateTable
    Sunny kichloo Expert
    Currently Being Moderated
    You can easily execute the below mentioned script as mentioned in the link
    select owner,constraint_name,constraint_type,table_name,r_owner,r_constraint_name
    from all_constraints
    where constraint_type='R'
    and r_constraint_name in (select constraint_name from all_constraints
    where constraint_type in ('P','U') and table_name='TABLE_NAME');
    Then you can disable the constraints and latter truncate the table.



    And Close this thread if your issue is resolved.
  • 21. Re: TruncateTable
    yxes2013 Newbie
    Currently Being Moderated
    I want to generate the script so that it will be easier to alter specially if there hundreds of them :(
  • 22. Re: TruncateTable
    Sunny kichloo Expert
    Currently Being Moderated
    Have you executed the scripts and check how many are there ??
  • 23. Re: TruncateTable
    Alvaro Pro
    Currently Being Moderated
    You can use this one:
    >
    Select Table_Name,constraint_Name, Constraint_Type,r_Constraint_Name
    From dba_Constraints A
    Where R_Constraint_Name =(Select Constraint_Name
    From dba_Constraints B
                                  Where Table_Name='YOUR_PARENT_TABLE'
                                  And Constraint_Type='P'
                                  )
    And Constraint_Type='R'
    >


    Although, why are you trying to truncate a table with hundreds of foreign keys pointing to it ? This sounds a bit strange.

    The above script is the one I use, it identifies the child constraints.

    Edited by: Alvaro on 09/05/2013 12:49
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points