Home » RDBMS Server » Server Utilities » Deleting Orphans After Export
Deleting Orphans After Export [message #295540] Tue, 22 January 2008 11:43 Go to next message
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 #295541 is a reply to message #295540] Tue, 22 January 2008 11:49 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Could be easily scripted.
But to automate this, you need to know the X and Y tables.
What is the relationship between them?
I mean, how to identify the X and Y tables?

[Updated on: Tue, 22 January 2008 11:50]

Report message to a moderator

Re: Deleting Orphans After Export [message #295550 is a reply to message #295540] Tue, 22 January 2008 12:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
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

Use a foreign key and let Oracle handle that.

Regards
Michel
Re: Deleting Orphans After Export [message #295557 is a reply to message #295550] Tue, 22 January 2008 13:41 Go to previous messageGo to next message
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 #295558 is a reply to message #295557] Tue, 22 January 2008 13:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
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.

Why don't you enable the foreign key and let Oracle handles the wrong data?
Why do you want to do it by yourself?

Regards
Michel
Re: Deleting Orphans After Export [message #295559 is a reply to message #295558] Tue, 22 January 2008 14:12 Go to previous messageGo to next message
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 #295564 is a reply to message #295559] Tue, 22 January 2008 15:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If your constraint is enable and validate then you CAN'T have wrong data.

You create the constraint BEFORE inserting data not after.

Regards
Michel
Re: Deleting Orphans After Export [message #295576 is a reply to message #295559] Tue, 22 January 2008 15:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is something you don't understand.
Foreign keys are part of your model; before any data exist.
Then Oracle guarantees that your data will never break the rules, the foreign keys.
You don't have anything to do, this is Oracle job.

Regards
Michel
Re: Deleting Orphans After Export [message #295608 is a reply to message #295576] Tue, 22 January 2008 22:05 Go to previous message
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.
Previous Topic: problem to open SQLLDR
Next Topic: Problem during loading data to Oracle XE table
Goto Forum:
  


Current Time: Sat Dec 03 05:52:40 CST 2016

Total time taken to generate the page: 0.30607 seconds