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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.