COMPARISON BETWEEN THE THREE APPROACHES (exp/imp, Create as Select and
Copy):
-----------------------------------------------------------------------
Export/import is the only approach that will preserve all of the table's
triggers, grants, indexes, constraints, and comments. If you use create as
select or copy, such table properties must be manually recreated, since
these
methods merely transfer the data from one table to another. On the other
hand,
export/import is the most time-consuming of the three approaches. Create as

select and copy are generally faster, but keep in mind that these methods
require that you have enough space to hold both tables. When using create
as
select, make sure you have enough redolog and rollback space to cope with
the
amount of redo and rollback that the statement will generate. This is not a

major issue with export/import or copy because these methods allow you to
specify an insert array to be committed once the corresponding number of
rows
has been inserted into the table.

If your goal is to defragment the table,

- Export/import is the simplest approach.
- Create as select is faster than export/import.
- Copy will not defragment the table at all.

If your goal is to change the storage parameters,

- Create as select is the easiest way to go. Again, make sure you have
enough redo and rollback space.
- Copy cannot be used to change the storage parameters.

If your goal is to move the table from one tablespace to another,

- Create as select is the simplest solution.

If your goal is to change the ownership of the table,

- Copy is the best approach. The only drawback is that you need to know the

passwords of both the original and the new owner of the table.

If the table contains a column of type LONG,

- Copy is the simplest approach.

- Create as select cannot be used.


Regards


-----Original Message-----
From: Kathy Wright
[mailto:oracledba-ezmlmshield-x71627200.[Email address protected]
Sent: Thursday, August 30, 2007 2:56 PM
To: LazyDBA Discussion
Subject: RE: create tbl as select *

Watch out on the copy - it works great unless you have BLOBS. I got bit on
this one!

>>> "Todd Pepling " <oracledba-ezmlmshield-x18738280.[Email address
protected] 8/30/2007 1:51:45 PM >>>
COPY command is used to copy data from one table to another table
(destination table can be in the same database or another database).
Following is an example of copying data from one table to another table in
the same database:

COPY FROM scott/[Email Address Removed]
CREATE STUDENT (STUDENT_ID, STUDENT_NAME)
USING SELECT ROLL_NO, NAME FROM STUDENTS
WHERE CLASS_ID= 12;





---------------------------------------------------------------------
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