Copy a table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Copy a table

  1. #1
    Join Date
    Jun 2000
    Location
    chennai,tamil nadu,india
    Posts
    159

    Copy a table

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

  2. #2
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    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.
    Reddy,Sam

  3. #3
    Join Date
    Jun 2000
    Location
    chennai,tamil nadu,india
    Posts
    159
    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

  4. #4
    Join Date
    Aug 2001
    Posts
    267
    Then you can't use COPY command. only possible way is

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

  5. #5
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    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.
    Reddy,Sam

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    COPY FROM USER/***@ABC TO USER/***@XYZ -
    INSERT/REPLACE/APPEND/CREATE Table_xyz -
    USING SELECT * FROM Table_abc

  7. #7
    Join Date
    Jun 2000
    Location
    chennai,tamil nadu,india
    Posts
    159
    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
    ----------------------------------------------

  8. #8
    Join Date
    Sep 2002
    Posts
    411
    increase your temp tablespace

  9. #9
    Join Date
    Jul 2000
    Posts
    243
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width