Am using oracle 11g, and i have a sql file with 'spool on' which runs for at least 7+ hours, as it has to spool huge data. But spool output is dumped only when the whole sql is finished, but i would like to know is there any other way to know the progress of my sql, or data spooled until that point in time, so that am rest assured that my sql is running properly as expected. Please help with your inputs.
Asked
Active
Viewed 554 times
0
-
did you try to spool out to a file? SPOOL file.out – Sebas Feb 16 '13 at 12:47
-
Yeah, i have tried spooling into the file only, like 'spool spk.log' – SPK Feb 16 '13 at 13:21
-
Are you doing it through pl/sql? – Plouf Feb 16 '13 at 13:31
-
Use a log table. See http://stackoverflow.com/questions/4919437/dbms-output-put-line – tbone Feb 16 '13 at 13:51
1 Answers
1
Sounds like you are using DBMS_OUTPUT, which always only starts to actually output the results after the procedure completes.
If you want to have real/near time monitoring of progress you have 3 options:
Use
utl_fileto write to a OS file. You will need access to the db server OS file system for this.Write to a table and use
PRAGMA AUTONOMOUS_TRANSACTIONso you can commit the log table entries without impacting your main processing. This is easy to implement, and readily accessible. Implemented in a good way this can become a de facto standard for all your procedures. You may then need to implement some sort of house keeping to avoid this getting too big and unwieldy.A quick and dirty option which is transient, is to use
DBMS_APPLICATION.SET_CLIENT_INFO, and then queryv$session.client_info. This works well, good for keeping track of things, fairly unobtrusive and because it is a memory structure is fast.
DBMS_OUTPUT really is limited.
-
Thnx alot TenG, yeah its a PL/SQL and am using DBMS_OUTPUT(Sorry for not mentioning this in the first place). hopefully this should help. Thanks again. – SPK Feb 16 '13 at 15:36