Get installed sqlpatches in database - ORACLEAGENT BLOG

Database growth per month

select to_char(creation_time, ‘MM-RRRR’) “Month”, sum(bytes)/1024/1024/1024 “Growth in GB
from sys.v_$datafile
where to_char(creation_time,’RRRR’)=’&YEAR_IN_YYYY_FORMAT’
group by to_char(creation_time, ‘MM-RRRR’)
order by to_char(creation_time, ‘MM-RRRR’);

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

Generate resize datafile script

select ‘alter database datafile’||’ ”’||file_name||””||’ resize ‘||round(highwater+2)||’ ‘||’m’||’;’ from (
select /+ rule */
a.tablespace_name,
a.file_name,
a.bytes/1024/1024 file_size_MB,
(b.maximum+c.blocks-1)d.db_block_size/1024/1024 highwater
from dba_data_files a ,
(select file_id,max(block_id) maximum
from dba_extents
group by file_id) b,
dba_extents c,
(select value db_block_size
from v$parameter
where name=’db_block_size’) d
where a.file_id= b.file_id
and c.file_id = b.file_id
and c.block_id = b.maximum
order by a.tablespace_name,a.file_name);

Get database uptime

select to_char(startup_time, ‘DD-MM-YYYY HH24:MI:SS’),floor(sysdate-startup_time) DAYS from v$Instance;

Scn to Timestamp and Timestamp to Scn

— Get current scn value:
select current_scn from v$database;
— Get scn value at particular time:
select timestamp_to_scn(’26-FEB-21:05:10:00′) from dual;
— Get timestamp from scn:
select scn_to_timestamp(298411441)from dual;

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

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
/

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;

Feb, 2021

Oracle database Health check scripts

PERFOMANCE TUNING SCRIPTS

MONITOR SESSION
SQL PROFILE & BASELINE
ALL IN ONE SCRIPT
HISTORY OF EXECUTED QUERIES
SQL HISTORY
WAIT EVENTS, AWR ,ASH , TIME MODEL SCRIPTS
MONITOR SQL’S

MAIL SCRIPTS

Get DDL ‘s For All objects in oracle database

TEMP TABLESPACE scripts

UNDO TABLESPACE scripts

TUNING SCRIPTS

Root cause, Problems and Solutions

MONITOR SESSION

SQL PROFILE & BASELINE

ALL IN ONE SCRIPT

HISTORY OF EXECUTED QUERIES

SQL HISTORY

WAIT EVENTS, AWR ,ASH , TIME MODEL SCRIPTS

MONITOR SQL’S

Session monitoring scripts

Total user count on database:

set lines 750 pages 9999 compute SUM of tot on report compute SUM of active on report compute SUM of inactive on report col username for a50 select DECODE(username,NULL,'INTERNAL',USERNAME) Username, count(*) TOT, COUNT(DECODE(status,'ACTIVE',STATUS)) ACTIVE, COUNT(DECODE(status,'INACTIVE',STATUS)) INACTIVE from gv$session where status in ('ACTIVE','INACTIVE') group by username;

Users Details Session:

For Cluster:

set linesize 750 pages 9999 column box format a30 column spid format a10 column username format a30 column program format a30 column os_user format a20 col LOGON_TIME for a20 select b.inst_id,b.sid,b.serial#,a.spid, substr(b.machine,1,30) box,to_char (b.logon_time, 'dd-mon-yyyy hh24:mi:ss') logon_time,substr(b.username,1,30) username, substr(b.osuser,1,20) os_user,substr(b.program,1,30) program,status,b.last_call_et AS last_call_et_secs,b.sql_id from gv$session b,gv$process a where b.paddr = a.addr and a.inst_id = b.inst_id and type='USER' order by logon_time;

For Standalone:

set pages 500 set linesize 750 column box format a30 column spid format a10 column username format a30 column program format a30 column os_user format a20 select b.sid,b.serial#,a.spid, substr(b.machine,1,30) box,b.logon_time logon_date , to_char (b.logon_time, 'hh24:mi:ss') logon_time, substr(b.username,1,30) username, substr(b.osuser,1,20) os_user, substr(b.program,1,30) program,status,b.last_call_et AS last_call_et_secs,b.sql_id from v$session b,v$process a where b.paddr = a.addr and type='USER' order by b.sid;

(or)

set head off set verify off set echo off set pages 1500 set linesize 100 set lines 120 prompt prompt Details of SID / SPID / Client PID prompt ================================== select /+ CHOOSE/ 'Session Id.............................................: '||s.sid, 'Serial Num..............................................: '||s.serial#, 'User Name ..............................................: '||s.username, 'Session Status .........................................: '||s.status, 'Client Process Id on Client Machine ....................: '||''||s.process||'' Client, 'Server Process ID ......................................: '||p.spid Server, 'Sql_Address ............................................: '||s.sql_address, 'Sql_hash_value .........................................: '||s.sql_hash_value, 'Schema Name ..... ......................................: '||s.SCHEMANAME, 'Program ...............................................: '||s.program, 'Module .................................................: '|| s.module, 'Action .................................................: '||s.action, 'Terminal ...............................................: '||s.terminal, 'Client Machine .........................................: '||s.machine, 'LAST_CALL_ET ...........................................: '||s.last_call_et, 'S.LAST_CALL_ET/3600 ....................................: '||s.last_call_et/3600 from v$session s, v$process p where p.addr=s.paddr and s.sid=nvl('&sid',s.sid) and p.spid=nvl('&spid',p.spid) and nvl(s.process,-1) = nvl('&ClientPid',nvl(s.process,-1));

To Find Session Information Details based on SID or SPID or CLIENTPID :

col program for a15 col machine for a15 col terminal for a15 set lines 152 select s.sid,s.serial#,''||s.process||'' Client,p.spid Server,s.sql_address,s.sql_hash_value,s.username,s.action, s.program || s.module,s.terminal,s.machine, s.status,s.last_call_et,s.last_call_et/3600 from gv$session s, gv$process p where p.addr=s.paddr and s.sid=nvl('&sid',s.sid) and p.spid=nvl('&spid',p.spid) and nvl(s.process,-1) = nvl('&ClientPid',nvl(s.process,-1));

Timing details, Client PID of associated oracle SID:

undefine spid set pagesize 40 col INST_ID for 99 col spid for a10 set linesize 150 col action format a10 col logon_time format a16 col module format a13 col cli_process format a7 col cli_mach for a15 col status format a10 col username format a10 col last_call_et for 9999.99 col sql_hash_value for 9999999999999 select p.INST_ID,p.spid,s.sid, s.serial#, s.status, s.username, s.action, to_char(s.logon_time, 'DD-MON-YY, HH24:MI') logon_time, s.module,s.program,s.last_call_et/3600 last_call_et ,s.process cli_process,s.machine cli_mach,s.sql_hash_value from gv$session s, gv$process p where p.addr=s.paddr and p.spid in(&SPID);

Checking Timing Details of SID and waiting event:

select a.sid, a.serial#, a.status, a.program, b.event,to_char(a.logon_time, 'dd-mon-yy hh24:mi') LOGON_TIME, to_char(Sysdate, 'dd-mon-yy--hh24:mi') CURRENT_TIME, (a.last_call_et/3600) "Hrs connected" from v$session a, v$session_wait b where a.sid in(&SIDs) and a.sid=b.sid;

Checking for active transactions SID

select username,t.used_ublk,t.used_urec from v$transaction t,v$session s where t.addr=s.taddr and s.sid in(&SIDs);

Checking what is the Last SQL

undefine sid col "Last SQL" for a70 select s.username, s.sid, s.serial#,t.sql_text "Last SQL" from gv$session s, gv$sqlarea t where s.sql_address =t.address and s.sql_hash_value =t.hash_value and s.sid in (&SIDs);

All Active and Inactive connections

col program for a15F col machine for a15 col terminal for a15 set lines 152 select s.sid,s.serial#,''||s.process||'' Client,p.spid Server,s.sql_address,s.sql_hash_value,s.username,s.action, s.program || s.module,s.terminal,s.machine, s.status,s.last_call_et,s.last_call_et/3600 from gv$session s, gv$process p where p.addr=s.paddr and s.type != 'BACKGROUND';

Active sessions

select p.spid "Thread", s.sid "SID-Top Sessions", substr(s.osuser,1,15) "OS User", substr(s.program,1,25) "Program Running" from v$process p, v$session s where p.addr=s.paddr order by substr(s.osuser,1,15);

Session details from Session long ops

select SID,SERIAL#,OPNAME,SOFAR,TOTALWORK,START_TIME,LAST_UPDATE_TIME,username from v$session_longops where sid=&SID and serial#=&SERIAL;

To list the nodes

set head off set verify off set echo off set pages 1500 set linesize 70 prompt prompt Environment sketch prompt ================================== select /+ CHOOSE/ 'NODE_NAME.................: '||NODE_NAME, 'CREATION_DATE.............: '||CREATION_DATE, 'CREATED_BY ...............: '||CREATED_BY, 'SUPPORT_CP ...............: '||SUPPORT_CP, 'SUPPORT_FORMS ............: '||SUPPORT_FORMS, 'SUPPORT_WEB ..............: '||SUPPORT_WEB, 'SUPPORT_ADMIN ............: '||SUPPORT_ADMIN, 'STATUS ...................: '||STATUS, 'HOST.DOMAIN ..... ........: '||HOST||'.'||DOMAIN, 'SUPPORT_DB ..............: '||SUPPORT_DB from apps.fnd_nodes;

Session details through SPID:

select sid, serial#, USERNAME, STATUS, OSUSER, PROCESS, MACHINE, MODULE, ACTION, to_char(LOGON_TIME,'yyyy-mm-dd hh24:mi:ss') from v$session where paddr in (select addr from v$process where spid = '18924');

To list count of connections from other machines:

select count(1),machine from gv$session where inst_id=3 group by machine;

To get total count of sessions and processes:

select count(*) from v$session;

select count(*) from v$process;

select (select count(*) from v$session) sessions, (select count(*) from v$process) processes from dual;

To find sqltext through sqladdress:

select sql_address from v$session where sid=8794;

select sql_text from v$sqltext where ADDRESS='A00000089A00FA0' order by PIECE;

To find sqltext for different sql hashvalue:

select hash_value,sql_text from v$sql where hash_value in (193756789,156412346, 456812142,8971248686,2358702945);

To list long running forms user sessions:

select s.sid,s.process,p.spid,s.status ,s.action,s.module, (s.last_call_et/3600) from v$session s, v$process p where round(last_call_et/3600) >4 and action like '%FRM%' and p.addr=s.paddr ;

To list inactive Sessions respective username:

SELECT username,count(*) num_inv_sess FROM v$session where last_call_et > 3600 and username is not null AND STATUS='INACTIVE' group by username order by num_inv_sess DESC;

SELECT count() FROM v$session where last_call_et > 43200 and username is not null AND STATUS='INACTIVE';
SELECT count() FROM v$session where last_call_et > 3600 and username is not null AND STATUS='INACTIVE';

To find session id with set of SPIDs:

select sid from v$session, v$process where addr=paddr and spid in ('11214','12948','90088');

To find Sql Text given SQLHASH & SQLADDR:

select piece,sql_text from v$sqltext where HASH_VALUE = &hash and ADDRESS ='&addr' order by piece;
select piece,sql_text from v$sqltext where ADDRESS ='&addr' order by piece;

To find Undo Generated For a given session:

select username, t.used_ublk ,t.used_urec from gv$transaction t,gv$session s where t.addr=s.taddr and s.sid='&sessionid';

Tag Archives: Get installed sqlpatches in database

Feb, 2021

Total user count on database:

Users Details Session:

To Find Session Information Details based on SID or SPID or CLIENTPID :