Oracle Spazio

ASM

Gestione ASM

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(case when maxbytes=0 then bytes else maxbytes end)/(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(case when maxbytes=0 then bytes else maxbytes end)/(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;

Per 10.2

with
 tf as (select tablespace_name,count(*) nf,round(sum(bytes)/(1024*1024)) mba,round(sum(case when maxbytes=0 then bytes else maxbytes end)/(1024*1024)) max from dba_temp_files group by tablespace_name),
 tsh as (select tablespace_name,round(sum(bytes_used)/(1024*1024)) used,round(sum(bytes_free)/(1024*1024)) free from v$temp_space_header group by tablespace_name)
select
        t.tablespace_name,
        f.nf nfiles,
        f.mba,
        f.max,
        tsh.used mballoc,
        tsh.free mbfree
   from tf f
 right outer join dba_tablespaces t on (t.tablespace_name=f.tablespace_name)
 right outer join tsh on (tsh.tablespace_name=f.tablespace_name)
 where t.contents='TEMPORARY'
 order by 1,2,3;

Sort Segments

SELECT tf.tablespace_name,
       ROUND(tf.total_space_mb) AS total_mb,
       ROUND(NVL(tu.used_space_mb, 0)) AS used_mb,
       ROUND(tf.total_space_mb - NVL(tu.used_space_mb, 0)) AS free_mb
FROM (SELECT tablespace_name, SUM(bytes)/(1024*1024) AS total_space_mb 
      FROM dba_temp_files GROUP BY tablespace_name) tf
LEFT JOIN (SELECT tablespace_name, SUM(used_blocks)*(SELECT value FROM v$parameter WHERE name='db_block_size')/(1024*1024) AS used_space_mb
           FROM v$sort_segment GROUP BY tablespace_name) tu
ON tf.tablespace_name = tu.tablespace_name;

Autoextend datafiles

Verificare se autoextend:

select TABLESPACE_NAME,FILE_ID,AUTOEXTENSIBLE,MAXBYTES/(1024*1024) MAX_MB,INCREMENT_BY from dba_data_files;

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;
/

Su un solo tablespace

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

Mette in autoextend i files non in autoextend, ma con maxsize uguale alla grandezza reale

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

Autoextend tempfiles

Verificare se autoextend:

select TABLESPACE_NAME,FILE_ID,AUTOEXTENSIBLE,bytes/(1024*1024) MB,MAXBYTES/(1024*1024) MAX_MB,INCREMENT_BY from dba_temp_files;

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;
/

Preservando maxsize:

begin
 for c1 in (select file_id,bytes from dba_temp_files)
 loop
   execute immediate 'alter database tempfile ' || c1.file_id || ' autoextend on next 128M maxsize ' || c1.bytes;
 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
/

aggiunta datafiles e tempfiles

aggiunge datafiles dal 43 al 60 (in caso che ci siano già 42 datafiles nel tablespace)

begin
   for c1 in 43..60
   loop
    execute immediate 'alter tablespace TBSDATA add datafile ''/u01/app/oracle/oradata/ORCL/tbsdata_0' || c1 || '.dbf'' size 8M autoextend on next 128M maxsize unlimited';
   end loop;
end;
/

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;
/