Oracle数据移动-不同用户不同表空间之间的数据移动

不同用户不同表空间之间的数据移动:

将nezha用户下的数据移动到zhubajie用户下。
表空间为:poineer_data、PIONEER_INDX 到lianxi

1、查看两个用户的信息及数据:

SQL> conn nezha/nezha;
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as nezha

SQL> select TABLE_NAME  , TABLESPACE_NAME    from user_tables;
TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
SALES                          PIONEER_DATA
CUSTOMERS                      PIONEER_DATA


SQL> conn zhubajie/zhubajie;
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as zhubajie

SQL> select TABLE_NAME  , TABLESPACE_NAME    from user_tables;
TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
ZHUTTABLE                      LIANXI


2、查看nezha用户的所有信息:
SQL> select object_name,object_type,status from user_objects;
OBJECT_NAME                                                                      OBJECT_TYPE         STATUS
-------------------------------------------------------------------------------- ------------------- -------
SALES                                                                            TABLE               VALID
CUSTOMERS                                                                        TABLE               VALID
SALES_PROD_ID                                                                    INDEX               VALID
SALES_CUST_ID                                                                    INDEX               VALID
SALES_CHANNEL_ID                                                                 INDEX               VALID
CUSTOMERS_GENDER_IDX                                                             INDEX               VALID
CUSTOMERS_CITY_IDX                                                               INDEX               VALID
7 rows selected

3、查看索引所在的表空间:
SQL> select index_name,tablespace_name from user_indexes;
INDEX_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
CUSTOMERS_GENDER_IDX           PIONEER_INDX
CUSTOMERS_CITY_IDX             PIONEER_INDX
SALES_PROD_ID                  PIONEER_INDX
SALES_CUST_ID                  PIONEER_INDX
SALES_CHANNEL_ID               PIONEER_INDX

4、导数据参数:

  DIRECTORY=DATA_PUMP_DIR
  SCHEMAS=nezha
  DUMPFILE=schema_nezha.dat
  EXCLUDE=PACKAGE
  EXCLUDE=VIEW
保存txt文件。eg。22.txt

5、导出数据:


C:\>expdp system/ttt parfile=c:\orcl1122\22.txt;

6、导入数据参数:

  DIRECTORY=DATA_PUMP_DIR  
  DUMPFILE=schema_nezha.dat
  REMAP_SCHEMA=NEZHA:ZHUBAJIE
  REMAP_TABLESPACE=USERS:PIONEER_DATA
 保存txt文件  eg:44.txt

7、导入数据:
  impdp system/ttt  parfile=c:\orcl1122\44.txt;












评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值