今天要整理个库出来,RMAN32位升级到64BIT了,但是还需要做个干净的库,准备放到云上,又不希望大量的历史数据存在,于是将大量的分区表的数据用EXPDP+query的方式进行了过滤。这个适用于希望导出表结构,但是又不希望导出某些表的数据。
[oracle@xml-ora1 expdp]$ expdp xmjl/xxxx dumpfile=expdp_xmjl_20120631.dmp directory=expdir logfile=expdp_xmjl_20110631.log schemas=xmjl query=GIS_TEST_TYGJ_CELLDATA:'" where 1=2"',GIS_SWITCH_STATUS_INFO:'" where 1=2"',GIS_SEN_DIST_INFO:'" where 1=2"',GIS_VEHICLE_REALTIME_INFO:'" where 1=2"',GIS_VEH_CANDATA_1MIN_INFO:'" where 1=2"',GIS_VEH_CANDATA_5MIN_INFO:'" where 1=2"',GIS_VEH_REALTIME_STATE:'" where 1=2"',GIS_VEHICLE_PETROAMT_INFO:'" where 1=2"',GIS_PHOTO_INFO:'" where 1=2"',GIS_OVER_ACC_ENVENT:'" where 1=2"',GIS_VEHICLE_LOGIN_LOG:'" where 1=2"',GIS_REALTIMEALARM_INFO:'" where 1=2"'
Export: Release 10.2.0.1.0 - 64bit Production on Saturday, 30 June, 2012 13:52:00
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "XMJL"."SYS_EXPORT_SCHEMA_01": xmjl/******** dumpfile=expdp_xmjl_20120631.dmp directory=expdir logfile=expdp_xmjl_20110631.log schemas=xmjl query=GIS_TEST_TYGJ_CELLDATA:" where 1=2",GIS_SWITCH_STATUS_INFO:" where 1=2",GIS_SEN_DIST_INFO:" where 1=2",GIS_VEHICLE_REALTIME_INFO:" where 1=2",GIS_VEH_CANDATA_1MIN_INFO:" where 1=2",GIS_VEH_CANDATA_5MIN_INFO:" where 1=2",GIS_VEH_REALTIME_STATE:" where 1=2",GIS_VEHICLE_PETROAMT_INFO:" where 1=2",GIS_PHOTO_INFO:" where 1=2",GIS_OVER_ACC_ENVENT:" where 1=2",GIS_VEHICLE_LOGIN_LOG:" where 1=2",GIS_REALTIMEALARM_INFO:" where 1=2"
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 80.36 GB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/VIEW/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TYPE/TYPE_BODY
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/JOB
. . exported "XMJL"."GIS_TEST_TYGJ_CELLDATA":"VEC04" 11.46 KB 0 rows
. . exported "XMJL"."GIS_TEST_TYGJ_CELLDATA":"VEC01" 11.46 KB 0 rows
. . exported "XMJL"."GIS_TEST_TYGJ_CELLDATA":"VEC02" 11.46 KB 0 rows
. . exported "XMJL"."GIS_SEN_DIST_INFO":"SEN3" 6.75 KB 0 rows
. . exported "XMJL"."GIS_VEH_REALTIME_STATE" 9.875 KB 0 rows
. . exported "XMJL"."GIS_VEH_CANDATA_1MIN_INFO" 6.796 KB 0 rows
. . exported "XMJL"."GIS_VEHICLE_REALTIME_INFO":"GPS3" 8.078 KB 0 rows
. . exported "XMJL"."GIS_SWITCH_STATUS_INFO":"SWT3" 9.851 KB 0 rows
. . exported "XMJL"."GIS_SEN_DIST_INFO":"SEN2" 6.75 KB 0 rows
. . exported "XMJL"."GIS_VEHICLE_REALTIME_INFO":"GPS2" 8.078 KB 0 rows
. . exported "XMJL"."GIS_SWITCH_STATUS_INFO":"SWT2" 9.851 KB 0 rows
. . exported "XMJL"."GIS_VEH_CANDATA_5MIN_INFO" 6.796 KB 0 rows
. . exported "XMJL"."GIS_VEHICLE_PETROAMT_INFO" 5.843 KB 0 rows
. . exported "XMJL"."GIS_PHOTO_INFO" 7.960 KB 0 rows
. . exported "XMJL"."GIS_OVER_ACC_ENVENT" 6.453 KB 0 rows
. . exported "XMJL"."GIS_VEHICLE_LOGIN_LOG" 8.625 KB 0 rows
. . exported "XMJL"."GIS_REALTIMEALARM_INFO" 8.007 KB 0 rows
. . exported "XMJL"."GIS_BAD_DRIVING_RECORD" 76.42 MB 2080154 rows
. . exported "XMJL"."GIS_BAD_OPREATEION_TYPE_INFO" 66.13 MB 1036729 rows
。。。。。。。。。。。。。。
. . exported "XMJL"."VEHICLES_GREENAREA" 0 KB 0 rows
. . exported "XMJL"."VEH_TEMPERATURE_CONFIG" 0 KB 0 rows
Master table "XMJL"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for XMJL.SYS_EXPORT_SCHEMA_01 is:
/u02/rman/expdp/expdp_xmjl_20120631.dmp
Job "XMJL"."SYS_EXPORT_SCHEMA_01" successfully completed at 14:09:53