If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Also, there are products that can auto-archive data from your Oracle DB. But again, you must have some column in each table upon which the archiver can use to determine what to delete. You may have to add a date-time column to some of your tables and just set it to current date. That way it will delete off in 6 months.

Re: Time limit on Data

Before running the archive data process the archiver database package needs to be installed on the database.

Start a sqlplus session on the Server database logged on with the same user and password used by Server.

Then run the following two scripts in the specified order. The scripts can be found in the db directory of a
standard Server build after version 1.0.20. For earlier versions the scripts will be supplied separately.

1. archiver.sql
2. archiver_body.sql

To set up default values for the number of days ticket and log records to keep run the following script.

3. archive_config_setup.sql

To change the default values run the following script.

4. archive_config_update.sql n m

Values of n and m must both be supplied. n is the number of days worth of tickets to retain and m is the number of
days worth of log records to be retained.

Both n and m should be positive integers greater than zero.
Other values will be accepted by this update script but will cause subsequent runs of the archive data process
that rely on these default values to fail.

Another script is supplied to run the process using these default parameters.

5. archive_data.sql

The first four of these scripts are designed to be run interactively from sqlplus. The fifth is designed to be run
automatically by cron, ServiceGuard or a shell script. The command from the unix shell to do this is:

sqlplus <USER>/<PASSWORD>@<SID> @<PATH>archive_data.sql

Where <USER>, <PASSWORD> and <SID> specify the database and schema used by Server and <PATH> is the path
in which the archive_data.sql script has been saved.

This script contains an "exit;" command to close sqlplus when it is done. To run it interactively from an sqlplus
session without exiting at the end, edit the script to remove the "exit;" statement from the end of the script.

Similarly, to run any of the other 4 scripts from a shell script add an "exit;" command to the end of the script
and use a statement in the same form as above.

* OPERATION

To simply run the process with default parameters from an sqlplus session execute the following statment.

EXEC ARCHIVER.DELETE_AGED_DATA

To run the process with different parameters as a one off execute the following statment.

EXEC ARCHIVER.DELETE_AGED_DATA(n,m)

Where n is the number of days of tickets to keep and m is the number of days of log records to keep. These values
will only apply to the current run and will not change the default values. In order to specify one of these values
and accept the default for the other then use 0 (zero) as a placeholder for the default value of either parameter.

N.B. The process will never actually use 0 as a "real" parameter. If specified as direct input it will be ignored
and the default value on the database will be used. If it is specified on the database as a default and no valid
alternative is supplied at runtime then an error will be raised.

To run the process regularly through CRON the command that should be added to the CRONTAB file is as follows.

sqlplus <USER>/<PASSWORD>@<SID> @<PATH>archive_data.sql

Where <USER>, <PASSWORD> and <SID> specify the database and schema used by Server and <PATH> is the path
in which the archive_data.sql script has been saved.

To check which version of the archiver package is currently installed run the following SQL:

SELECT
archiver.version_number
FROM DUAL;

* LOGGING

All information and error messages generated by the process are written to the database log table with a category
of ARCHIVER.

If valid values of both n and m are not supplied either as parameters to the process or stored in the database table
configuration then one of the follwoing two errors will be written to the log.

'AR01: No valid parameter supplied for age of tickets to delete.'
'AR02: No valid parameter supplied for age of log records to delete.'

In normal operation the following informational messages will be written to the log in the following order:

'AR05: Ticket deletion process has started for tickets older than n day(s)'
'AR06: Ticket deletion process has ended. Number of tickets deleted was: xxxxx'
'AR08: Log record deletion process has started for log records older than m day(s)'
'AR09: Log record deletion process has ended. Number of log records deleted was: xxxxx'

If the first of these messages appears but the last one does not then the process is currently running unless one
of the following error messages appears:

If either of these messages appears then the process will have aborted. The ticket handle or isn should identify the
last record processed before the error or teh record that was being processed when the error occurred. If possible
any Oracle error associated with this failure will be appended to the message.

Re: Time limit on Data

Originally posted by westsan

You can use cron, but cron knows nothing about Oracle. You still need to write the delete script and then fire from cron with a "sqlplus login/password @script" command. Make sure you put an "exit" at the end of your script otherwise the sqlplus process will not end.