Oracle Spostamento Oggetti - Cronologia

WikiSysop: /* rimettere in logging */

2026-10-05T15:38:19Z

rimettere in logging

← Versione meno recente		Versione delle 15:38, 5 ott 2026
Riga 78:		Riga 78:

	versione con inner join		versione con inner join
	select '/* ' \|\| i.table_name \|\| ' ' \|\| t.logging \|\| ' */ alter index ' \|\| i.owner \|\| '.' \|\| i.index_name \|\| ' logging;'		select '/* ' \|\| i.table_owner \|\| '.' \|\| i.table_name \|\| ' ' \|\| t.logging \|\| ' */ alter index ' \|\| i.owner \|\| '.' \|\| i.index_name \|\| ' logging;'
	from dba_indexes i		from dba_indexes i
	inner join dba_users u on (u.username=i.owner and u.oracle_maintained='N')		inner join dba_users u on (u.username=i.owner and u.oracle_maintained='N')

WikiSysop: /* rimettere in logging */

2026-10-05T15:37:04Z

rimettere in logging

← Versione meno recente		Versione delle 15:37, 5 ott 2026
Riga 76:		Riga 76:
	and not exists (select 1 from dba_users u where u.username=i.owner and u.oracle_maintained='Y')		and not exists (select 1 from dba_users u where u.username=i.owner and u.oracle_maintained='Y')
	and not exists (select 1 from dba_tables t where t.table_name=i.table_name and t.owner=i.table_owner and t.logging='NO');		and not exists (select 1 from dba_tables t where t.table_name=i.table_name and t.owner=i.table_owner and t.logging='NO');

			versione con inner join
			select '/* ' \|\| i.table_name \|\| ' ' \|\| t.logging \|\| ' */ alter index ' \|\| i.owner \|\| '.' \|\| i.index_name \|\| ' logging;'
			from dba_indexes i
			inner join dba_users u on (u.username=i.owner and u.oracle_maintained='N')
			inner join dba_tables t on (t.table_name=i.table_name and t.owner=i.table_owner and t.logging='YES')
			where i.logging='NO' and i.owner !='PERFSTAT';

	==Spostamento LOB==		==Spostamento LOB==

WikiSysop: /* rimettere in logging */

2026-10-05T15:31:08Z

rimettere in logging

← Versione meno recente		Versione delle 15:31, 5 ott 2026
Riga 72:		Riga 72:

	non va messo in logging un indice su una tabella in nologging:		non va messo in logging un indice su una tabella in nologging:
	select 'alter index ' \|\| i.owner \|\| '.' \|\| i.index_name \|\| ' logging;' from dba_indexes i		select '/* ' \|\| i.table_name \|\| ' */ alter index ' \|\| i.owner \|\| '.' \|\| i.index_name \|\| ' logging;' from dba_indexes i
	where i.logging='NO' and i.owner !='PERFSTAT'		where i.logging='NO' and i.owner !='PERFSTAT'
	and not exists (select 1 from dba_users u where u.username=i.owner and u.oracle_maintained='Y')		and not exists (select 1 from dba_users u where u.username=i.owner and u.oracle_maintained='Y')

WikiSysop: /* segment Advisor */

2026-10-01T07:01:58Z

segment Advisor

← Versione meno recente		Versione delle 07:01, 1 ott 2026
Riga 201:		Riga 201:
	-- table(dbms_space.asa_recommendations('FALSE', 'FALSE', 'FALSE'));		-- table(dbms_space.asa_recommendations('FALSE', 'FALSE', 'FALSE'));
	spool segadv.txt		spool segadv.txt
	select '/' \|\| recommendations \|\| ' / ' \|\| c1 from		select '/' \|\| recommendations \|\| ' / ' \|\| c1 \|\| ';' from
	table(dbms_space.asa_recommendations('FALSE', 'FALSE', 'FALSE')) where c1 is not null order by segment_type,reclaimable_space;		table(dbms_space.asa_recommendations('FALSE', 'FALSE', 'FALSE')) where c1 is not null order by segment_type,reclaimable_space;
	spool off		spool off

WikiSysop: /* Spostamento Indici */

2026-09-30T22:45:23Z

Spostamento Indici

← Versione meno recente		Versione delle 22:45, 30 set 2026
Riga 41:		Riga 41:

	Ordinati per grandezza		Ordinati per grandezza
	select '/* ' \|\| s.bytes/(10241024) i.table_name \|\| '/ alter index ' \|\| i.owner \|\| '.' \|\| i.index_name \|\| ' rebuild tablespace &tbs_destinazione online nologging compute statistics;'		select '/* size ' \|\| s.bytes/(10241024) \|\| ' table ' \|\| i.table_name \|\| ' / alter index ' \|\| i.owner \|\| '.' \|\| i.index_name \|\| ' rebuild tablespace &tbs_destinazione online nologging compute statistics;'
	from dba_indexes i inner join dba_segments s on s.segment_name=i.index_name and s.owner=i.owner		from dba_indexes i inner join dba_segments s on s.segment_name=i.index_name and s.owner=i.owner
	where i.index_type='NORMAL' and s.tablespace_name='&tbs_sorgente' order by s.bytes desc;		where i.index_type='NORMAL' and s.tablespace_name='&tbs_sorgente' order by s.bytes desc;

WikiSysop: /* Spostamento Indici */

2026-09-30T22:43:39Z

Spostamento Indici

← Versione meno recente		Versione delle 22:43, 30 set 2026
Riga 40:		Riga 40:
	select 'alter index ' \|\| owner \|\| '.' \|\| index_name \|\| ' rebuild tablespace &2 online nologging compute statistics;' from dba_indexes where index_type='NORMAL' and tablespace_name='&1' or status='UNUSABLE';		select 'alter index ' \|\| owner \|\| '.' \|\| index_name \|\| ' rebuild tablespace &2 online nologging compute statistics;' from dba_indexes where index_type='NORMAL' and tablespace_name='&1' or status='UNUSABLE';

			Ordinati per grandezza
	select '/* ' \|\| s.bytes \|\| '*/ alter index ' \|\| i.owner \|\| '.' \|\| i.index_name \|\| ' rebuild tablespace &tbs_destinazione online nologging compute statistics;'		select '/* ' \|\| s.bytes/(10241024) i.table_name \|\| '/ alter index ' \|\| i.owner \|\| '.' \|\| i.index_name \|\| ' rebuild tablespace &tbs_destinazione online nologging compute statistics;'
	from dba_indexes i inner join dba_segments s on s.segment_name=i.index_name and s.owner=i.owner		from dba_indexes i inner join dba_segments s on s.segment_name=i.index_name and s.owner=i.owner
	where i.index_type='NORMAL' and s.tablespace_name='&tbs_sorgente' order by s.bytes desc;		where i.index_type='NORMAL' and s.tablespace_name='&tbs_sorgente' order by s.bytes desc;

WikiSysop: /* Spostamento Indici */

2026-09-30T22:42:13Z

Spostamento Indici

@@ Riga 44: / Riga 44: @@
   where i.index_type='NORMAL' and s.tablespace_name='&tbs_sorgente' order by s.bytes desc;
 rebuild indici UNUSABLE:
@@ Riga 73: / Riga 64: @@
  end;
+ /
 ==Spostamento LOB==

WikiSysop: /* Spostamento Indici */

2026-09-30T22:39:34Z

Spostamento Indici

← Versione meno recente		Versione delle 22:39, 30 set 2026
Riga 51:		Riga 51:
	select 'alter index ' \|\| i.owner \|\| '.' \|\| i.index_name \|\| ' logging;' from dba_indexes i		select 'alter index ' \|\| i.owner \|\| '.' \|\| i.index_name \|\| ' logging;' from dba_indexes i
	where i.logging='NO' and i.owner !='PERFSTAT'		where i.logging='NO' and i.owner !='PERFSTAT'
	and not exists (select 1 from dba_users u where u.~~osername~~=i.owner and u.oracle_maintained='Y')		and not exists (select 1 from dba_users u where u.username=i.owner and u.oracle_maintained='Y')
	and not exists (select 1 from dba_tables t where t.table_name=i.table_name and t.owner=i.table_owner and t.logging='NO');		and not exists (select 1 from dba_tables t where t.table_name=i.table_name and t.owner=i.table_owner and t.logging='NO');

WikiSysop: /* Spostamento Indici */

2026-09-30T22:38:50Z

Spostamento Indici

← Versione meno recente		Versione delle 22:38, 30 set 2026
Riga 49:		Riga 49:

	non va messo in logging un indice su una tabella in nologging:		non va messo in logging un indice su una tabella in nologging:
	select 'alter index ' \|\| owner \|\| '.' \|\| index_name \|\| ' logging;' from dba_indexes ~~where LOGGING='NO' and owner !='PERFSTAT' and owner in (select username from dba_users where oracle_maintained='N') and not exists (select 1 from dba_tables where t.table_name=~~i~~.table_name and t.owner=i.table_owner and t.logging='NO');~~		select 'alter index ' \|\| i.owner \|\| '.' \|\| i.index_name \|\| ' logging;' from dba_indexes i
			where i.logging='NO' and i.owner !='PERFSTAT'
			and not exists (select 1 from dba_users u where u.osername=i.owner and u.oracle_maintained='Y')
			and not exists (select 1 from dba_tables t where t.table_name=i.table_name and t.owner=i.table_owner and t.logging='NO');

	rebuild indici UNUSABLE:		rebuild indici UNUSABLE:

WikiSysop: /* Spostamento Indici */

2026-09-30T22:34:48Z

Spostamento Indici

← Versione meno recente		Versione delle 22:34, 30 set 2026
Riga 49:		Riga 49:

	non va messo in logging un indice su una tabella in nologging:		non va messo in logging un indice su una tabella in nologging:
			select 'alter index ' \|\| owner \|\| '.' \|\| index_name \|\| ' logging;' from dba_indexes where LOGGING='NO' and owner !='PERFSTAT' and owner in (select username from dba_users where oracle_maintained='N') and not exists (select 1 from dba_tables where t.table_name=i.table_name and t.owner=i.table_owner and t.logging='NO');


	rebuild indici UNUSABLE:		rebuild indici UNUSABLE: