Oracle Database Discussions

Hi,

This is a best practice question. Given a clean slate for creating tablespaces for a 1-2TB database ....

I have a number of tables that will be partitioned monthly and each month will hold approx 1-2Gb of data. We keep 36 months of data for this stuff so I want to create a single data tablespace and a single index tablespace. The data tablespace will be LMT uniform with extent size of 100M. It will be 70Gb in size and I'll create it with 35 * 2Gb datafiles.

We have an IBM Shark SAN and I have 18 distinct ranks (i.e. the ranks do not share any physical disks). I will create my 35 2Gb data datafiles by cycling round ranks 1-9. The index tablespace is only 36Gb so that will consist of 18 2Gb datafiles created by cycling round ranks 10 to 18. I'll create that as an LMT with Uniform 20Mb (the biggest single index partition is 400Mb).

However I have been asked to create seperate tablespaces for each months data.

WHY?

There are about 10 or so of these tables and I will follow that same principle as above for them all and this covers about 1Tb of the database. I will also alternate the indexes and tables with regard to the ranks. So the next table will have its data datafiles on ranks 10 thru 18 and it's index datafiles on 1 thru 9.

Any issues?

The rest of the database has 10 schemas with thousands of objects in each. The objects range in size from 64k to 2Gb (distribution is no even i.e. there are only a few of the 2Gb objects but many hundreds of the 64K ones) and each schema maybe has a total of 20Gb of these objects in data and 10Gb in indexes. My plan for these is to create a data and index tablespace for each schema of LMT AUTOALLOCATE and again spread the tablespace by creating it using 2Gb datafiles spread across the ranks, keeping the index and data datafiles on opposite sides of my logically seperated ranks.

This will minimise the number of tablespaces and remove the need for micromanaging the objects.

I'm just looking for practical advice on the pros and cons of the above approach.

As I mentioned I have been 'requested' to create seperate tablespaces for each monthly partition, but this is a pain for automating the partitioning in an non-asm environment.

Thanks,

John.