Migrate table to another Oracle Server-01

1.copy from to

Please note that this COPY command only supports a limited set of Oracle datatypes: char, date, long, varchar2, number.
COPY FROM username1/passwd1@PROD tousername2/passwd2@SANDBOX
    INSERT TABLE_C (*) USING (SELECT * FROM TABLE_C );

COPY FROM username1/passwd1@//192.168.3.17:1521/PROD_SERVICE to username2/passwd2@//192.168.4.17:1521/SANDBOX_SERVICE
    INSERT TABLE_C (*) USING (SELECT * FROM TABLE_C );

2.only dblink

#source:192.168.23.131 test1.localdomain
#target:192.168.23.132 test2.localdomain
#table: test.test

SQL> set long 1000
SQL> set pagesize 0
SQL> select DBMS_METADATA.GET_DDL('TABLE','TEST') from DUAL;--查看create table语句
SQL> select DBMS_METADATA.GET_DDL('INDEX','TEST_TS_BTREE') from DUAL;--查看create index语句

1>.#source: 事先已经建好test表 ,再创建索引 默认btree 
  --CREATE TABLE "TEST"."TEST"
  -- (    "TABLESPACE" VARCHAR2(80),
   --     "TOTAL" NUMBER,
   --     "USED" NUMBER,
   --     "FREE" NUMBER
  -- )

test1.localdomain:oracle:/oracle11g/app/oracle:>sqlplus test/test

SQL> create index test_ts_btree on test(tablespace);

Index created.

SQL>select index_name,STATUS from user_indexes where table_name='TEST';

INDEX_NAME              STATUS
-----------------    -----------------
TEST_TS_BTREE             VALID

SQL>  select DBMS_METADATA.GET_DDL('INDEX','TEST_TS_BTREE') from DUAL;--查看create index 语句

  CREATE INDEX "TEST"."TEST_TS_BTREE" ON "TEST"."TEST" ("TABLESPACE")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TEST"

2>.#target: 主要操作在目标端
test2.localdomain:oracle:/oracle11g/app/oracle:>sqlplus test/test

SQL> create database link linktest1 connect to test identified by test using 'TEST1_HO'; -------TEST1_HO 是 tnsnames.ora 的网络服务名，test/test 是test1.localdomain的Oracle库的用户/密码

Database link created.

SQL>  select count(*) from  test.test@linktest1;  --查看行数

  COUNT(*)
----------
         5

SQL> create table test as  select * from test.test@linktest1; --create table

Table created.

SQL> select count(*) from test; --比对行数

  COUNT(*)
----------
         5

SQL> select index_name,TABLE_OWNER ,TABLE_NAME ,STATUS from user_indexes where table_name='TEST';

no rows selected

证明： dblink迁移表，索引不会迁移过去，故手工创建

SQL>  CREATE INDEX "TEST"."TEST_TS_BTREE" ON "TEST"."TEST" ("TABLESPACE")
  2  ;

Index created.

SQL> set pagesize 0;
SQL> select DBMS_METADATA.GET_DDL('INDEX','TEST_TS_BTREE') from DUAL;--查询下

  CREATE INDEX "TEST"."TEST_TS_BTREE" ON "TEST"."TEST" ("TABLESPACE")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TEST"

SQL> set pagesize 10;
SQL>  select index_name,TABLE_OWNER ,TABLE_NAME ,STATUS from user_indexes where table_name='TEST';
INDEX_NAME                     TABLE_OWNER                            TABLE_NAME                     STATUS
------------------------------ ------------------------------         ------------------------------ --------
TEST_TS_BTREE                  TEST                                     TEST                           VALID