asked on

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?

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

themeeper1

ASKER

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?

Sean Stuber

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

SOLUTION

Mark Geerlings

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial