expdp /impdp 实践

• --任务描述 将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