Populating an Oracle tablespace

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'