Important queries on snapshots

-- Show all the Available Snapshots

  SELECT s.snap_id "Snap ID",

         di.instance_name "Instance Name",

         di.host_name "Host Name",

         di.parallel "RAC Instance",

         TO_CHAR (s.startup_time, ' dd Mon "at" HH24
:mi:ss')

            "Instance Start time",

         TO_CHAR (s.snap_time, 'dd Mon YYYY HH24:mi') "Snap Date",

         s.snap_level "Snap level",

         SUBSTR (s.ucomment, 1, 60) "Comment"

    FROM stats$snapshot s, stats$database_instance di

   WHERE     s.dbid = (SELECT dbid FROM v$database)

         AND di.dbid = (SELECT dbid FROM v$database)

         AND s.instance_number = (SELECT instance_number FROM v$instance)

         AND di.instance_number = (SELECT instance_number FROM v$instance)

         AND di.startup_time = s.startup_time

         AND s.snap_time > (SYSDATE - 1)

ORDER BY s.snap_id;

-- Valid Snapshots that can be used together with the input snapId

  SELECT s.snap_id "Snap ID",

         di.instance_name "Instance Name",

         di.host_name "Host Name",

         di.parallel "RAC Instance",

         TO_CHAR (s.startup_time, ' dd Mon "at" HH24:mi:ss')

            "Instance Start time",

         TO_CHAR (s.snap_time, 'dd Mon YYYY HH24:mi') "Snap Date",

         s.snap_level "Snap level",

         SUBSTR (s.ucomment, 1, 60) "Comment"

    FROM stats$snapshot s, stats$database_instance di

   WHERE     s.dbid = (SELECT dbid FROM v$database)

         AND di.dbid = (SELECT dbid FROM v$database)

         AND s.instance_number = (SELECT instance_number FROM v$instance)

         AND di.instance_number = (SELECT instance_number FROM v$instance)

         AND di.startup_time = s.startup_time

         AND s.startup_time = (SELECT b.startup_time

                                 FROM stats$snapshot b

                                WHERE b.snap_id = &1)

ORDER BY s.snap_id;

--- Available Snapshots created between the two input dates

  SELECT s.snap_id "Snap ID",

         di.instance_name "Instance Name",

         di.host_name "Host Name",

         di.parallel "RAC Instance",

         TO_CHAR (s.startup_time, ' dd Mon "at" HH24:mi:ss')

            "Instance Start time",

         TO_CHAR (s.snap_time, 'dd Mon YYYY HH24:mi') "Snap Date",

         s.snap_level "Snap level",

         SUBSTR (s.ucomment, 1, 60) "Comment"

    FROM stats$snapshot s, stats$database_instance di

   WHERE     s.dbid = (SELECT dbid FROM v$database)

         AND di.dbid = (SELECT dbid FROM v$database)

         AND s.instance_number = (SELECT instance_number FROM v$instance)

         AND di.instance_number = (SELECT instance_number FROM v$instance)

         AND di.startup_time = s.startup_time

         AND s.snap_time BETWEEN TO_DATE (&1, 'mm/dd/yyyy')

                             AND TO_DATE (&2, 'mm/dd/yyyy')

ORDER BY s.snap_id;

--- Valid Snapshots that can be used together with the input snapId

  SELECT s.snap_id "Snap ID",

         di.instance_name "Instance Name",

         di.host_name "Host Name",

         di.parallel "RAC Instance",

         TO_CHAR (s.startup_time, ' dd Mon "at" HH24:mi:ss')

            "Instance Start time",

         TO_CHAR (s.snap_time, 'dd Mon YYYY HH24:mi') "Snap Date",

         s.snap_level "Snap level",

         SUBSTR (s.ucomment, 1, 60) "Comment"

    FROM stats$snapshot s, stats$database_instance di

   WHERE     s.dbid = (SELECT dbid FROM v$database)

         AND di.dbid = (SELECT dbid FROM v$database)

         AND s.instance_number = (SELECT instance_number FROM v$instance)

         AND di.instance_number = (SELECT instance_number FROM v$instance)

         AND di.startup_time = s.startup_time

         AND s.startup_time = (SELECT b.startup_time

                                 FROM stats$snapshot b

                                WHERE b.snap_id = &1)

ORDER BY s.snap_id;