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):


#set the environment
export PATH=$PATH:/usr/local/bin
. oraenv

export NLS_LANG="English_United Kingdom.AL32UTF8"

#piping password in is good practise for not seeing it when doing a ps -ef | grep expdp
echo $PASSWORD | $ORACLE_HOME/bin/expdp myuser \
DUMPFILE=mydumpfilename.dmp \
LOGFILE=mylogfilename.log \
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


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.


and finally, watch your spaces, I found that leaving spaces like this:

VERSION = 11.1.0 \

caused the parameter to be ignored

VERSION=11.1.0 \

it is was taken into account


How to write messages to the Oracle alert log and custom trace files - dbms_system.ksdwrt

>> Sunday, 24 April 2016

I've been finding this useful on occasion because I have plenty of jobs reading and reporting on alert log errors, so it's saved me having to think of another way to yell "hey DBA" :

exec dbms_system.ksdwrt(1, 'This message goes to trace file in the udump location');

exec dbms_system.ksdwrt(2, 'This message goes to the alert log');

exec dbms_system.ksdwrt(3, 'This message goes to the alert log and trace file in the udump location');


exec dbms_system.ksdwrt(2, 'ORA-09999 hey you wake up and investigate this');

You can put a wrapper around the call and grant execute on the function:

create or replace function write_alert_log(log_or_trace in number, text_message in varchar2) return number

  return 0;
  when others then
  return 1;


Talent and intellect is never displayed in disparagement (aka the worst flashlight I have ever owned)

>> Sunday, 31 January 2016

A simple statement appeared on twitter 

"This feature request scares me, but I reserve the right to be wrong."
This request was to allow sql statements to be run on multiple databases at the same time through a tool.  So a bit like OEM allows you to do multi database reporting or even patching.  Bearing in mind that anyone connected to a database via a tool will only be able to do what they actually have access to do, I don't really see this as a problem.  My response was:

"With privilege comes responsibility"

 I can have a staging server that can attach to multiple databases, I can write a simple shell script to run through all those database and execute whatever I like within the access privileges of the user I connect with.  Simples.   I could create a patch plan to patch an entire estate at the same time.  No problem. I could write a report to run on a production group of databases that does *this. (Hold that thought in mind, it will resurface.)

Some of the twitter circle within which I roam decided that 'people' would destroy the planet with such dangerous functionality:

"If it was my product I'd be worried about giving 'em so much rope" 

So who gets to decide the length of rope the common DBA can hang themselves with? My feeling is: the access level they are granted - nothing like an rm -r * as the oracle software owner to guarantee a long night ahead - who allowed the Oracle DBA access to the server they had to install the software on?! Fools, fools everywhere!

I think that the role of experienced people within a community, if they choose to engage, is to offer advice and to share experience but not to judge.

"We should judge" 

I wonder if perhaps more practical comments might be "experience has shown me this could easily cause problems" or "this sort of functionality should be fairly hard to 'switch on' or have high clarity"

But the general ensuing conversation was around: 'people' are idiots, 'people' would break stuff with it.

Would they?  All of them? Some might. But then but some people will also chown -R oracle:dba * on a production server at \  (Got root?! How he lol'd as he rebuilt the server ... not) 

But is it right to say you can't have potentially useful functionality because you could potentially break something with it?  

"..that's what people asking for {the} capability will do.." 

Will they?  Some might get bitten but if something is well designed then mistakes are less likely and people who forge ahead and do something that is a bad idea....well shit happens and we learn by mistakes (or preferably practise in a sandpit) not by being protected from ourselves.

So back to *this 

 "The relevance is, that's what people want to do to their 60 db's simultaneously"

Do they?  Maybe they want to do something useful and productive too.

"My experience has shown me that people do this

Not with this tool, because that functionality isn't there.

And I suspect it never will be there, but that's ok because my answer to the person asking for it would be: there's more than one way to skin a cat and have a look at your other options for running actions on multiple databases if that's what you need to do.

But to the experienced, highly skilled, highly talented people out there:  Who made you the idiot police?

Show me someone that's never screwed up and I'll show you someone that's never pushed themselves or worked dog tired under stress. On huge estates wrong services or servers sometimes get shut down. Admins are flogged in public or occasionally dismissed.

Should Jonathan Lewis have been considered unsafe to be let loose with sqlplus because he ran a 'bad' query. Should the idiot police say 'this is what people will do if we allow them to'

No of course not. And Jonathan himself says about it 'hey world look at this, my bad, probably best if you don't do the same' by sharing his experience. (His comments about the poor quotes and the part reproduction of the shared information is a side issue).

I think it is usually best not to say 'why on earth do you want to do that you fool?' but to say 'what do you want to achieve here?' (This has generally been the art of response to worrying questions on Ask Tom that was Tom and seems to continue now)

Sometimes we have to remember the very real pressure of a shouty manager saying they want some information from every database by 4pm today and that so often, when the most bizarre JFDIs come in, there is no option to say 'well that's dumb' but we just have to work out a way to do something with least damage all round.

Sometimes the real world pressures can make questions look foolish and sometimes the true talent of an individual is the way in which those 'foolish' questions are answered.

But true talent and intellect is never displayed in disparagement.

But back to tools:  "TOOLS DO WHATEVER YOU USE THEM FOR"  (Special thanks to the ever gracious @thatjeffsmith for that link balm)

And some people will use them in a way that will make you face-palm

And others will never fail to surprise you.


  © Blogger template Simple n' Sweet by Ourblogtemplates.com 2009

Back to TOP