Can we create a table in specific datafile - Oracle 11g R2?

Question

I am buying a new server than has two HDs: 300 GB with 15000 rpm. 600 GB with 10000 rpm.

I am thinking to allocate the datafiles in both hard disks. and put the less used tables in the data files that reside in the slower HD that is 600 GB with 10000 rpm.

So, Can we create a table in specific data file - ?

I am using Oracle 11g R2 SOE .

Regards,

• user1822: You can only put tables in tablespaces, not in data files.
• Gaius: Have a read of the OFA documentation.
• David Aldridge: Arguably, for very frequently used and cached tables you might put them on the slower disks. In any case I'd be sure to look at v$_segment_stats for physical i/o when deciding which to place where.

Answer 1

You can create two tablespaces, put all the data files for one tablespace on one drive and all the data files for the other tablespace on the other side, and then create the tables in whichever tablespace you would like. It seems unlikely, however, that you would really want to do this. It is very unusual to run a database on a server without any sort of RAID configuration. And it is generally the case that any automatic I/O load balancing that you would implement would outperform your manual load balancing.

• TomTom: -1 for the last comment. While I am all to have the system do the work, it is not THAT Unusual to have DAS (Directly Atached Storage) and separate core high throughput and lower throughput for example for archive tables. I Know of some databases (one here in my work) deploying three levels actually - because some tables see a TON of activity, others do not, especially in the update area. A Raid controller will not magically make a decent decision - especially not taking financials into account.
• Justin Cave: @TomTom - I did say "generally". There are certainly systems that deal with a lot of historical data where having storage tiers makes sense. Though those systems would still have a separate RAID array for each storage tier rather than using a single drive. It is still generally the case that it would be very difficult for most systems to figure out how to manually distribute objects to maximize performance in this sort of setup unless you're willing to spend ridiculous amounts of time monitoring and redistributing load.