=ASM=
[[Oracle_ASM | Gestione ASM ]]
=Files e Tablespaces=
==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.

<pre>
--

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;

</pre>

===Per 10.2===
<pre>
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;
</pre>

Sort Segments

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



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

==Shrink datafiles==
<pre>
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
/

</pre>

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

==aggiunta datafiles e tempfiles==

aggiunge datafiles dal 43 al 60 (in caso che ci siano già 42 datafiles nel tablespace e non abbiamo db_create_file_dest valorizzato)
<pre>
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;
/
</pre>

=Recyclebin=
==Schedula pulizia recycle bin==
Cancella dal recyclebin tutti gli oggetti cancellati da almeno 3 giorni
<pre>
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;
/

</pre>

=Temporary=

=Segmenti=

[[Categoria:Database]]
[[Categoria:Oracle]]

Oracle Spazio

2026-09-29T18:22:23Z

WikiSysop: /* Controllo Allocazione Spazio */

=ASM=
[[Oracle_ASM | Gestione ASM ]]
=Files e Tablespaces=
==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.

<pre>
--

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;

</pre>

===Per 10.2===
<pre>
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;
</pre>

Sort Segments

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



==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==
<pre>
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
/

</pre>

==aggiunta datafiles e tempfiles==

aggiunge datafiles dal 43 al 60 (in caso che ci siano già 42 datafiles nel tablespace e non abbiamo db_create_file_dest valorizzato)
<pre>
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;
/
</pre>

=Recyclebin=
==Schedula pulizia recycle bin==
Cancella dal recyclebin tutti gli oggetti cancellati da almeno 3 giorni
<pre>
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;
/

</pre>

=Temporary=

=Segmenti=

[[Categoria:Database]]
[[Categoria:Oracle]]

Oracle Spazio

2026-09-29T18:21:11Z

WikiSysop: /* Schedula pulizia recycle bin */

=ASM=
[[Oracle_ASM | Gestione ASM ]]
=Files e Tablespaces=
=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.

<pre>
--

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;

</pre>

===Per 10.2===
<pre>
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;
</pre>

Sort Segments

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



==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==
<pre>
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
/

</pre>

==aggiunta datafiles e tempfiles==

aggiunge datafiles dal 43 al 60 (in caso che ci siano già 42 datafiles nel tablespace e non abbiamo db_create_file_dest valorizzato)
<pre>
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;
/
</pre>

=Recyclebin=
==Schedula pulizia recycle bin==
Cancella dal recyclebin tutti gli oggetti cancellati da almeno 3 giorni
<pre>
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;
/

</pre>

=Temporary=

=Segmenti=

[[Categoria:Database]]
[[Categoria:Oracle]]