5: Oracle Storage in Software

Create Code 39 Extended in Software 5: Oracle Storage

6 Make the tablespace read only, observe the effect, and make it read-write again This is shown in the next illustration

7 Enable Oracle-Managed Files for datafile creation:

alter system set db_create_file_dest='/home/db11g/oradata';

8 Create a tablespace, using the minimum syntax now possible:

create tablespace omftbs;

9 Determine the characteristics of the OMF file:

select file_name,bytes,autoextensible,maxbytes,increment_by from dba_data_files where tablespace_name='OMFTBS';

Note the file is initially 100MB, autoextensible, with no upper limit 10 Adjust the OMF file to have more sensible characteristics Use whatever system-generated filename was returned by Step 9:

alter database datafile '/oradata/ORCL11G/datafile/o1_mf_omftbs_3olpn462_dbf' resize 500m; alter database datafile '/home/db11g/oradata/ORCL11G/datafile/o1_mf_omftbs_3olpn462_dbf' autoextend on next 100m maxsize 2g;

11 Drop the tablespace, and use an operating system command to confirm that the file has indeed gone:

drop tablespace omftbs including contents and datafiles;

Space management occurs at several levels First, space is assigned to a tablespace This is done by sizing the datafiles, as already described Second, space within a tablespace is assigned to segments This is done by allocating extents Third, space within a segment is assigned to rows This is done by maintaining bitmaps that track how much space is free in each block

The extent management method is set per tablespace and applies to all segments in the tablespace There are two techniques for managing extent usage: dictionary management or local management The difference is clear: local management should always be used; dictionary management should never be used Dictionary-managed extent management is still supported, but only just It is a holdover from previous releases Dictionary extent management uses two tables in the data dictionary SYSUET$ has rows describing used extents, and SYSFET$ has rows describing free extents Every time the database needs to allocate an extent to a segment, it must search FET$ to find an appropriate bit of free space, and then carry out DML operations against FET$ and UET$ to allocate it to the segment This mechanism causes negative problems with performance, because all space management operations in the database (many of which could be initiated concurrently) must serialize on the code that constructs the transactions

Local extent management was introduced with release 8i and became default with release 9i It uses bitmaps stored in each datafile Each bit in the bitmap covers a range of blocks, and when space is allocated, the appropriate bits are changed from zero to one This mechanism is far more efficient than the transaction-based mechanism of dictionary management The cost of assigning extents is amortized across bitmaps in every datafile that can be updated concurrently, rather than being concentrated (and serialized) on the two tables When creating a locally managed tablespace, an important option is uniform size If uniform is specified, then every extent ever allocated in the tablespace will be that size This can make the space management highly efficient, because the block ranges covered by each bit can be larger: only one bit per extent Consider this statement:

create tablespace large_tabs datafile 'large_tabs_01dbf' size 10g extent management local uniform size 160m;

Every extent allocated in this tablespace will be 160MB, so there will be about 64 of them The bitmap needs only 64 bits, and 160MB of space can be allocated by updating just one bit This should be very efficient provided that the segments in the tablespace are large If a segment were created that needed space for only a few rows, it would still get an extent of 160MB Small objects need their own tablespace:

create tablespace small_tabs datafile 'small_tabs_01dbf' size 1g extent management local uniform size 160k;

create tablespace any_tabs datafile 'any_tabs_01dbf' size 10g extent management local autoallocate;

When segments are created in this tablespace, Oracle will allocate a 64KB extent As a segment grows and requires more extents, Oracle will allocate extents of 64KB up to 16 extents, from which it will allocate progressively larger extents Thus fastgrowing segments will tend to be given space in ever-increasing chunks TIP Oracle Corporation recommends AUTOALLOCATE, but if you know how big segments are likely to be and can place them accordingly, UNIFORM SIZE may well be the best option It is possible that if a database has been upgraded from previous versions, it will include dictionary-managed tablespaces You can verify this with the query:

select tablespace_name, extent_management from dba_tablespaces;

Any dictionary-managed tablespaces should be converted to local management with the provided PL/SQL program, which can be executed as follows:

execute dbms_space_admintablespace_migrate_to_local('tablespacename');