实验之将老库上非系统用户下的对象的表导入到新库上（expdp/impdp）
1.查询非系统用户（在老库10.200.152.38)
SQL> select s.username, s.account_status, s.created,s.profile
2 from dba_users s
3 where s.username not in ('DIP',
4 'MDDATA',
5 'SCOTT',
6 'SPATIAL_WFS_ADMIN_USR',
7 'SPATIAL_CSW_ADMIN_USR',
8 'ORACLE_OCM',
9 'XS$NULL',
10 'DBSNMP',
11 'SI_INFORMTN_SCHEMA',
12 'ORDPLUGINS',
13 'CTXSYS',
14 'ORDSYS',
15 'XDB',
16 'EXFSYS',
17 'DMSYS',
18 'SYSMAN',
19 'ANONYMOUS',
20 'WMSYS',
21 'MDSYS',
22 'OLAPSYS',
23 'APPQOSSYS',
24 'ORDDATA',
25 'SYSTEM',
26 'SYS',
27 'MGMT_VIEW',
28 'OUTLN',
29 'TSMSYS',
30 'OWBSYS',
31 'OWBSYS_AUDIT',
32 'APEX_030200',
33 'FLOWS_FILES',
34 'APEX_PUBLIC_USER');
USERNAME ACCOUNT_STATUS CREATED PROFILE
-------------------- --------------- ------------------- ------------------------------
TDEIPUSR OPEN 2016-11-22 19:15:44 PROF_PROD
UTOPTEA OPEN 2016-11-22 19:15:51 PROF_PROD
UBAK OPEN 2016-11-22 19:15:49 PROF_PROD
PRD_SOAINFRA OPEN 2016-11-22 19:12:44 PROF_PROD
PRD_ORASDPM OPEN 2016-11-22 19:12:37 PROF_PROD
PRD_IAU OPEN 2016-11-22 19:12:05 PROF_PROD
PRD_IAU_APPEND OPEN 2016-11-22 19:12:02 PROF_PROD
PRD_IAU_VIEWER OPEN 2016-11-22 19:12:04 PROF_PROD
EIPUSR OPEN 2016-11-22 19:11:59 PROF_PROD
PRD_ORABAM OPEN 2016-11-22 19:12:28 PROF_PROD
SNPM11 OPEN 2016-11-22 19:14:49 PROF_PROD
USERNAME ACCOUNT_STATUS CREATED PROFILE
-------------------- --------------- ------------------- ------------------------------
SNPW11 OPEN 2016-11-22 19:15:00 PROF_PROD
SNPWBMS OPEN 2016-11-22 19:15:23 PROF_PROD
PRD_MDS OPEN 2016-11-22 19:12:12 PROF_PROD
14 rows selected.
2.查询老库上这些用户的总的数据大小：
SQL> SELECT /*+parallel(a,12)*/
2 A.OWNER,
3 ROUND(SUM(A.BYTES) / 1024 / 1024 / 1024, 2) UG
4 FROM DBA_SEGMENTS A
5 WHERE A.OWNER in (
6 select s.username
7 from dba_users s
8 where s.username not in ('DIP',
9 'MDDATA',
10 'SCOTT',
11 'SPATIAL_WFS_ADMIN_USR',
12 'SPATIAL_CSW_ADMIN_USR',
13 'ORACLE_OCM',
14 'XS$NULL',
15 'DBSNMP',
16 'SI_INFORMTN_SCHEMA',
17 'ORDPLUGINS',
18 'CTXSYS',
19 'ORDSYS',
20 'XDB',
21 'EXFSYS',
22 'DMSYS',
23 'SYSMAN',
24 'ANONYMOUS',
25 'WMSYS',
26 'MDSYS',
27 'OLAPSYS',
28 'APPQOSSYS',
29 'ORDDATA',
30 'SYSTEM',
31 'SYS',
32 'MGMT_VIEW',
33 'OUTLN',
34 'TSMSYS',
35 'OWBSYS',
36 'OWBSYS_AUDIT',
37 'APEX_030200',
38 'FLOWS_FILES',
39 'APEX_PUBLIC_USER')
40 and s.account_status='OPEN')
41 GROUP BY A.OWNER ORDER BY A.OWNER;
OWNER UG
------------------------------ ----------
EIPUSR 0
PRD_IAU 0
PRD_MDS .5
PRD_ORABAM .01
PRD_ORASDPM 0
PRD_SOAINFRA 36.64
SNPM11 .01
SNPW11 .14
SNPWBMS .01
TDEIPUSR 0
10 rows selected.
3.老库导出作业：
——建目录对象；
-bash-4.1$ pwd
/oracle/dir10
-bash-4.1$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri Feb 10 19:36:43 2017
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create directory dir as
2 '/oracle/dir10';
Directory created.
——建parfile(expdp10.par)
-bash-4.1$ pwd
/oracle/dir10
-bash-4.1$ ls
expdp10.par
-bash-4.1$ cat expdp10.par
userid="/ as sysdba"
directory=dir
dumpfile=expdp_10_%U.dmp
logfile=expdp_10.log
parallel=4
filesize=2048M
schemas=TDEIPUSR,UTOPTEA,UBAK,PRD_SOAINFRA,PRD_ORASDPM,PRD_IAU,PRD_IAU_APPEND,PRD_IAU_VIEWER,EIPUSR,PRD_ORABAM,SNPM11,SNPW11,SNPWBMS,PRD_MDS
exclude=STATISTICS
-bash-4.1$
--开始导出(先查看操作系统是否有空间，df -g)
-bash-4.1$ nohup expdp parfile=/oracle/dir10/expdp10.par &
-bash-4.1$ ls
expdp_10_01.dmp expdp_10_04.dmp expdp_10_07.dmp expdp_10_10.dmp expdp_10_13.dmp expdp_10_16.dmp expdp_10_19.dmp nohup.out
expdp_10_02.dmp expdp_10_05.dmp expdp_10_08.dmp expdp_10_11.dmp expdp_10_14.dmp expdp_10_17.dmp expdp_10.log
expdp_10_03.dmp expdp_10_06.dmp expdp_10_09.dmp expdp_10_12.dmp expdp_10_15.dmp expdp_10_18.dmp expdp10.par
打包：
-bash-4.1$ tar -czvf /oracledata/dir10.tar.gz dir10
查看导出日志路径,检查导出日志是否有错误
-bash-4.1$ tail -10 expdp_10.log
/oracle/dir10/expdp_10_11.dmp
/oracle/dir10/expdp_10_12.dmp
/oracle/dir10/expdp_10_13.dmp
/oracle/dir10/expdp_10_14.dmp
/oracle/dir10/expdp_10_15.dmp
/oracle/dir10/expdp_10_16.dmp
/oracle/dir10/expdp_10_17.dmp
/oracle/dir10/expdp_10_18.dmp
/oracle/dir10/expdp_10_19.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 20:06:47
-bash-4.1$
4.将导出的dmp文件传到新库上：
需查看新库可用空间df-h，考虑到要解压，同时解压后要注意oracle用户的权限，是否能执行；
5.创建目录对象；
SQL> create or replace directory dir as '/data/dir10';
Directory created.
5.开始导入数据
cat impdp_10.par
userid="/ as sysdba"
directory=dir
dumpfile=expdp_10_%U.dmp
logfile=impdp_10.log
parallel=4
schemas=TDEIPUSR,UTOPTEA,UBAK,PRD_SOAINFRA,PRD_ORASDPM,PRD_IAU,PRD_IAU_APPEND,PRD_IAU_VIEWER,EIPUSR,PRD_ORABAM,SNPM11,SNPW11,SNPWBMS,PRD_MDS
执行导入脚本
nohup impdp parfile=/data/dir10/impdp_10.par &
6.验证：查看导入日志路径
cat /data/dir10/impdp_10.log
参考：转译
expdp \'\/ as sysdba\' attach=SYS_EXPORT_SCHEMA_03
实验之将老库上非系统用户下的对象的表导入到新库上（expdp/impdp）
-
不一样的天空w
2017-07-06 08:31:07 -
Oracle
- 原创