Databases: Oracle 10g and it's storage management quirk

i encountered this problem when i was doing my database assignment. we were supposed to create two fragmented tables (a table with more than one extent) then write an SQL script that would take in a tablespace name and then run thru all the tables in it and defragment it. i hit the problem when i was creating the fragmented table.

alright, i'm not sure if this is actually a quirk in Oracle 10g or not. it might just be that the people at Oracle decided to change the way stuff works (that's pretty irritating too).

so this has to do with Oracle's extent management. i created a table in Oracle using this:

CREATE TABLESPACE TESTTBS
    DATAFILE 'C:\ORACLE\ORADATA\DB\SAMPLE01.DBF'
    SIZE 2M AUTOEXTEND OFF;

then later, i created a table in this tablespace using this:

CREATE TABLE TESTTABLE (F1 CHAR(1024))
    STORAGE (INITIAL 256K NEXT 256K
    MINEXTENTS 2 MAXEXTENTS UNLIMITED)
    PCTFREE 20
    PCTUSED 70
    TABLESPACE TESTTBS;

when i head to the data dictionary DBA_EXTENTS to check on the status of the tablespace, it reports that i have been allocated 8 extents!!!

woah!!!

hold yer horses there... but i thought that i specified only 2 extents in the storage clause? i know i'm supposed to create a fragmented table, but not THAT fragmented right???

ahhh that's where the irritating quirk comes in. a little snooping around on the internet and correspondence with database experts brought me the solution. apparently, last time, Oracle would actually create only 2 extents for you. but all that has changed.

because the extent management clause was not specified in the CREATE TABLESPACE statement, Oracle would add in the default extent management clause for me, which is EXTENT MANAGEMENT LOCAL AUTOALLOCATE.

locally managed tablespace would in effect ignore what i have in my storage clause because objects in system allocated locally managed tablespaces always begin with a minimum extent size of 64K and go on allocating 64K each time it has to allocate an extent.

so this is what Oracle would do:

1. it would calculate how much space it has to allocate the moment the table is created. in this case, it would be 256K * 2 MINEXTENTS = 512K.


2. it would then give me the 512K by assigning blocks of 64K extents, which is 512/64 = 8 extents.

ahhhh so there lies the mystery. so how should i create a table with 2 extents each having 256K? simply create a tablespace with a the clause EXTENT MANAGEMENT DICTIONARY. that ought to solve the problem.

so just in case you were wondering, yes, my database assignments are killing me... grrrr...