| Deleting Orphans After Export [message #295540] |
Tue, 22 January 2008 11:43  |
faiz_hyd
Messages: 294
Registered: February 2006
|
Senior Member |
|
|
Hi,
Is there a Pl Sql or Sql Script which can help me in
cleaning Orphan records ( children without parent )which came as a result of Export
from Last night export on a live PROD system.
What i used to do is run simple delete 1 by 1 table from bottom up approach on all tables
delete from X where not exists (select id from Y );
I have to run one by one statements for all (majority of Transactional Tables ).
DBA's don't want to use Consistent =Y on huge PROD system( being constantly accessed OLTP ).
they it will have impact
Is there a SQL or Pl SQL which will generate a list of delete statements from bottom Up approach on all the tables, This we have to run frequently when ever we take export from a live system for building new regions.
Oracle Version 10g Rel 2.
Thanks
[Updated on: Tue, 22 January 2008 11:45] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Deleting Orphans After Export [message #295557 is a reply to message #295550] |
Tue, 22 January 2008 13:41  |
faiz_hyd
Messages: 294
Registered: February 2006
|
Senior Member |
|
|
Thanks All for your Response,
Michel
Use a foreign key and let Oracle handle that.
Of course we do have Foreign Key , and the whole purpose of asking the question was to validate FK's constraints after correcting the data.
Mahesh
What is the relationship between them?
I mean, how to identify the X and Y tables?
They are Parent --> Child Releation ship.
I know the list of tables, let's say the 15 most changing transcational tables, How can i hard code / dynamic those 15 tables from where orphans have to be deleted.
Any hellp in writing a script of Pl SQL proc will help me.
Thanks
|
|
|
|
|
|
| Re: Deleting Orphans After Export [message #295559 is a reply to message #295558] |
Tue, 22 January 2008 14:12 |
faiz_hyd
Messages: 294
Registered: February 2006
|
Senior Member |
|
|
Thanks Michel,
I think i was not clear in explaining my requirements.Constraints will get imported but they will be invalid since there are child without Parents,
And what do you mean by Why don't you enable the foreign key and let Oracle handles the wrong data?
How will Oracle handle wrong data , Since constraints are already there but not validated, Am i missing some thing or didn't understand your point, can you clear me this one please.
By the way just did this example which shows that you cann't create a FK constraint if Parent Records are not there, Just to make it clear what i wanted.
SQL> create table A_Master ( id number Primary Key, b varchar2(100));
Table created.
SQL> insert into a_master values (1,'abc');
1 row created.
SQL> insert into a_master values (2,'BCD');
1 row created.
SQL> commit;
Commit complete.
SQL> create table B_Child(b_id number Primary key,id number,c char(1));
Table created.
SQL> insert into b_child values (1,3,'A');
1 row created.
SQL> insert into b_child values (2,1,'B');
1 row created.
SQL> insert into b_child values (3,2,'C');
1 row created.
SQL> commit;
Commit complete.
SQL> alter table b_child add constraint A_FK foreign key (id) references A_MASTER(id);
alter table b_child add constraint A_FK foreign key (id) references A_MASTER(id)
*
ERROR at line 1:
ORA-02298: cannot validate (R1APP90.A_FK) - parent keys not found
Thanks, and Hope i was able to clear my question.
|
|
|
|
|
|
|
|
| Re: Deleting Orphans After Export [message #295608 is a reply to message #295576] |
Tue, 22 January 2008 22:05 |
faiz_hyd
Messages: 294
Registered: February 2006
|
Senior Member |
|
|
thanks Michel,
It's much clear to me, I appreciate your patience,
well what i was doing is a straigth forward import , so what Oracle was doing was loading data first and then constraints,
Now i should try first structure and constraints and then the data, followed by indexes.
Thanks Again.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
