前陣子遇到一個案例，需要將資料庫中的幾個表從USER A 移動到USER B下麵，在ORACLE中，這個叫做更改表的所有者或者修改表的Schema。其實遇到這種案例，有好幾種解決方法。下麵我們通過實驗來測試、驗證一下。首先準備簡單測試數據,如下所示: SQL> CREATE TABLE TEST.K... ...
前陣子遇到一個案例，需要將資料庫中的幾個表從USER A 移動到USER B下麵，在ORACLE中，這個叫做更改表的所有者或者修改表的Schema。其實遇到這種案例，有好幾種解決方法。下麵我們通過實驗來測試、驗證一下。首先準備簡單測試數據,如下所示:
SQL> CREATE TABLE TEST.KKK
( ID INT , NAME VARCHAR2(12) ,
CONSTRAINT PK_KKK PRIMARY KEY(ID)
);
Table created.SQL> INSERT INTO TEST.KKK
2 VALUES(1000, 'kerry');
1 row created.SQL> commit;
Commit complete.
方法1： 常規方法，在目標用戶下創建表，並拷貝數據過去。
SQL> CREATE TABLE TEST1.KKK( ID INT, NAME VARCHAR2(12) ,CONSTRAINT PK_KKK PRIMARY KEY (ID));
Table created.SQL> INSERT INTO TEST1.KKK
2 SELECT * FROM TEST.KKK;
1 row created.SQL> COMMIT;
Commit complete.SQL> DROP TABLE TEST.KKK;
Table dropped.SQL>
當然也可以使用CREATE TABLE TEST1.KKK AS SELECT * FROM TEST.KKK; 但是使用這種方式需要註意，索引和約束都無法Copy過去。所以一定要慎用CREATE TABLE AS （CTAS）這種語法。
方法2： 使用expdp/impdp，導出表然後導入表修改表的Schema。使用exp/imp方式也是差不多，在此不做介紹。
[oracle@DB-Server dpdump]$ expdp system/xxxx tables=test.kkk directory=data_pump_dir dumpfile=kkk.dmp logfile=kkk.log;
Export: Release 11.2.0.1.0 - Production on Mon Jul 10 15:57:22 2017
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/******** tables=test.kkk directory=data_pump_dir dumpfile=kkk.dmp logfile=kkk.log
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "TEST"."KKK" 5.421 KB 1 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/u01/app/oracle/admin/gsp/dpdump/kkk.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 15:57:26
[oracle@DB-Server dpdump]$ impdp system/xxxx tables=test.kkk directory=data_pump_dir remap_schema=test:test1 dumpfile=kkk.dmp logfile=import.log
Import: Release 11.2.0.1.0 - Production on Mon Jul 10 15:58:07 2017
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLE_01": system/******** tables=test.kkk directory=data_pump_dir remap_schema=test:test1 dumpfile=kkk.dmp logfile=import.log
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TEST1"."KKK" 5.421 KB 1 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at 15:58:09
註意上面方法無法移動表到其它表空間，所以，如果你也必須移動表到對應的表空間，那麼就必須使用參數remap_tablespace， 如下所示：
impdp system/xxxxx tables=test.kkk directory=data_pump_dir remap_schema=test:test1 remap_tablespace=tbs_test_data:tbs_test1_data dumpfile=kkk.dmp logfile=import.log
方法3：修改系統表obj$、sys.con$等 （這個僅僅作為實驗測試而已，不可應用於生產環境）,ASK TOM裡面強烈不建議使用這種方法，原文如下，不過不妨礙我們在測試環境玩一玩，瞭解一下。
SQL> select obj#, owner#, name, namespace from obj$ where name='KKK';
OBJ# OWNER# NAME NAMESPACE
---------- ---------- ------------------------------ ---------- 93220 85 KKK 1SQL> select user_id , username from dba_users where username in ('TEST', 'TEST1');
