Modifying a tablespace to enable compression
- From: "Murray Sobol" <murrays@xxxxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 19 Nov 2010 14:10:18 -0500
I am trying to modify an Windows 64-bit Oracle 10.2.0.4 database to
enable table-level compression.
I would like to be able to do this at a tablespace level, rather than on
table basis.
I first ran this query: select * from v$option;
Which produced these results:
PARAMETER VALUE
----------------------------------------------------------------
----------------------------------------------------------------
Partitioning FALSE
Objects TRUE
Real Application Clusters FALSE
Advanced replication FALSE
Bit-mapped indexes FALSE
Connection multiplexing TRUE
Connection pooling TRUE
Database queuing TRUE
Incremental backup and recovery TRUE
Instead-of triggers TRUE
Parallel backup and recovery FALSE
Parallel execution FALSE
Parallel load TRUE
Point-in-time tablespace recovery FALSE
Fine-grained access control FALSE
Proxy authentication/authorization TRUE
Change Data Capture FALSE
Plan Stability TRUE
Online Index Build FALSE
Coalesce Index FALSE
Managed Standby FALSE
Materialized view rewrite FALSE
Materialized view warehouse refresh FALSE
Database resource manager FALSE
Spatial FALSE
Visual Information Retrieval FALSE
Export transportable tablespaces FALSE
Transparent Application Failover TRUE
Fast-Start Fault Recovery FALSE
Sample Scan TRUE
Duplexed backups FALSE
Java TRUE
OLAP Window Functions TRUE
Block Media Recovery FALSE
Fine-grained Auditing FALSE
Application Role FALSE
Enterprise User Security FALSE
Oracle Data Guard FALSE
Oracle Label Security FALSE
OLAP FALSE
Table compression FALSE
Join index FALSE
Trial Recovery FALSE
Data Mining FALSE
Online Redefinition FALSE
Streams Capture FALSE
File Mapping FALSE
Block Change Tracking FALSE
Flashback Table FALSE
Flashback Database FALSE
Data Mining Scoring Engine FALSE
Transparent Data Encryption FALSE
Backup Encryption FALSE
Unused Block Compression FALSE
Oracle Database Vault FALSE
Real Application Testing FALSE
56 rows selected.
So I tried 2 approaches:
1. Alter tablespace smartsoft_data default compress;
This produced the following error message:
ERROR at line 1:
ORA-00439: feature not enabled: Table compression
2. Create a new tablespace:
CREATE TABLESPACE "SMARTSOFT2_DATA"
DATAFILE 'E:\Database\Development\DataFiles\SMARTsoft2_Data.dbf'
SIZE 10M
REUSE
AUTOEXTEND ON
MAXSIZE 4096M
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 64K
SEGMENT SPACE MANAGEMENT AUTO
DEFAULT STORAGE (INITIAL 50K NEXT 5K PCTINCREASE 1);
;
I got this error:
CREATE TABLESPACE "SMARTSOFT2_DATA"
*
ERROR at line 1:
ORA-25143: default storage clause is not compatible with allocation
policy
1 CREATE TABLESPACE "SMARTSOFT2_DATA"
2 DATAFILE
'E:\Database\Development\DataFiles\SMARTsoft2_Data.dbf'
3 SIZE 10M
4 REUSE
5 AUTOEXTEND ON
6 MAXSIZE 4096M
7 LOGGING
8 ONLINE
9 PERMANENT
10 EXTENT MANAGEMENT LOCAL
11 UNIFORM SIZE 64K
12 SEGMENT SPACE MANAGEMENT AUTO
13* DEFAULT STORAGE (INITIAL 50K NEXT 5K PCTINCREASE 1)
The online documentation is confusing, at best. I could
not find any reference as to whether this option was ONLY available in
Enterprise edition.
Table compression was introduced in Oracle 9i as a space saving feature
for data warehousing projects. In 11g it is now considered a mainstream
feature that is acceptable for OLTP databases. In addition to saving
storage space, compression can result in increased I/O performance and
reduced memory use in the buffer cache. These advantages do come at a
cost, since compression incurs a CPU overhead, so it won't be of benefit
to everyone.
The compression clause can be specified at the tablespace, table or
partition level with the following options:
*NOCOMPRESS - The table or partition is not compressed. This is the
default action when no compression clause is specified.
*COMPRESS - This option is considered suitable for data warehouse
systems. Compression is enabled on the table or partition during
direct-path inserts only.
*COMPRESS FOR DIRECT_LOAD OPERATIONS - This option has the same affect
as the simple COMPRESS keyword.
*COMPRESS FOR ALL OPERATIONS - This option is considered suitable for
OLTP systems. As the name implies, this option enables compression for
all operations, including regular DML statements. This option requires
the COMPATIBLE initialization parameter to be set to 11.1.0 or higher.
In 11gR2 this option has been renamed to COMPRESS FOR OLTP and the
original name has been deprecated.
The restrictions associated with table compression include:
*Compressed tables can only have columns added or dropped if the
COMPRESS FOR ALL OPERATIONS option was used.
*Compressed tables must not have more than 255 columns.
*Compression is not applied to lob segments.
*Table compression is only valid for heap organized tables, not index
organized tables.
*The compression clause cannot be applied to hash or hash-list
partitions. Instead, they must inherit their compression settings from
the tablespace, table or partition settings.
*Table compression cannot be specified for external or clustered tables.
Any assistance would be appreciated.
Murray Sobol
dbcSMARTsoftware inc.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
