How to extract DDL for scheduler jobs owned by SYS?

We can’t export scheduler jobs which are owned by SYS user. If you try to get DDL for scheduler jobs owned by SYS you’ll hit ORA-31603 error
The solution of this problem is to copy the scheduler job to another user (e.g. vfenoll) and then extract the DDL with dbms_metadata.

 

Here is the steps with an example:

— create the temporary user
create user vfenoll identified by tempuser1234;

— build the copy statement
set head off lines 130
select ‘exec dbms_scheduler.copy_job(”SYS.’ || job_name || ”’, ”VFENOLL.’ || job_name || ”’);’ from dba_scheduler_jobs where owner=’SYS’;

— select the jobs you want to copy (probably at the end of the list) and execute the copy_job for your selection
[…]
exec dbms_scheduler.copy_job(‘SYS.PURGE_FGA_TRAILS’, ‘VFENOLL.PURGE_FGA_TRAILS’);
[…]

# Build the DDL statements
SET ECHO OFF NEWP 0 SPA 0 PAGES 0 FEED OFF HEAD OFF TRIMS ON TAB OFF SERVEROUT OFF VER 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);

spool gen_sys_scheduler_jobs.sql
select ‘select dbms_metadata.get_ddl(”PROCOBJ”,”’ || job_name || ”’,”VFENOLL”) from dual;’ from dba_scheduler_jobs where owner=’VFENOLL’;
spool off

# extract the DDL
set long 2000 head off
spool sys_scheduler_jobs.sql
@gen_sys_scheduler_jobs.sql
spool off
[…]

BEGIN
dbms_scheduler.create_job(‘”PURGE_FGA_TRAILS”‘,
job_type=>’PLSQL_BLOCK’, job_action=>
‘BEGIN DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(2, TRUE); END;’
, number_of_arguments=>0,
start_date=>NULL, repeat_interval=>
‘FREQ=HOURLY;INTERVAL=24’
, end_date=>NULL,
job_class=>'”DEFAULT_JOB_CLASS”‘, enabled=>FALSE, auto_drop=>TRUE,comments=>
‘Audit clean job = ”PURGE_FGA_TRAILS”’
);

COMMIT;
END;
/

[…]
spool off

 

— Format the output, change enabled=>FALSE   to TRUE, remove the COMMITs.

You can also recreate the jobs in Cloud Control with the all the informations you gathered.

 

— drop the temporary user with the jobs
drop user vfenoll cascade;

 

It’s never a good idea to create jobs (or other objects) in SYS, prefer another schema :)

Author: V. Fenoll Oracle DBA Montreal

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