It's been a long while since my last post, workload has been crazy this past couple of months. Everything has gone cloud, we are in full on "cloudification" mode and it's consuming all of my time to the point where hands on oracle is something of a rarity these days.....
Anyway hopefully that will change sometime soon.
A short post (and maybe just a reminder) that data filtering for datapump is just as effective going in as it is coming out - that may not seem initially something that you though might be possible but in fact the import process can use the query clause in the same way as export.
Here is a short example of that with remap thrown in to maybe answer another question that could come up related to this.
This is my parameter file for import
directory=GOLD
dumpfile=exportdp-ENDURF_PRD_%U_of_6.dmp
schemas=ENDURF_PRD
remap_tablespace=ENDURF_PRD:ENV01
remap_schema=ENDURF_PRD:ENV01
logfile=impdp_ENV01.log
PARALLEL=8
QUERY=ENDURF_PRD.SIM_BLOB:"where 1=0"
QUERY=ENDURF_PRD.IDX_ADJ_MATRIX_DET_SAVE:"where 1=0"
A couple of things to pick out here
1) I'm remapping schemas but the query clause applies to the original schema name not the remapped one (even though ultimately the filtered data ends up in the new schema)
2) For multiple tables you just list multiple query items so you can apply clause to lots of different objects
Running this and we see output like this in the TABLE_DATA section
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "ENV01"."SIM_BLOB_HISTORY" 6.273 KB 0 rows
. . imported "ENV01"."SIM_BLOB" 39.51 GB 0 out of 5754 rows
. . imported "ENV01"."DW_IMPACT_VOLUME_CHANGE_RTS" 11.04 KB 0 rows
. . imported "ENV01"."IDX_ADJ_MATRIX_DET_SAVE" 80.05 GB 0 out of 2298801194 rows
Again this can be useful - though in a lot of cases you filter on the way out to reduce export time and save space.
Another thing i noticed when doing this was that on 12c we get this useful message back
import done in US7ASCII character set and AL16UTF16 NCHAR character set
export done in WE8ISO8859P15 character set and AL16UTF16 NCHAR character set
WARNING: possible data loss in character set conversions
I'm sure that comes and goes with different version of datapump - anyway good to see it seems to be back for now.......
Comments
Post a Comment