Oracle Spostamento Oggetti - Cronologia

WikiSysop: /* Comandi Spostamento LOB */

2021-11-03T23:27:55Z

Comandi Spostamento LOB

← Versione meno recente		Versione delle 23:27, 3 nov 2021
Riga 90:		Riga 90:
	select 'alter table ' \|\| l.owner \|\| '.' \|\| l.table_name \|\| ' move lob (' \|\|		select 'alter table ' \|\| l.owner \|\| '.' \|\| l.table_name \|\| ' move lob (' \|\|
	listagg(l.column_name,') store as securefile (nocache nologging) lob (')		listagg(l.column_name,') store as securefile (nocache nologging) lob (')
	within group (order by l.column_name)		within group (order by l.column_name) \|\|
	over (partition by l.table_name) \|\|
	') store as securefile (nocache nologging) nologging;'		') store as securefile (nocache nologging) nologging;'
	from dba_lobs l where l.securefile='NO'		from dba_lobs l where l.securefile='NO'
	and l.owner not in ('SYS','SYSTEM','XDB','OUTLN','WMSYS','AUDSYS') ;		and l.owner not in ('SYS','SYSTEM','XDB','OUTLN','WMSYS','AUDSYS')
			group by l.owner,l.table_name;

	compressione e deduplica richiedono avanced compression.		compressione e deduplica richiedono avanced compression.

WikiSysop: /* Comandi Spostamento LOB */

2021-11-03T22:58:43Z

Comandi Spostamento LOB

← Versione meno recente		Versione delle 22:58, 3 nov 2021
Riga 89:		Riga 89:

	select 'alter table ' \|\| l.owner \|\| '.' \|\| l.table_name \|\| ' move lob (' \|\|		select 'alter table ' \|\| l.owner \|\| '.' \|\| l.table_name \|\| ' move lob (' \|\|
	listagg(l.column_name,') store as securefile (nocache nologging) lob ')		listagg(l.column_name,') store as securefile (nocache nologging) lob (')
	within group (order by l.column_name)		within group (order by l.column_name)
	over (partition by l.table_name) \|\|		over (partition by l.table_name) \|\|
	') store as securefile (nocache nologging) nologging;'		') store as securefile (nocache nologging) nologging;'
	from dba_lobs l where l.securefile='NO';		from dba_lobs l where l.securefile='NO'
			and l.owner not in ('SYS','SYSTEM','XDB','OUTLN','WMSYS','AUDSYS') ;

	compressione e deduplica richiedono avanced compression.		compressione e deduplica richiedono avanced compression.

WikiSysop: /* Comandi Spostamento LOB */

2021-11-03T22:53:37Z

Comandi Spostamento LOB

← Versione meno recente		Versione delle 22:53, 3 nov 2021
Riga 92:		Riga 92:
	within group (order by l.column_name)		within group (order by l.column_name)
	over (partition by l.table_name) \|\|		over (partition by l.table_name) \|\|
	') store as securefile (nocache nologging);'		') store as securefile (nocache nologging) nologging;'
	from dba_lobs l where l.securefile='NO';		from dba_lobs l where l.securefile='NO';



	compressione e deduplica richiedono avanced compression.		compressione e deduplica richiedono avanced compression.

WikiSysop: /* Comandi Spostamento LOB */

2021-11-03T22:52:37Z

Comandi Spostamento LOB

← Versione meno recente		Versione delle 22:52, 3 nov 2021
Riga 84:		Riga 84:
	select 'alter table ' \|\| owner \|\| '.' \|\| TABLE_NAME \|\| ' move lob (' \|\| COLUMN_NAME \|\| ') store as securefile (tablespace &2);'		select 'alter table ' \|\| owner \|\| '.' \|\| TABLE_NAME \|\| ' move lob (' \|\| COLUMN_NAME \|\| ') store as securefile (tablespace &2);'
	from dba_lobs where TABLESPACE_NAME='&1';		from dba_lobs where TABLESPACE_NAME='&1';


			utilizzo funzione listagg per spostare tutti i lob di una tabella con un solo comando

			select 'alter table ' \|\| l.owner \|\| '.' \|\| l.table_name \|\| ' move lob (' \|\|
			listagg(l.column_name,') store as securefile (nocache nologging) lob ')
			within group (order by l.column_name)
			over (partition by l.table_name) \|\|
			') store as securefile (nocache nologging);'
			from dba_lobs l where l.securefile='NO';

WikiSysop: /* Comandi Spostamento LOB */

2021-05-04T22:56:21Z

Comandi Spostamento LOB

← Versione meno recente		Versione delle 22:56, 4 mag 2021
Riga 80:		Riga 80:
	ALTER TABLE table_name MOVE LOB (lob_column) STORE AS (TABLESPACE tablespace_name);		ALTER TABLE table_name MOVE LOB (lob_column) STORE AS (TABLESPACE tablespace_name);

			ALTER TABLE table_name MOVE LOB (lob_column) STORE AS securefile (TABLESPACE tablespace_name);
⚫	select 'alter table ' \|\| owner \|\| '.' \|\| TABLE_NAME \|\| ' move lob (' \|\| COLUMN_NAME \|\| ') store as (tablespace &2);'

		⚫	select 'alter table ' \|\| owner \|\| '.' \|\| TABLE_NAME \|\| ' move lob (' \|\| COLUMN_NAME \|\| ') store as securefile (tablespace &2);'
	from dba_lobs where TABLESPACE_NAME='&1';		from dba_lobs where TABLESPACE_NAME='&1';


	compressione e deduplica richiedono avanced compression. ~~Online, oltre a richiedere EE, utilizza un grosso quantitativo di spazio~~		compressione e deduplica richiedono avanced compression.
			La clausola online, oltre a richiedere EE, utilizza un grosso quantitativo di spazio
	per segmenti temporanei sul tablespace sorgente:		per segmenti temporanei sul tablespace sorgente:

Riga 108:		Riga 111:
	from dba_lobs l inner join dba_segments s on (l.owner=s.owner and l.segment_name=s.segment_name)		from dba_lobs l inner join dba_segments s on (l.owner=s.owner and l.segment_name=s.segment_name)
	where s.owner='LOBUSER';		where s.owner='LOBUSER';


	===plsql===		===plsql===

WikiSysop: /* Spazio occupato dai LOB */

2021-05-04T22:48:11Z

Spazio occupato dai LOB

← Versione meno recente		Versione delle 22:48, 4 mag 2021
Riga 56:		Riga 56:
	===Spazio occupato dai LOB===		===Spazio occupato dai LOB===
	--		--

	set pagesize 200 linesize 200 trim on trims on		set pagesize 200 linesize 200 trim on trims on
	col owner for a20		col owner for a20
Riga 65:		Riga 64:
	col comp for a6		col comp for a6
	col dedup for a4		col dedup for a4

	select l.owner,l.segment_name,s.tablespace_name,l.column_name,l.table_name,		select l.owner,l.segment_name,s.tablespace_name,l.column_name,l.table_name,
	s.bytes/(1024*1024) mb,l.compression comp,l.deduplication dedup,l.logging,l.securefile		s.bytes/(1024*1024) mb,l.compression comp,l.deduplication dedup,l.logging,l.securefile

WikiSysop: /* Spostamento LOB */

2021-05-04T22:47:31Z

Spostamento LOB

← Versione meno recente		Versione delle 22:47, 4 mag 2021
Riga 53:		Riga 53:

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

			===Spazio occupato dai LOB===
			--

			set pagesize 200 linesize 200 trim on trims on
			col owner for a20
			col segment_name for a25
			col column_name for a30
			col table_name for a30
			col tablespace_name for a15
			col comp for a6
			col dedup for a4

			select l.owner,l.segment_name,s.tablespace_name,l.column_name,l.table_name,
			s.bytes/(1024*1024) mb,l.compression comp,l.deduplication dedup,l.logging,l.securefile
			from dba_lobs l inner join dba_segments s on (l.owner=s.owner and l.segment_name=s.segment_name)
			and s.tablespace_name not in ('SYSTEM','SYSAUX')
			order by 6;

			===Comandi Spostamento LOB===


	con <code>nls_length_semantics</code> a <code>char</code> ci sono brutti effetti collaterali:		con <code>nls_length_semantics</code> a <code>char</code> ci sono brutti effetti collaterali:

WikiSysop: /* Spostamento LOB */

2020-11-15T21:10:28Z

Spostamento LOB

← Versione meno recente		Versione delle 21:10, 15 nov 2020
Riga 65:		Riga 65:


	compressione e deduplica richiedono avanced compression. Online, oltre a richiedere EE utilizza un grosso quantitativo di spazio		compressione e deduplica richiedono avanced compression. Online, oltre a richiedere EE, utilizza un grosso quantitativo di spazio
	per segmenti temporanei sul tablespace sorgente:		per segmenti temporanei sul tablespace sorgente:

	select 'alter table ' \|\| l.OWNER \|\| '.' \|\| l.TABLE_NAME \|\| ' move lob(' \|\| l.COLUMN_NAME \|\| ') store as (compress high deduplicate nocache nologging tablespace TBSLOB ) online;'		select 'alter table ' \|\| l.OWNER \|\| '.' \|\| l.TABLE_NAME \|\| ' move lob (' \|\| l.COLUMN_NAME \|\| ') store as securefile (compress high deduplicate nocache nologging tablespace TBSLOB ) online;'
	from dba_lobs l		from dba_lobs l
	where l.owner not in ('SYS','SYSTEM','XDB','OUTLN','WMSYS','AUDSYS') and l.column_name !='USER_PROP' and tablespace_name !='TBSLOB'		where l.owner not in ('SYS','SYSTEM','XDB','OUTLN','WMSYS','AUDSYS') and l.column_name !='USER_PROP' and tablespace_name !='TBSLOB'

WikiSysop: /* Spostamento LOB */

2020-11-15T21:05:34Z

Spostamento LOB

← Versione meno recente		Versione delle 21:05, 15 nov 2020
Riga 65:		Riga 65:


	compressione e deduplica richiedono avanced compression:		compressione e deduplica richiedono avanced compression. Online, oltre a richiedere EE utilizza un grosso quantitativo di spazio
			per segmenti temporanei sul tablespace sorgente:

	select 'alter table ' \|\| l.OWNER \|\| '.' \|\| l.TABLE_NAME \|\| ' move lob(' \|\| l.COLUMN_NAME \|\| ') store as (compress high deduplicate tablespace TBSLOB ) online;'		select 'alter table ' \|\| l.OWNER \|\| '.' \|\| l.TABLE_NAME \|\| ' move lob(' \|\| l.COLUMN_NAME \|\| ') store as (compress high deduplicate nocache nologging tablespace TBSLOB ) online;'
			from dba_lobs l
			where l.owner not in ('SYS','SYSTEM','XDB','OUTLN','WMSYS','AUDSYS') and l.column_name !='USER_PROP' and tablespace_name !='TBSLOB'
			order by l.OWNER ;

			per riportare in logging:

			select 'alter table ' \|\| l.OWNER \|\| '.' \|\| l.TABLE_NAME \|\| ' modify lob(' \|\| l.COLUMN_NAME \|\| ') (cache logging );'
	from dba_lobs l		from dba_lobs l
	where l.owner not in ('SYS','SYSTEM','XDB','OUTLN','WMSYS','AUDSYS') and l.column_name !='USER_PROP' and tablespace_name !='TBSLOB'		where l.owner not in ('SYS','SYSTEM','XDB','OUTLN','WMSYS','AUDSYS') and l.column_name !='USER_PROP' and tablespace_name !='TBSLOB'

WikiSysop: /* Spostamento Indici */

2020-11-15T21:01:28Z

Spostamento Indici

← Versione meno recente		Versione delle 21:01, 15 nov 2020
Riga 40:		Riga 40:

	begin		begin
	for c1 in (select index_name from dba_indexes where owner='PERFSTAT' and tablespace_name='USERS')		for c1 in (select owner, index_name from dba_indexes where owner='PERFSTAT' and tablespace_name='USERS')
	loop		loop
	begin		begin
	execute immediate 'alter index ~~perfstat~~.' \|\| c1.index_name \|\| ' rebuild tablespace "PERFSTAT" nologging';		execute immediate 'alter index "' \|\| c1.owner \|\| '"."' \|\| c1.index_name \|\| '" rebuild tablespace "PERFSTAT" online nologging';
	exception		exception
	when others then		when others then