Home » RDBMS Server » Server Administration » Best Practice When Table Dropped (Oracle 10g 10.1.0.4.0, Sun Solaris 9)
Best Practice When Table Dropped [message #306814] Sun, 16 March 2008 23:53 Go to next message
suiren97
Messages: 48
Registered: May 2007
Location: Malaysia
Member
Getting ORA-00942 in one of the application. Invalid trigger was detected due to user drop and recreate the tables in remote DB.
What is the best practice to take when table is dropped and recreate?
Recompile all invalid objects?
When recompile the procedure, if the tables referred by the procedure will be recompiled?

Pls advise.
Thank you.

Re: Best Practice When Table Dropped [message #306815 is a reply to message #306814] Sun, 16 March 2008 23:57 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>What is the best practice to take when table is dropped and recreate?
Do NOT drop & recreate; TRUNCATE
Re: Best Practice When Table Dropped [message #306830 is a reply to message #306815] Mon, 17 March 2008 01:07 Go to previous messageGo to next message
suiren97
Messages: 48
Registered: May 2007
Location: Malaysia
Member
Do u mean if truncate, status will not become invalid for the reference objects?

What if drop is needed and table has recreated, will the trigger that refer to this object need to be recompiled to ensure object status='VALID'?



Re: Best Practice When Table Dropped [message #306854 is a reply to message #306830] Mon, 17 March 2008 02:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Recompilation is done on the fly, yuo don't have to care about it.
But as Ana said, you should not drop and create table on the fly. Do you rewrite and recompile your program on the fly? Tables are part of code.

Regards
Michel
Re: Best Practice When Table Dropped [message #306871 is a reply to message #306854] Mon, 17 March 2008 02:37 Go to previous messageGo to next message
suiren97
Messages: 48
Registered: May 2007
Location: Malaysia
Member
Testing is in progress on some new application.

For some reasons, if DROP is required where TRUNCATE or RENAME is not able to use, besides recreate of all associated indexes, integrity constraints, and triggers, we have to ensure all dependant objects of the dropped table to have 'VALID' status before next use in all related DB -- am I right?
Re: Best Practice When Table Dropped [message #306874 is a reply to message #306871] Mon, 17 March 2008 02:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You MUST not create and drop table on the fly.
Do you change the name of the function and recompile your program on the fly?
I repeat: tables are part of the code. They are static.

Regards
Michel
Re: Best Practice When Table Dropped [message #306888 is a reply to message #306874] Mon, 17 March 2008 02:56 Go to previous messageGo to next message
suiren97
Messages: 48
Registered: May 2007
Location: Malaysia
Member
I know table is static, but what happen if table structure is require to change as to meet the business requirement?



Re: Best Practice When Table Dropped [message #306895 is a reply to message #306888] Mon, 17 March 2008 03:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The same thing as when business requirement needs to change the code: you build a new program release.

Regards
Michel
Re: Best Practice When Table Dropped [message #307012 is a reply to message #306895] Mon, 17 March 2008 09:11 Go to previous messageGo to next message
suiren97
Messages: 48
Registered: May 2007
Location: Malaysia
Member
Michel Cadot wrote on Mon, 17 March 2008 16:35
The same thing as when business requirement needs to change the code: you build a new program release.



Sorry, is it possible for u to explain how should we do if table need to drop? thanks.
Re: Best Practice When Table Dropped [message #307013 is a reply to message #307012] Mon, 17 March 2008 09:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A table should never need to be dropped.
Do you need to drop part of your code?

Regards
Michel
Re: Best Practice When Table Dropped [message #307044 is a reply to message #307012] Mon, 17 March 2008 12:49 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
suiren97 wrote on Mon, 17 March 2008 10:11
Michel Cadot wrote on Mon, 17 March 2008 16:35
The same thing as when business requirement needs to change the code: you build a new program release.



Sorry, is it possible for u to explain how should we do if table need to drop? thanks.



Give us just ONE reason why a table should be dropped? You also do realize that your triggers are dropped when your table is dropped, your triggers are not invalid, they are non-existent.
Re: Best Practice When Table Dropped [message #307115 is a reply to message #307044] Mon, 17 March 2008 19:45 Go to previous messageGo to next message
suiren97
Messages: 48
Registered: May 2007
Location: Malaysia
Member
Thanks for all the response.
Can you guys pls explain to me in what kind of circumstances A table can only be dropped then?

If it can not be dropped, what is the alternative - if the table structure need to be modified?
Re: Best Practice When Table Dropped [message #307175 is a reply to message #307115] Tue, 18 March 2008 01:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Same questions, same answers.

Regards
Michel
Re: Best Practice When Table Dropped [message #307323 is a reply to message #307115] Tue, 18 March 2008 08:17 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
suiren97 wrote on Mon, 17 March 2008 20:45
Thanks for all the response.
Can you guys pls explain to me in what kind of circumstances A table can only be dropped then?

If it can not be dropped, what is the alternative - if the table structure need to be modified?



ALTER TABLE command can be used to add or drop columns.
Re: Best Practice When Table Dropped [message #307336 is a reply to message #307323] Tue, 18 March 2008 09:05 Go to previous message
suiren97
Messages: 48
Registered: May 2007
Location: Malaysia
Member
OK. got it. Will find out the actual reason why table is dropped by developer. Anyway, thanks for your sharing.
Previous Topic: Explain High water mark level in depth??
Next Topic: reclaiming space from tablespace
Goto Forum:
  


Current Time: Sat Dec 03 03:45:52 CST 2016

Total time taken to generate the page: 0.22074 seconds