A unix script to collect top 10 SQL

Hi, I'm fairly new to Oracle but I want to create a unix script for collecting the daily top 10 sql from 9:00am to 5:00pm, I'm currently doing it everyday with the SQL snapshots but I would like to automate this process where should I start and is this going to be possible to load into an excel spreadsheet?
themeeper1Asked:
Who is Participating?
 
sdstuberCommented:
I would use a dbms_job (in any version) or dbms_scheduler (in 10g and higher)  and not use an OS script at all.

What does the data you are capturing look like and what sort of output are you expecting?
Creating a CSV file is probably the easiest Excel-importable output.
0
 
themeeper1Author Commented:
I just need the actual SQL statement and the name of the user who ran it and etc, basically everything that the  SQL snapshot shows as top SQL. After I schedule this using the dbms_scheduler how would I import it into excel though?
0
 
sdstuberCommented:
write a csv file of your data with utl_file

it might look something like this...
DECLARE
    v_file   UTL_FILE.file_type;
BEGIN
    v_file := UTL_FILE.fopen ('DTEMP', 'test_out.csv', 'w', 32767);

    FOR x IN (SELECT username, sql_text, gets, other_column, another_column
                FROM your_sql_snapshot)
    LOOP
        BEGIN
            UTL_FILE.put_line (v_file,
                                  x.username
                               || ','
                               || x.sql_text
                               || ','
                               || x.gets
                               || ','
                               || x.other_column
                               || ','
                               || x.another_column
                              );
        END;
    END LOOP;

    UTL_FILE.fflush (v_file);
    UTL_FILE.fclose (v_file);
END;

Open in new window

0
 
Mark GeerlingsDatabase AdministratorCommented:
I agree with the recommendation to use one of Oracle's job schedulers for this, since this is a database task, not an O/S task.  An Oracle PL\SQL procedure can run as a scheduled job that way, and can run any SQL query you want.  The procedure can use UTL_FILE to write the output to an ASCII file (either fixed-length or comma-delimitted) that Excel can then import (after you get the file from the database server over to your client P/C).  
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.