++++++++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
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
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;
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