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

Issue a RECOVER for a Tablespace/Datafile that does not need recovery

Today's Question : What happens if you issue an RMAN RECOVER for a Tablespace or Datafile that is current and does not need recovery ?


Notice the smart "recover if needed" that is executed ?

RMAN> recover datafile 5;

Starting recover at 14-AUG-12
using channel ORA_DISK_1

starting media recovery
media recovery failed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 08/14/2012 22:57:48
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed
 datafile 5
ORA-00283: recovery session canceled due to errors
ORA-01124: cannot recover data file 5 - file is in use or recovery
ORA-01110: data file 5: '/home/oracle/app/oracle/oradata/orcl/example01.dbf'

RMAN> sql 'alter tablespace example offline';

sql statement: alter tablespace example offline

RMAN> recover datafile 5;

Starting recover at 14-AUG-12
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 14-AUG-12

RMAN> sql 'alter tablespace example online';

sql statement: alter tablespace example online

RMAN> 

I did a "dummy" recovery with the tablespace offline. .
.
.
.