How to find whether your tablespace is in exadata storage? How to use hybrid columnar compression in non exadata.

Ofcourse you will have a manual that states about your environment.

But how to get it from the database.

In otherwords if you have given a database to check your tablespaces that is running in exadata storage and you want to uae hybrid columnar compression (possible only with exadata)

Humor; dont say you run create tablespace. ...... Compress for high. and if you get error then its not exadata storage.

before going forward, Look at this ouput of dba_tablespace , in 11g

SQL> select tablespace_name,predicate_evaluation from dba_tablespaces;

TABLESPACE_NAME                PREDICA
------------------------------ -------
SYSTEM                         HOST
SYSAUX                         HOST
TEMP                           HOST
USERS                          HOST

10 rows selected.

SQL>

A new column predicate_evaluation is added, lets see 10.2.0.4 dba_tablespaces description

10.2.0.4 Output
==============

SQL> desc dba_tablespaces
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
TABLESPACE_NAME                           NOT NULL VARCHAR2(30)
BLOCK_SIZE                                NOT NULL NUMBER
INITIAL_EXTENT                                     NUMBER
NEXT_EXTENT                                        NUMBER
MIN_EXTENTS                               NOT NULL NUMBER
MAX_EXTENTS                                        NUMBER
PCT_INCREASE                                       NUMBER
MIN_EXTLEN                                         NUMBER
STATUS                                             VARCHAR2(9)
CONTENTS                                           VARCHAR2(9)
LOGGING                                            VARCHAR2(9)
FORCE_LOGGING                                      VARCHAR2(3)
EXTENT_MANAGEMENT                                  VARCHAR2(10)
ALLOCATION_TYPE                                    VARCHAR2(9)
PLUGGED_IN                                         VARCHAR2(3)
SEGMENT_SPACE_MANAGEMENT                           VARCHAR2(6)
DEF_TAB_COMPRESSION                                VARCHAR2(8)
RETENTION                                          VARCHAR2(11)
BIGFILE                                            VARCHAR2(3)

SQL>

Its not there, in 11gr2 this column along with other new two columns there.

11.2.0.2 Output (infact from 11.1.0.0 onwards)
===============

SQL> desc dba_tablespaces
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
TABLESPACE_NAME                           NOT NULL VARCHAR2(30)
BLOCK_SIZE                                NOT NULL NUMBER
INITIAL_EXTENT                                     NUMBER
NEXT_EXTENT                                        NUMBER
MIN_EXTENTS                               NOT NULL NUMBER
MAX_EXTENTS                                        NUMBER
MAX_SIZE                                           NUMBER
PCT_INCREASE                                       NUMBER
MIN_EXTLEN                                         NUMBER
STATUS                                             VARCHAR2(9)
CONTENTS                                           VARCHAR2(9)
LOGGING                                            VARCHAR2(9)
FORCE_LOGGING                                      VARCHAR2(3)
EXTENT_MANAGEMENT                                  VARCHAR2(10)
ALLOCATION_TYPE                                    VARCHAR2(9)
PLUGGED_IN                                         VARCHAR2(3)
SEGMENT_SPACE_MANAGEMENT                           VARCHAR2(6)
DEF_TAB_COMPRESSION                                VARCHAR2(8)
RETENTION                                          VARCHAR2(11)
BIGFILE                                            VARCHAR2(3)
PREDICATE_EVALUATION                               VARCHAR2(7)
ENCRYPTED                                          VARCHAR2(3)
COMPRESS_FOR                                       VARCHAR2(12)

What does those three extra columns

PREDICATE_EVALUATION : Indicates whether predicates are evaluated by host (HOST) or by storage (STORAGE)

ENCRYPTED   : Indicates whether the tablespace is encrypted (YES) or not (NO)

COMPRESS_FOR : Default compression for what kind of operations: •DIRECT LOAD ONLY•FOR ALL OPERATIONS•NULL

Now back to Post, how to find the tablespace is in exadata storage? WELL the answer is to know about what is predicate_evaluation above?

This columns says whether the disk attribute is set for exadata storage or normal storage,

If its exadata storage ( show storage) some options like "compress high" etc will be run on those tablespaces.

If its normal storage (show host) , compress high etc will not be possible.

As my above output shows,  HOST my tablespaces are in normal database with normal storage. not in the exadata.

SQL> select tablespace_name,predicate_evaluation from dba_tablespaces;

TABLESPACE_NAME                PREDICA
—————————— ——-
SYSTEM                         HOST
SYSAUX                         HOST
TEMP                           HOST
USERS                          HOST

Still if you want to play around, you can change the predicate_evaluation for tablespace (when using asm diskgroup) by changing the attribute using kfed (upcoming post)

-Thanks
Geek DBA