Copy a table

[msuresh]

I need to copy a table from different database with some condition using copy command.Can some one give me the exact command syntax.

[sreddy]

set copycommit 999
set arraysize 999

COPY FROM USER/***@ABC TO USER/***@XYZ -
INSERT/REPLACE/APPEND Table_xyz -
USING SELECT * FROM Table_abc

This is basic syntax, look docs for specifics on restricts, parameters , error messages.

[msuresh]

Hi,
Scenario is
table xyz should be copied from database2 to database1 with some filter condition.Right now there is no table present in database1 and i have a database link to database2 from database1.What would be the command look like.

From database - database2
table - xyz

To Database - database1(from where i have to issue the copy command)
- no xyz table
- have db link to database2

[raghud]

Then you can't use COPY command. only possible way is

Create table table_name as select * from table_name1@dblink where ....

[sreddy]

You do not need COPY command if you have link.

from db1:

create table_xyz as select **columns to be selected** from table_at_db2@dblink_db2 **your where criteria** ;

This should be enough.

[pando]

COPY FROM USER/***@ABC TO USER/***@XYZ -
INSERT/REPLACE/APPEND/CREATE Table_xyz -
USING SELECT * FROM Table_abc

[msuresh]

we were using create table xyy as select * from xyz@link where condition and it started failing from y'day.We have enough space in temp and the default tablespace.

DROP TABLE rmt_na_nc_component
/

CREATE TABLE rmt_na_nc_component NOLOGGING
AS
SELECT * FROM ncsystem.nc_component@tscensusam_data
WHERE isdeleted IS NULL
/
-------------------------------
The number of record in that table is 6.4 million.

From yesterday this script started giving problem. Following is the problem
-----------------------------------
Creating Table RMT_NA_NC_COMPONENT
WHERE isdeleted IS NULL
*
ERROR at line 4=
ORA-01652= unable to extend temp segment by in tablespace
ORA-02063= preceding line from TSCENSUSAM_DATA
----------------------------------------------

[mike2000]

increase your temp tablespace

[shawish_sababa]

Hi

From metalink
ORA-01652: unable to extend temp segment by %s in tablespace %s Cause: Failed to allocate an extent for temp segment in tablespace.
Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or
more files to the tablespace indicated or create the object in other tablespace.