SQL Extensions   «Prev 

Database Space Usage in Oracle

1) This illustration shows a database in which two physical files (FILE1 and FILE2) are assigned to one tablespace (USER1).

2) Here, a new table(PERSON) is created with 50K of initial space allocated. It is assigned to the USER1 tablespace. So, 50K is allocated inside USER1, and in turn 50K is allocated inside USER1, and in turn, 50K is allocated in FILE1.

3) Next, another new table (ORDERS) is created with 100K of initial space. The table is also assigned to the USER1 tablespace. 100K is allocated inside the USER1 tablespace for this table. In turn, 100K is allocated in the physical files. First, 50K is allocated from FILE1 and then 50K is allocated from FILE2.

4) Some time has passed, and the initial extent for the PERSON table has been used up. A new row inserted into the PERSON table causes the database to allocate a new extent. The NEXT parameter for the PERSON table directs the database to allocate 15K for the new extent. The USER1 tablespace assigns another 15K to the PERSON table and that 15K is allocated to FILE2.

5) The same event happens to the ORDERS table that happened to the PERSON table: more space is needed. In this case, the NEXT parameter specifies that 250K be allocated in the next extent. USER1 and USER2 in turn allocate 250K for this table. FILE2 is now full, as is the USER1 tablespace.

6) A new file (FILE3) is assigned to the USER1 tablespace, giving it another 50K of space.

7) In this final illustration, the PERSON table has used up the first and second extents and a new row is being inserted. This causes the database to allocate a third extent. The PCTINCR parameter specifies that each subsequent extent should be increased by 50% over the previews extent. Therefore, 20K of space is allocated to the PERSON table. This space is allocated in the USER1 tablespace and in the FILE3 datafile. As you can see, the physical allocation of the data can be very different from the logical location of the data in the tablespace.