In Chapter 8, "Creating and Modifying Tables," you learned the essential steps of creating tables. For the sake of clarity, we did not discuss how Oracle uses physical storage for table and index data. Now we can focus on the important topic of managing space.
The installation procedures for Personal Oracle7 and the Oracle Workgroup Server were designed to fulfill the operational requirements for a broad range of Oracle users. That was a lofty goal, and, to a large extent, it was achieved. However, to attain better organization and efficiency, especially if you plan on upsizing your database, you'll want to follow the guidelines described in this chapter.
You may recall from Chapter 3, "Personal Oracle7 for Windows 95: Database Administration Tools," that the Database Expander is no longer a part of Personal Oracle7 for Windows 95. Instead, you must use the Navigator to view a tablespace. However, Personal Oracle7 for Windows 95 has a new feature--;it will automatically expand a tablespace as needed. This feature minimizes the need to manually expand a tablespace via SQL*Plus, although you may still manage the tablespaces with SQL*Plus if you wish.
Oracle stores data in a hierarchical fashion, as shown in the diagram in Figure 26.1.
At the bottom of the hierarchy is the Oracle block, which forms the basis for storing all Oracle database objects. Two or more contiguous Oracle blocks are grouped into an entity called an extent. Multiple extents that have been allocated for a particular object--;say, a table or index--;are referenced as a segment. A tablespace is composed of one or more operating system files called datafiles. All the extents for a particular object must be stored in the same tablespace.
The Oracle block is the basic storage building block of every Oracle database. Oracle developed the concept of an Oracle block to simplify the porting of the product to a variety of operating systems. An Oracle block is a multiple of the block size of the operating system (OS) file system in which Oracle resides. The default block size for most Oracle installations is 2,048 bytes, which is also true for Personal Oracle7. This value is established before a database is created.
An extent is a group of contiguous blocks that are allocated to an Oracle database object such as a table or index. When a table is created, the blocks allocated to that table are referred to as the initial extent. As rows are added to the table, they are stored in the extent's blocks. When a user inserts a row into the table and the extent's blocks are full, Oracle allocates the next extent for the table--;another set of contiguous Oracle blocks that may or may not be the same size as the initial extent.
A segment is the set of extents that have been allocated to a particular database object. If you inspect the DBA_SEGMENTS data dictionary view, you'll find a variety of segment types.
SQL> select distinct segment_type 2 from dba_segments; SEGMENT_TYPE ----------------- CACHE CLUSTER INDEX ROLLBACK TABLE
When you create a table or index, you have the option of specifying the tablespace in which it is created. A tablespace is a logical structure. Its purpose is to link the storage of Oracle tables, indexes, and other objects with the files of the operating system where Oracle is running. Every Personal Oracle7 tablespace is associated with one or more DOS files.
Personal Oracle7 manages the contents of these DOS files for table and index storage. Oracle refers to these files as datafiles. These datafiles are preallocated; as a developer or DBA, you are responsible for specifying the size of these files or for allocating additional files to the Personal Oracle7 database.
In Oracle RDBMS Version 7.2--;the version contained in Personal Oracle7 for Windows 95--;a new option called autoextend was added to the tablespace. In previous versions of Oracle, a tablespace could not automatically extend itself if its free space was depleted. In Version 7.2, you can enable autoextend for a tablespace so that it can allocate additional disk space from the file system. The tablespaces in the starter database supplied with Personal Oracle7 for Windows 95 have the autoextend option enabled.
When you install Personal Oracle7, a starter database is installed--;unless you choose not to install it. The following tablespaces are contained in the starter database:
Let's examine the purpose of each of these tablespaces.
Every Oracle database must have a SYSTEM tablespace. The primary constituent of the SYSTEM tablespace is the Oracle data dictionary. You should store application objects--;tables and indexes--;in a tablespace other than the SYSTEM tablespace for two reasons:
In Chapter 17, "Defining Transactions," I discussed the relationship between transactions and Oracle rollback segments. A rollback segment is a database object that stores a transaction's changes before they are committed. Rollback segments also support concurrency--;user A can modify a row in a table while user B reads the same row. You can retrieve the default rollback segments created during the Personal Oracle7 installation by selecting from the DBA_ROLLBACK_SEGS data dictionary view.
SQL> select segment_name, tablespace_name 2 from dba_rollback_segs; SEGMENT_NAME TABLESPACE_NAME ------------------------------ ------------------------------ SYSTEM SYSTEM RB_TEMP SYSTEM RB1 ROLLBACK_DATA RB2 ROLLBACK_DATA
Occasionally, Oracle needs to create a temporary segment to process a SQL statement. For instance, if you use the UNION operator on two tables, Oracle creates a temporary segment to construct the query results. The TEMPORARY_DATA tablespace stores these temporary segments.
The USER_DATA tablespace is intended to store all user-created tables and indexes. When you create a new user with User Manager, the USER_DATA tablespace is the default storage location for any tables that the new user creates.
By creating an additional tablespace for each application or logical group of users, you maintain the organization of your database. This storage scheme isn't mandatory, but it makes your life as an Oracle developer or DBA much easier than if you allowed different projects to share the same set of tablespaces. For instance, if you're building a database to support two groups of users--;the marketing team for Fuzzy Wuzzy and the engineering team for the RT100 Remote Echidna Detector--;you'd probably want to create a tablespace for each group: FUZZY_WUZZY and RT100_RED. Then, you can set the default tablespace for each user to the appropriate tablespace. (See Chapter 27, "Managing Users and Roles," for details.)
Using the Navigator, connect to the Local Database by double-clicking the icon. Once you are connected to the Local Database, right-click Properties. You'll see two tab folders named General and Tablespace--;select Tablespace. (See Figure 26.2.)
When you first look at the folder, no tablespace information will be displayed. You must select the tablespace that you want to inspect from the Name drop-drown list. For example, to view the used and free space in the USER_DATA tablespace, highlight USER_DATA in the Name list box. The Navigator will display the free and used bytes in the USER_DATA tablespace and also portray this information via a three-dimensional pie chart. (See Figure 26.3.)
The Database Expander is one of Oracle's Database Administration Tools. Its program icon resides in the Personal Oracle7 program group. The Database Expander has two functions:
Here is the procedure for adding space to a tablespace with the Database Expander. First, the Database Expander displays a dialog box confirming that you want to connect to the Personal Oracle7 database. (See Figure 26.4.) Click the OK button.
Enter the database password--;by default, oracle--;in the next dialog box and click OK. (See Figure 26.5.)
The Oracle Database Expander screen has a drop-down list of tablespaces. Click the down arrow and select one of the tablespaces. The Database Expander will query the database to determine the total amount of space in the tablespace and the used and free portions.
The Database Expander gives you three sizes to choose from when adding a datafile to a tablespace.
Click the down arrow of the Expand by drop-down list to select one of these values. Then click the Expand button. (See Figure 26.6.)
The Database Expander displays a message to confirm that you really want to expand the tablespace by the specified amount. (See Figure 26.7.) Expanding a tablespace cannot be reversed. Click the OK button.
After the space has been added to the tablespace, the Database Expander updates the storage information displayed for the tablespace. (See Figure 26.8.)
Unfortunately, one of the things that the Database Expander can't do is create a new tablespace. However, you can use SQL*Plus to do so.
You can create a new tablespace by issuing the CREATE TABLESPACE statement via SQL*Plus with the following syntax:
CREATE TABLESPACE tablespace-name
DATAFILE 'datafile-name'
SIZE datafile-size
[AUTOEXTEND ON|OFF [NEXT autoextend-size M|K]
[MAXSIZE UNLIMITED|autoextend-limit M|K]]
DEFAULT STORAGE
(INITIAL initial-extent-size NEXT next-extent-size
MINEXTENTS minimum-number-of-extents
MAXEXTENTS maximum-number-of-extents
PCTINCREASE growth-rate)
The following variables are defined: tablespace-name is the name of the tablespace to be created.
datafile-name is the directory and filename of the DOS file to be created for use by the tablespace.
datafile-size is the size to be used in creating datafile-name. (You can append a K or M to this number to indicate that the size is expressed in kilobytes or megabytes, respectively.)
autoextend-size is the amount of disk space that will be allocated to the datafile when it is autoextended. (You can append a K or M to this number to indicate that the size is expressed in kilobytes or megabytes, respectively.)
autoextend-limit is the maximum amount of disk space that can be allocated to the datafile.
initial-extent-size is the size of the first extent allocated for the table, expressed in bytes.
next-extent-size is the size of the next extent to be allocated for the table, expressed in bytes.
minimum-number-of-extents is the minimum number of extents to be allocated for the table.
maximum-number-of-extents is the maximum number of extents to be allocated for the table.
growth-rate is the percentage increase in the extent size for each extent allocation.
These parameters--;initial-extent-size, next-extent-size, minimum-number-of-extents, maximum-number-of-extents, and growth-rate--;are the default values that are used for a table that is stored in the tablespace if no storage parameters are specified for the table.
As an example, you can create a new tablespace named FRAYED_WIRES that will become the default tablespace for all tables and indexes used in the repair store application.
SQL> create tablespace frayed_wires 2 datafile 'c:\orawin\dbs\wdbfray.ora' 3 size 10M 4 default storage 5 (initial 50k next 50k 6 minextents 1 maxextents 121 pctincrease 15); Tablespace created.
If a user creates a table in this tablespace without specifying any storage parameters, Oracle allocates an initial extent of 50KB for the table.
As an alternative to executing the Database Expander, you can use the ALTER TABLESPACE statement from SQL*Plus to expand a tablespace. You can also use the same statement to modify the default storage parameters with the following syntax:
ALTER TABLESPACE tablespace-name [ADD DATAFILE 'datafile-name' SIZE datafile-size] DEFAULT STORAGE (INITIAL initial-extent-size NEXT next-extent-size MINEXTENTS minimum-number-of-extents MAXEXTENTS maximum-number-of-extents PCTINCREASE growth-rate)
The following variables are defined: tablespace-name is the name of the tablespace to be created.
datafile-name is the directory and filename of the DOS file to be created for use by the tablespace.
datafile-size is the size to be used in creating datafile-name. (You can append a K or M to this number to indicate that the size is expressed in kilobytes or megabytes, respectively.)
initial-extent-size is the size of the first extent allocated for the table, expressed in bytes.
next-extent-size is the size of the next extent to be allocated for the table, expressed in bytes.
minimum-number-of-extents is the minimum number of extents to be allocated for the table.
maximum-number-of-extents is the maximum number of extents to be allocated for the table.
growth-rate is the percentage increase in the extent size for each extent allocation.
As an example, you can add another datafile to the FRAYED_WIRES tablespace with the following statement:
SQL> alter tablespace frayed_wires 2 add datafile 'c:\orawin\dbs\wdbfray2.ora' 3 size 5m; Tablespace altered.
You can change the size of the initial extent for the FRAYED_WIRES tablespace in this way:
SQL> alter tablespace frayed_wires 2 default storage 3 (initial 75k next 50k 4 minextents 1 maxextents 121 pctincrease 15); Tablespace altered.
TipWhen you create or alter a tablespace for a Personal Oracle7 database, try to keep all the datafiles in the same directory--;C:\ORAWIN\DBS--;and to use a consistent datafile-naming convention. Each datafile is named wdbTS.ora where TS is a five-character abbreviation for the tablespace. If you're adding a new datafile to an existing tablespace, add a digit to the datafile name to indicate the number of datafiles in the tablespace.
As you might expect, you can use several methods to determine the amount of free space in a tablespace:
TipThe Navigator and the Database Expander provide summary information about free space, whereas the data dictionary view available in SQL*Plus provides detailed information about each free extent in a tablespace.
The next section shows you how to use SQL*Plus to display a tablespace's used and free space.
Through SQL*Plus, you can query the DBA_FREE_SPACE data dictionary view to determine a tablespace's free space. You must connect to Oracle as a user who has been granted the DBA role. The columns of the view are
SQL> desc sys.dba_free_space Name Null? Type ------------------------------- -------- ---- TABLESPACE_NAME NOT NULL VARCHAR2(30) FILE_ID NOT NULL NUMBER BLOCK_ID NOT NULL NUMBER BYTES NUMBER
BLOCKS NOT NULL NUMBER
The only columns in DBA_FREE_SPACE that we're really interested in are Tablespace_Name and Bytes. To see the same information that the Database Expander displays for a tablespace, use the following query:
SQL> select sum(bytes) 2 from dba_free_space 3 where 4 tablespace_name = 'USER_DATA'; SUM(BYTES) ---------- 2783232
Given the results of this query, you might think that you can create a table with an initial extent of 2,700,000 bytes because that size is smaller than the 2,783,232 bytes of available space in USER_DATA. However, the previous query of DBA_FREE_SPACE summed the bytes in each free extent in a tablespace. An important piece of information is missing--;the number of extents that the sum represents. You have no way of knowing whether the free space exists as a single large extent or a hundred small extents. This information can be crucial if your table requires a large next extent. To determine the composition of the free space--;how few or how many extents--;you can modify the query this way:
SQL> select bytes
2 from dba_free_space
3 where
4 tablespace_name = 'USER_DATA';
BYTES
---------
2670240
8192
204800
As you can see from the results of this query, the free space in USER_DATA is actually composed of three extents. The largest extent is only 2,570,240--;smaller than the initial extent that you specified when trying to create the table.
NoteWhen you drop a table or index, its space is returned to the list of free extents. One nice feature of Oracle's storage management is that Oracle will coalesce multiple contiguous free extents, thereby creating one larger free extent.
In Chapter 8, I described the elementary steps needed to create a table. One aspect I didn't cover was an optional clause of the CREATE TABLE statement: the STORAGE clause. You use the STORAGE clause to provide detailed instructions to Oracle regarding the manner in which the table's storage should be allocated. Along with the STORAGE clause, you may also specify the tablespace in which the new table should reside. The syntax of a table's STORAGE clause is
CREATE TABLE table-name (column-specification, ... column-specification, ... constraint-declaration, ... constraint-declaration) [PCTFREE pct-block-free] [PCTUSED pct-block-used] [TABLESPACE tablespace-name] [STORAGE ( INITIAL initial-extent-size [NEXT next-extent-size] [MINEXTENTS minimum-number-of-extents] [MAXEXTENTS maximum-number-of-extents] [PCTINCREASE growth-rate] )
The following variables are defined: table-name, column-specification, and constraint-declaration have the same definitions as shown in Chapter 8 and Chapter 14, "Defining Table and Column Contraints."
pct-block-free is a number from 0 to 99 indicating the percentage of each block that should remain available if a row in the block needs additional storage.
pct-block-used is a number from 1 to 99 indicating the percentage of each block that should be used for storage.
tablespace-name is the name of the tablespace where the table data is stored.
initial-extent-size is the size of the first extent allocated for the table, expressed in bytes.
next-extent-size is the size of the next extent to be allocated for the table, expressed in bytes.
minimum-number-of-extents is the minimum number of extents to be allocated for the table.
maximum-number-of-extents is the maximum number of extents to be allocated for the table.
growth-rate is the percentage increase in the extent size for each extent allocation.
Here's an example that illustrates how to specify a table's storage parameters. You will create a table named XYZ and assign it to the FRAYED_WIRES tablespace.
SQL> create table XYZ 2 (Dummy number) 3 tablespace FRAYED_WIRES 4 storage (initial 20k next 50k 5 minextents 1 maxextents 121); Table created. SQL> select tablespace_name, pct_free, pct_used, 2 initial_extent, next_extent, min_extents, max_extents 3 from user_tables 4 where 5 table_name = 'XYZ'; TABLESPACE_NAME PCT_FREE PCT_USED INITIAL_EXTENT NEXT_EXTENT MIN_EXT MAX_EXT --------------- -------- --------- -------------- ----------- ------- ------- FRAYED_WIRES 10 40 20480 51200 1 121
After a table has been created, you might want to change its storage parameters. For instance, you might want to change the value for PCTINCREASE. To change the storage parameters of a table, use the ALTER TABLE statement with the following syntax:
ALTER TABLE table-name (column-specification, ... column-specification, ... constraint-declaration, ... constraint-declaration) [PCTFREE pct-block-free] [PCTUSED pct-block-used] [STORAGE ( [INITIAL initial-extent-size] [NEXT next-extent-size] [MINEXTENTS minimum-number-of-extents] [MAXEXTENTS maximum-number-of-extents] [PCTINCREASE growth-rate] )]
The following variables are defined: table-name, column-specification, and constraint-declaration have the same definitions as shown in Chapters 8 and 14.
pct-block-free is a number from 0 to 99 indicating the percentage of each block that should remain available if a row in the block needs additional storage.
pct-block-used is a number from 1 to 99 indicating the percentage of each block that should be used for storage.
initial-extent-size is the size of the first extent allocated for the table, expressed in bytes.
next-extent-size is the size of the next extent to be allocated for the table, expressed in bytes.
minimum-number-of-extents is the minimum number of extents to be allocated for the table.
maximum-number-of-extents is the maximum number of extents to be allocated for the table.
growth-rate is the percentage increase in the extent size for each extent allocation.
NoteAfter you've created a table, you can't change its tablespace. You can, however, create another table in a different tablespace and copy the contents of the original table, either with the CREATE TABLE ... AS statement or the SQL*Plus COPY command.
The following example illustrates how to use the ALTER TABLE statement. By default, the value of PCTINCREASE is 50 as the following query shows:
SQL> select segment_name, segment_type, tablespace_name, pct_increase 2 from user_segments 3 where 4 segment_name = 'XYZ'; SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME PCT_INCREASE -------------- ----------------- ------------------------------ ------------ XYZ TABLE USER_DATA 50
You can change the value of PCTINCREASE for additional extents allocated to table XYZ in the following manner:
SQL> alter table xyz 2 storage (pctincrease 20); Table altered.
By querying USER_SEGMENTS once more, you can see that PCTINCREASE has been changed.
SQL> select segment_name, segment_type, tablespace_name, pct_increase 2 from user_segments 3 where 4 segment_name = 'XYZ'; SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME PCT_INCREASE --------------- ----------------- ------------------------------ ------------ XYZ TABLE USER_DATA 20
If not specified, a table's storage parameters are assigned the default values that exist for the tablespace in which it resides.
Here are the default values that have been defined for the tablespaces in Personal Oracle7's starter database:
SQL> select * 2 from dba_tablespaces; TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT MIN_EXT MAX_EXT PCT_INCREASE STATUS --------------- -------------- ----------- ------- ------- ------------ ------ SYSTEM 4096 4096 1 121 10 ONLINE USER_DATA 4096 4096 1 121 10 ONLINE ROLLBACK_DATA 102400 102400 2 121 50 ONLINE TEMPORARY_DATA 4096 4096 1 121 10 ONLINE
As you'll see in more detail in Chapter 27, a default tablespace may be--;and should be--;assigned to an Oracle user. The default tablespace is where table and index storage is allocated if a tablespace is not specified when the table or index is created.
The storage parameters, PCTFREE and PCTUSED, control how Oracle uses space for each data block in a table. Every data block contains a block header, which is approximately 100 bytes in size. (See Figure 26.9.) Another portion of each data block, called a table directory, contains information about the table whose rows are contained in the block. The row directory is an area of the block describing the rows that are stored in the block. Finally, each data block contains an area of free space where rows can be added to the block.
When a row is inserted in a block, Oracle looks at the free space remaining in the block. If the percentage of free space is less than that specified by the value for PCTFREE, Oracle doesn't insert the row in the block and instead uses a different block for storing the row. Oracle sets aside a portion of each row as free space for a specific purpose. When a row is first inserted in a block, many of its columns may be null. As information becomes available, the row is updated and a column that was previously null is set to some value. By keeping this updated row information in the same block as the originally inserted row, Oracle has to read only one data block to retrieve the entire row.
As rows are deleted from a block, the space that they consumed is returned to the block's free space. When the amount of free space drops to the value specified by PCTUSED or less, Oracle will once again begin inserting rows into the block. For Personal Oracle7, the default values for PCTFREE and PCTUSED are 10 and 40 percent, respectively.
PCTFREE and PCTUSED must always be less than or equal to 100. You can change these values for an existing table; however, the new values for PCTFREE or PCTUSED will affect only extents that are allocated after PCTFREE or PCTUSED have been changed.
TipFor a table whose rows are rarely updated, you might want to set PCTFREE to just 2 or 3 percent. By reducing the value of PCTFREE, you increase the amount of data in each block and generally reduce the time needed to process a query.
An index consumes space in almost the same fashion as a table. An index may either reside in the same tablespace as its table or you may assign it to a different tablespace. The syntax for an index's STORAGE clause is similar to the STORAGE clause used for a table. The CREATE INDEX statement uses the following syntax to specify an index's storage parameters:
CREATE INDEX index-name ON table-name (column-name, ..., column-name) [PCTFREE pct-block-free] [TABLESPACE tablespace-name] [STORAGE ( [INITIAL initial-extent-size] [NEXT next-extent-size] [MINEXTENTS minimum-number-of-extents] [MAXEXTENTS maximum-number-of-extents] [PCTINCREASE growth-rate] )]
The following variables are defined: index-name is the name of the index to create.
table-name is the name of an existing table for which the index-name is to be created.
column-name is a column in table-name to be used in creating the index.
pct-block-free is a number from 0 to 99 indicating the percentage of each block that should remain available.
tablespace-name is the name of the tablespace where the index data is stored.
initial-extent-size is the size of the first extent allocated for the index, expressed in bytes.
next-extent-size is the size of the next extent to be allocated for the index, expressed in bytes.
minimum-number-of-extents is the minimum number of extents to be allocated for the index.
maximum-number-of-extents is the maximum number of extents to be allocated for the index.
growth-rate is the percentage increase in the extent size for each extent allocation.
The following index on the Shipment table illustrates this syntax:
SQL> create index Shipment_Perf_1 2 on Shipment (Shipment_ID, Product_Code) 3 tablespace User_Data 4 storage (initial 30K next 50K pctincrease 15); Index created.
You can use the ALTER INDEX statement to modify the storage parameters for an existing index.
ALTER INDEX index-name [STORAGE ( [INITIAL initial-extent-size] [NEXT next-extent-size] [MINEXTENTS minimum-number-of-extents] [MAXEXTENTS maximum-number-of-extents] [PCTINCREASE growth-rate] )]
The following variables are defined: index-name is the name of the index to alter.
initial-extent-size is the size of the first extent allocated for the index, expressed in bytes.
next-extent-size is the size of the next extent to be allocated for the index, expressed in bytes.
minimum-number-of-extents is the minimum number of extents to be allocated for the index.
maximum-number-of-extents is the maximum number of extents to be allocated for the index.
growth-rate is the percentage increase in the extent size for each extent allocation.
The following ALTER INDEX statement changes a storage parameter for an existing index. This example modifies the previously created index by increasing the value of PCTINCREASE.
SQL> alter index Shipment_Perf_1 2 storage (pctincrease 25); Index altered.
This chapter on managing space covers the following main points: