0481-0483

Previous Table of Contents Next

Page 481

This command creates the tablespace and makes it immediately available. The name of the file in quotation marks is named just as you name files on the operating system ”which, in this case, is assumed to be a UNIX file system. The size of the file always is given in terms of bytes, kilobytes, or megabytes. After this command is issued, you will find a file at the specified location and with the specified size . Oracle automatically formats the file into Oracle blocks, which are the smallest units in which storage is allocated.

It also is possible to specify a default storage parameter that has the same syntax as the storage clause ”for example, initial extent size, next extent size, percentage increase, and so on. This parameter specifies the default storage used when an object is created in the tablespace but when no storage clause is included with the object definition.

Creating Additional Tablespaces

You can create additional tablespaces in the same manner as described previously. You do not need to create additional rollback segments as long as at least two rollback segments still are enabled.

Adding Files to a Tablespace

The filename and size given for a tablespace eventually might fill up as the free Oracle blocks are allocated to the database segments. To increase the number of files a tablespace can use, enter the following command, which adds another file to the tablespace:

 SQLPLUS> ALTER TABLESPACE kashmir ADD DATAFILE `\disk01\myfile2.dbs' SIZE 10M; 

The tablespace now has another 10MB of storage to use. Objects already created in the tablespace now can allocate extents from the two files that belong to the tablespace.

TIP

To check the amount of free space remaining in a tablespace, query the dba_free_space data dictionary view. This view lists the fragments of free space for each tablespace in the database.

Increasing the Size of Database Files

You can change the size of a database file after the data file is added to the database by using the RESIZE option of the ALTER DATABASE SQL command:

 ALTER DATABASE DATAFILE `\disk01\myfile2.dbs' RESIZE 9M; 

You can increase or decrease the size. You cannot decrease the size below the size of all objects currently in the database file.

Page 482

In addition, you can specify that the database file grow automatically if there is not enough free storage for objects in that file. Often, the file will need to grow when large inserts are performed into objects in that data file. This growth is accomplished by using the AUTOEXTEND option. If this option is specified, the DBA is not informed that the database file has grown.

The following example shows the AUTOEXTEND option:

ALTER DATABASE DATAFILE `\disk01\myfile2.dbs' AUTOEXTEND ON NEXT 512KB MAXSIZE 50M;

In this example, the database file can grow in increments of 512KB if and when more free space is required in the file, but the file cannot grow beyond the 50MB maximum size specified.

To stop the file from growing any larger, you can issue the following command:

 ALTER DATABASE DATAFILE `\disk01\myfile2.dbs' AUTOEXTEND OFF; 

Dropping Tablespaces

You easily can drop a tablespace by issuing this command:

 SQLPLUS> DROP TABLESPACE kashmir; 

This command removes information about the tablespace from both the Oracle data dictionary and the control file. The next time the instance starts up, it does not attempt to open the file. This command does not delete the file from the operating system, however; the database administrator must do that at the operating system level.

If any objects are still using extents in the tablespace, the DROP command shown fails. You should check which objects are using storage in the tablespace by querying the dba_segments data dictionary view. You can use the following optional clause to drop all the database objects in the tablespace before the tablespace is dropped:

SQLPLUS> DROP TABLESPACE kashmir INCLUDING CONTENTS;

This command fails if active rollback segments are still using storage in the tablespace. You must deactivate these segments before you remove the tablespace.

Using Temporary Segments

Temporary segments are database objects automatically created by Oracle when extra working space is needed. This usually is the case when large sort -type operations are performed. When the order by, group by, and union clauses are run on a large table, for example, they might cause a temporary segment to be created.

It is difficult to see the temporary segments because they are deleted automatically by the SMON background process as soon as they are no longer required. If you suspect that a temporary segment exists, query the dba_segments data dictionary view and look for a TEMPORARY segment type. The v$ sysstat dynamic performance table also shows how often temporary segments have been created since the instance was started.

Page 483

To control where temporary segments are created on a user-by- user basis, issue the following command:

 SQLPLUS> ALTER USER lave TEMPORARY TABLESPACE kashmir; 

All users should be defined so that their temporary segments are created in the same tablespace. These definitions ensure that free space fragmentation occurs in only one part of the database.

You also can control how the default tablespace is used. The following statement, for example, means that when a user issues a CREATE statement and does not explicitly state where the object should be created, it is placed in the kashmir tablespace:

 SQLPLUS> ALTER USER lave DEFAULT TABLESPACE kashmir; 

The dba_users data dictionary view shows the temporary and default tablespaces for all users.

Analyzing Storage

Often, you'll have a large amount of storage allocated to a table, but you'll want to determine exactly how much storage the table actually uses and how much is empty. You might want to reclaim storage, for example.

The ANALYZE command serves two purposes. By gathering statistics about the data, you can use the Oracle cost-based optimizer to make more intelligent decisions about how to run a statement against the table. Similarly, you can find out exactly how much storage a table uses.

To analyze a table, use this command:

 SQLPLUS> ALTER TABLE taejen COMPUTE STATISTICS; 

This command goes through all the data for the table and gathers information about how many Oracle blocks are used or free and what percentage of them contain data. This information is available in the dba_tables and dba_indexes data dictionary tables.

It can take time to gather a complete set of statistics for a large table. You might want to gather a representative sample of statistics instead. The following command, for example, produces statistics based on a random sample of 20 percent of the rows:

 SQLPLUS> ALTER TABLE taejen ESTIMATE STATISTICS SAMPLE 20 PERCENT; 

If you do not want the cost-based optimizer to use the statistics you gather, you can delete the statistics by issuing this command:

 SQLPLUS> ALTER TABLE taejen DELETE STATISTICS; 

After you determine how much storage is used by the table, you can use the export and import utilities to re-create the table with the optimal storage parameters.

Previous Table of Contents Next