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

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

SELECT DBMS_METADATA.GET_DDL(‘DB_LINK’,db.db_link,db.owner) 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

For each column, count a value

For each column of the table “my_table”, I want to count how many rows containing “my_value_or_string” there are in the table.

In the script, change “my_table” and “my_value_or_string”.

BEGIN
  declare
      cursor c_my_table is
      select COLUMN_NAME
        from dba_tab_columns
       where TABLE_NAME = 'my_table';
  
    ch_column_name dba_tab_columns.COLUMN_NAME%TYPE;
    n_count        number(20);
    sql_stmt       VARCHAR2(200);
  
  BEGIN
  
    open c_my_table;
    LOOP
      FETCH c_my_table
        INTO ch_column_name;
    
      EXIT WHEN c_my_table%NOTFOUND;
      begin
        sql_stmt := 'select count(*) from my_table where ' ||
                    ch_column_name || '=my_value_or_string';
      
        execute immediate sql_stmt
          into n_count;
        dbms_output.put_Line(ch_column_name || ',' || n_count);
      
      exception
        when others then
          dbms_output.put_Line(ch_column_name || ',' || 'N/A');
        
      end;
    
    END LOOP;
    CLOSE c_my_table;
  
  END;
END;
/

Author: Vincent Fenoll, Oracle DBA Montreal