Desfragmentação de Tabela

[NiggazParticipante]

Fala hudsona, obrigado pela resposta.!!

Acho que não vai rolar CTAS pq são tabelas utilizadas num sistema, e tem que ser de outra forma mesmo.

Dê uma olhada no script que eu estou montando(futuramente vou criar uma procedure que pegue todos os usuários do meu banco)…Ainda está meio amador, até pq não sou expert em script…rs


DECLARE
--Variaveis
v_emp VARCHAR(8);
v_owner DBA_TABLES.OWNER%TYPE;
v_table DBA_TABLES.TABLE_NAME%TYPE;
v_blocks DBA_TABLES.BLOCKS%TYPE;
v_eBlocks DBA_TABLES.EMPTY_BLOCKS%TYPE;
v_tBlocks VARCHAR2(50);
v_analyzed DBA_TABLES.LAST_ANALYZED%TYPE;
v_tamanho VARCHAR2(50);
v_indexName DBA_INDEXES.INDEX_NAME%TYPE;
--v_sql VARCHAR(50);
--Cursor DBA_TABLES
CURSOR c_dbaTables IS
SELECT
OWNER
,TABLE_NAME
,BLOCKS
,EMPTY_BLOCKS
,BLOCKS*8192/1024/1024 "TAMANHO"
,TO_CHAR(LAST_ANALYZED,'DD/MM/YYY') "ANALYZED"
FROM DBA_TABLES
WHERE OWNER = 'UNIS253'--MUDAR
AND TABLE_NAME IN
('TPT200',
'TPT210',
'TPT300',
'TPT310',
'TPT701',
'TPT703');
--Cursor DBA_SEGMENTS
CURSOR c_dbaSegments IS
SELECT
OWNER
,SEGMENT_NAME
,SUM(BYTES)/1024/1024 "TAMANHO"
FROM DBA_SEGMENTS
WHERE OWNER = 'UNIS253'
AND SEGMENT_NAME IN
('TPT200',
'TPT210',
'TPT300',
'TPT310',
'TPT701',
'TPT703')
GROUP BY OWNER , SEGMENT_NAME;
--Cursor DBA_INDEXES
CURSOR c_dbaIndexes IS
SELECT
OWNER
,INDEX_NAME
FROM DBA_INDEXES
WHERE OWNER LIKE 'UNIS%'
AND STATUS = 'UNUSABLE';
BEGIN
OPEN c_dbaIndexes;
LOOP
FETCH c_dbaIndexes INTO v_owner, v_indexName;
EXIT WHEN c_dbaIndexes%NOTFOUND;
EXECUTE IMMEDIATE 'ALTER INDEX '||v_owner||'.'||v_indexName||' REBUILD';
END LOOP;
CLOSE c_dbaIndexes;
OPEN c_dbaTables;
LOOP
FETCH c_dbaTables INTO v_owner, v_table, v_blocks, v_eBlocks, v_tBlocks, v_analyzed;
EXIT WHEN c_dbaTables%NOTFOUND;
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>v_owner,TABNAME=>v_table,ESTIMATE_PERCENT=>NULL,METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO',DEGREE =>6);
INSERT INTO Analisa VALUES (1, v_owner, v_table, v_blocks, v_eBlocks, v_tBlocks, sysdate);
END LOOP;
CLOSE c_dbaTables;
OPEN c_dbaSegments;
LOOP
FETCH c_dbaSegments INTO v_owner, v_table, v_tamanho;
EXIT WHEN c_dbaSegments%NOTFOUND;
INSERT INTO Analisa(ETAPA, OWNER,TABELA,TAMANHO) VALUES (2,v_owner, v_table, v_tamanho);
EXECUTE IMMEDIATE 'ALTER TABLE '||v_owner||'.'||v_table||' MOVE';
END LOOP;
CLOSE c_dbaSegments;
END;
/


Acrescentaria alguma coisa ou tiraria outra??

Abraços