Daily Scripts

++++++++Query to check Tablespaces space utilization
Undo:

col name for a25   
select a.tbl "Name",a.tsz "Total Size",b.fsz "Free Space",
   round((1-(b.fsz/a.tsz))*100) "Pct Used",round((b.fsz/a.tsz)*100) "Pct Free" from
               (select tablespace_name tbl,sum(bytes)/1024/1024 TSZ from dba_data_files
               where tablespace_name like '%UNDO%' group by tablespace_name) a,
               (select tablespace_name tblsp,sum(bytes)/1024/1024 FSZ from dba_free_space
               where tablespace_name like '%UNDO%' group by tablespace_name) b
   Where a.tbl=b.tblsp;

SYSAUX:

col name for a25   
select a.tbl "Name",a.tsz "Total Size",b.fsz "Free Space",
   round((1-(b.fsz/a.tsz))*100) "Pct Used",round((b.fsz/a.tsz)*100) "Pct Free" from
               (select tablespace_name tbl,sum(bytes)/1024/1024 TSZ from dba_data_files
               where tablespace_name like '%SYSAUX%' group by tablespace_name) a,
               (select tablespace_name tblsp,sum(bytes)/1024/1024 FSZ from dba_free_space
               where tablespace_name like '%SYSAUX%' group by tablespace_name) b
   Where a.tbl=b.tblsp;

Temp Tablespace   

 SELECT A.tablespace_name tablespace, D.mb_total,
    SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
    D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
   FROM v$sort_segment A,
    (
   SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
    FROM v$tablespace B, v$tempfile C
     WHERE B.ts#= C.ts#
      GROUP BY B.name, C.block_size) D
    WHERE A.tablespace_name = D.name
    GROUP by A.tablespace_name, D.mb_total;  

Query To check Datafiles:

col file_name for a60
select file_name,bytes/1024/1024 from dba_data_files
where tablespace_name ='SYSAUX';

*******Queries to cleanup inactive Datapump jobs

Clean up datapump jobs which are inactive:

1. Determine in SQL*Plus if Data Pump jobs exist in the dictionary

SET lines 150
COL owner_name FORMAT a10
COL job_name FORMAT a20
COL operation FORMAT a10
SELECT owner_name, job_name, operation
FROM dba_datapump_jobs where state='NOT RUNNING' and attached_sessions=0;

Identify these jobs and ensure that the listed jobs in dba_datapump_jobs are not export/import Data Pump jobs that are active: status should be ‘NOT RUNNING’ and not attached to a session:

Step 2: Drop the master tables

set head off
SELECT 'drop table ' || owner_name || '.' || job_name || ';'
FROM dba_datapump_jobs WHERE state='NOT RUNNING' and attached_sessions=0;

purge dba_recyclebin;

SELECT owner_name, job_name, operation
FROM dba_datapump_jobs where state='NOT RUNNING' and attached_sessions=0;

********Queries to check Long Running Jobs:

monitoring Longops:

set lines 300 pages 300
col username for a30
col opname for a30
SELECT SID, SERIAL#,username, opname, SOFAR, TOTALWORK,
ROUND(SOFAR/TOTALWORK*100,2) COMPLETE
FROM   V$SESSION_LONGOPS
WHERE
TOTALWORK != 0
AND    SOFAR != TOTALWORK
order by 1;

Note: the same query can be used to monitor RMAN backup status

SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,
       ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%'
  AND OPNAME NOT LIKE '%aggregate%'
  AND TOTALWORK != 0
  AND SOFAR  != TOTALWORK;

******************Query to get DDL

set heading off;
set echo off;
Set pages 999;
set long 90000;
SELECT DBMS_METADATA.GET_DDL('PACKAGE_BODY','DBMS_IJOB','SYS') FROM DUAL;

set heading off;
set echo off;
Set pages 999;
set long 90000;
select dbms_metadata.get_ddl('SYNONYM','SYNONYM_NAME','OWNER') from dual;

set heading off;
set echo off;
Set pages 999;
set long 90000;
select dbms_metadata.get_ddl('TRIGGER','TRIGGER_NAME','OWNER') from dual;

set heading off;
set echo off;
Set pages 999;
set long 90000;
SELECT DBMS_METADATA.GET_DDL('PACKAGE_BODY','PACKAGE_NAME','OWNER') FROM DUAL;

To get tablespace ddl:

select 'select dbms_metadata.get_ddl(''TABLESPACE'','''|| tablespace_name || ''') from dual;' from dba_tablespaces;

To Get Function DDL:
SELECT DBMS_METADATA.GET_DDL('FUNCTION', OBJECT_NAME) FROM USER_PROCEDURES WHERE OBJECT_TYPE = 'FUNCTION';

To get Package DDL:
SELECT DBMS_METADATA.GET_DDL('PACKAGE','OBJECT_NAME','SCHEMA_NAME') FROM DUAL;

To get package body DDL: 
SELECT DBMS_METADATA.GET_DDL('PACKAGE_BODY','OBJECT_NAME','SCHEMA_NAME') FROM DUAL;

To get constraints DDL:
SELECT DBMS_METADATA.GET_DEPENDENT_DDL('REF_CONSTRAINT','OBJECT_NAME','SCHEMA_NAME') from dual;

To get system grants:
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','SCHEMA_NAME') from dual;

To get ddl for role grants:

SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','SCHEMA_NAME') from dual;

++++++++++++++++++sample script for create database+++++++++++++++++++++++++  

CREATE DATABASE DATABASE_NAME
   USER SYS IDENTIFIED BY xxxxxxxx
   USER SYSTEM IDENTIFIED BY xxxxxxxx
   LOGFILE GROUP 1 ('LOCATION_PATH/redo01.log') SIZE 100M,
           GROUP 2 ('LOCATION_PATH/redo02.log') SIZE 100M,
           GROUP 3 ('LOCATION_PATH/redo03.log') SIZE 100M
   MAXLOGFILES 5
   MAXLOGMEMBERS 5
   MAXLOGHISTORY 1
   MAXDATAFILES 100
   CHARACTER SET US7ASCII
   NATIONAL CHARACTER SET AL16UTF16
   EXTENT MANAGEMENT LOCAL
   DATAFILE 'LOCATION_PATH/system01.dbf' SIZE 800M
   SYSAUX DATAFILE 'LOCATION_PATH/sysaux01.dbf' SIZE 800M
   DEFAULT TABLESPACE users
      DATAFILE 'LOCATION_PATH/users01.dbf'
      SIZE 500M
   DEFAULT TEMPORARY TABLESPACE tempts1
      TEMPFILE 'LOCATION_PATH/temp01.dbf' SIZE 500M
   UNDO TABLESPACE UNDOTBS1
      DATAFILE 'LOCATION_PATH/undotbs01.dbf' SIZE 500M;

     
      Run Scripts to Build Data Dictionary Views
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
@?/sqlplus/admin/pupbld.sql
EXIT

*********************************************************************************************************************************************

Script Description
CATALOG.SQL Creates the views of the data dictionary tables, the dynamic performance views, and public synonyms for many of the views. Grants PUBLIC access to the synonyms.
CATPROC.SQL Runs all scripts required for or used with PL/SQL.
PUPBLD.SQL Required for SQL*Plus. Enables SQL*Plus to disable commands by user.
*********************************************************************************************************************************************
Query to check tablespaces which are used more than 80%:
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
col name for a25 
select a.tbl "Name",a.tsz "Total Size",b.fsz "Free Space",
   round((1-(b.fsz/a.tsz))*100) "Pct Used",round((b.fsz/a.tsz)*100) "Pct Free" from
               (select tablespace_name tbl,sum(bytes)/1024/1024 TSZ from dba_data_files
               where tablespace_name like '%%' group by tablespace_name) a,
               (select tablespace_name tblsp,sum(bytes)/1024/1024 FSZ from dba_free_space
               where tablespace_name like '%%' group by tablespace_name) b
   Where a.tbl=b.tblsp and round((1-(b.fsz/a.tsz))*100)>80;
 
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

TABLESPACE Management:
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
col name for a25 
select a.tbl "Name",a.tsz "Total Size",b.fsz "Free Space",
   round((1-(b.fsz/a.tsz))*100) "Pct Used",round((b.fsz/a.tsz)*100) "Pct Free" from
               (select tablespace_name tbl,sum(bytes)/1024/1024 TSZ from dba_data_files
               where tablespace_name like '%Tablespace_Name%' group by tablespace_name) a,
               (select tablespace_name tblsp,sum(bytes)/1024/1024 FSZ from dba_free_space
               where tablespace_name like '%Tablespace_Name%' group by tablespace_name) b
   Where a.tbl=b.tblsp;

col file_name for a60
select file_name,bytes/1024/1024 from dba_data_files
where tablespace_name ='Tablespace_Name';
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Query to extract ddl of tablespaces:

select 'Create tablespace '||tablespace_name||' datafile '||'''+diskgroup_name'''||' size '||sum(bytes)/1024/1024||'m;' from dba_data_files where tablespace_name not in ('SYSTEM','SYSAUX','UNDOTBS1','TEMP','UNDOTBS2','USERS') group by tablespace_name;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Queries to generate snapshots and generating awr and addm reports:

EXEC dbms_workload_repository.create_snapshot;
!date
@?/rdbms/admin/awrrpti
@?/rdbms/admin/addmrpti
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Query to check user roles and grants:

select
lpad(' ', 2*level) || granted_role "User, his roles and privileges"
from
(
/* THE USERS */
  select
    null     grantee,
    username granted_role
  from
    dba_users
  where
    username like upper('username')
/* THE ROLES TO ROLES RELATIONS */
union
  select
    grantee,
    granted_role
  from
    dba_role_privs
/* THE ROLES TO PRIVILEGE RELATIONS */
union
  select
    grantee,
    privilege
  from
    dba_sys_privs
)
start with grantee is null
connect by grantee = prior granted_role;

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Queries to check blocking sessions:

set time on
!date|awk '{print $4}'
set echo off
column blocker format a11;
column blockee format a10;
column sid format 99999;
select
           (select username from v$session where sid=a.sid) blocker,
         a.sid,
       ' is blocking ' "IS BLOCKING",
         (select username from v$session where sid=b.sid) blockee,
             b.sid
    from v$lock a, v$lock b
   where a.block = 1
     and b.request > 0
     and a.id1 = b.id1
     and a.id2 = b.id2
/

select spid, sid, a.serial#, b.username from v$session a, v$process b where sid=#### and a.paddr=b.addr;

select 'alter system kill session '''||sid||','||serial#||''''||' immediate;' from gv$session where status='INACTIVE' and username='Schema_name';

***********************************************************************************

Query for checking object locks:
select   c.owner,c.object_name,c.object_type,b.sid,b.serial#,b.status,b.osuser,b.machine from gv$locked_object a ,gv$session b,dba_objects c where b.sid = a.session_id and a.object_id = c.object_id;

=============================================================

Show the ten largest objects in the database:
============================================
col owner format a15
col segment_name format a30
col segment_type format a15
col mb format 999,999,999
select owner
, segment_name
, segment_type
, mb
from (
select owner
, segment_name
, segment_type
, bytes / 1024 / 1024 "MB"
from dba_segments
order by bytes desc
)
where rownum < 11
/

=============================================================
What's in undo:
==============

select tablespace_name
, status
, count(*) as HOW_MANY
from dba_undo_extents
group by tablespace_name
, status
/

=============================================================
Is anything rolling back at the moment?:
=======================================
set lines 100 pages 999
col username format a15
col command format a20
select ses.username
, substr(ses.program, 1, 19) command
, tra.used_ublk
from v$session ses
, v$transaction tra
where ses.saddr = tra.ses_addr
/
=============================================================
This Estimate the time percent complete for long SQL statements (DML) on the database: (powerfull)
=========
This Query fits all DML Statement:
=================================

set linesize 150
col username format a20
col opname format a35
SELECT * FROM (select
username,opname,sid,serial#,context,sofar,totalwork
,round(sofar/totalwork*100,2) "% Complete"
from v$session_longops)
WHERE round(sofar/totalwork*100,2) != 100;

%copmlete here will shows you the progress of long statements on the system.

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Query to check sync between standby and primary

Check for the redo applied and recieved
===============================

Execute the below query on the Primary database

SQL> SELECT THREAD# Thread,SEQUENCE# Last Sequence Generated FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#) ORDER BY 1;

Thread Last Sequence Generated

1                      21
1                      21


Execute the belowquery on the Standby database


SQL> SELECT ARCH.THREAD# Thread, ARCH.SEQUENCE# Last Sequence Received, APPL.SEQUENCE# Last Sequence Applied, (ARCH.SEQUENCE# APPL.SEQUENCE#) Difference FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

Thread Last Sequence Received Last Sequence Applied Difference

1                     21                    21          0


Compare value of Last Sequence Generated in query executed on Primary with Last Sequence Received in query executed on Standby for all threads.

If both values are same than this means that standby has received the last sequence generated on primary.
If both values are not same then there are some archives missing on standby, Hence standby is not in sync with the primary.

Check for GAP
============

On physical standby,execute the below SQL. If it does not returns any row and output is no row selected than this means there is no archive gap on standby.

SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

no rows selected
Powered by Blogger.