OLTP DATABASE DEFINITION

Hi all

We have an OLTP application with an oracle database. This database was define like this on NCR UNIX and Oracle 7.3.4 :

• There are 2 big tables (4Go < sizeTable < 8Go) with their dedicated tablespaces (DATA and Index) so 4 tablespaces was being defined.
• There are 5 tables ( 100Mo < sizeTable < 2Go) with their dedicated tablespaces (Data and Index) so 10 tablespaces was being defined.
• There are 60 small tables (sizeTable < 100Mo) with 1 tablespace for Data and 1 tablespace for Index.
• There is 1 tablespace for ROLLBACK (Size 800Mo), 10 rollbacks segments was defined (INITIAL=NEXT=2Mo, MinExtent=10, Optimal=22Mo). These rollbacks are used for OLTP and BATCH.
• There is 1 Temporary Tablespace (Size 1Go).

We will migrate it on Sun Solaris and Oracle 8.1.7, but we want change the database definition like this.

    1 Very Big tablespace Data for the 2 tables (4Go < sizeTable < 8Go).     1 Very Big Tablespace Index for their indexes.

    1 big Tablespace Data for the 5 tables (100Mo < sizeTable < 2Go).     1 big Tablespace Index for their Indexes.

    1 Medium Tablespace Data for tables ( 10Mo < sizeTable < 100Mo)     1 Medium Tablespace Index for their Indexes.

    1 Small Tablespace Data for tables ( 1Mo < SizeTable < 10Mo)     1 Small Tablespace Index for their indexes.

    1 Very Small Tablespace Data for tables ( 256KO < SizeTable < 1Mo)     1 Very Small Tablespace Index for Indexes.

    1 Tablespace ROLLBACK_TX (Size 100Mo) for OLTP transactions, with 10 rollbacks

    (Initial=Next=256Ko,MinExtent=20, OPTIMAL=5Mo)

    1 Tablespace ROLLBACK_BATCH (Size 700Mo) for BATCH Transaction, with 2 Rollback

    (Initial=Next=10M,MinExtent=20, OPTIMAL=200Mo)

    1 Temporary Tablespace (Size 1Go) .

Questions:
Is it a good approach to do this?
Can we use partitions for big tables on OLTP application ?

Thanks in advice. Received on Wed Mar 13 2002 - 10:56:03 CST