Home » Infrastructure » Unix » Needed Script to export Yesterday partition. (Unix)
Needed Script to export Yesterday partition. [message #457836] Wed, 26 May 2010 02:24 Go to next message
lokeshorafaq
Messages: 9
Registered: December 2009
Location: NJ
Junior Member
Hi,

I needed script to export one table partition every day.
-----------------------------------------------------------------
I wrote the script but It is not running properly.

#!/bin/bash


# Oracle Environment Variables

ORACLE_SID=dwsales1
export ORACLE_SID
CONNECT=salesqa2/salesqa2@dwsales1
#CONNECT=sys/tera1234@dwsales1

# set oracle environment
#if [ -f /home/oracle/scripts/orarpt.sh ]; then
# . /home/oracle/scripts/orarpt.sh
#fi


partition_name=`sqlplus -s / as sysdba <<EOF
set heading off
set feedback off
set echo off
set term off
select 'PIN_MESSAGE_D' || to_char(sysdate -1,'YYYYMMDD') from dual;
exit
EOF`
echo $partition_name

EXPORT_DIRECTORY=MANDBA_DUMP_DIR

#expdp $CONNECT directory=$EXPORT_DIRECTORY dumpfile=$partition_name.dmp logfile=$partition_name.log tables=PIN_MESSAGE:$partition_name

declare
V_partname Varchar2(240);
begin
select 'PIN_MESSAGE_D' || to_char(sysdate -1,'YYYYMMDD') into V_partname from dual;

end;


select to_char(sysdate, 'Dy DD-Mon-YYYY HH24:MI:SS') from dual;

spool off ;

--------------------------------------------------------------
(I am exporting this partition Successfully, When I ran directly in Unix).
#expdp CONNECT directory=EXPORT_DIRECTORY dumpfile=partition_name.dmp logfile=partition_name.log tables=EMPLOYEES:partition_name.
--------------------------------------------------------------

But When I Insert this export command in Unix Script it is not working.

Can Anyone Modify/Rewrite the script.

My Requirement is: I need to export the Yesterday Partition.


Re: Needed Script to export Yesterday partition. [message #457840 is a reply to message #457836] Wed, 26 May 2010 02:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you "termout off" then you will have nothing in "partition_name" variable.
In addition you don't need to pass to Oracle to get 'PIN_MESSAGE_D'||YYYYMMDD in your variable just do it in Unix:
> echo 'PIN_MESSAGE_D'`date +"20%y%m%d"`
PIN_MESSAGE_D20100526

Regards
Michel

Re: Needed Script to export Yesterday partition. [message #461078 is a reply to message #457840] Wed, 16 June 2010 08:26 Go to previous message
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
Senior Member
Michel Cadot wrote on Wed, 26 May 2010 03:31
If you "termout off" then you will have nothing in "partition_name" variable.


WRONG:
$ cat k0
#!/usr/bin/ksh
partition_name=`sqlplus -s / <<EOF
set head off feed off echo off
set term off
select 'PIN_MESSAGE_D' || to_char(sysdate -1,'YYYYMMDD') from dual;
exit
EOF`
echo $partition_name

$ ./k0
PIN_MESSAGE_D20100615
$

[Updated on: Wed, 16 June 2010 08:31] by Moderator

Report message to a moderator

Previous Topic: AIX memory usage
Next Topic: Not able to use sqlldr
Goto Forum:
  


Current Time: Fri Mar 29 01:23:55 CDT 2024