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

No hay comentarios.: