Suppose that we want to move all the mid-size indexes for a given schema FOO, into their own tablespace. First, let’s list them all:
SELECT ext.owner, index_name, SUM (bytes) / (1024 * 1024) MB
FROM dba_extents ext
JOIN
dba_indexes idx
ON idx.owner = ext.owner AND idx.index_name = ext.segment_name
WHERE ext.owner = 'FOO'
AND segment_type = 'INDEX'
AND index_type = 'NORMAL'
GROUP BY ext.owner, index_name
HAVING SUM (bytes) / (1024 * 1024) > 8
ORDER BY MB DESC
How large does the tablespace need to be?
SELECT 1.25 * SUM (bytes) / (1024 * 1024) MB
FROM dba_extents ext
JOIN
dba_indexes idx
ON idx.owner = ext.owner AND idx.index_name = ext.segment_name
WHERE ext.owner = 'FOO'
AND segment_type = 'INDEX'
AND index_type = 'NORMAL'
ORDER BY MB DESC
To populate the tablespace we could use:
SELECT 'alter index '||ext.owner||'.'||segment_name||' rebuild tablespace '||:new_ts||';'
FROM dba_extents ext
JOIN
dba_indexes idx
ON idx.owner = ext.owner AND idx.index_name = ext.segment_name
WHERE ext.owner = 'FOO'
AND segment_type = 'INDEX'
AND index_type = 'NORMAL'
GROUP BY ext.owner, segment_name, segment_type
HAVING SUM (bytes) / (1024 * 1024) > 8
ORDER BY SUM (bytes) / (1024 * 1024) DESC
Now suppose we want to move all tables (including index organized) for a given schema FOO into their own tablespace. To list them:
SELECT owner, table_name, SUM (bytes) / (1024 * 1024) MB
FROM (SELECT ext.owner, table_name, bytes
FROM dba_extents ext
JOIN
dba_tables tbl
ON tbl.owner = ext.owner AND table_name = ext.segment_name
WHERE ext.owner = 'FOO'
UNION ALL
SELECT ext.owner, table_name, bytes
FROM dba_extents ext
JOIN
dba_indexes idx
ON idx.owner = ext.owner
AND idx.index_name = ext.segment_name
WHERE ext.owner = 'FOO'
AND segment_type = 'INDEX'
AND index_type = 'IOT - TOP')
GROUP BY owner, table_name, bytes
HAVING SUM (bytes) / (1024 * 1024) > 8
ORDER BY MB DESC;
How large does the tablespace need to be?
SELECT 1.25 * SUM (bytes) / (1024 * 1024) MB
FROM (SELECT ext.owner, table_name, bytes
FROM dba_extents ext
JOIN
dba_tables tbl
ON tbl.owner = ext.owner AND table_name = ext.segment_name
WHERE ext.owner = 'FOO'
UNION ALL
SELECT ext.owner, table_name, bytes
FROM dba_extents ext
JOIN
dba_indexes idx
ON idx.owner = ext.owner
AND idx.index_name = ext.segment_name
WHERE ext.owner = 'FOO'
AND segment_type = 'INDEX'
AND index_type = 'IOT - TOP')
To populate the tablespace we could use:
SELECT 'alter table '|| owner|| '.'|| table_name
|| ' move tablespace '|| :new_ts|| ';'
FROM (SELECT owner, table_name
FROM dba_tables tbl
WHERE owner = 'FOO'
UNION ALL
SELECT owner, table_name
FROM dba_indexes idx
WHERE owner = 'FOO' AND index_type = 'IOT - TOP')
SELECT 'alter index ' || owner || '.' || index_name || ' rebuild;'
FROM dba_indexes
WHERE owner = 'FOO' AND status = 'UNUSABLE'
