Search My Oracle Blog

Custom Search

17 August, 2012

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.
.
.
.

No comments:

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016