Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2003
    Posts
    46

    Post Unanswered: Idiot Question: Time limit on Data

    There is no DBA so I must do it on my own.
    I am pretty new to databases so forgive me if this is too lame a question:

    Our DB has data from 2 years ago that we dont need.
    how can we limit the amount of data to be retained to be w/in 6 months.

    I cannot even find the manual here yet but please tell me what to look up when I find it.

    Thanks IA

  2. #2
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273
    Is your data in Backup form or it is in the database itself?
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

  3. #3
    Join Date
    Nov 2003
    Location
    Rotterdam, Netherlands
    Posts
    127
    There is no ready made option in oracle available to do this job.

    Your system may have a field with date stamp on each record.
    based on that you should delete the data.

    If u want to delete them periodically u should run a process may be END OF DAY.

  4. #4
    Join Date
    Oct 2003
    Posts
    87
    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.
    Oracle - DB2 - MS Access -

  5. #5
    Join Date
    Jan 2003
    Posts
    46

    Time limit on Data

    Thanks all

    I am suprised this cannot be done automaticaly w/in oracle.



    Yes, its on the database itself.

  6. #6
    Join Date
    Jan 2003
    Posts
    46

    Arrow Time limit on Data

    Is there some way to do this with a cron or something?
    I was reading this in another products manual but was unable to elicit how they where doing it???

  7. #7
    Join Date
    Jan 2003
    Posts
    46

    Arrow Re: Time limit on Data

    ************************************************** ****************************
    * archive_data.HOWTO
    ************************************************** ****************************

    * INSTALLATION

    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:

    'AR04: Error deleting ticket with ticket_handle: xxxxxxxxxxxxxxxx Number of tickets deleted was: xxxx';
    'AR07: Error deleting log record with isn: xxxxxxxxxx Number of log records deleted was: xxxx'

    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.

  8. #8
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •