Re: copy table to another schema programatically

From: joel garry <>
Date: Mon, 21 Jan 2008 10:51:17 -0800 (PST)
Message-ID: <>

On Jan 20, 1:19 pm, wrote:
> DA Morgan <> 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 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 :-)


-- is bogus.
Received on Mon Jan 21 2008 - 12:51:17 CST

Original text of this message