Storage Allocation

Here's a quick demo to show how specifying STORAGE parameters can affect the actual allocation.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table A_LARGE_TABLE (col_1 number, col_2 varchar2(5)) storage (initial 400M);

Table created.

SQL> insert into A_LARGE_TABLE values (1,'ABC');

1 row created.

SQL> commit;

Commit complete.

SQL> select bytes/1048576, extents from user_segments where segment_name = 'A_LARGE_TABLE';

BYTES/1048576 EXTENTS
------------- ----------
400 8

SQL> select extent_id, bytes/1024 from user_extents where segment_name = 'A_LARGE_TABLE' order by extent_id;

EXTENT_ID BYTES/1024
---------- ----------
0 65536
1 65536
2 65536
3 65536
4 65536
5 65536
6 8192
7 8192

8 rows selected.

SQL> select tablespace_name, initial_extent , next_extent, pct_increase
2 from user_tables where table_name = 'A_LARGE_TABLE'
3 /

TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT PCT_INCREASE
------------------------------ -------------- ----------- ------------
USERS 419430400 1048576

SQL> select tablespace_name, allocation_type, initial_extent, next_extent
2 from dba_tablespaces where tablespace_name = 'USERS';

TABLESPACE_NAME ALLOCATIO INITIAL_EXTENT NEXT_EXTENT
------------------------------ --------- -------------- -----------
USERS SYSTEM 65536

SQL>
So, I have a USERS tablespace with SYSTEM -- i.e. AUTOALLOCATE -- allocation. I have a table with a defined INITIAL of 400MB which got created with 6 Extents of 64MB and 2 Extents of 8MB. What happens if I TRUNCATE the table ?
SQL> truncate table A_LARGE_TABLE;

Table truncated.

SQL> select bytes/1048576, extents from user_segments where segment_name = 'A_LARGE_TABLE';

BYTES/1048576 EXTENTS
------------- ----------
400 8

SQL> select extent_id, bytes/1024 from user_extents where segment_name = 'A_LARGE_TABLE' order by extent_id;

EXTENT_ID BYTES/1024
---------- ----------
0 65536
1 65536
2 65536
3 65536
4 65536
5 65536
6 8192
7 8192

8 rows selected.

SQL>
*None* of the pre-allocated extents were released ! Oracle doesn't follow the rule "keep the first extent", it follows the rule "keep as many extents as required to satisfy the INITIAL_EXTENT size. What if I rebuild the table with an ALTER TABLE MOVE ?
SQL> alter table A_LARGE_TABLE move;

Table altered.

SQL> select bytes/1048576, extents from user_segments where segment_name = 'A_LARGE_TABLE';

BYTES/1048576 EXTENTS
------------- ----------
400 8

SQL> select extent_id, bytes/1024 from user_extents where segment_name = 'A_LARGE_TABLE' order by extent_id;

EXTENT_ID BYTES/1024
---------- ----------
0 65536
1 65536
2 65536
3 65536
4 65536
5 65536
6 8192
7 8192

8 rows selected.

SQL>
Again, *no difference*. Even the MOVE did not reduce the size of the table. What about a SHRINK ?
SQL> alter table A_LARGE_TABLE shrink space;
alter table A_LARGE_TABLE shrink space
*
ERROR at line 1:
ORA-10636: ROW MOVEMENT is not enabled


SQL> alter table A_LARGE_TABLE enable row movement
2 /

Table altered.

SQL> alter table A_LARGE_TABLE shrink space;

Table altered.

SQL> select bytes/1048576, extents from user_segments where segment_name = 'A_LARGE_TABLE';

BYTES/1048576 EXTENTS
------------- ----------
.3125 1

SQL> select extent_id, bytes/1024 from user_extents where segment_name = 'A_LARGE_TABLE' order by extent_id;

EXTENT_ID BYTES/1024
---------- ----------
0 320

SQL>
Finally, I am able to shrink the table down. Not to 64KB but to 320KB.
.
.
.