If you're just trying to get the table definition, then use a where
clause:

Create table as Select * from table1 where 1 = 2;

If you need the data, you could do a for loop to minimize the
transaction:

Create table up here...

declare

Counter pls_integer := 0;

begin

For i in (select cols from table1)
Loop

Insert into table1 ( )...

Counter = counter + 1;

If counter = 10,000 then

Commit;
Counter = 0;

End loop;

end


I'm sure there are other ways, and the ones above might not be
syntactically correct - it's been a while - but they might get you
close.



-----Original Message-----
From: Steele Theresa
[mailto:oracledba-ezmlmshield-x73641496.[Email address protected]
Sent: Wednesday, August 29, 2007 12:40 PM
To: LazyDBA Discussion
Subject: create tbl as select *

Hi All-

What is an alternative way to
do: "create table as select * from table1;"?

Can anyone send me sample code?

The problem is table1 has 1.2 million rows.

I think doing the above will create a single, enormous
transaction that doesn't commit until the end.

Thanks in advance.

Theresa Corral-Steele

MDS Global Services
Sr. Oracle Database Administrator



This email and any files transmitted with it may contain privileged or
confidential information and may be read or used only by the intended
recipient. If you are not the intended recipient of the email or any of
its attachments, please be advised that you have received this email in
error and any use, dissemination, distribution, forwarding, printing or
copying of this email or any attached files is strictly prohibited. If
you have received this email in error, please immediately purge it and
all attachments and notify the sender by reply email or contact the
sender at the number listed.


---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html



Oracle LazyDBA home page