In Oracle, data files contain a single tablespace that may contain one or more objects. Depending upon how the DBA configured the database, a single Oracle data file may have extremely high I/O, and moving the data file from one disk to another will only move the hot spot around. In these cases, we need to spread the hot data file across numerous physical disks by data striping.
To do this, you segregate an individual table or index into a separate tablespace.
What is the purpose of File Striping in Oracle
File striping in Oracle is a method of distributing data across multiple disk drives to improve performance and reliability.
The purpose of file striping is to:
Enhance disk I/O performance by spreading the data across multiple disk drives, reducing I/O bottlenecks and allowing multiple I/O requests to be handled concurrently.
Improve reliability by spreading the data across multiple disks, reducing the risk of data loss due to disk failures.
Provide better scalability by allowing administrators to add additional disk drives as needed to accommodate growth in data volume.
File striping is often used in combination with other technologies such as mirroring, data replication, or backup and recovery solutions to provide a complete data protection strategy.
Stripe data across Several Disks
This is how you would stripe a large customer table across several disks.
Create the tablespace to consist of multiple files, each on a separate disk.
Now that the tablespace exists, copy the table into the new tablespace, using the MINEXTENTS parameter to place a separate table extent into each physical data file.
Create table new_customer
Select * from customer
Storage ( INITIAL 100m NEXT 100m miNEXTents 3 );
Now the customer table is spread evenly across three disks, with one extent on each disk.
After running CTAS to copy the table, you must drop the indexes from the old table and re-create them on the new table.
The next lesson is about OS methods for striping data files.