Unanswered: Retrieve Index_Stats Using Access 2000
I have an Oracle database here at my work place that has 4 indexes in a tablespace. About once a month I have to rebuild the indexes. What I have been doing is:
1. Validate <indexname>
2. select name Name,
(del_lf_rows *100)/lf_rows Frag
3. Alter <indexname> rebuild
4. Repeat steps 1 and 2.
The results of this goes into a spool file, but that also catches every line of SQL, blank lines, my get <filename>.sql statements, etc...
Although this gets me the percentage amount of fragmentation of the index, and I can edit the spool file to just relevant data, it is a pain.
Now what I would like to be able to do is run this query in Access2000 and append the returned 4 fields into a table within Access. Then, I could take the results, run a comparisson test, and if the fragmentation is over a certain limit, do the alter...rebuild, append that to the table as well.
Then each month I can run a report to show the amount of fragmentation each month, and year to date, and whatever else my manager would like to see.
Can anyone assist me with this. I would like to get this to the point of an Access form with a command button to validate the index and query Index_Stats, etc... If I do the validation in using SQL*Plus Worksheet, it validates fine, but when I query the table Index_Stats, it never returns any values.