How to create a JOB in ORACLE for backup?

Question

Good afternoon, you guys!

I am still novice in the subject when it comes to sql, I would like to know how to create a JOB (or other operation that does the same) that performs a data extraction from a table to a file . sql in a directory I set.

If possible, it should do this automatically every day in a time interval that I determine.

NOTE: My BD is local and this in Oracle 11g Express version

It is possible?

Already I thank you!

Answer 1

There is how to make a backup, you can make a DUMP from your bank. In the installation folder of oracle server has a folder called "bin" where it has several executables and we will use the "expdp".

Create a file for batch command execution (.bat) and within it use the following command line:

expdp usuario/password@banco Tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log

You can see more details on: https://oracle-base.com/articles/10g/oracle-data-pump-10g

With the . bat created in the same folder as the "expdp" executable you will generate a . dump file with the data from your database.

Important information: in the command line you have a parameter called "directory" and this parameter is not where you place the folder url to wherever the file (DUMP) of your backup is stored. He is an "alias" as if it were a nickname for the place in fact. This "alias" is referencing a real path in the table: DBA_DIRECTORIES

You can see more details on: https://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_1050.htm#i1576965

Cool, the command is configured and the file is going to the folder you want. Now for you to automate this by making the DUMP be done every day at a certain time, you can create a scheduled task on Windows, but be sure to leave enabled to perform the task with administrator rights if it does not work.

I hope I’ve helped!

See you around!