Category: database administration

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'

SUSE LVM and Oracle Database

One of the reasons I like deploying Oracle Database on SLES is that SuSE Linux includes LVM by default. Having become accustomed to LVM implementations on HP-UX and AIX I was disappointed to discover it was not a standard feature of all Linux distros. An extensive LVM guide is available at the Linux Documentation Project. Jeff Hunter’s site has some notes and a copy of a good early white paper on SuSE LVM

To list all volume groups, physical volumes and logical volumes

vgs lvs pvs

To initialize a blank disk and make it an LVM physical volume (PV):

pvcreate /dev/sdx

To display the details of a physical volume: 

pvdisplay /dev/sdx

To create a volume group containing a physical volume: 

vgcreate vg01 /dev/sdx

To add a physical volume to an existing VG: 

vgextend vg01 /dev/sdy

To display the details of the volume group: 

vgdisplay

To create an LVM logical volume: 

lvcreate --size 2050m --name lv_sls_idx_128m00 vg01

To move physical extents from one PV to another in a VG. Requires LVs not in use. 

pvmove /dev/hdb /dev/sdf

The SuSE white paper also discusses (pp.20+) how to map an LV to a raw device suitable as an Oracle datafile. On a properly tuned host this should result in better performance. More importantly, in my opinion, it reduces the need to allocate, resize and monitor host filesystems for Oracle data. The simplest approach is to use raw. Given that mappings are not persistent it is best to script them in /etc/init.d/boot.local.

/usr/sbin/raw /dev/raw/raw128 /dev/vg01/lv_sls_idx_128m00
chown oracle:dba /dev/raw/raw128

Once the device is mapped it can be added to a tablespace like this: 

CREATE TABLESPACE sls_idx_128m DATAFILE '/dev/raw/raw128' SIZE 2050M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128M;

More readable device names can be created like this:

rm -f /dev/raw/raw131
mknod /dev/raw/rlv_sls_dat_4m00.dbf c 162 131
raw /dev/raw/rlv_sls_dat_4m00.dbf /dev/vg01/lv_sls_dat_4m00
chown oracle:dba /dev/raw/rlv_*.dbf

Tablespaces can then be created like this:

CREATE TABLESPACE sls_dat_4m DATAFILE '/dev/raw/rlv_sls_dat_4m00.dbf' SIZE 2050M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 4M;

Posts navigation

1 2
Scroll to top