實驗之將老庫上非系統使用者下的物件的表匯入到新庫上（expdp/impdp）
實驗之將老庫上非系統使用者下的物件的表匯入到新庫上（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
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
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31397003/viewspace-2141636/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- expdp與impdp全庫匯出匯入
- 將表匯入到其他使用者的impdp命令
- expdp與impdp全庫匯出匯入（二）
- 資料庫泵（expdp/impdp）匯入匯出流程資料庫
- oracle匯入匯出之expdp/impdpOracle
- expdp impdp 資料庫匯入匯出命令詳解資料庫
- oracle 10.2.0.4 expdp全庫匯出 和分使用者impdp匯入的記錄Oracle
- 【EXPDP/IMPDP】使用 EXPDP/IMPDP工具“模糊”匯出和匯入
- Oracle expdp/impdp匯出匯入命令及資料庫備份Oracle資料庫
- Oracle expdp/impdp匯入匯出備份資料庫指令碼Oracle資料庫指令碼
- 如何將外部資料庫 匯入到系統的SQL中資料庫SQL
- Oracle 12c expdp和impdp匯出匯入表Oracle
- expdp impdp只匯出匯入viewView
- oracle資料庫的impdp，expdpOracle資料庫
- oracle expdp、impdp匯入從原表空間更換到其他表空間 ----匯入到另個表空間測試Oracle
- impdp+network link 跳過expdp直接匯入目標庫
- 非歸檔模式下遷移10g單機庫到新的儲存上模式
- expdp時不能匯出sys使用者下的物件！物件
- impdp在同一個資料庫中將一個使用者下的物件複製到另一個使用者下資料庫物件
- Oracle資料泵匯出匯入（expdp/impdp）Oracle
- 將asm上的資料庫移動到普通的filesystem上ASM資料庫
- IMPDP 多個表空間物件匯入到一個表空間中物件
- 將excel中資料從window上匯入到linux中oracle資料庫ExcelLinuxOracle資料庫
- 【impdp】使用impdp工具排除特定表的匯入
- expdp/impdp中匯出/匯入任務的管理和監控
- Oracle 低版本匯入高版本按使用者expdp/impdpOracle
- IMPDP匯入遠端資料庫資料庫
- 使用EXPDP/IMPDP匯入匯出表中資料/後設資料測試
- 如何將SAP API Hub 上提供的工作流匯入到 SAP BTP 上API
- 然後再全庫匯入排除view資料庫在impdp匯入View資料庫
- EXP COMPRESS以及EXP/IMP EXPDP/IMPDP匯入表結構注意
- 【impdp】IMPDP中的TRANSFORM引數--【資料泵】EXPDP匯出表結構(真實案例)後傳ORM
- 使用SecureCRT的SFTP將檔案上傳到Liunx系統上SecurecrtFTP
- expdp 全庫匯入報錯總結
- mysqldump匯出匯入所有庫、某些庫、某些表的例子MySql
- oracle實驗記錄 （expdp/impdp使用）Oracle
- 【實驗】【SQL*Loader】使用SQLLDR將Linux的使用者檔案passwd匯入資料庫SQLLinux資料庫
- 使用expdp、impdp遷移資料庫資料庫