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.
 
 
Search WWH ::




Custom Search