| Best Practice When Table Dropped [message #306814] |
Sun, 16 March 2008 23:53  |
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 #306830 is a reply to message #306815] |
Mon, 17 March 2008 01:07  |
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 #306871 is a reply to message #306854] |
Mon, 17 March 2008 02:37 |
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 #307012 is a reply to message #306895] |
Mon, 17 March 2008 09:11 |
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 #307044 is a reply to message #307012] |
Mon, 17 March 2008 12:49 |
joy_division
Messages: 4963
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 |
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 #307323 is a reply to message #307115] |
Tue, 18 March 2008 08:17 |
joy_division
Messages: 4963
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.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
