Se avete intenzione di modificare un TB su un ambiente di produzione prima ancora di pensarlo prendetevi subito e nell’ordine seguente:
1> una vacanza di almeno 7 giorni (occorre sempre e cmq!)
2> una buona dose di tranquillanti (ogni buon dba oracle/sysadmin unix conserva sempre qualcosa di chimico nel cassetto!)
3> un backup full del DB (non vi ho ancora parlato di export data pump e di RMAN vero? Beh lo faro’..intanto sappiate che Google è un vostro amico![cit.])
Bene ho scritto “Tablespace problematici” e non Tablespace.. già perchè spesso il comando da manuale per il resize:
ALTER DATABASE DATAFILE ‘/home/oracle/oradata/tbmaledetto.dbf’ size 10G
indovinate? Non funziona…
i motivi sono tanti e a volte basta semplicemente un PURGE RECYCLEBIN per risolvere
altre volte invece, quando siete tanto ma taanto ma proprio taaaanto sfigatti il vostro db proviene da svariati
porting e durante il suo percorso, di release in release, il tablespace in questione ha sempre più frammentato i suoi dati all’interno fino a rendere praticamente impossibile perdere tempo tra i blocks liberi e fantomatici recuperi “standard”…
Quindi la soluzione è:
1> Create un TS NUOVO con le dimensioni/datafiles utili a contenere il vecchio..dategli un nome PROVVISORIO
(io ho spostato USERS, e mi sono bastati due DATAFILE da 20Gb ciascuno)
create tablespace USERS2 datafile ‘/home/oracle/oradata/user1.dbf’ size 20G autoextend on,’/home/oracle/oradata/user2.dbf’ size 20G autoextend on;
2> muovete tutti gli oggetti del TS “vecchio” spoolando ed eseguendo questo script:
(io l’ho usato per spostare tutti gli oggetti di USERS dentro USERS2.. modificatelo voi stessi secondo quello
che vi serve oppure se siete fighi sostituite USERS e USERS2 con &VARIABILE1 e &VARIABILE2 
set pagesize 2000
set linesize 200
set echo off
set heading off
select ‘Transporting tablespace ‘ || ‘USERS’ || ‘ to tablespace ‘ || ‘USERS2′ from dual;
select ‘Size: ‘ || to_char((sum(ext.bytes) / 1048576), ’9,990.00′) || ‘ MB’
from dba_objects ob
inner join (
select ta.owner, ta.table_name as nom, ta.tablespace_name as tbs, ‘TABLE’ as tipo from dba_tables ta
union
select ind.owner, ind.index_name as nom, ind.tablespace_name as tbs, ‘INDEX’ as tipo from dba_indexes ind
) tb on tb.owner=ob.owner and tb.nom=ob.object_name and tb.tipo=ob.object_type
inner join dba_extents ext on ext.segment_name = ob.object_name
where ob.object_type in (‘INDEX’, ‘TABLE’, ‘LOB’) and (tb.tbs = ‘USERS’);
select ‘alter user ‘ || ob.owner || ‘ quota unlimited on ‘ || ‘USERS2′ || ‘ default tablespace ‘ || ‘USERS2′ || ‘;’
from dba_objects ob
inner join (
select ta.owner, ta.table_name as nom, ta.tablespace_name as tbs, ‘TABLE’ as tipo from dba_tables ta
union
select ind.owner, ind.index_name as nom, ind.tablespace_name as tbs, ‘INDEX’ as tipo from dba_indexes ind
) tb on tb.owner=ob.owner and tb.nom=ob.object_name and tb.tipo=ob.object_type
where ob.object_type in (‘INDEX’, ‘TABLE’, ‘LOB’) and (tb.tbs = ‘USERS’)
group by ob.owner;
select
decode(ob.object_type,
‘TABLE’,
‘alter table “‘ || ob.owner || ‘”.”‘ || ob.object_name || ‘” move tablespace ‘ || ‘USERS2′ || ‘;’,
‘alter index “‘ || ob.owner || ‘”.”‘ || ob.object_name || ‘” rebuild tablespace ‘ || ‘USERS2′ || ‘;’
)
from dba_objects ob
inner join (
select ta.owner, ta.table_name as nom, ta.tablespace_name as tbs, ‘TABLE’ as tipo from dba_tables ta
union
select ind.owner, ind.index_name as nom, ind.tablespace_name as tbs, ‘INDEX’ as tipo from dba_indexes ind
) tb on tb.owner=ob.owner and tb.nom=ob.object_name and tb.tipo=ob.object_type
where ob.object_type in (‘TABLE’) and (tb.tbs = ‘USERS’);
select
‘alter table “‘ || lo.owner || ‘”.”‘ || lo.table_name ||
‘” move lob (“‘ || lo.column_name || ‘”) store as (tablespace ‘ || ‘USERS2′ || ‘);’
from dba_lobs lo
inner join dba_segments se on se.segment_name = lo.segment_name
where se.tablespace_name = ‘USERS’;
select
decode(ob.object_type,
‘TABLE’,
‘alter table “‘ || ob.owner || ‘”.”‘ || ob.object_name || ‘” move tablespace ‘ || ‘USERS2′ || ‘;’,
‘alter index “‘ || ob.owner || ‘”.”‘ || ob.object_name || ‘” rebuild tablespace ‘ || ‘USERS2′ || ‘;’
)
from dba_objects ob
inner join (
select ta.owner, ta.table_name as nom, ta.tablespace_name as tbs, ‘TABLE’ as tipo from dba_tables ta
union
select ind.owner, ind.index_name as nom, ind.tablespace_name as tbs, ‘INDEX’ as tipo from dba_indexes ind
) tb on tb.owner=ob.owner and tb.nom=ob.object_name and tb.tipo=ob.object_type
where ob.object_type in (‘INDEX’) and (tb.tbs = ‘USERS’);
Adesso il vecchio TS è vuoto… cancellatelo e insultatelo quanto volete:
DROP TABLESPACE users INCLUDING CONTENTS AND DATAFILES;
(PS: se il TS è il DEFAULT USER settate come DEFAULT USERS quello nuovo così:
ALTER database default tablespace users2; )
e rinominate il nuovo con:
ALTER TABLESPACE users2 RENAME TO users;
Finalmente avete risparmiato lo spazio che desideravate e in più avete il vostro bel TS
con i dati frammetati e quindi resizzabile quando e quanto credete.
Amen
)
