| Needed Script to export Yesterday partition. [message #457836] |
Wed, 26 May 2010 02:24  |
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 #461078 is a reply to message #457840] |
Wed, 16 June 2010 08:26  |
 |
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
|
Senior Member |
|
|
Michel Cadot wrote on Wed, 26 May 2010 03:31If 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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
