Re: Fwd: A problem

Hi !

May be this will help you (I hope).
This solution is for Windows (X) but
with some work it will do also in UNIX.

A procedure called BackupOSC is created by the DBA in his schema.
A job is created with at command, invoking the batch command file backup_OSC.cmd wich in turn calls SQLPLUSW to run the above procedure. The outcome is a batch command file called exp_OSC.cmd wich is invoked by the firts batch file to perform the real export.

The "secret" is to use the SQL to create the second file since DOS hasn't the ability to handle strings.

Hope it helps.
Dias Costa

P.S.: The files are attached.

        The file exp_Ora.ftp is a parameter file for 
        to FTP the DMP files to other machine.
        If you find some strange words in the files,
        they are Portuguese.

Jared Still wrote:
>
> ---------- Forwarded Message ----------
> Date: Wed, 21 Nov 2001 15:34:27 +0100
>
> I have created a job to export a user shema
> How can I tell Oracle each time it make the export to use lets say
> <name><date>.dmp
> I mean for example if today is 21.11.2001
> the exported file whoud have name21112001.dmp or something like this...
>
> -------------------------------------------------------
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Jared Still
> INET: jkstill_at_cybcon.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

exp_OSC.cmd
Description: application/unknown-content-type-cmdfile

The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail.

This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact Postmaster_at_fatcity.com for clarification.

• file: backupSmartUser.sql
• ------------------------- set linesize 300 set echo on set feed off set serveroutput on size 10000 set sqlprompt 'REM ' spool D:\Export_Oracle\exp_OSC.cmd execute system.BackupOSC; spool off exit
• file: cp_BackupOSC.sql
• ---------------------- --
• procedure invoked for the backup of schema: OSC -- -- create or replace procedure BackupOSC as -- Linha0 varchar2 (20); Linha1 varchar2 (100); Linha2 varchar2 (200); Linha3 varchar2 (30); Linha4 varchar2 (40); Linha5 varchar2 (250); Linha6 varchar2 (250); Linha7 varchar2 (10);

begin

    Linha0 := 'ECHO ON';

--
    SELECT   'ECHO 
-------------------------------------------------------------------------------- ' 
    into      Linha1
    FROM      DUAL;
--     
    SELECT   'ECHO Backing up the OSC user (Instance DC8i)  done on: ' || 
TO_CHAR(SYSDATE,'YYYY/MM/DD') || ' starting time ' || TO_CHAR(SYSDATE,'hh:mm:ss') 
    into      Linha2
    FROM      DUAL;
--
    Linha3 := 'SET ORACLE_SID=DC8i';
    Linha4 := 'SET ORACLE_HOME=E:\ORACLE\ORA81';
--
    SELECT   'exp USERID=system/manager owner=OSC FILE=d:\Export_Oracle\OSC_' || 
TO_CHAR(SYSDATE,'YYYYMMDD_HHMI') || ' LOG=d:\Export_Oracle\OSC_' || 
TO_CHAR(SYSDATE,'YYYYMMDD_HHMI') || ' BUFFER=400000 COMPRESS=y' 
    into      Linha5
    FROM      DUAL;
--
    SELECT   'ECHO Backup finished: ' || TO_CHAR(SYSDATE,'hh:mi:ss') || ' >> 
d:\Export_Oracle\OSC_backups.log' 
    into      Linha6
    FROM      DUAL;
--
    Linha7 := 'EXIT';
--
    dbms_output.put_line(linha0);
    dbms_output.put_line(linha1);
    dbms_output.put_line(linha2);
--    dbms_output.put_line(Linha3);     not needed
--    dbms_output.put_line(Linha4);     not needed
    dbms_output.put_line(linha5);
    dbms_output.put_line(linha6);
--    dbms_output.put_line(linha7);     will see
end BackupOSC;
/



oracle01
franca4
binary
prompt no
cd /usr/oraexp
mput d:\Export_Oracle\*.DMP
bye




backup_OSC.cmd
Description: application/unknown-content-type-cmdfile