Home » SQL & PL/SQL » SQL & PL/SQL » Create table as select....
Create table as select.... [message #259411] Wed, 15 August 2007 10:03 Go to next message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
We had a near disk failure which caused table corruption and I had to recreate the table in our development environment from the same table in our production environment. I used the 'create table as select (....) and everything worked fine.

The column defaults were not copied over from production to my development environment with the 'create table as select...', why is that?

Also, I executed a 'drop table cascade constraints' before I recreated the table. Would the 'cascade constraints' also drop triggers associated with that table? I found this to be the case as I had to recreate the triggers for the recreated table. Why would the triggers be dropped as well?

Thanks.

[Updated on: Wed, 15 August 2007 10:03]

Report message to a moderator

Re: Create table as select.... [message #259413 is a reply to message #259411] Wed, 15 August 2007 10:08 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You only created the column-structure with a create-as-select.
The triggers were dropped when you dropped the table. Triggers cannot exist without the table they are assigned to.
Re: Create table as select.... [message #259414 is a reply to message #259411] Wed, 15 August 2007 10:08 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>Why would the triggers be dropped as well?
Trigger are a dependent object for the associated table.
When the table is dropped, then Oracle drops any associated triggers.
What good is a trigger if/when its table is dropped?
When a table gets dropped any trigger refering to it is no longer valid.
Re: Create table as select.... [message #259416 is a reply to message #259414] Wed, 15 August 2007 10:11 Go to previous messageGo to next message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
Thanks!
Is there a way I can structure my 'create table as select ...' statement to include column defaults in the future?

Thanks.
Re: Create table as select.... [message #259418 is a reply to message #259411] Wed, 15 August 2007 10:12 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
alternatively use DBMS_METADATA to created desired SQL.
Re: Create table as select.... [message #259420 is a reply to message #259418] Wed, 15 August 2007 10:14 Go to previous message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
I'll check that out.
Thanks very much for your time.
Previous Topic: Error(80,12): PLS-00306: wrong number or types of arguments in call to 'PUT_LINE'
Next Topic: row number count
Goto Forum:
  


Current Time: Thu Dec 08 12:30:25 CST 2016

Total time taken to generate the page: 0.08432 seconds