dotmaik1

--Creating Normal and Guaranteed Restore Points

CREATE RESTORE POINT before_upgrade;


CREATE RESTORE POINT before_upgrade GUARANTEE FLASHBACK DATABASE;


--Listing Restore Points

dotmaik1

This report uses data from views that are licensed with the Diagnostics and Tuning Pack.

http://www.oracle.com/technetwork/database/manageability/sql-detail-099420.html



How do I generate a SQL Details Active Report?
From SQL*Plus, invoke the dbms_sqltune.report_sql_detail function and spool the output

dotmaik1

SET PAGESIZE 50000
SET FEEDBACK OFF
SET MARKUP HTML ON SPOOL ON
SET NUM 24
SPOOL PROD_PAPERLESS_SEGMENT_ADVISOR.xls


-- Display the findings.
SET LINESIZE 250
COLUMN task_name FORMAT A20

dotmaik1

DECLARE
  l_object_id  NUMBER;
BEGIN
 -- Create a segment advisor task for the PAPERLESS tablespace.
DBMS_ADVISOR.create_task (
advisor_name      => 'Segment Advisor',
task_name         => 'PAPERLESS_SEGMENT_ADVISOR',
task_desc         => 'Segment Advisor For PAPERLESS');

DBMS_ADVISOR.create_object (

dotmaik1

05 8-17 * * 1-5 /dbms/oracle/local/AMCMPRN1/etc/Scripts/awrreport_html_hourly.ksh AMCMPRN1 > /tmp/AWR_report_AMCMPRN1.out  2>&1
08 8-17 * * 1-5 /dbms/oracle/local/AMCMPRN1/etc/Scripts/addmreport_hourly.ksh AMCMPRN1 > /tmp/addm_report_AMCMPRN1.out  2>&1
05 8-17 * * 1-5 /dbms/oracle/monitor/bin/ashreport_html_hourly.ksh -sAMCMPRN1 -t60 > /tmp/AWR_report_AMCMPRN1.out  2>&1



AWR:


oracle@mwamdb3p[DUMMY] /home/oracle => cat /dbms/oracle/local/AMCMPRN1/etc/Scripts/awrreport_html_hourly.ksh

dotmaik1

set linesize 300
column REDOLOG_FILE_NAME format a50
SELECT
    a.GROUP#,
    a.THREAD#,
    a.SEQUENCE#,
    a.ARCHIVED,
    a.STATUS,
    b.MEMBER    AS REDOLOG_FILE_NAME,
    (a.BYTES/1024/1024) AS SIZE_MB

dotmaik1

--REPORT_SQL_MONITOR_LIST

SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 1000
SET PAGESIZE 0
SET TRIM ON
SET TRIMSPOOL ON
SET ECHO OFF
SET FEEDBACK OFF

dotmaik1

set verify off
set trimspool on
set lines 132
set pages 10000
set markup html on;
set linesize 300
col INSTANCE_NAME format a10
col HOST_NAME format a30
col JOB format 9999999999
col SCHEMA_USER format a30

dotmaik1

set verify off
set trimspool on
set lines 132
set pages 10000
set markup html on;
set linesize 300
col INSTANCE_NAME format a10
col HOST_NAME format a30
col JOB format 9999999999
col SCHEMA_USER format a30

dotmaik1

set lines 200
SELECT TABLE_OWNER, 
TABLE_NAME, 
INSERTS, 
UPDATES, 
DELETES, 
TO_CHAR(TIMESTAMP,'DD-MON-YY HH24:MI:SS') TIMESTAMP, 
TRUNCATED, 
DROP_SEGMENTS 
FROM ALL_TAB_MODIFICATIONS