I have a situation where I have a production database that we have to spool each customers transactions by hour to there own spool file(that is about 500 customers with an average of 5000 transactions per hour). This is due to legal issues.
I really don't want to get into managing tons of scripts and was hoping to build something that can work on the fly.
1) Are they seperate users on the oracle database like I mean are there sepearte schema for each and every customer or the customer details are stored in a single tables and linked by a Key to other tables
Option 1 If they are seperate users
If they are seperate users as far as I can think is write a script on unix or a batch file on Windows NT/200 to accept parameters like
Username,password and path of the spool file which are going to be stored in a text file
Execute the the script file thru a loop and make it accept parameters from the above text file line by line and execute the output to the specified spool file.
Using cronjob in Unix or Windows Scheduler in NT/2000 and schedule it every one hour.
Option 2 If customer Info is available in a single schema
For this you need to write a procedure to do the above following steps and you can execute the above procedure every 1 hour using dbms_job or using Oracle Enterprise Manager