Query Varie
Jump to navigation
Jump to search
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
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; /