Re: copy table to another schema programatically

On Jan 20, 1:19 pm, m..._at_pixar.com wrote:
> DA Morgan <damor..._at_psoug.org> wrote:
> > Sean Nakasone wrote:
> > > anyone have code snippets that copy a table from one schema to another?  
> > > i usually use toad to do this but i really would like to find more
> > > automated way.
>
> > CREATE TABLE new_table AS
> > SELECT * FROM schema_name.existing_table;
>
> If we wanted to do this with several tables, would we have
> any issues with relationship integrity?  I.e., if we do
> several create tables in a transaction, will the
> tables be consistent?

It is important to remember that DDL does an implicit commit before and after the DDL.

>
> create table1 from other.table1;
> (other.table1, other.table2 updated by other people)
> create table2 from other.table2;
> commit;
>

So start counting how many commits are really there :-)

One way around this is to do the creates so that they don't actually get any rows, then you can insert into...select... in a consistent manner within a transaction.

I think this is better than the deferred constraint option because you then don't have to worry about violations at all, the set of data is already valid - the deferred option could potentially bite you at the end of a large transaction, or at least make work checking exception tables.

I don't know how many times I've read concepts manuals and Tom's books and coded stuff like this (but not newfangled deferred), and I still need to periodically review the rules. Dan, if I'm missing something obvious, please educate me! I'm sure at some point data volume becomes a tipping point, perhaps when it becomes a substantial fraction of undo ts size? But since my undo "needs" to be nearly as big as the rest of the db, I haven't run into that recently. You can tell I'm not a DW guy :-)

jg

--
@home.com is bogus.
http://www.wired.com/techbiz/people/magazine/16-02/ff_aimystery?currentPage=all