重庆思庄Oracle、KingBase、PostgreSQL、Redhat认证学习论坛

标题: 数据泵（network_link）迁移远端数据库用户数据 [打印本页]

作者: mahan 时间: 2025-5-25 15:18
标题: 数据泵（network_link）迁移远端数据库用户数据
一、源库创建scott用户测试数据
SQL> create table tt (id int);

Table created.

SQL> insert into tt values (100);

1 row created.

SQL> insert into tt values (200);

1 row created.

SQL> insert into tt values (300);

1 row created.

SQL> commit;

Commit complete.

二、源库创建测试用户test
SQL> create user test identified by "oracle#123" account unlock;

User created.

SQL> grant connect,resource to test;

Grant succeeded.

SQL> grant exp_full_database to test;

Grant succeeded.

三、目标库创建Scott用户
SQL> create user scott identified by oracle account unlock;

User created.

SQL> grant connect,resource to scott;

Grant succeeded.

SQL> alter user scott default tablespace test;

User altered.

SQL> alter user scott quota unlimited on test;

User altered.

四、目标库创建dblink
[oracle@19c01 admin]$ vi tnsnames.ora
ORCLTEST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.162.130)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL)
)
)

create public database link orcltest connect to test identified by "oracle#123" using
'(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.162.130)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL)
)
)';

测试dblink
SQL> select * from scott.tt@to_orcl;

ID
----------
100
200
300

五、创建directory
create or replace directory dmpdir as '/home/oracle’;

六、迁移scott用户数据
[oracle@19c01 admin]$ impdp \'/ as sysdba \' directory=DMPDIR network_link=orcltest logfile=impdp_orcl_test.log tables=scott.tt

Import: Release 19.0.0.0.0 - Production on Fri May 23 18:57:18 2025
Version 19.16.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYS"."SYS_IMPORT_TABLE_01": "/******** AS SYSDBA" directory=DMPDIR network_link=orcltest logfile=impdp_orcl_test.log tables=scott.tt
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
. . imported "SCOTT"."TT" 3 rows
Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Fri May 23 18:57:27 2025 elapsed 0 00:00:08

[oracle@19c01 admin]$

七、目标库验证测试数据
[oracle@19c01 admin]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat May 24 00:19:56 2025
Version 19.16.0.0.0

Copyright (c) 1982, 2022, Oracle. All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0

SQL> conn scott/oracle
Connected.
SQL> select * from tt;

ID
----------
100
200
300

SQL>

迁移完成。

欢迎光临重庆思庄Oracle、KingBase、PostgreSQL、Redhat认证学习论坛 (http://bbs.cqsztech.com/)