Re: How to store a table on multiple datafiles (one tablespace of course) ??

From: Chip Dawes <ACD008_at_email.mot.com>
Date: 1995/12/15
Message-ID: <30D1875C.41C6_at_email.mot.com>#1/1


Pierre;
In addition to having your tablespace sit on multiple datafiles, on seperate disks, you should create your table initially relatively small and then use the ALTER TABLE ALOCATE EXTENT DATAFILE '...' to force Oracle to spread the table onto the desired disks.

For example, if you have a 2 gig table (BIB_TABLE) by itself in a 2 gig tablespace that you want to spread over 4 disks:

  1. create your tablespace: CREATE TABLESPACE DISTRIB_TS DATAFILE '/oracle/data/disk1.file' size 256M;
  2. create your table: CREATE TABLE BIG_TABLE(....) TABLESPACE DISTRIB_TS STORAGE (INITIAL 256M NEXT 256M PCTINCREASE 0)
  3. add the additional disk space to the tablespace: ALTER TABLESPACE DISTRIB_TS ADD DATAFILE '/oracle/data/disk2.file' size 256M; ALTER TABLESPACE DISTRIB_TS ADD DATAFILE '/oracle/data/disk3.file' size 256M; ALTER TABLESPACE DISTRIB_TS ADD DATAFILE '/oracle/data/disk4.file' size 256M; ALTER TABLESPACE DISTRIB_TS ADD DATAFILE '/oracle/data/disk1.file' size 256M; ALTER TABLESPACE DISTRIB_TS ADD DATAFILE '/oracle/data/disk2.file' size 256M; ALTER TABLESPACE DISTRIB_TS ADD DATAFILE '/oracle/data/disk3.file' size 256M; ALTER TABLESPACE DISTRIB_TS ADD DATAFILE '/oracle/data/disk4.file' size 256M;
  4. add the additional disk space to the table: ALTER TABLE BIG_TABLE ALLOCATE EXTENT (DATAFILE '/oracle/data/disk2.file' SIZE 256M); ALTER TABLE BIG_TABLE ALLOCATE EXTENT (DATAFILE '/oracle/data/disk3.file' SIZE 256M); ALTER TABLE BIG_TABLE ALLOCATE EXTENT (DATAFILE '/oracle/data/disk4.file' SIZE 256M); ALTER TABLE BIG_TABLE ALLOCATE EXTENT (DATAFILE '/oracle/data/disk1.file' SIZE 256M); ALTER TABLE BIG_TABLE ALLOCATE EXTENT (DATAFILE '/oracle/data/disk2.file' SIZE 256M); ALTER TABLE BIG_TABLE ALLOCATE EXTENT (DATAFILE '/oracle/data/disk3.file' SIZE 256M); ALTER TABLE BIG_TABLE ALLOCATE EXTENT (DATAFILE '/oracle/data/disk4.file' SIZE 256M);
  5. import your table and create your indexes

Merry Christmas from Chicago,
Chip Dawes
Motorola Received on Fri Dec 15 1995 - 00:00:00 CET

Original text of this message