Mostrando las entradas con la etiqueta move tablespace. Mostrar todas las entradas
Mostrando las entradas con la etiqueta move tablespace. Mostrar todas las entradas

09 septiembre 2006

Reorganizing tables in Oracle

I show the details to move tables to other tablespaces.
The steps are as follows:

1 .- We need to know who is the object you want to move, so that we make the following script:


SELECT
a.owner "OWNER",
a.segment_name "TABLE",
b.tablespace_name "TABLESPACE",
round(NVL(SUM(a.bytes),0)/(1024*1024),2) "SIZE_MB",
SUM(a.extents) "EXTENTS"
FROM dba_segments a, dba_tables b
WHERE a.segment_name = b.table_name
AND a.segment_type = 'TABLE'
AND a.owner='FULANO'
GROUP BY a.owner, a.segment_name, b.tablespace_name ORDER BY 4 DESC


2 .- Once we have the objects, we know the size and that tablespace is hosted.

3 .- We do the same for those indexes with following script:

SELECT
a.owner "OWNER",
a.segment_name "INDEX",
b.tablespace_name "TABLESPACE",
round(NVL(SUM(a.bytes),0)/(1024*1024),2) "SIZE_MB",
SUM(a.extents) "EXTENTS"
FROM dba_segments a, dba_indexes b
WHERE a.segment_name = b.index_name
AND a.segment_type = 'INDEX'
AND b.owner='FULANO'
GROUP BY a.owner, a.segment_name, b.tablespace_name ORDER BY 4 DESC


4 .- Move the tables to another tablespace

SELECT
'alter table '|| a.owner || '.' || a.segment_name ||' move tablespace NWTBS;'
FROM dba_segments a, dba_tables b
WHERE a.segment_name = b.table_name
AND a.segment_type = 'TABLE'
AND a.owner IN ('FULANO','SUTANO')
group by a.owner,a.segment_name;





5 .- Move indexes

SELECT
'alter index '|| a.owner || '.' || a.segment_name ||' rebuild tablespace INDICE;'
FROM dba_segments a, dba_indexes b
WHERE a.segment_name = b.index_name
AND a.segment_type = 'INDEX'
AND a.owner IN ('FULANO','SUTANO')
group by a.owner,a.segment_name,b.tablespace_name