ORACLEAGENT BLOG

Generate Bulk AWR reports in database

select SNAP_ID, BEGIN_INTERVAL_TIME, END_INTERVAL_TIME
from dba_hist_snapshot
where trunc(BEGIN_INTERVAL_TIME)=trunc(sysdate-&no)
order by 1;

CREATE OR REPLACE DIRECTORY awr_reports_dir AS ‘/tmp/awrreports’;

DECLARE
— Adjust before use.
l_snap_start NUMBER := 4884; –Specify Initial Snap ID
l_snap_end NUMBER := 4892; –Specify End Snap ID
l_dir VARCHAR2(50) := ‘AWR_REPORTS_DIR’;

l_last_snap NUMBER := NULL;
l_dbid v$database.dbid%TYPE;
l_instance_number v$instance.instance_number%TYPE;
l_file UTL_FILE.file_type;
l_file_name VARCHAR(50);

BEGIN
SELECT dbid
INTO l_dbid
FROM v$database;

SELECT instance_number
INTO l_instance_number
FROM v$instance;

FOR cur_snap IN (SELECT snap_id
FROM dba_hist_snapshot
WHERE instance_number = l_instance_number
AND snap_id BETWEEN l_snap_start AND l_snap_end
ORDER BY snap_id)
LOOP
IF l_last_snap IS NOT NULL THEN
l_file := UTL_FILE.fopen(l_dir, ‘awr_’ || l_last_snap || ‘_’ || cur_snap.snap_id || ‘.html’, ‘w’, 32767);

FOR cur_rep IN (SELECT output
FROM TABLE(DBMS_WORKLOAD_REPOSITORY.awr_report_html(l_dbid, l_instance_number, l_last_snap, cur_snap.snap_id)))
LOOP
UTL_FILE.put_line(l_file, cur_rep.output);
END LOOP;
UTL_FILE.fclose(l_file);
END IF;
l_last_snap := cur_snap.snap_id;
END LOOP;

EXCEPTION
WHEN OTHERS THEN
IF UTL_FILE.is_open(l_file) THEN
UTL_FILE.fclose(l_file);

Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

How to find current redo log size in database

SELECT le.leseq “Current log sequence No”,
100*cp.cpodr_bno/le.lesiz “Percent Full”,
cp.cpodr_bno “Current Block No”,
le.lesiz “Size of Log in Blocks”
FROM x$kcccp cp, x$kccle le
WHERE le.leseq =CP.cpodr_seq
AND bitand(le.leflg,24) = 8
/

View hidden parameters

Set lines 2000
col NAME for a45
col DESCRIPTION for a100
SELECT name,description from SYS.V$PARAMETER WHERE name LIKE ‘_%’ ESCAPE ‘\’
/

Get database link details in database

set pagesize 200
set lines 200
col db_link for a19
set long 999
SELECT db_link,
owner_id,
logged_on,
heterogeneous,
open_cursors,
in_transaction,
update_sent
FROM gv$dblink
ORDER BY db_link;

How to Modify window_size/ window baseline size

— Check the current moving window baseline size:
select BASELINE_TYPE,MOVING_WINDOW_SIZE from dba_hist_baseline;

— Modify window_size to (7 days):
execute dbms_workload_repository.modify_baseline_window_size(window_size=> 7);

How to Modify window_size/ window baseline size

— Check the current moving window baseline size:
select BASELINE_TYPE,MOVING_WINDOW_SIZE from dba_hist_baseline;

— Modify window_size to (7 days):
execute dbms_workload_repository.modify_baseline_window_size(window_size=> 7);

Get DDL of privileges granted to user

set feedback off pages 0 long 900000 lines 20000 pagesize 20000 serveroutput on
accept USERNAME prompt “Enter username :”

execute dbms_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,’SQLTERMINATOR’,true);

SELECT DBMS_METADATA.GET_DDL(‘USER’,username) as script from DBA_USERS where username=’&username’
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL(‘SYSTEM_GRANT’,grantee)as script from DBA_SYS_PRIVS where grantee=’&username’ and rownum=1
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL(‘ROLE_GRANT’,grantee)as script from DBA_ROLE_PRIVS where grantee=’&username’ and rownum=1
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL(‘OBJECT_GRANT’,grantee)as script from DBA_TAB_PRIVS where grantee=’&username’ and rownum=1;

Get timezone information in database

SELECT version FROM v$timezone_file;

SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE ‘DST_%’
ORDER BY PROPERTY_NAME;

How to View / Modify AWR retention period

— View current AWR retention period

select retention from dba_hist_wr_control;

— Modify retention period to 7 days and interval to 30 min

select dbms_workload_repository.modify_snapshot_settings (interval => 30, retention => 10080);

NOTE – 7 DAYS = 7243600= 10080 minutes

Check the Characterset info of database

set pagesize 200
set lines 200
select parameter,value from v$nls_parameters where parameter like ‘NLS_%CHAR%’;