Oracle Spazio - Cronologia

WikiSysop: /* shrink tempfiles */

2026-10-06T23:22:20Z

shrink tempfiles

← Versione meno recente		Versione delle 23:22, 6 ott 2026
Riga 270:		Riga 270:

	==shrink tempfiles==		==shrink tempfiles==

			select 'alter database tempfile ' \|\| file_id \|\| ' resize 8M;' from dba_temp_files where AUTOEXTENSIBLE='YES' and maxbytes >= bytes;

	begin		begin
Riga 283:		Riga 285:
	end;		end;
	/		/



	===shrink temporary tablespace===		===shrink temporary tablespace===

2026-10-05T13:51:29Z

Autoextend tempfiles

← Versione meno recente		Versione delle 13:51, 5 ott 2026
Riga 248:		Riga 248:
	Verificare se autoextend:		Verificare se autoextend:
	select TABLESPACE_NAME,FILE_ID,AUTOEXTENSIBLE,bytes/(10241024) MB,MAXBYTES/(10241024) MAX_MB,INCREMENT_BY from dba_temp_files;		select TABLESPACE_NAME,FILE_ID,AUTOEXTENSIBLE,bytes/(10241024) MB,MAXBYTES/(10241024) MAX_MB,INCREMENT_BY from dba_temp_files;

			select 'alter database tempfile ' \|\| file_id \|\| ' autoextend on next 128M;' from dba_temp_files where autoextensible='NO' and tablespace_name='TEMPNEW2';

2026-09-30T16:08:31Z

Segmenti

← Versione meno recente		Versione delle 16:08, 30 set 2026
Riga 332:		Riga 332:
	Tablespace map		Tablespace map

			col owner for a20
	select owner,segment_type,sum(bytes)/(1024*1024) mb from dba_segments where tablespace_name='&1' group by owner,segment_type order by 1,2;		select owner,segment_type,sum(bytes)/(1024*1024) mb from dba_segments where tablespace_name='&1' group by owner,segment_type order by 1,2;

			col owner for a20
	select tablespace_name,owner,segment_type,sum(bytes)/(1024*1024) mb from dba_segments group by tablespace_name,owner,segment_type order by 1,2,3;		select tablespace_name,owner,segment_type,sum(bytes)/(1024*1024) mb from dba_segments group by tablespace_name,owner,segment_type order by 1,2,3;

2026-09-30T16:07:50Z

Segmenti

← Versione meno recente		Versione delle 16:07, 30 set 2026
Riga 334:		Riga 334:
	select owner,segment_type,sum(bytes)/(1024*1024) mb from dba_segments where tablespace_name='&1' group by owner,segment_type order by 1,2;		select owner,segment_type,sum(bytes)/(1024*1024) mb from dba_segments where tablespace_name='&1' group by owner,segment_type order by 1,2;

	select tablespace_name owner,segment_type,sum(bytes)/(1024*1024) mb from dba_segments group by tablespace_name,owner,segment_type order by 1,2,3;		select tablespace_name,owner,segment_type,sum(bytes)/(1024*1024) mb from dba_segments group by tablespace_name,owner,segment_type order by 1,2,3;

	=Segment Advisor=		=Segment Advisor=

2026-09-30T16:06:34Z

Segmenti

← Versione meno recente		Versione delle 16:06, 30 set 2026
Riga 336:		Riga 336:
	select tablespace_name owner,segment_type,sum(bytes)/(1024*1024) mb from dba_segments group by tablespace_name,owner,segment_type order by 1,2,3;		select tablespace_name owner,segment_type,sum(bytes)/(1024*1024) mb from dba_segments group by tablespace_name,owner,segment_type order by 1,2,3;

			=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

			Per eseguire lo shrink delle tabelle:

			--
			set verify off echo off
			alter table &1 enable row movement;
			-- non mette lock esclusivo ma non libera lo spazio
			alter table &1 shrink space compact;
			-- libera lo spazio ma mette lock esclusivo per liberare spazio alla fine
			alter table &1 shrink space;
			alter table &1 disable row movement;

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

2026-09-30T14:55:24Z

Segmenti

← Versione meno recente		Versione delle 14:55, 30 set 2026
Riga 330:		Riga 330:

	=Segmenti=		=Segmenti=
			Tablespace map

			select owner,segment_type,sum(bytes)/(1024*1024) mb from dba_segments where tablespace_name='&1' group by owner,segment_type order by 1,2;

			select tablespace_name owner,segment_type,sum(bytes)/(1024*1024) mb from dba_segments group by tablespace_name,owner,segment_type order by 1,2,3;

2026-09-30T10:47:22Z

Autoextend datafiles

← Versione meno recente		Versione delle 10:47, 30 set 2026
Riga 177:		Riga 177:

	begin		begin
	for c1 in (select file_id from dba_data_files)		for c1 in (select file_id from dba_data_files where autoextensible='NO')
	loop		loop
	execute immediate 'alter database datafile ' \|\| c1.file_id \|\| ' autoextend on next 128M maxsize unlimited';		execute immediate 'alter database datafile ' \|\| c1.file_id \|\| ' autoextend on next 128M maxsize unlimited';
Riga 187:		Riga 187:

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

2026-09-29T19:05:41Z

shrink tempfiles

← Versione meno recente		Versione delle 19:05, 29 set 2026
Riga 282:		Riga 282:
	/		/

			===shrink temporary tablespace===

	select 'alter tablespace ' \|\| tablespace_name \|\| ' shrink space;' from dba_tablespaces where contents='TEMPORARY';		select 'alter tablespace ' \|\| tablespace_name \|\| ' shrink space;' from dba_tablespaces where contents='TEMPORARY';

2026-09-29T19:04:12Z

aggiunta datafiles e tempfiles

← Versione meno recente		Versione delle 19:04, 29 set 2026
Riga 287:		Riga 287:
	==aggiunta datafiles e tempfiles==		==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)		aggiunge datafiles dal 5 al 10 (in caso che non abbiamo db_create_file_dest valorizzato)
	<pre>		<pre>
	begin		begin
	for c1 in 43..60		for c1 in 5..10
	loop		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';		execute immediate 'alter tablespace TBSDATA add datafile ''/u01/app/oracle/oradata/ORCL/tbsdata_0' \|\| c1 \|\| '.dbf'' size 8M autoextend on next 128M maxsize unlimited';

2026-09-29T18:40:24Z

shrink tempfiles

← Versione meno recente		Versione delle 18:40, 29 set 2026
Riga 283:		Riga 283:


	alter tablespace ~~temp~~ ~~shtink~~ space;		select 'alter tablespace ' \|\| tablespace_name \|\| ' shrink space;' from dba_tablespaces where contents='TEMPORARY';

	==aggiunta datafiles e tempfiles==		==aggiunta datafiles e tempfiles==