EXPDP using QUERY with to_timestamp or to_date
>> Friday, 20 May 2016
For expdp with select queries using to_timestamp or to_date I've had a bit of game getting the apostrophes to play happy bunny. Within a parfile I was getting nowhere, so back to my easy friend ksh (run against an 11.2 database):
#!/bin/ksh
#set the environment
export PATH=$PATH:/usr/local/bin
export ORACLE_SID=MYSID
export ORAENV_ASK=NO
. oraenv
unset ORAENV_ASK
export NLS_LANG="English_United Kingdom.AL32UTF8"
#piping password in is good practise for not seeing it when doing a ps -ef | grep expdp
PASSWORD=myuserpassword
echo $PASSWORD | $ORACLE_HOME/bin/expdp myuser \
DIRECTORY=MY_DPUMP_DIR \
CONTENT=ALL \
COMPRESSION=ALL \
DUMPFILE=mydumpfilename.dmp \
LOGFILE=mylogfilename.log \
EXCLUDE=STATISTICS \
tables=schemaowner.table_name \
QUERY=\"where column_name \> to_timestamp\(\'2015-11-10 00:00:00:000000\',\'YYYY-MM-DD HH24:MI:SS:FF\'\)\"means the parameter is simply ignored
Notes:
Whilst out of habit I set NLS_LANG parameter, in expdp it is not really needed any more. See utilities manual for further advice
DIRECTORY: remember for datapump you need to define the directory in oracle:
Create directory MY_DPUMP_DIR as '/myfilesystem/mydirectory';
grant read, write on directory MY_DPUMP_DIR to myuser;
select * from dba_directories;
My other parameters like CONTENT, COMPRESSION, EXCLUDE are there for reasons relevant to what I was doing at the time. You need to think about what you need for the type of file you require, where it is going to be imported to and what is actually needed there.
http://docs.oracle.com/cd/B28359_01/server.111/b28319/dp_export.htm#i1006293
and finally, watch your spaces, I found that leaving spaces like this:
VERSION = 11.1.0 \
caused the parameter to be ignored
whereas:
VERSION=11.1.0 \
it is was taken into account
0 comments:
Post a Comment