Larger LOBs

Page 37

NOTE
RMAN also has a new drop database command in Oracle Database 10g !

If you use LOBs in your database (NCLOB, BLOB, or CLOB), then you will be happy to know that the limits on LOBs have been increased in Oracle Database 10g. The new maximum limits are calculated at (4GB - 1 byte) * (the database block size). Thus, if the database block size is 8KB, there is essentially a 32GB limitation on LOBs in that database. Note that Bfiles are limited to 4GB in size. Load 'em up folks, it's ready to rumble!

Automatic Undo Retention

Oracle Database 10g no longer requires that you configure the UNDO_RETENTION parameter. The database now makes a best effort to configure the retention of Undo based on database usage if UNDO_RETENTION is set to 0, which is the default. When automatic Undo retention is enabled, Oracle will never set UNDO_RETENTION to a value of 15 minutes or less.

Shrinking and Compacting Segments Online

Oracle Database 10g now allows you to manually shrink the overall size of a table, removing unused space. This feature, combined with the ability to compact the segment and adjust the high-water mark all at the same time, can result in great space savings. This all can occur online, and the recovered space will be released to the database after the operation is complete. You can optionally compact the segment and then shrink it, in two separate alter operations. Most Oracle segment types can be shrunk and/or compacted, including tables (partitioned or not), index-organized tables (IOTs), and indexes. Here is an example of shrinking and compacting a table with the alter table clause:

 Alter table my_table shrink space cascade;

Note the use of the cascade option, which causes all dependent objects (e.g., indexes) to be shrunk as well.

Another thing to know is that before you can shrink a heap-organized segment, you must enable row movement on that segment with the alter table enable row