Query Varie

Processi e sessioni

Elenco processi di background

select s.sid,  s.process, s.program, p.name from v$session s join v$bgprocess p using (paddr) ;

sessioni

set linesize 200
set pagesize 200
set autotrace off
col machine for a35
col username for a20
col program for a20
col sid_ser for a15
col spid for a10
alter session set nls_date_format='DD-MM-YYYY HH24:MI:SS';
select s.sid ||','||s.serial# as sid_ser,p.spid,
 s.username,s.machine,s.sql_id,s.prev_sql_id,
 s.logon_time,substr(s.program,1,20) as program,s.seconds_in_wait as siw
 from v$session s
 left outer join v$process p on (p.addr=s.paddr)
 where s.type='USER' and s.status='ACTIVE'
 order by s.logon_time;

Controllo Allocazione Spazio

Questa query controlla lo spazio allocato, e lo confronta con lo spazio di massima crescita dei datafiles. Utile per evidenziare casi in cui è necessario aggiungere altri datafiles.

select TBS.MB_MAX,
       TBS.MB_TBS,
       SEG.MB_SEG,
       TBS.MB_MAX-SEG.MB_SEG MB_FREE,
       TBS.TABLESPACE_NAME
  from (select round(sum(f.maxbytes)/(1024*1024)) MB_MAX,
               round(sum(f.user_bytes)/(1024*1024)) MB_TBS,
               f.tablespace_name TABLESPACE_NAME
          from dba_data_files f
         group by f.tablespace_name) TBS,
       (select round(sum(s.bytes)/(1024*1024)) MB_SEG,
               s.tablespace_name TABLESPACE_NAME
          from dba_segments s
         group by s.tablespace_name) SEG
 where SEG.tablespace_name=TBS.tablespace_name;

Shrink datafiles

set linesize 1000 pagesize 0 feedback off trimspool on
with
 hwm as (
  -- get highest block id from each datafiles ( from x$ktfbue as we don't need all joins from dba_extents )
  select /*+ materialize */ ktfbuesegtsn ts#,ktfbuefno relative_fno,max(ktfbuebno+ktfbueblks-1) hwm_blocks
  from sys.x$ktfbue group by ktfbuefno,ktfbuesegtsn
 ),
 hwmts as (
  -- join ts# with tablespace_name
  select name tablespace_name,relative_fno,hwm_blocks
  from hwm join v$tablespace using(ts#)
 ),
 hwmdf as (
  -- join with datafiles, put 5M minimum for datafiles with no extents
  select file_name,nvl(hwm_blocks*(bytes/blocks),5*1024*1024)
hwm_bytes,bytes,autoextensible,maxbytes
  from hwmts right join dba_data_files using(tablespace_name,relative_fno)
 )
select
 case when autoextensible='YES' and maxbytes>=bytes
 then -- we generate resize statements only if autoextensible can grow back to current size
  '/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999)
   ||'M from '||to_char(ceil(bytes/1024/1024),999999)||'M */ '
   ||'alter database datafile '''||file_name||''' resize '||ceil(hwm_bytes/1024/1024)||'M;'
 else -- generate only a comment when autoextensible is off
  '/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999)
   ||'M from '||to_char(ceil(bytes/1024/1024),999999)
   ||'M after setting autoextensible maxsize higher than current size for file '
   || file_name||' */'
 end SQL
from hwmdf
where
 bytes-hwm_bytes>1024*1024 -- resize only if at least 1MB can be reclaimed
order by bytes-hwm_bytes desc
/

Schedula pulizia recycle bin

Cancella dal recyclebin tutti gli oggetti cancellati da almeno 3 giorni

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'purge_recyclebin_3days',
    job_type        => 'PLSQL_BLOCK',
    job_action      => '
begin
  for c1 in (select owner,type,OBJECT_NAME from dba_recyclebin where to_date(DROPTIME,''YYYY-MM-DD:HH24:MI:SS'') < sysdate-3 and CAN_PURGE=''YES'')
   loop
     begin
       execute immediate ''purge '' || c1.type || '' '' || c1.owner || ''."'' || c1.object_name || ''"'';
       exception
        when others then
          null;
     end;
   end loop;
end;
',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=daily; byhour=0; byminute=0; bysecond=0;',
    enabled         => TRUE);
END;
/

Ricerca files orfani ASM

http://anjo.pt/wp/keyword-oracle/2013/02/26/find-orphan-asm-files/

DEFINE ASMGROUP="DGDATA"
 
set linesize 200
set pagesize 50000
col reference_index noprint
col type format a15
col files format a80

WITH v_asmgroup AS (SELECT group_number FROM v$asm_diskgroup WHERE name='&ASMGROUP'),
     v_parentindex AS (SELECT parent_index 
                    FROM v$asm_alias 
              WHERE group_number = (SELECT group_number FROM v_asmgroup) 
                AND alias_index=0),
  v_asmfiles AS (SELECT file_number, type 
              FROM v$asm_file 
           WHERE group_number = (SELECT group_number FROM v_asmgroup)),
 v_dbname AS (SELECT '/'||upper(db_unique_name)||'/' dbname from v$database)
SELECT 'rm '|| files files FROM -- this line show the delete command
(
  SELECT '+&ASMGROUP'||files files, type 
  FROM (SELECT upper(sys_connect_by_path(aa.name,'/')) files, aa.reference_index, b.type
        FROM (SELECT file_number,alias_directory,name, reference_index, parent_index 
        FROM v$asm_alias) aa,
             (SELECT parent_index FROM v_parentindex) a,
             (SELECT file_number, type FROM v_asmfiles) b
  WHERE aa.file_number=b.file_number(+)
    AND aa.alias_directory='N'
   -- missing PARAMETERFILE, DATAGUARDCONFIG
   AND b.type in ('DATAFILE','ONLINELOG','CONTROLFILE','TEMPFILE')
  START WITH aa.PARENT_INDEX=a.parent_index
  CONNECT BY PRIOR aa.reference_index=aa.parent_index)
  WHERE substr(files,instr(files,'/',1,1),instr(files,'/',1,2)-instr(files,'/',1,1)+1) = (select dbname FROM v_dbname)
MINUS (
  SELECT upper(name) files, 'DATAFILE' type FROM v$datafile
    UNION ALL 
  SELECT upper(name) files, 'TEMPFILE' type FROM v$tempfile
    UNION ALL
 SELECT upper(name) files, 'CONTROLFILE' type FROM v$controlfile WHERE name like '+&ASMGROUP%'
    UNION ALL
 SELECT upper(member) files, 'ONLINELOG' type FROM v$logfile WHERE member like '+&ASMGROUP%'
)
);

Reverse Grants

Elenco delle grants concesse all'utente. Utile quando l'utente va ricreato, ad esempio in caso di import.

set long 2000000 longchunksize 20000 pagesize 0 linesize 1000 feedback off verify off trimspool on
column ddl format a1000
 
begin
     dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SQLTERMINATOR', true);
     dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'PRETTY', true);
end;
/
 
variable v_username VARCHAR2(30);
 
exec:v_username := upper('UTENTE01');
 
spool grants_UTENTE01.sql

select dbms_metadata.get_granted_ddl('ROLE_GRANT', rp.grantee) AS ddl
  from   dba_role_privs rp
  where  rp.grantee = :v_username
  and    rownum = 1
 union all
  select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', sp.grantee) AS ddl
  from   dba_sys_privs sp
  where  sp.grantee = :v_username
  and    rownum = 1
 union all
  select dbms_metadata.get_granted_ddl('OBJECT_GRANT', tp.grantee) AS ddl
  from   dba_tab_privs tp
  where  tp.grantee = :v_username
  and    rownum = 1
 /

spool off;

Locked Objects

alter session set nls_date_format='DD/MM/YYYY HH24:MI:SS';

col object_name for a30
col machine for a30

SELECT O.OBJECT_NAME, S.SID, S.SERIAL#, s.inst_id, P.SPID, S.PROGRAM,S.USERNAME,S.MACHINE, S.PORT, 
S.LOGON_TIME, SQ.SQL_FULLTEXT 
FROM GV$LOCKED_OBJECT L, DBA_OBJECTS O, GV$SESSION S, GV$PROCESS P, GV$SQL SQ 
WHERE L.OBJECT_ID = O.OBJECT_ID 
AND (L.SESSION_ID = S.SID  and l.inst_id=s.inst_id)
AND (S.PADDR = P.ADDR  and s.inst_id=p.inst_id)
AND (S.SQL_ADDRESS = SQ.ADDRESS and s.inst_id=sq.inst_id)
;


SELECT O.OBJECT_NAME, S.SID, S.SERIAL#, P.SPID, S.PROGRAM,S.USERNAME,S.MACHINE, S.PORT, 
S.LOGON_TIME, SQ.SQL_FULLTEXT 
FROM V$LOCKED_OBJECT L, DBA_OBJECTS O, V$SESSION S, V$PROCESS P, V$SQL SQ 
WHERE L.OBJECT_ID = O.OBJECT_ID 
AND (L.SESSION_ID = S.SID)
AND (S.PADDR = P.ADDR)
AND (S.SQL_ADDRESS = SQ.ADDRESS)
;