Monitoring Scripts

::::::::::::::
ASM_check.sql
::::::::::::::

set line 220 pages 1000 heading off feedback off termout off
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
col db_unique_name for a10
spool 1_asm_data.log appendSELECT d.db_unique_name,v.name
diskgroup,round((total_mb/1024),1) "Total_GB",round((free_mb/1024),1) "FREE_GB",round(((total_mb-free_mb)/total_mb*100),1) as Percentage FROM V$asm_diskgroup v, v$database d;
spool off

exit

::::::::::::::
DB_check_PDB.sql
::::::::::::::

set line 220 pages 100 heading off feedback off termout off
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
col OPEN_MODE for a10
col OPEN_TIME for a40
col name for a10
col RESTRICTED for a12

spool pdb_1_highlevel.log append
select inst_id,name, OPEN_MODE,RESTRICTED,OPEN_TIME,(select round(sum(bytes)/1024/1024/1024,0) from dba_segments) size_gb from gv$PDBS;

spool off

spool 4_outstandingalerts.log append
col reason for a70
col creation_time for a35
col suggested_action for a70
select name Database,creation_time,message_type,reason,suggested_action from dba_outstanding_alerts,v$database;
spool off

spool 5_tablespace.log append
select name Database,tablespace_name,round(((used_space*8192)/1024/1024/1024),1) "USED_GB",round(((tablespace_size*8192)/1024/1024/1024),1) "TOTAL_GB",round(USED_PERCENT,1) Percentage_USED FROM dba_tablespace_usage_metrics,v$database where USED_PERCENT>80;

spool off

exit

::::::::::::::
DB_check_RO.sql
::::::::::::::

set line 220 pages 1000 heading off feedback off termout off
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

col name for a10
col instance_name for a15
col host_name for a35
col open_mode for a21
col database_role for a17
col SWITCHOVER_STATUS for a20
col startup_time for a20
col flashback_on for a12
col processes for a15
col sessions for a15

spool 1_highlevel.log append

select a.name,b.instance_name,b.host_name,a.open_mode,database_role,startup_time,

(select CURRENT_UTILIZATION || '/' || trim(limit_value) ||'('||round((CURRENT_UTILIZATION/limit_value*100),1)||'%)' from v$resource_limit where resource_name='processes') processes,(select CURRENT_UTILIZATION || '/' || trim(limit_value)||'('||round((CURRENT_UTILIZATION/limit_value*100),1)||'%)' from v$resource_limit where resource_name='sessions') sessions,(select round(sum(bytes)/1024/1024/1024,1) from dba_segments) "Size(GB)" from v$database a,v$instance b;

set heading off

select a.name,b.instance_name,b.host_name,a.open_mode,database_role,startup_time,(select CURRENT_UTILIZATION || '/' || trim(limit_value) ||'('||round((CURRENT_UTILIZATION/limit_value*100),1)||'%)' from v$resource_limit where resource_name='processes') processes,(select CURRENT_UTILIZATION || '/' || trim(limit_value) ||'('||round((CURRENT_UTILIZATION/limit_value*100),1)||'%)' from v$resource_limit where resource_name='sessions') sessions 
from gv$database a,gv$instance b where a.inst_id=b.inst_id and a.inst_id=2;

spool off

spool 2_standbylag.log append

col source for a10
col target for a10
col primary_time for a20
col standby_TIME for a20

SELECT source, target, primary_log, standby_log,primary_log-standby_log LOG_GAP, sysdate primary_time,standby_TIME,round((sysdate-standby_TIME)*1440,0) Time_gap_min
FROM
(
SELECT db_unique_name source
FROM v$database
),
(
SELECT MAX(SEQUENCE#) primary_log
FROM v$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED='YES'
),
(
SELECT name target,MAX(SEQUENCE#) standby_log,MAX(COMPLETION_TIME) standby_TIME
FROM v$ARCHIVED_LOG WHERE name='DSNASB' AND APPLIED='YES' group by name
)
where standby_TIME>sysdate-30
/

spool off

spool 3_otherinfo.log append

col spfile for a65
col last_DB_bkp for a20
col last_arc_bkp for a20
col MMON for a44

select
(select instance_name from v$instance) name,
(select max(END_TIME) from v$RMAN_STATUS WHERE OPERATION='BACKUP' and STATUS='COMPLETED' and OBJECT_TYPE='DB FULL' or OBJECT_TYPE='DB INCR') last_DB_bkp,(select max(END_TIME) from v$RMAN_STATUS WHERE OPERATION='BACKUP' and OBJECT_TYPE='ARCHIVELOG' and STATUS='COMPLETED') last_arc_bkp,(select DISPLAY_VALUE from v$parameter where name='spfile') spfile
from dual;

spool off

spool 4_outstandingalerts.log append

col reason for a70
col creation_time for a35
col suggested_action for a70

select name Database,creation_time,message_type,reason,suggested_action from dba_outstanding_alerts,v$database;

spool off

spool 5_tablespace.log append

select name Database,tablespace_name,round(((used_space*8192)/1024/1024/1024),1) "USED_GB",round(((tablespace_size*8192)/1024/1024/1024),1) "TOTAL_GB",round(USED_PERCENT,1) Percentage_USED FROM dba_tablespace_usage_metrics,v$database where USED_PERCENT>80;

spool off

spool 6_recoveryarea.log append

col RECOVERY_AREA_USAGE for a30

select i.instance_name as DB_NAME,flashback_on, (select count(*) from v$restore_point) RESTORE_POINTS, round((f.space_used/1024/1024/1024),0)||'/'||(f.space_limit/1024/1024/1024)||' ('||round((f.space_used/space_limit*100),1)||'%)' "RECOVERY_AREA_USAGE" from v$database d, v$instance i,v$recovery_file_dest f;

spool off

exit

::::::::::::::

DB_check.sql

::::::::::::::

set line 220 pages 1000 heading off feedback off termout off
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
col name for a10
col instance_name for a15
col host_name for a35
col open_mode for a21
col database_role for a17
col SWITCHOVER_STATUS for a20
col startup_time for a20
col flashback_on for a12
col processes for a15
col sessions for a15

spool 1_highlevel.log append

select a.name,b.instance_name,b.host_name,a.open_mode,database_role,startup_time,(select CURRENT_UTILIZATION || '/' || trim(limit_value) ||'('||round((CURRENT_UTILIZATION/limit_value*100),1)||'%)' from v$resource_limit where resource_name='processes') processes,(select CURRENT_UTILIZATION || '/' || trim(limit_value) ||'('||round((CURRENT_UTILIZATION/limit_value*100),1)||'%)' from v$resource_limit where resource_name='sessions') sessions,(select round(sum(bytes)/1024/1024/1024,1) from dba_segments) "Size(GB)" from v$database a,v$instance b;

set heading off

select a.name,b.instance_name,b.host_name,a.open_mode,database_role,startup_time,(select CURRENT_UTILIZATION || '/' || trim(limit_value) ||'('||round((CURRENT_UTILIZATION/limit_value*100),1)||'%)' from v$resource_limit where resource_name='processes') processes, (select CURRENT_UTILIZATION || '/' || trim(limit_value) ||'('||round((CURRENT_UTILIZATION/limit_value*100),1)||'%)' from v$resource_limit where resource_name='sessions') sessions from gv$database a,gv$instance b where a.inst_id=b.inst_id and a.inst_id=2;

spool off

spool 2_standbylag.log append

col source for a10
col target for a10
col primary_time for a20
col standby_TIME for a20

SELECT source, target, primary_log, standby_log,primary_log-standby_log LOG_GAP, sysdate primary_time,standby_TIME,round((sysdate-standby_TIME)*1440,0) Time_gap_min
FROM
(
SELECT db_unique_name source
FROM v$database
),
(
SELECT MAX(SEQUENCE#) primary_log
FROM v$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED='YES'
),
(
SELECT name target,MAX(SEQUENCE#) standby_log,MAX(COMPLETION_TIME) standby_TIME
FROM v$ARCHIVED_LOG WHERE DEST_ID in (select dest_id from v$archive_dest where destination is not null and dest_id!=1) AND APPLIED='YES' group by name
)
where standby_TIME>sysdate-30
/

spool off

spool 3_otherinfo.log append

col spfile for a65
col last_DB_bkp for a20
col last_arc_bkp for a20
col MMON for a44

select
(select instance_name from v$instance) name,(select max(END_TIME) from v$RMAN_STATUS WHERE OPERATION='BACKUP' and STATUS='COMPLETED' and OBJECT_TYPE='DB FULL' or OBJECT_TYPE='DB INCR') last_DB_bkp, (select max(END_TIME) from v$RMAN_STATUS WHERE OPERATION='BACKUP' and OBJECT_TYPE='ARCHIVELOG' and STATUS='COMPLETED') last_arc_bkp,(select DISPLAY_VALUE from v$parameter where name='spfile') spfile 
from dual;

spool off

spool 4_outstandingalerts.log append

col reason for a70
col creation_time for a35
col suggested_action for a70
select name Database,creation_time,message_type,reason,suggested_action from dba_outstanding_alerts,v$database;

spool off

spool 5_tablespace.log append

select name Database, tablespace_name,round(((used_space*8192)/1024/1024/1024),1) "USED_GB",round(((tablespace_size*8192)/1024/1024/1024),1) "TOTAL_GB",round(USED_PERCENT,1) Percentage_USED FROM dba_tablespace_usage_metrics,v$database where USED_PERCENT>80;

spool off

spool 6_recoveryarea.log append

col RECOVERY_AREA_USAGE for a30

select i.instance_name as DB_NAME,flashback_on, (select count(*) from v$restore_point) RESTORE_POINTS, round((f.space_used/1024/1024/1024),0)||'/'||(f.space_limit/1024/1024/1024)||' ('||round((f.space_used/space_limit*100),1)||'%)' "RECOVERY_AREA_USAGE" from v$database d, v$instance i,v$recovery_file_dest f;

spool off

exit 

How to define Oracle Prompt in SQL*Plus

cd $ORACLE_HOME/sqlplus/admin

vi glogin.sql

set sqlprompt "_user'@'_connect_identifier> " 

sqlplus / as sysdba

SYS @ GGATE1>

After DML on the Master Table(s) of Local Materialized View, USER_MVIEWS.COMPILE_STATE becomes 'NEEDS_COMPILE' and USER_OBJECTS.STATUS becomes 'INVALID' (Doc ID 264036.1)

APPLIES TO:

Oracle Database - Enterprise Edition - Version 9.0.1.0 and later

Information in this document applies to any platform.

SYMPTOMS

After executing a DML statement on base table(s) of MV that is on the same database as its master table(s) (i.e. a local MV), USER_OBJECTS.STATUS becomes INVALID for the MV.  Also, USER_MVIEWS.COMPILE_STATE shows status "NEEDS_COMPILE'.

Example :

SQL> select object_name, object_type, status from user_objects

2 where object_type = 'MATERIALIZED VIEW';

Output :

OBJECT_NAME                    OBJECT_TYPE        STATUS

------------------------------ ------------------ -------

EMP_MV_PK                      MATERIALIZED VIEW  VALID

SQL> select mview_name, compile_state from user_mviews;

Output :

MVIEW_NAME                     COMPILE_STATE

------------------------------ -------------------

EMP_MV_PK                      VALID

SQL> update emp set empno = empno where empno = 605;

SQL> commit;

SQL> select object_name, object_type, status from user_objects

2 where object_type = 'MATERIALIZED VIEW';

Output :

OBJECT_NAME                    OBJECT_TYPE        STATUS

------------------------------ ------------------ -------

EMP_MV_PK                      MATERIALIZED VIEW  INVALID

SQL> select mview_name, compile_state from user_mviews;

Output :

MVIEW_NAME                      COMPILE_STATE

------------------------------ -------------------

EMP_MV_PK                       NEEDS_COMPILE

CHANGES

DML was executed and committed on master table of the MV since the MV was last refreshed.

CAUSE

This is expected behavior. Dependencies related to MVs are automatically maintained to ensure correct operation. When an MV is created, the materialized view depends on the master tables referenced in its definition. Any DML operation, such as an INSERT, or DELETE, UPDATE, or DDL operation on any dependency in the materialized view will cause it to become invalid.

SOLUTION

Though the status is INVALID, the MV can still be queried.  However, the query on MV will not return the latest data in master table unless the MV is refreshed.

Example :

SQL> select object_name, object_type, status from user_objects

2 where object_type = 'MATERIALIZED VIEW';

Output :

OBJECT_NAME                    OBJECT_TYPE        STATUS

------------------------------ ------------------ -------

EMP_MV_PK                      MATERIALIZED VIEW  INVALID

SQL> select mview_name, compile_state from user_mviews;

Output :

MVIEW_NAME                     COMPILE_STATE

------------------------------ -------------------

EMP_MV_PK                      NEEDS_COMPILE

SQL> select count(*) from emp_mv_pk;

Output :

COUNT(*)

----------

2686976

- On next refresh, the status becomes VALID.

Example :

SQL> execute dbms_mview.refresh('emp_mv_pk');

SQL> select object_name, object_type, status from user_objects

2 where object_type = 'MATERIALIZED VIEW';

Output :

OBJECT_NAME                    OBJECT_TYPE        STATUS

------------------------------ ------------------ -------

EMP_MV_PK                      MATERIALIZED VIEW  VALID

SQL> select mview_name, compile_state from user_mviews;

Output :

MVIEW_NAME                     COMPILE_STATE

------------------------------ -------------------

EMP_MV_PK                      VALID

"REFRESH ON COMMIT" MVs are the exception to this behavior because they are refreshed at the same time of DML.

Using Regular Expression - REGEXP_SUBSTR example

SELECT REGEXP_SUBSTR('(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orcl-orcl2.myhost.ca)(PORT=41295))(CONNECT_DATA=(SID=ORCL2)))', '[(]HOST[=][^()]+[)]',1,1,'i') RESULT
FROM dual;


Result:

(HOST=orcl-orcl2.myhost.ca)

Find All Non-Numeric Values in a Column

Return All Non-Numeric Values:

SELECT uen

FROM scott.dept 

WHERE NOT REGEXP_LIKE(uen, '^[-+]?[0-9]*\.?[0-9]+([eE][-+]?[0-9]+)?$');

Return Non-Integers:

SELECT c1 FROM t1 WHERE NOT REGEXP_LIKE(c1, '^[0-9]+$');

WM_CONCAT function - How to aggregate data from a number of rows into a single row

Term: WM_CONCAT

Definition:
The Oracle PL/SQL WM_CONCAT function is used to aggregate data from a number of rows into a single row, giving a list of data associated with a specific value. In effect, it cross-tabulates a comma delimited list.

Note that WM_CONCAT is undocumented and unsupported by Oracle, meaning it should not be used in production systems. The LISTAGG function, which can produce the same output asWM_CONCAT is both documented and supported by Oracle.


Example Usage:

CREATE TABLE test_wm (

column_1 VARCHAR2(5),

column_2 VARCHAR2(20));



INSERT INTO test_wm VALUES (111, 'This');

INSERT INTO test_wm VALUES (111, 'is');

INSERT INTO test_wm VALUES (111, 'a');

INSERT INTO test_wm VALUES (111, 'test');

INSERT INTO test_wm VALUES (222, 'This is not');



SELECT * FROM test_wm;



col concat format a40



SELECT column_1, wmsys.wm_concat(column_2) CONCAT

FROM test_wm

GROUP BY column_1;



SELECT column_1, TRANSLATE(wmsys.wm_concat(column_2), 'A,', 'A ') CONCAT

FROM test_wm

GROUP BY column_1;

Find roles granted to users start with "X" or "T" following by any number

select grantee, GRANTED_ROLE 
from dba_role_privs 
where REGEXP_LIKE(GRANTEE, '^[X|T][0-9]+$')order by grantee;

Like operator with IN clause

  logon_triggers_exclude values:

MYDB

YDB%

SELECT DISTINCT a.DATABASE_ID,  FROM cp_database a  WHERE NOT EXISTS ( SELECT 1                        FROM logon_triggers_exclude lt                      WHERE a.database_name LIKE lt.database_name);

Speeding Up Queries with Semi-Joins and Anti-Joins: How Oracle Evaluates EXISTS, NOT EXISTS, IN, and NOT IN

by Roger Schrag
Database Specialists, Inc.

From http://www.dbspecialists.com/files/presentations/semijoins.html

Introduction
Optimizing SQL usually gives the most significant results when DBAs are called upon to “make the system run faster.” Using tools like Statspack or Enterprise Manager, it is often easy to find the slow SQL. But how do you make the queries run faster? That is the challenge! In this paper we will discuss the semi-join and the anti-join, two powerful SQL constructs Oracle offers for use in your quest for faster queries. In particular, we will define these two terms, discuss when and why you might want to use the [NOT] EXISTS or [NOT] IN constructs, and demonstrate how you can use optimizer hints and make minor query changes in order to enable Oracle to use some very powerful and efficient access paths.

How to check Time Zone of Database

select DBTIMEZONE from dual;

select systimestamp from dual

Delete database with DBCA in silent mode

dbca -deleteDatabase -silent -sourceDB MYDB -sysDBAUserName SYS -sysDBAPassword mypass

Check the total possible shrinkage for tablespace

SELECT SUM(shrinkage_possible_mb) FROM 
(SELECT NVL ( ROUND ( ( blocks-hwm + 1 ) * bytes / blocks / 1024 / 1024 ) , 
              ROUND ( ( blocks ) * bytes / blocks / 1024 / 1024 ) ) shrinkage_possible_mb
   FROM dba_data_files a , 
       ( SELECT file_id , max ( block_id + blocks ) hwm 
         FROM dba_extents GROUP BY file_id ) b 
  WHERE a.file_id =b.file_id ( + ) AND
        a.tablespace_name = 'DATA01'
);

Check which segment resides in the last block of each datafile

SELECT de.file_id, owner, segment_name, partition_name, segment_type
  FROM dba_extents de,
      (SELECT file_id, MAX(block_id) mblock_id
         FROM dba_extents
       WHERE tablespace_name = 'DATA01'
       GROUP BY file_id
      ) fmax
WHERE de.file_id = fmax.file_id AND de.block_id = fmax.mblock_id 
ORDER BY file_id;

How to check possible shrinkage for datafiles

SELECT file_name , Round(bytes/1024/1024) file_size_MB, 
Nvl ( Round ( ( blocks-hwm + 1 ) *bytes / blocks / 1024 / 1024 ) , 
Round ( ( blocks ) *bytes / blocks / 1024 / 1024 ) ) shrinkage_possible_mb , 
Nvl ( Round ( hwm *bytes / blocks / 1024 / 1024 ) , 0 ) size_after_shrinkage_mb 
FROM dba_data_files a , 
( SELECT file_id , max ( block_id + blocks ) hwm FROM dba_extents GROUP BY file_id ) b 
WHERE a.file_id =b.file_id ( + ) AND
      a.tablespace_name = 'USERS'

DBA day-to-day tips

• Don't use rm -rf *.* for any reason at anytime, do rm *.log or *.lis or *.trc: It is safer to back up a directory and use rmdir instead. It would be even better if you renamed the entire directory and left it in place renamed for a day or two.

• Assuming that all of the datafiles in a certain directory only pertain to one database is a recipe for disaster, those files can be created anywhere on the filesystem as long as Oracle has write access

• Modifying access for a production instance at the SQL*Plus level is unusual and generally not granted to programming staff unless there is a single point of accountability, such as a lead programmer.

Example of DBMS_STATS

BEGIN

      DBMS_STATS.GATHER_TABLE_STATS ( ownname => 'MYUSR',

                                      tabname => 'MY_PART_TABLE',

                                      estimate_percent =>1,

                                      method_opt =>'FOR ALL INDEXED COLUMNS SIZE 1',

                                      granularity =>'PARTITION',

                                      DEGREE=>8,

                                      NO_INVALIDATE => TRUE,

                                      Cascade => TRUE);

END;

Connect to oracle via SQL*Plus using Connection String

conn dbsnmp/dbsnmppw@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=myserver-vip.com)(PORT=41521)))(CONNECT_DATA=(SERVICE_NAME=MYPRODDB)));

Example of function-based index

Tom Kyte Blog - asktom.oracle.com

My point is, the optimizer thinks that very few rows will be returned by the FBI, that will be the index it really wants to use to access this table then. 0 or 1 regardless. 0 would be even better than 1. This is actually an example I use in my seminars when talking about indexing actually. ops$tkyte%ORA11GR1> create table t as 2 select 'Y' processed_flag, a.* from stage a; Table created.

Summary results for dates

select 1979 + years.year  year
from   ( select  rownum year
         from    dual
         connect
         by      rownum < 6 ) years
/

YEAR
----------
      1980
      1981
      1982
      1983
      1984
      1985

Or 

Generate Ascii codes:

select 96 + codes.code  code
from   ( select  rownum code
         from    dual
         connect
         by      rownum < 27 ) codes

select DBMS_RANDOM.string('L',3) from dba_objects    

STRING

The STRING function returns a string of random characters of the specified length. The OPT parameter determines the type of string produced as follows:

'u', 'U' - uppercase alpha characters
'l', 'L' - lowercase alpha characters
'a', 'A' - mixed case alpha characters
'x', 'X' - uppercase alpha-numeric characters
'p', 'P' - any printable characters


The LEN parameter, not surprisingly, specifies the length of the string returned.

SET SERVEROUTPUT ON
BEGIN
  FOR i IN 1 .. 5 LOOP
    DBMS_OUTPUT.put_line('string(''x'',10)= ' || DBMS_RANDOM.string('x',10));
  END LOOP;
END;
/
string('x',10)= BL69189JC0
string('x',10)= XKSI33Z5E8
string('x',10)= WMK7LWIXK7
string('x',10)= E9T9KAZTIX
string('x',10)= 5NTMSELFXD

PL/SQL procedure successfully completed.

SQL>