EXP from command file (.cmd)

[pierreo]

Please let me know if there is an oportunity to automate the exp utility from Oracle and to provide all required details from a command file (.cmd) which can be scheduled at a specific time and date on Windows NT using command AT.

The reason of this request is that I need to have regular database dumps.

Thanks in advance,
Pierre

 

[sbix]

exp dba_user/dba_user_pswd@%ORACLE_SID% file=exp_%ORACLE_SID%.dmp log=exp_%ORACLE_SID%.log buffer=100000 full=y > C:\TRASH_BIN.LOG

if you wish you can change buffer value depending on the size of your tables.
You can also write a SQL script which generates the command stated upon with the export filename based on the system date.

 

[dbtoo2001]

(I believe this question has been answered in this forum before, but I have not found the search utility to be too useful).
The next logical question is, I want to have the date in the export file name...:

NT – How to put the date into an export .dmp file name


1. Create a .bat file and enter the following information (where orc1 is the sid of the database you want to export, and that sqlplusw is in your path variable, and d:\scripts is where your script will be located, and system/password is the userid/password you are using for the export ):
set ORACLE_SID=ORC1
sqlplusw system/password@ORC1 @d:\scripts\export_database.sql
exit;

2. Create a file called export_database.sql (created at the directory that you specified in 1.)
Enter the following:

column instnc new_value v_inst noprint
column instdate new_value v_instdate noprint
SELECT name instnc
FROM v$database;
SELECT TO_CHAR(sysdate,'-dd-Mon-yyyy-hh24-mi-ss') instdate FROM dual;
host exp system/system full=y consistent=y file=d:\oracle\admin\&&v_inst\exp\&&v_inst_exp&&v_instdate..dmp log=d:\oracle\admin\&&v_inst\exp\&&v_inst_exp&&v_instdate..log
exit


Notes:
1. Step 1 is optional, you can run step 2 directly from sqlplusw. The .bat file enables you to schedule it with AT.
2. When you use the date in the filename, unless you perform additional maintenance to clean up the old .dmp files, you will soon run out of space on the hard drive.
3. In step 2, the host command should be on one line in your .sql file, unless you add the continuation character, better yet, put the details into a parameter file and use the parameter file in the exp command. The line is wrapped in this text file and will not work if you are just copy and pasting.