--任务描述 将132.194.42.214中的sid_test用户的结构导入到132.194.36.57中
--1、在57中创建表空间为214表空间一半左右大小
SELECT b.tablespace_name, file_num,
round(total_bytes / 1024 / 1024 / 1024, 2) "total_space(g)",
round(free_bytes / 1024 / 1024 / 1024, 2) "free_space(g)",
round(free_bytes * 100 / total_bytes, 2) "free_per(%)",
round((total_bytes - free_bytes) * 100 / total_bytes, 2) "used_per(%)"
FROM (SELECT a.tablespace_name, COUNT(1) file_num, SUM(a.bytes) total_bytes
FROM dba_data_files a
GROUP BY a.tablespace_name) b,
(SELECT a.tablespace_name, SUM(a.bytes) free_bytes
FROM dba_free_space a
GROUP BY a.tablespace_name) c
WHERE b.tablespace_name = c.tablespace_name
ORDER BY b.tablespace_name;
--lsvg -o 查看激活状态的raw
--lsvg -l vgcmssdb11 查看指定的raw
ALTER TABLESPACE UNDOTBS1 ADD DATAFILE '/dev/rundo02','/dev/rundo03','/dev/rundo04','/dev/rundo05';
CREATE TABLESPACE TBS_2101 DATAFILE '/dev/rdata0001','/dev/rdata0002','/dev/rdata0003','/dev/rdata0004','/dev/rdata0005','/dev/rdata0006';
CREATE TABLESPACE TBS_2102 DATAFILE '/dev/rdata0007','/dev/rdata0008','/dev/rdata0009','/dev/rdata0010','/dev/rdata0011','/dev/rdata0012';
CREATE TABLESPACE TBS_2103 DATAFILE '/dev/rdata0013','/dev/rdata0014','/dev/rdata0015','/dev/rdata0016','/dev/rdata0017','/dev/rdata0018';
CREATE TABLESPACE TBS_EAI01 DATAFILE '/dev/rdata0019','/dev/rdata0020','/dev/rdata0021','/dev/rdata0022','/dev/rdata0023',
'/dev/rdata0024','/dev/rdata0025','/dev/rdata0026','/dev/rdata0027','/dev/rdata0028','/dev/rdata0029','/dev/rdata0030','/dev/rdata0031',
'/dev/rdata0032','/dev/rdata0033','/dev/rdata0034','/dev/rdata0035','/dev/rdata0036','/dev/rdata0037','/dev/rdata0038';
CREATE TABLESPACE TBS_EAI02 DATAFILE '/dev/rdata0039','/dev/rdata0040','/dev/rdata0041','/dev/rdata0042','/dev/rdata0043',
'/dev/rdata0044','/dev/rdata0045','/dev/rdata0046','/dev/rdata0047';
SELECT * from dba_data_files;
SELECT a.*,ROWNUM from (
SELECT '''/dev/rdata'||LPAD(ROWNUM,4,0)||''',' ,ROWNUM ROWh from dba_objects)a WHERE rowh>=39 AND rowh<=47;
--2、创建用户sid_test
CREATE USER SID_TEST IDENTIFIED BY sid_test DEFAULT TABLESPACE TBS_EAI01;
GRANT CONNECT ,RESOURCE TO SID_TEST;
--3、创建导出目录
CREATE DIRECTORY exp_dir AS '/oracle';
--4、导出
expdp \'/ as sysdba\' directory=exp_dir dumpfile=sid_test.dmp logfile=sid_test.log schemas=sid_test CONTENT=METADATA_ONLY
--5、将dmp文件传送到57上并导入
ftp 132.194.42.214
put /ora10g/sid_test.dmp /oracle/sid_test.dmp
impdp \'/ as sysdba\' directory=exp_dir dumpfile=sid_test.dmp logfile=sid_test_imp.log schemas=sid_test table_exists_action=replace;
--6、检查导入情况
SELECT COUNT(*),a.object_type from dba_objects a WHERE a.owner='SID_TEST' AND a.status='VALID'GROUP BY a.object_type ;
57表空间使用情况迁移前后对比
迁移前
TABLESPACE_NAME
FILE_NUM
total_space(g)
free_space(g)
free_per(%)
used_per(%)
SYSAUX
1
4
3.39
84.77
15.23
SYSTEM
1
1
0.27
26.58
73.42
TBS_2101
12
47.99
46.74
97.4
2.6
TBS_2102
11
43.99
43.33
98.5
1.5
TBS_2103
11
43.99
41.84
95.12
4.88
TBS_EAI01
81
323.92
110.81
34.21
65.79
TBS_EAI02
9
35.99
13.88
38.55
61.45
UNDOTBS1
10
39.99
39.3
98.26
1.74
迁移后
TABLESPACE_NAME
FILE_NUM
total_space(g)
free_space(g)
free_per(%)
used_per(%)
SYSAUX
1
0.32
0.01
2.46
97.54
SYSTEM
1
0.62
0.03
4.83
95.17
TBS_2101
6
60
60
100
0
TBS_2102
6
60
60
100
0
TBS_2103
6
60
60
100
0
TBS_EAI01
20
200
196.69
98.35
1.65
TBS_EAI02
9
90
89.82
99.8
0.2
UNDOTBS1
5
16.02
15.56
97.12
2.88
USERS
1
0
0
91.25
8.75
sid_test用户下有效对象迁移前后对比
迁移前
迁移后
有效对象数量
对象类型
有效对象数量
对象类型
121
INDEX
119
INDEX
580
INDEX PARTITION
580
INDEX PARTITION
8526
INDEX SUBPARTITION
8526
INDEX SUBPARTITION
14
LOB
14
LOB
219
LOB PARTITION
219
LOB PARTITION
4800
LOB SUBPARTITION
4800
LOB SUBPARTITION
5
PROCEDURE
4
PROCEDURE
2
SEQUENCE
2
SEQUENCE
212
TABLE
210
TABLE
610
TABLE PARTITION
595
TABLE PARTITION
5582
TABLE SUBPARTITION
5582
TABLE SUBPARTITION
2
TRIGGER
expdp /impdp 实践
最新推荐文章于 2024-06-26 07:00:00 发布