SelectEtoile : Base de connaissance SGBD

Posts Tagged 'copy from'

Utilisation de COPY FROM

Tache à réaliser

Copier une table d’une base vers une autre.

 

Le usage de la commande

usage: COPY FROM <db> TO <db> <opt> <table> { (<cols>) } USING <sel>

  <db>   : database string, e.g., hr/your_password@d:chicago-mktg

  <opt>  : ONE of the keywords: APPEND, CREATE, INSERT or REPLACE

  <table>: name of the destination table

  <cols> : a comma-separated list of destination column aliases

  <sel>  : any valid SQL SELECT statement

A missing FROM or TO clause uses the current SQL*Plus connection.

 

 

Le plus produit

Pas besoin d’avoir de droit DBA

Pas de déclaration dans le tnsnames.ora

Pas de création de db link

Pour import/export, il faut de l’espace disque et ce sont 2 étapes

Cette méthode peut être utile à un client et éviter l’intervention du DBA (heu … réfléchir 10s secondes avant quand même)

On peut sélectionner les colonnes, limiter le result set avec une clause where …

 

 

Modus operandi

La commande COPY ne fait comme son nom l’indique que la copie des données, pas la création de la table.

 

Etape 1 : création de la table cible

 

Sur la source, on lance utilise un package standard Oracle pour générer le DDL :

 

SQL> set long 10000

SQL> select dbms_metadata.get_ddl('TABLE','TABLE_NAME','SCHEMA') from dual;

 

DBMS_METADATA.GET_DDL('TABLE','TABLE_NAME','SCHEMA')

--------------------------------------------------------------------------------

 

  CREATE TABLE "SCHEMA"."TABLE_NAME"

   (    "COL1" DATE NOT NULL ENABLE

   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

  STORAGE(INITIAL 65536 NEXT 65536 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

  TABLESPACE "SCHEMA_DAT_64K"

 

Nota bene : le SET LONG 10000 n’est pas que décoratif ; sans cela l’ordre SQL est tronqué

 

Sur la target, copy / paste de l’ordre

 

SQL>   CREATE TABLE "SCHEMA"."TABLE_NAME"

  2     (    "COL1" DATE NOT NULL ENABLE

  3    ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

  4    STORAGE(INITIAL 65536 NEXT 65536 MINEXTENTS 1 MAXEXTENTS 2147483645

  5    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

  6    TABLESPACE "SCHEMA_DAT_64K"

  7  /

 

Table created.

 

Etape 2 : la copie proprement dite

 

SQL> copy from SCHEMA/schema_pwd@'//host_source:1526/ORACLE_SID_SOURCE' to SCHEMA/SCHEMA_PWD@ORACLE_SID_CIBLE INSERT TABLE_NAME USING SELECT * from TABLE_NAME;

 

Array fetch/bind size is 15. (arraysize is 15)

Will commit when done. (copycommit is 0)

Maximum long size is 80. (long is 80)

   30 rows selected from SCHEMA@//host_source:1526/ORACLE_SID_SOURCE.

   30 rows inserted into TABLE_NAME.

   30 rows committed into TABLE_NAME at SCHEMA@ORACLE_SID_CIBLE.

 

Nota bene : on a copier la table mais pas ses index, ni ses contraintes, ni ses triggers …

 

Explications

En 9i on peut se connecter à une base en se passant de la résolution de nom, ie. du tnsnames.ora en indiquant explicitement toutes les information :

 

$ sqlplus SCHEMA/schema_pwd@'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=host_source)(PORT=1526)))(CONNECT_DATA=(SID =ORACLE_SID_SOURCE)))'

 

SQL*Plus: Release 10.2.0.3.0 - Production on Thu Jun 18 18:23:44 2009

 

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

 

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

 

SQL> select instance_name from v$instance;

 

INSTANCE_NAME

----------------

ORACLE_SID_SOURCE

 

Depuis la 10g on peut se connecter à une base en indiquant une descritpion syntétique « //host:port/SID »

 

$ sqlplus SCHEMA/schema_pwd@'//host_source:1526/ORACLE_SID_SOURCE'

                                                                          

Pour plus d’info : http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/apb.htm

 

 

Apparement dans la doc, on peut même créer la table mais je ne vois pas comment lui spécifier un tablespace (la table ira dans le tablespace par default L) :

 

SQL> copy from SCHEMA/schema_pwdt@'//host_source:1526/ORACLE_SID_SOURCE' -

> create SCHEMA.TABLE_NAME -

> tablespace SCHEMA_DAT_64K -

> USING SELECT * from TABLE_NAME;

SP2-0498: missing parenthetical column list or USING keyword

 

SQL> ! oerr SP2 0498

00498,0, "missing parenthetical column list or USING keyword\n"

// *Cause:  A parenthetical list was missing in the column list or the

//          USING keyword is missing in the COPY command.

// *Action: Check the syntax of the COPY command for the correct

//          options.

 

SQL> copy from SCHEMA/schema_pwd@'//host_source:1526/ORACLE_SID_SOURCE' -

> create SCHEMA.TABLE_NAME -

> USING SELECT * from TABLE_NAME -

> tablespace SCHEMA_DAT_64K;

 

Array fetch/bind size is 15. (arraysize is 15)

Will commit when done. (copycommit is 0)

Maximum long size is 80. (long is 80)

 

SP2-0502: SCHEMA_DAT_64K

SP2-0503: *

SP2-0501: Error in SELECT statement: ORA-00933: SQL command not properly ended

 

 

 

Merci à Nicolas pour cet article

 

Articles tagged