1, Oracle automatically backs up single table data for one month
Mode 1: three step processing (batch file creation, sql file writing, task plan setting)
1. Step 1: create a batch file
@echo off Set OrclSid=orcl Set ExpUser=Database name Set ExpPass=Database password Set FileDir=D:\oracleBack\log Set SysDate=%date:~0,4%%date:~5,2%%date:~8,2%_%time:~0,2%%time:~3,2%%time:~6,2% Set FileName=%ExpUser%~%OrclSid%_%SysDate% @echo * * * * * * * * * * * * * * * * * * * * @echo * * * * * * * * * * * * * * * * * * * * @echo * * * * * ORACLE Database backup * * * * * * @echo * * * * * * * * * * * * * * * * * * * * @echo * * * * * * * * * * * * * * * * * * * * @echo service name = %OrclSid% @echo user name = %ExpUser% @echo password = %ExpPass% @echo catalog = %FileDir% @echo Time = %SysDate% @echo file name = %FileName%.rar @echo Start database backup... exp %ExpUser%/%ExpPass%@%OrclSid% file=%FileDir%/%FileName%.dmp log=%FileDir%/%FileName%.log tables=(T_SAP_IMPORT_DATA) query=\"where to_char(CREATED_DATE,'yyyy-MM-dd HH24-mi-ss') < to_char(SYSDATE,'yyyy-MM-dd HH24-mi-ss') and to_char(CREATED_DATE,'yyyy-MM-dd HH24-mi-ss')>to_char(add_months(sysdate,-1),'yyyy-MM-dd HH24-mi-ss')\" @echo Database backup complete! @echo Log in to the database sqlplus %ExpUser%/%ExpPass%@%OrclSid% @D:\oracleBack\bat\truncate.sql>truncate.txt @echo Import backed up data imp %ExpUser%/%ExpPass%@%OrclSid% file=%FileDir%/%FileName%.dmp ignore=Y tables=(T_SAP_IMPORT_DATA) @echo Sign out cmd ~exit
2. Step 2: write the sql file to clear the database and free the space
truncate table T_SAP_IMPORT_DATA; exit;
3. Step 3: use the task scheduler provided by windows
A) Open task scheduler and create a new basic task plan
B) Fill in the name and description and click Next
C) The trigger selects the corresponding rules, as shown in the figure, and executes once a day
D) Set the execution time and click Next
E) Next step
F) Fill in the path and starting parameters of the batch file (the folder where the batch file is located)
G) Click Finish
H) Double click the created task twice
1) Select whether the user is logged in or not to start
J) Enter the user name and password, OK
At this point, the task scheduler of windows is used to execute the batch file to realize the automatic backup of one month's data in Oracle database.