script to clear database. Can it be done? (All)

[racerrunner]

Does anyone know how to write a script to automate the task of clearing the database? I'm using the peoplesoft crm system. I want to write a program/script to clear the daily records in the database. How do i do it? Or can it be done?

Thank you.

[Paul S]

Hi

Don't know much (if anything) about your database appliction (something to do with Oracle?) but could you not schedule jobs like you can with SQL Server?

Sorry I could not be of more help.

<font color=green>Paul S - WinGuides Moderator</font color=green>

[motoflop]

Databases contains tablespace, tables, indexes and some other objects. You cannot easily clear database just by that. When there is application which uses database at specific way, you have to know how it uses it. Otherwise you corrupt your application installation totally. Some applications has published tools for maintaining their database. You should first check if Peoplesoft CRM has such tools available. If not, then find out what is architecture of their database. Then you know which tables that is held and what are relationships between different tables. Usually there are referential constraints that forces you to process tables with specific way. For example you might not be able to delete a customer record if that customer has open orders. The data is usually stored on several tables. You have to know what are thsoe tables and in which order you should process them and what lines you can delete and what lines you must not touch.

When you know all those facts and you know which database vendor Peoplesoft CRM uses (most likely Oracle), you can check what are command line tools to access oracle databases. When you know all that you can write SQL statements for deleting records. Of course you have to know how to write SQL statements. You also need to find out structure of those tables holding data. That you can do with oracle management tools.

So it is possible, but with complicated applications like Peoplesoft CRM, do not try it if there are no tools for easy maintenace.