How to check the maximum number of session connected

For the last days, I want to know the maximum number of sessions on a database or for each node of my RAC.

The considered period is sysdate > AWR retention time

select a.instance_number, current_utilization, end_interval_time
from sys.wrh$_resource_limit a, sys.wrm$_snapshot b
where a.resource_name like ‘%sessions%’
and a.snap_id = b.snap_id
and a.instance_number = b.instance_number
and a.instance_number = 1
and b.begin_interval_time > sysdate – 30
order by current_utilization desc;

 

For the second node of a RAC change a.instance_number = 1 by a.instance_number=2.

 

NOTES:

AWR retention time:

select
extract( day from snap_interval) *24*60+
extract( hour from snap_interval) *60+
extract( minute from snap_interval ) “Snapshot Interval”,
extract( day from retention) *24*60+
extract( hour from retention) *60+
extract( minute from retention ) “Retention Interval”
from dba_hist_wr_control;

To change AWR retention time:

execute dbms_workload_repository.modify_snapshot_settings ( interval => 60,  retention => 100800);

 

Author: Vincent Fenoll Oracle DBA Montreal

ASM Space used

I want to monitor space usage (free and used) on Oracle ASM diskgroups.

 

With v$ view and sql*Plus:

SELECT name, free_mb, total_mb, free_mb/total_mb*100 as percentage
FROM v$asm_diskgroup;

NAME FREE_MB TOTAL_MB PERCENTAGE
———————————————————— ———- ———- ———-
DATA 62532 1536216 4.07052133
MGMT 5760 42000 13.7142857
OCRVOTE 20144 20480 98.359375
RECOVER 441296 445496 99.0572306

With asmcmd:

$ asmcmd lsdg
State Type Rebal Sector Logical_Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED EXTERN N 512 512 4096 4194304 1536216 498044 0 498044 0 N DATA/
MOUNTED EXTERN N 512 512 4096 4194304 42000 5760 0 5760 0 N MGMT/
MOUNTED EXTERN N 512 512 4096 4194304 20480 20144 0 20144 0 Y OCRVOTE/
MOUNTED EXTERN N 512 512 4096 4194304 445496 441296 0 441296 0 N RECOVER/

If you have it, Cloud control give also the information.

Vincent Fenoll – Oracle OCP Database administrator in Montreal

SQL Get list of all characters in column

I have an Oracle database table (e.g employee) with a lot of addresses in different languages and special characters.

I want to get the list of all distinct characters and have a count of each occurrences.

with data
as
(select level r from dual connect by level <= 100)
select substr( a.address, r, 1 ) as Character , count(*) as Number_occurrence
from employee a, data
where data.r <= length(a.address)
group by substr(a.address,r,1)
order by substr(address) ;

For this example, the length of the address field is 100; that’s why we connect 100 times.

Output:

Character Number_occurrence
! 147
” 356
# 19939
$ 1042
% 47
& 21247
‘ 18708
( 49995
) 48235
* 28379
+ 412
, 8051
– 69373
. 107809
/ 52027
: 590
; 14
< 5
= 18

Script by: Ulpia ITTU Oracle DBA (Montreal)

How to convert rows to one column in SQL?

The goal is to convert all the row values in a column to a single concatenated list.
You can change the space separated by comma or other csv like fields.

Example of a result:

ID ADDRESS
id1 addres1 addres2 addres3
id2 addres4
id3 addres5 addres6
id4 addres7 addres8 addres9 addres10 addres11

select ID,
max(decode(my_seq,1,ADDRESS)) || ‘ ‘ ||
max(decode(my_seq,2,ADDRESS)) || ‘ ‘ ||
max(decode(my_seq,3,ADDRESS)) || ‘ ‘ ||
max(decode(my_seq,4,ADDRESS)) || ‘ ‘ ||
max(decode(my_seq,5,ADDRESS)) emp_list
from (
select ID,
ADDRESS,
row_number() over
(partition by ID order by ADDRESS) my_seq
from customer_table
)
WHERE ADDRESS IS NOT NULL
group by ID
order by 1

Author: Vincent Fenoll Oracle DBA Montreal
compatibility: Oracle 10g 11g 12c

How to generate DDL for db_links

Is there anyway to extract ddl for all database links and perhaps find the password of the connect string?
YES

COLUMN Text FORMAT a2480 WORD_WRAPPED
SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON head off
exec DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, ‘SQLTERMINATOR’, true);
exec DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, ‘PRETTY’, true);

SELECT DBMS_METADATA.GET_DDL(‘DB_LINK’,db.db_link,db.owner) Text from dba_db_links db;

Add a Where clause for a specific DDL:
where upper(db_link) like ‘%example%’;

Next, look at your TNS_ADMIN directory (or tnsnping the alias) to find what is the target.

 

If your DB is <= 11.2.0.2, you can crack the password of the dblink using this method:

select passwordx from sys.link$ where name=’example;

PASSWORDX
————————————————–
05CA78F36B77C902B9286FFC981F4C9A92F8479D406ADWA670

set serveroutput on
declare
db_link_password varchar2(200);
begin
db_link_password := ’05CA78F36B77C902B9286FFC981F4C9A92F8479D406ADWA670′;

dbms_output.put_line (‘Password: ‘ || utl_raw.cast_to_varchar2 ( dbms_crypto.decrypt ( substr (db_link_password, 19) , dbms_crypto.DES_CBC_PKCS5 , substr (db_link_password, 3, 16) ) ) );
end;
/

Author: Vincent Fenoll – Oracle DBA Montreal