How to create a on-demand AWR snapshot?

Using dbms_workload_repository.create_snapshot() one can create AWR snapshot.

Example: Creates the snapshot and returns the snap id. If ‘ALL’ is not passed it defaults to typical (statistics_level).

SQL> select dbms_workload_repository.create_snapshot('ALL') from dual;


1 row selected.

To view snapshot

SQL> select end_interval_time, error_count from DBA_HIST_SNAPSHOT where snap_id = 856;

————————————————————————— ———–
25-AUG-10 PM 0


  1. Hi,

    Do you know if one can create a snapshot from a unix shell
    script ? I have sqlplus running nicely from a shell script and can issue SQL commands with no problem, but when I exec dbms_workload_repository.create_snapshot();
    it will not work. I have Goggled for days, and have found no exapmples of this

    Thank you for your time and expertise!

    • Hi Chris,

      What is the error you see, which user does the script login as and can you send the script that you are using?

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.