Compressed Tablespaces/Table Samples

TABLESPACE_NAME	EXTENT_MANAGEMENT	SEGMENT_SPACE_MANAGEMENT	DEF_TAB_COMPRESSION	COMPRESS_FOR
TEST_COMPRESS	LOCAL	AUTO	ENABLED	OLTP
TEST_COMPRESS_ADV	LOCAL	AUTO	ENABLED	OLTP ? -- Seems to be Bug 18059873
TEST_COMPRESS_BASIC	LOCAL	AUTO	ENABLED	BASIC

select tablespace_name,extent_management,segment_space_management,def_tab_compression,compress_for

from dba_tablespaces where tablespace_name like '%COMPRESS%'

TABLESPACE_NAME	EXTENT_MANAGEMENT	SEGMENT_SPACE_MANAGEMENT	DEF_TAB_COMPRESSION	COMPRESS_FOR
TEST_COMPRESS	LOCAL	AUTO	ENABLED	OLTP
TEST_COMPRESS_ADV	LOCAL	AUTO	ENABLED	OLTP
TEST_COMPRESS_BASIC	LOCAL	AUTO	ENABLED	BASIC

Data/Index Compression Techniques

COMPRESSION TYPE:	SUITABLE FOR:
Basic Compression	Read only tables and partitions in Data Warehouse environments or “inactive” data partitions in OLTP environments
Advanced Row Compression	Active tables and partitions in OLTP and Data Warehouse environments
Advanced LOB Compression and Deduplication	Non-relational data in OLTP and Data Warehouse environments
*Advanced Network Compression and Data Guard Redo Transport Compression*	All environments
*RMAN/Data Pump Backup Compression*	All environments
Index Compression	Indexes on tables for OLTP and Data Warehouse
Hybrid Columnar Compression – Query Level	Query mostly tables and partitions in OLTP and Data Warehouse environments
Hybrid Columnar Compression – Archive Level	“Inactive” data tables/partitions in OLTP and Data Warehouse environments

Create test user and get some data

Create test user

create user "USER1" identified by elcaro profile "DEFAULT" account unlock default tablespace "TEST_COMPRESS_ADV" temporary tablespace "TEMP";

grant "CONNECT" to "USER1";

grant "RESOURCE" to "USER1";

Create table with lob

SQL>conn user1/elcaro@pdb3

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics, Real Application Testing

and Unified Auditing options

CREATE TABLE CM_ORIGINALNI_ZAHTEVI_REP_S

(

CM_OZV_ID NUMBER(10) NOT NULL,

CM_OZV_FILE CLOB NOT NULL,

CM_DAT_INS DATE NOT NULL,

CM_USR_INS VARCHAR2(50 BYTE) NOT NULL,

CM_DAT_UPD DATE NOT NULL,

CM_USR_UPD VARCHAR2(50 BYTE) NOT NULL,

CM_UPD_REASON VARCHAR2(200 BYTE),

STATUS VARCHAR2(20 BYTE),

CRL_LIST_ID NUMBER,

CM_DAT_TS DATE

)

LOB (CM_OZV_FILE) STORE AS (

TABLESPACE TEST_COMPRESS_ADV

ENABLE STORAGE IN ROW

CHUNK 8192

RETENTION

NOCACHE

STORAGE (

INITIAL 64K

NEXT 1M

MINEXTENTS 1

MAXEXTENTS UNLIMITED

PCTINCREASE 0

BUFFER_POOL DEFAULT

FLASH_CACHE DEFAULT

CELL_FLASH_CACHE DEFAULT

))

TABLESPACE TEST_COMPRESS_ADV

RESULT_CACHE (MODE DEFAULT)

PCTUSED 0

PCTFREE 10

INITRANS 1

MAXTRANS 255

STORAGE (

INITIAL 64K

NEXT 1M

MINEXTENTS 1

MAXEXTENTS UNLIMITED

PCTINCREASE 0

BUFFER_POOL DEFAULT

FLASH_CACHE DEFAULT

CELL_FLASH_CACHE DEFAULT

)

NOCACHE

NOPARALLEL

MONITORING;

Get Data from existing System

expz.sh

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

export PATH=/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/oracle/bin:export:/u01/app/oracle/product/11.2.0/dbhome_1/bin:$ORACLE_HOME/:.

export ORACLE_SID=etpm1

export LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0/dbhome_1/lib

export ORACLE_BASE=/u01/app/oracle

export NLS_LANG=american_america.al32utf8

export ORACLE_UNQNAME=etpm

HOSTNAME=cletpm1

exp parfile=expz.par

expz.par

userid=PRODADM1/*******

tables=CM_ORIGINALNI_ZAHTEVI_REP_S

QUERY='WHERE ROWNUM<112'

LOG=expz.log

FILE=expz.dmp

After executing exps.sh

About to export specified tables via Conventional Path ...

. . exporting table CM_ORIGINALNI_ZAHTEVI_REP_S 111 rows exported

Import data to compresed tablespace

impz.bat

set ORACLE_HOME=D:\app\darkoj\product\12.1.0\dbhome_1

set PATH=%ORACLE_HOME%/bin;%PATH%

set ORACLE_SID=ORCLD

set NLS_LANG=AMERICAN_AMERICA.AL32UTF8

imp user1/elcaro@pdb3 file=expz.dmp fromuser=PRODADM1 touser=user1 log=imp.log ignore=y

select table_name,tablespace_name,compress_for,compression from cdb_tables where owner='USER1'

TABLE_NAME	TABLESPACE_NAME	COMPRESS_FOR	COMPRESSION
CM_ORIGINALNI_ZAHTEVI_REP_S	TEST_COMPRESS_ADV	ADVANCED	ENABLED

select table_name,column_name,segment_name, compression, deduplication, securefile

from cdb_lobs where owner='USER1'

TABLE_NAME	COLUMN_NAME	SEGMENT_NAME	COMPRESSION	DEDUPLICATION	SECUREFILE
CM_ORIGINALNI_ZAHTEVI_REP_S	CM_OZV_FILE	SYS_LOB0000092087C00002$$	NO	NO	YES

Compress Data High

SQL> show user

USER is "USER1"

SQL> alter table CM_ORIGINALNI_ZAHTEVI_REP_S modify lob(CM_OZV_FILE) (compress high) ;

Table altered.

select table_name,column_name,segment_name, compression, deduplication, securefile from cdb_lobs where owner='USER1'

TABLE_NAME	COLUMN_NAME	SEGMENT_NAME	COMPRESSION	DEDUPLICATION	SECUREFILE
CM_ORIGINALNI_ZAHTEVI_REP_S	CM_OZV_FILE	SYS_LOB0000092087C00002$$	HIGH	NO	YES

http://www.oracle.com/technetwork/articles/sql/11g-securefiles-084075.html