Database Reference
In-Depth Information
you're a new DBA on the project and didn't realize it), and create a tablespace on a mount point. Then, when you
discovered that you needed more space, you wouldn't know that you couldn't add another data file on a different
mount point for this tablespace.
Enabling Default Table Compression within a Tablespace
When working with large databases, you may want to consider compressing the data. Compressed data results in
less disk space, less memory, and fewer I/O operations. Queries reading compressed data potentially execute faster
because fewer blocks are required to satisfy the result of the query. But, compression does have a cost; it requires
more CPU resources, as the data are compressed and uncompressed while reading and writing.
When creating a tablespace, you can enable data compression features. Doing so doesn't compress the
tablespace. Rather, any tables you create within the tablespace inherit the compression characteristics of the
tablespace. This example creates a tablespace with
ROW STORE COMPRESS ADVANCED
:
CREATE TABLESPACE tools_comp
DATAFILE '/u01/dbfile/O12C/tools_comp01.dbf'
SIZE 100m
SEGMENT SPACE MANAGEMENT AUTO
DEFAULT ROW STORE COMPRESS ADVANCED;
■
Note
if you're using oracle 11g, then use the
COMPRESS FOR OLTP
clause instead of
ROW STORE COMPRESS ADVANCED
.
Now when a table is created within this tablespace, it will automatically be created with the
ROW STORE COMPRESS
ADVANCED
feature. You can verify the compression characteristics of a tablespace via this query:
SQL> select tablespace_name, def_tab_compression, compress_for from dba_tablespaces;
If a tablespace is already created, you can alter its compression characters, as follows:
SQL> alter tablespace tools_comp default row store compress advanced;
Here's an example that alters a tablespace's default compress to
BASIC
:
SQL> alter tablespace tools_comp default compress basic;
You can disable tablespace compression via the
NOCOMPRESS
clause:
SQL> alter tablespace tools_comp default nocompress;
■
Note
Most compression features require the enterprise edition and the advanced Compression option.