1.登录linux系统,su - oracle
备份
expdp system/password directory=DATA_PUMP_DIR dumpfile=20220914.dmp logfile=20230804.log schemas=zs
2.登录win系统
创建新数据库系统,创建用户user zs 并赋予权限
3.查询原表的表空间,并在win中创建表空间
SELECT tablespace_name, file_name
FROM dba_data_files;
查询结果如下
1 USERS /u01/app/oradata/ncdb/users01.dbf 0
2 UNDOTBS1 /u01/app/oradata/ncdb/undotbs01.dbf 32
3 SYSAUX /u01/app/oradata/ncdb/sysaux01.dbf 23.68
4 SYSTEM /u01/app/oradata/ncdb/system01.dbf 16.41
5 NC_zs_DATA_01 /u01/app/oradata/ncdb/nc_zs_data_0101.dbf 20
6 NC_zs_DATA_01 /u01/app/oradata/ncdb/nc_zs_data_0102.dbf 20
7 NC_zs_DATA_01 /u01/app/oradata/ncdb/nc_zs_data_0103.dbf 20
8 NC_zs1_DATA_01 /u01/app/oradata/ncdb/nc_zs1_data_0101.dbf 17.21
9 NC_zs_DATA_01 /u01/app/oradata/ncdb/nc_zs_data_0120.dbf 20
10 NC_ISM_DATA_01 /u01/app/oradata/ncdb/nc_ism_data_0101.dbf 2
11 NC_MA_DATA_01 /u01/app/oradata/ncdb/nc_ma_data_0101.dbf 2
12 NC_zs_DATA_01 /u01/app/oradata/ncdb/nc_zs_data_0104.dbf 20
13 NC_zs_DATA_01 /u01/app/oradata/ncdb/nc_zs_data_0105.dbf 20
14 NC_zs_DATA_01 /u01/app/oradata/ncdb/nc_zs_data_0106.dbf 20
15 NC_zs_DATA_01 /u01/app/oradata/ncdb/nc_zs_data_0107.dbf 20
16 NC_zs_DATA_01 /u01/app/oradata/ncdb/nc_zs_data_0108.dbf 20
17 NC_zs_DATA_01 /u01/app/oradata/ncdb/nc_zs_data_0109.dbf 20
18 NC_zs_DATA_01 /u01/app/oradata/ncdb/nc_zs_data_0110.dbf 20
19 NNC_INDEX01 /u01/app/oradata/ncdb/NNC_INDEX01_01.dbf 20
20 NNC_INDEX01 /u01/app/oradata/ncdb/NNC_INDEX01_02.dbf 20
21 NNC_INDEX01 /u01/app/oradata/ncdb/NNC_INDEX01_03.dbf 20
22 NC_zs_DATA_01 /u01/app/oradata/ncdb/nc_zs_data_0121.dbf 20
23 NNC_INDEX01 /u01/app/oradata/ncdb/NNC_INDEX01_04.dbf 20
24 NC_zs_DATA_01 /u01/app/oradata/ncdb/nc_zs_data_0111.dbf 20
25 NC_zs_DATA_01 /u01/app/oradata/ncdb/nc_zs_data_0112.dbf 20
26 NNC_INDEX01 /u01/app/oradata/ncdb/NNC_INDEX01_05.dbf 20
27 NC_zs_DATA_01 /u01/app/oradata/ncdb/nc_zs_data_0113.dbf 20
28 NC_zs_DATA_01 /u01/app/oradata/ncdb/nc_zs_data_0114.dbf 20
29 NC_zs_DATA_01 /u01/app/oradata/ncdb/nc_zs_data_0115.dbf 20
30 NNC_INDEX01 /u01/app/oradata/ncdb/NNC_INDEX01_06.dbf 20
31 NNC_INDEX01 /u01/app/oradata/ncdb/NNC_INDEX01_07.dbf 20
32 NC_zs_DATA_01 /u01/app/oradata/ncdb/nc_zs_data_0116.dbf 20
33 NC_zs_DATA_01 /u01/app/oradata/ncdb/nc_zs_data_0117.dbf 20
34 NC_zs_DATA_01 /u01/app/oradata/ncdb/nc_zs_data_0118.dbf 20
35 NNC_INDEX01 /u01/app/oradata/ncdb/NNC_INDEX01_08.dbf 20
36 NC_zs_DATA_01 /u01/app/oradata/ncdb/nc_zs_data_0119.dbf 20
37 NC_zs_DATA_01 /u01/app/oradata/ncdb/nc_zs_data_0122.dbf 20
38 NNC_INDEX01 /u01/app/oradata/ncdb/NNC_INDEX01_09.dbf 20
39 NC_zs_DATA_01 /u01/app/oradata/ncdb/nc_zs_data_0123.dbf 20
40 NC_zs_DATA_01 /u01/app/oradata/ncdb/nc_zs_data_0124.dbf 20
41 NNC_INDEX01 /u01/app/oradata/ncdb/NNC_INDEX01_10.dbf 20
42 NC_zs_DATA_01 /u01/app/oradata/ncdb/nc_zs_data_0125.dbf 10
43 NC_zs_DATA_01 /u01/app/oradata/ncdb/nc_zs_data_0126.dbf 10
44 NC_zs_DATA_01 /u01/app/oradata/ncdb/nc_zs_data_0127.dbf 10
45 NC_zs_DATA_01 /u01/app/oradata/ncdb/nc_zs_data_0128.dbf 10
46 NC_zs_DATA_01 /u01/app/oradata/ncdb/nc_zs_data_0129.dbf 10
47 NC_zs1_DATA_01 /u01/app/oradata/ncdb/nc_zs1_data_0102.dbf 10
48 NNC_INDEX01 /u01/app/oradata/ncdb/NNC_INDEX01_11.dbf 10
plsql链接win数据库,创建表空间
-- 创建表空间 NC_ZS_DATA_01
CREATE TABLESPACE NC_ZS_DATA_01 DATAFILE 'D:\app\Administrator\oradata\ncdbtest\nc_ZS_data_0101.dbf' SIZE 20G ,
'D:\app\Administrator\oradata\ncdbtest\nc_ZS_data_0102.dbf' SIZE 20G ,
'D:\app\Administrator\oradata\ncdbtest\nc_ZS_data_0103.dbf' SIZE 20G ,
'D:\app\Administrator\oradata\ncdbtest\nc_ZS_data_0104.dbf' SIZE 20G ,
'D:\app\Administrator\oradata\ncdbtest\nc_ZS_data_0105.dbf' SIZE 20G ,
'D:\app\Administrator\oradata\ncdbtest\nc_ZS_data_0106.dbf' SIZE 20G ,
'D:\app\Administrator\oradata\ncdbtest\nc_ZS_data_0107.dbf' SIZE 20G ,
'D:\app\Administrator\oradata\ncdbtest\nc_ZS_data_0108.dbf' SIZE 20G ,
'D:\app\Administrator\oradata\ncdbtest\nc_ZS_data_0109.dbf' SIZE 20G ,
'D:\app\Administrator\oradata\ncdbtest\nc_ZS_data_0110.dbf' SIZE 20G ,
'D:\app\Administrator\oradata\ncdbtest\nc_ZS_data_0111.dbf' SIZE 20G ,
'D:\app\Administrator\oradata\ncdbtest\nc_ZS_data_0112.dbf' SIZE 20G ,
'D:\app\Administrator\oradata\ncdbtest\nc_ZS_data_0113.dbf' SIZE 20G ,
'D:\app\Administrator\oradata\ncdbtest\nc_ZS_data_0114.dbf' SIZE 20G ,
'D:\app\Administrator\oradata\ncdbtest\nc_ZS_data_0115.dbf' SIZE 20G ,
'D:\app\Administrator\oradata\ncdbtest\nc_ZS_data_0116.dbf' SIZE 20G ,
'D:\app\Administrator\oradata\ncdbtest\nc_ZS_data_0117.dbf' SIZE 20G ,
'D:\app\Administrator\oradata\ncdbtest\nc_ZS_data_0118.dbf' SIZE 20G ,
'D:\app\Administrator\oradata\ncdbtest\nc_ZS_data_0119.dbf' SIZE 20G ,
'D:\app\Administrator\oradata\ncdbtest\nc_ZS_data_0120.dbf' SIZE 20G ,
'D:\app\Administrator\oradata\ncdbtest\nc_ZS_data_0121.dbf' SIZE 20G ,
'D:\app\Administrator\oradata\ncdbtest\nc_ZS_data_0122.dbf' SIZE 20G ,
'D:\app\Administrator\oradata\ncdbtest\nc_ZS_data_0123.dbf' SIZE 20G ,
'D:\app\Administrator\oradata\ncdbtest\nc_ZS_data_0124.dbf' SIZE 20G ,
'D:\app\Administrator\oradata\ncdbtest\nc_ZS_data_0125.dbf' SIZE 10G AUTOEXTEND ON MAXSIZE 20G,
'D:\app\Administrator\oradata\ncdbtest\nc_ZS_data_0126.dbf' SIZE 10G AUTOEXTEND ON MAXSIZE 20G,
'D:\app\Administrator\oradata\ncdbtest\nc_ZS_data_0127.dbf' SIZE 10G AUTOEXTEND ON MAXSIZE 20G,
'D:\app\Administrator\oradata\ncdbtest\nc_ZS_data_0128.dbf' SIZE 10G AUTOEXTEND ON MAXSIZE 20G,
'D:\app\Administrator\oradata\ncdbtest\nc_ZS_data_0129.dbf' SIZE 10G AUTOEXTEND ON MAXSIZE 20G,
'D:\app\Administrator\oradata\ncdbtest\nc_ZS_data_0130.dbf' SIZE 10G AUTOEXTEND ON MAXSIZE 20G;
-- 创建表空间 NC_ISM_DATA_01
CREATE TABLESPACE NC_ISM_DATA_01 DATAFILE 'D:\app\Administrator\oradata\ncdbtest\nc_ism_data_0101.dbf' SIZE 2G AUTOEXTEND ON MAXSIZE 20G;
-- 创建表空间 NC_MA_DATA_01
CREATE TABLESPACE NC_MA_DATA_01 DATAFILE 'D:\app\Administrator\oradata\ncdbtest\nc_ma_data_0101.dbf' SIZE 2G AUTOEXTEND ON MAXSIZE 20G;
-- 创建表空间 NC_ZS1_DATA_01
CREATE TABLESPACE NC_ZS1_DATA_01 DATAFILE 'D:\app\Administrator\oradata\ncdbtest\nc_ZS1_data_0101.dbf' SIZE 17.21G AUTOEXTEND ON MAXSIZE 20G;
ALTER TABLESPACE NC_ZS1_DATA_01 ADD DATAFILE 'D:\app\Administrator\oradata\ncdbtest\nc_ZS1_data_0102.dbf' SIZE 10G AUTOEXTEND ON MAXSIZE 20G;
-- 创建表空间 NNC_INDEX01
CREATE TABLESPACE NNC_INDEX01 DATAFILE 'D:\app\Administrator\oradata\ncdbtest\NNC_INDEX01_01.dbf' SIZE 20G AUTOEXTEND ON MAXSIZE 20G;
ALTER TABLESPACE NNC_INDEX01 ADD DATAFILE 'D:\app\Administrator\oradata\ncdbtest\NNC_INDEX01_02.dbf' SIZE 20G AUTOEXTEND ON MAXSIZE 20G;
ALTER TABLESPACE NNC_INDEX01 ADD DATAFILE 'D:\app\Administrator\oradata\ncdbtest\NNC_INDEX01_03.dbf' SIZE 20G AUTOEXTEND ON MAXSIZE 20G;
ALTER TABLESPACE NNC_INDEX01 ADD DATAFILE 'D:\app\Administrator\oradata\ncdbtest\NNC_INDEX01_04.dbf' SIZE 20G AUTOEXTEND ON MAXSIZE 20G;
ALTER TABLESPACE NNC_INDEX01 ADD DATAFILE 'D:\app\Administrator\oradata\ncdbtest\NNC_INDEX01_05.dbf' SIZE 20G AUTOEXTEND ON MAXSIZE 20G;
ALTER TABLESPACE NNC_INDEX01 ADD DATAFILE 'D:\app\Administrator\oradata\ncdbtest\NNC_INDEX01_06.dbf' SIZE 20G AUTOEXTEND ON MAXSIZE 20G;
ALTER TABLESPACE NNC_INDEX01 ADD DATAFILE 'D:\app\Administrator\oradata\ncdbtest\NNC_INDEX01_07.dbf' SIZE 20G AUTOEXTEND ON MAXSIZE 20G;
ALTER TABLESPACE NNC_INDEX01 ADD DATAFILE 'D:\app\Administrator\oradata\ncdbtest\NNC_INDEX01_08.dbf' SIZE 20G AUTOEXTEND ON MAXSIZE 20G;
ALTER TABLESPACE NNC_INDEX01 ADD DATAFILE 'D:\app\Administrator\oradata\ncdbtest\NNC_INDEX01_09.dbf' SIZE 20G AUTOEXTEND ON MAXSIZE 20G;
ALTER TABLESPACE NNC_INDEX01 ADD DATAFILE 'D:\app\Administrator\oradata\ncdbtest\NNC_INDEX01_10.dbf' SIZE 20G AUTOEXTEND ON MAXSIZE 20G;
ALTER TABLESPACE NNC_INDEX01 ADD DATAFILE 'D:\app\Administrator\oradata\ncdbtest\NNC_INDEX01_11.dbf' SIZE 10G AUTOEXTEND ON MAXSIZE 20G;
ALTER TABLESPACE NNC_INDEX01 ADD DATAFILE 'D:\app\Administrator\oradata\ncdbtest\NNC_INDEX01_12.dbf' SIZE 10G AUTOEXTEND ON MAXSIZE 20G;
4.导入dmp文件
impdp system/passwodr directory=DATA_PUMP_DIR dumpfile=20230804.dmp logfile=import20230804.log