SCHEMA MANAGEMENT in Font

Maker QR in Font SCHEMA MANAGEMENT

SQL> SELECT initial_extent,next_extent, extent_management,allocation_type,segment_space_management FROM dba_tablespaces; INITIAL_EXTENT NEXT_EXTENT EXTENT_MAN ALLOCATION_TYPE SEGMENT_MAN -------------------------------------------------------------------1048576 1048576 LOCAL UNIFORM AUTO SQL> If you choose the AUTOALLOCATE method of sizing extents, Oracle will size the extents starting with a 64KB (65536 bytes) minimum extent size. Note that you can specify the autoallocate method for extent sizing either by explicitly specifying it with the AUTOALLOCATE keyword, or by simply leaving out the keyword altogether, since by default, Oracle uses the AUTOALLOCATE method anyway. Listing 5-3 shows an example that creates a tablespace with system-managed (automatically allocated) extents: Listing 5-3. Creating a Tablespace with Automatically Allocated Extents SQL> CREATE TABLESPACE test01 DATAFILE '/pasx02/oradata/pasx/test01_01.dbf' SIZE 100M; Tablespace created. SQL> SQL> SELECT initial_extent,next_extent, extent_management,allocation_type,segment_space_management FROM dba_tablespaces; INITIAL_EXTENT NEXT_EXTENT EXTENT_MAN ALLOCATION_TYPE SEGMENT_MAN ----------------------------------------------------------------------65536 LOCAL SYSTEM AUTO SQL> Note that there is no value for the autoallocated tablespace for NEXT_EXTENT in Listing 5-3. When you choose the AUTOALLOCATE option (here it is chosen by default) rather than UNIFORM, Oracle allocates extent sizes starting with 64KB for the first extent. The next extent size will depend entirely upon the requirements of the segment (table, index, etc.) that you create in this tablespace.

You create tablespaces so that you can create various types of objects, such as tables and indexes, in them. When you create a new table or index segment, Oracle will use certain storage parameters to allocate the initial space and to alter allocations of space as the object grows in size. If you re using locally managed tablespaces, which happen to be the recommended type of tablespaces in Oracle Database 10g, you can omit the specification of storage parameters, such as INITIAL, NEXT, MINEXTENTS, MAXEXTENTS, and PCTINCREASE, when you create objects like tables and indexes in the tablespaces. For locally managed tablespaces, Oracle will manage the storage extents, so there is very little for you to specify in terms of storage allocation parameters. Oracle retains the storage parameters for backward compatibility only. You don t have to set the PCTUSED parameter if you re using locally managed tablespaces. If you set it, your object creation statement won t error out, but Oracle ignores the parameter. However, you can use the PCTFREE parameter to specify how much free space Oracle should leave in each block for future updates to data. The default is 10, which is okay if you don t expect the existing rows to get longer with time. If you do, you can change the PCTFREE parameter upward, say to 20 or 30 percent. Of course, there is a price to pay for this the higher the PCTFREE parameter, the more space you will waste in your database.

The default block size for all tablespaces is determined by the DB_BLOCK_SIZE initialization parameter for your database. You have the option of creating tablespaces with block sizes that are different from the standard database block size. In order to create a tablespace with a nonstandard block size, you must have already set the DB_CACHE_SIZE initialization parameter, and at least one DB_nK_CACHE_SIZE initialization parameter. For example, you must set the DB_16K_CACHE_SIZE parameter, if you wish to create tablespaces with a 16KB block size. By using a nonstandard block size, you can customize a tablespace for the types of objects it contains. For example, you can allocate a large table that requires a large number of reads and writes to a tablespace with a large block size. Similarly, you can place smaller tables in tablespaces with a smaller block size. Here are some points to keep in mind if you re considering using the multiple block size feature for tablespaces: Multiple buffer pools enable you to configure up to a total of five different pools in the buffer cache, each with a different block size. (This is discussed in 4.) The System tablespace always has to be created with the standard block size specified by the DB_BLOCK_SIZE parameter in the init.ora file. You can have up to four nonstandard block sizes. You specify the block size for tablespaces in the CREATE TABLESPACE statement by using the BLOCKSIZE clause. The nonstandard block sizes must be 2KB, 4KB, 8KB, 16KB, or 32KB. One of these sizes, of course, will have to be chosen as the standard block size by using the DB_BLOCK_SIZE parameter in the init.ora file. If you re transporting tablespaces between databases, using tablespaces with multiple block sizes makes it easier to transport tablespaces of different block sizes. You use the BLOCKSIZE keyword when you create a tablespace, to specify a nonstandard block size. The following statement creates a tablespace with a nonstandard block size of 16KB (the standard block size in this example is only 4KB, which is determined by the value you specify for the DB_BLOCK_SIZE initialization parameter): SQL> CREATE TABLESPACE test01 datafile '/u09/oradata/testdb/test01.dbf' BLOCKSIZE 8K;