Versione delle 01:33, 22 nov 2022

Job

Job Immediato

col job_name for a50
col owner for a20
select job_name, owner, enabled from dba_scheduler_jobs;

 begin
   dbms_scheduler.create_job(
     job_name    => 'il_mio_job',
     job_type    => 'PLSQL_BLOCK',
     job_action  => 'begin execute immediate ''create index owner.index_name on owner.table(col01) online nologging''; end;',
     enabled     => TRUE,
     auto_drop   => TRUE,
     comments    => 'crea indice'
   );
 end;
 /

Indici

Elenco Indici

set pagesize 200
set linesize 140
col column_name for a30
col pos for 99
col table_name for a30
col index_name for a35
select table_owner || '.' || table_name table_name, index_owner || '.' || index_name index_name, column_name, column_position pos
 from dba_ind_columns
 where table_name=upper('&&1')
 order by 1,2,4;

set pagesize 200
set linesize 140
set autotrace off
col column_name for a30
col pos for 99
col table_name for a38
col index_name for a43
col tbs for a20
select c.table_owner || '.' || c.table_name table_name, c.index_owner || '.' || c.index_name || ' ' || i.generated index_name,
       substr(i.uniqueness,1,1) U, c.column_name, c.column_position pos, i.tablespace_name tbs
from dba_ind_columns c
 inner join dba_indexes i on 
 (c.table_owner=i.table_owner and c.table_name=i.table_name and
  c.index_owner=i.owner and c.index_name=i.index_name)
where c.table_name=upper('&&1')
order by 1,2,4;

col owner for a40
col tablespace_name for a40
select owner,tablespace_name,bytes/(1024*1024) mb from dba_segments where segment_name=upper('&&1');

Indici con proprietario diverso

select owner,index_name,table_owner,table_name from dba_indexes where table_owner!=owner;

Processi e sessioni

verifica valori limite

col resource_name for a40
select resource_name,current_utilization,max_utilization,limit_value from v$resource_limit;

Elenco processi di background

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

sessioni

Sessioni Attive

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;

Sessioni ordinate per tipo

set linesize 200
set pagesize 200
set autotrace off
col machine for a21
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.status,
 s.username,substr(s.machine,1,20) as 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.username not in ('SYS','DBSNMP')
 order by s.status,s.logon_time;

--

alter session set nls_date_format='DD-MM-YYYY HH24:MI:SS';
select s.sid ||','||s.serial# as sid_ser,p.spid,
s.username,substr(s.machine,1,20) as 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'
and s.username not in ('SYS','DBSNMP')
order by s.logon_time;

long operations

SET LINESize 200
set pagesize 200
set autotrace off
COL sid FORMAT 9999
COL serial# FORMAT 9999999
col sid_ser for a12
COL username FORMAT A30
COL machine FORMAT A50
COL prog% FORMAT 999.00
COL elapsed FORMAT A10
COL remaining FORMAT A10
col message for a80
spool longops2.log
SELECT s.sid || ',' || s.serial# sid_ser,
s.username,
-- s.machine,
ROUND(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
ROUND(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,
ROUND(sl.sofar/sl.totalwork*100, 2) "prog%",
s.sql_id,
replace(sl.message,'Blocks done','') as message
FROM v$session s
inner join v$session_longops sl on ( s.sid = sl.sid AND s.serial# = sl.serial# AND sl.time_remaining > 0 )
order by s.sql_id;
spool off

Rollback

select state,UNDOBLOCKSDONE,UNDOBLOCKSTOTAL, UNDOBLOCKSDONE/UNDOBLOCKSTOTAL*100 from gv$fast_start_transactions;

Piano della query per sql_id

--
set autotrace off
set pagesize 200
set linesize 200
select DBMS_LOB.substr(sql_fulltext, 3000) from v$sql where sql_id = '&1';
-- select plan_table_output from table(dbms_xplan.display_cursor('&1',null,'basic'));
-- select plan_table_output from table(dbms_xplan.display_cursor('&1'));
select * from table(DBMS_XPLAN.DISPLAY_CURSOR(sql_id=>'&1',cursor_child_no=>&2));

Storico query che hanno usato più TEMP

Richiede AWR.

select sql_id,max(TEMP_SPACE_ALLOCATED)/(1024*1024*1024) gig
  from DBA_HIST_ACTIVE_SESS_HISTORY
 where sample_time > sysdate-2
   and TEMP_SPACE_ALLOCATED > (500*1024*1024)
group by sql_id order by 2;

Versione per statspack:

Segment Advisor

--
set pagesize 200
set linesize 200
set autotrace off
-- select tablespace_name, segment_name, segment_type, partition_name,
-- recommendations, c1 from
-- table(dbms_space.asa_recommendations('FALSE', 'FALSE', 'FALSE'));
spool segadv.txt
select '/*' || recommendations || ' */ ' || c1 from
 table(dbms_space.asa_recommendations('FALSE', 'FALSE', 'FALSE')) where c1 is not null order by segment_type,reclaimable_space;
spool off

Logging

-- tabelle
set pagesize 0 linesize 200 trim on trims on feedback off echo off timing off
select 'alter table ' || owner || '.' || table_name || ' logging;' from dba_tables where owner!='SYS' and logging='NO';
select 'alter index ' || owner || '.' || index_name || ' logging;' from dba_indexes where owner!='SYS' and logging='NO';
select 'alter table ' || owner || '.' || table_name || ' modify lob (' || column_name || ') (cache logging);' from dba_lobs where owner!='SYS' and logging='NO';

-- tabelle partizionate
select 'alter table ' || owner || '.' || table_name || ' modify default attributes logging; ' from dba_part_tables where owner!='SYS' and def_logging='NO';
select 'alter index ' || owner || '.' || index_name || ' modify default attributes logging; ' from dba_part_indexes where owner!='SYS' and def_logging='NO';
select 'alter table ' || table_owner || '.' || table_name || ' modify default attributes lob (' || column_name || ') (cache logging);' from dba_part_lobs where table_owner!='SYS' and def_logging='NO';

-- partizioni
select 'alter table ' || table_owner || '.' || table_name || ' modify partition ' || partition_name || ' logging;' from dba_tab_partitions where table_owner!='SYS' and logging='NO';
select 'alter index ' || index_owner || '.' || index_name || ' modify partition ' || partition_name || ' logging;' from dba_ind_partitions where index_owner!='SYS' and logging='NO' and status='USABLE';
 select 'alter table ' || table_owner || '.' || table_name || ' modify partition ' || partition_name || ' lob (' || column_name || ') (cache logging);' from dba_lob_partitions where table_owner!='SYS' and logging='NO';

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.

--

set pagesize 200
set linesize 200

with 
 segs as (select tablespace_name,sum(bytes)/(1024*1024) mbs, count(*) nsegs from dba_segments group by tablespace_name),
 files as (select tablespace_name,count(file_name) nfiles,sum(maxbytes)/(1024*1024) max,sum(bytes)/(1024*1024) mba
             from dba_data_files group by tablespace_name)
select	t.tablespace_name,
	t.block_size,
	f.nfiles,
        s.nsegs,
	round(f.max) max,
	round(s.mbs) mbs,
	round(f.max-s.mbs) sdiff,
	round(((f.max-s.mbs)/f.max)*100) pcts,
	round(f.mba) mba,
	round(f.mba-s.mbs) shrinkable,
	round(((f.mba-s.mbs)/f.mba)*100) pctsrnk
from dba_tablespaces t 
inner join files f on (t.tablespace_name=f.tablespace_name)
left outer join segs s on (t.tablespace_name=s.tablespace_name)
order by 10;

with
 tf as (select tablespace_name,count(*) nf,round(sum(bytes)/(1024*1024)) mba,round(sum(maxbytes)/(1024*1024)) max from dba_temp_files group by tablespace_name)
select	g.group_name,
	t.tablespace_name,
        f.nf nfiles,
	f.mba,
	f.max,
	round(s.allocated_space/(1024*1024)) mballoc,
	round(s.free_space/(1024*1024)) mbfree
   from tf f
 left outer join dba_tablespace_groups g on (g.tablespace_name=f.tablespace_name)
 left outer join dba_temp_free_space s on (s.tablespace_name=f.tablespace_name)
 right outer join dba_tablespaces t on (t.tablespace_name=f.tablespace_name)
 where t.contents='TEMPORARY'
 order by 1,2,3;

Autoextend datafiles

Autoextend datafiles:

begin
 for c1 in (select file_id from dba_data_files)
 loop
   execute immediate 'alter database datafile ' || c1.file_id || ' autoextend on next 128M maxsize unlimited';
 end loop;
end;
/

Autoextend tempfiles:

begin
 for c1 in (select file_id from dba_temp_files)
 loop
   execute immediate 'alter database tempfile ' || c1.file_id || ' autoextend on next 128M maxsize unlimited';
 end loop;
end;
/

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

constraints

Per vedere le colonne. Va raffinata:

select c.constraint_name || ' ' || c.table_name || '(' ||  u.column_name || ')'
from dba_constraints c
inner join dba_cons_columns u on (u.owner=c.owner and u.constraint_name=c.constraint_name)
where c.constraint_type='R';

Per vedere le referenze tra tabelle:

select c.constraint_name || ' ' || c.table_name || ' -> ' ||  u.table_name
from dba_constraints c
inner join dba_constraints u on (u.owner=c.r_owner and u.constraint_name=c.r_constraint_name)
where c.constraint_type='R'
and (c.table_name = '&&1' or u.table_name='&&1');

select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,STATUS,VALIDATED from dba_constraints where table_name='&&1';

Get DDL

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);
  dbms_metadata.set_transform_param(dbms_metadata.session_transform,'PARTITIONING',true);
end;
/

Tabelle

select dbms_metadata.get_ddl(object_type=>'TABLE',schema=>'SCHEMA_NAME',name=>'TABLE_NAME') from dual;

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_ddl('USER','UTENTE01') FROM dual;

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;

clear screen
accept uname prompt 'Enter User Name : '
accept outfile prompt  ' Output filename : '

spool &&outfile..gen

SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON

BEGIN
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
END;
/

SELECT dbms_metadata.get_ddl('USER','&&uname') FROM dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','&&uname') from dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','&&uname') from dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','&&uname') from dual;

spool off

Reverse Grants 2