Search My Oracle Blog

Custom Search

27 March, 2016

Now an OCP 12c

I have upgraded my Oracle Database Certified Professional status from 11g to 12c, having passed the 1Z0-060 Exam last Monday.

This is the list of material I used :

1.  Documentation :  Oracle 12c Database Documentation.  Not just the New Features Guide but a large number of pages spread throughout the documentation.  I went through *selected* chapters / pages of the Administrator's Guide, Reference Guide, PLSQL Package Guide, Developers Guide and Backup and Recovery Guide

2.  Books :
a.  Oracle Database 12c New Features  by Robert G. Freeman  (Oracle Press)
b.  OCP Upgrade to Oracle Database 12c Exam Guide  by Sam R. Alapati  (Oracle Press)
c.  Study Guide for 1Z0-060 Upgrade to Oracle Database 12c  by Matthew Morris (Self-Published as Oracle Certification Prep)

3.  FlashCards
a.  FlashCards with Test Software for 1Z0-060 at https://www.selftestsoftware.com
b.  FlashCards OCPFlash1Z0-060 on Google PlayStore  by Matthew Morris

4.  Practice Tests
a.  Practice Tests and Test Software for 1Z0-060 at https://www.selftestsoftware.com
b.  Oracle Certification Prep Practice Test for 1Z0-060  at http://www.oraclecertificationprep.com

5.  Links to other Resources :  http://www.oraclecertificationprep.com and  www.oracle-base.com


Note : This does NOT mean that I didn't have practice environments.  The books alone aren't sufficient.   I created three practice environments :
a.  Fresh Install of Oracle Linux, RDBMS 12.1.0.1 and creation NonCDB database on FileSystem
b. Donwloaded Oracle Virtual Box PreBuilt VM with 12.1.0.2 and MultiTenant Database
c. Fresh Install of Oracle Linux, creation of disks for ASM, install of Grid Infrastructure and RDBMS 12.1.0.2 and creation of NonCDB database on ASM
.
.
.

26 March, 2016

Compression -- 5 : OLTP Compression

Taking the test case from my first Blog Post on compression,  is there any way to support OLTP ?
As demonstrated in the first Blog Post and here, once the table undergoes DML, its size changes.

PDB1@ORCL> select segment_name, segment_type, bytes/1048576
  2  from user_segments
  3  where segment_name in ('SOURCE_DATA','COMPRESSED_1')
  4  /

SEGMENT_NAME         SEGMENT_TYPE   BYTES/1048576
------------------------------ ------------------ -------------
COMPRESSED_1         TABLE        12
SOURCE_DATA         TABLE        49

PDB1@ORCL> 
PDB1@ORCL> update compressed_1
  2  set owner=owner;

364496 rows updated.

PDB1@ORCL> commit;

Commit complete.

PDB1@ORCL> select bytes/1048576
  2  from user_segments
  3  where segment_name = 'COMPRESSED_1';

BYTES/1048576
-------------
    22

PDB1@ORCL> 
PDB1@ORCL> update compressed_1
  2  set owner=substr(owner||owner,1,30);

364496 rows updated.

PDB1@ORCL> commit;

Commit complete.

PDB1@ORCL> select bytes/1048576
  2  from user_segments
  3  where segment_name = 'COMPRESSED_1';

BYTES/1048576
-------------
    58

PDB1@ORCL> 

So, I setup another table that supports OLTP compression.  This requires the Advanced Compression licence.  Make sure that you have purchased the licence !

PDB1@ORCL> create table compressed_2 tablespace hemant COMPRESS FOR OLTP
  2  as select * from source_data where 1=2;

Table created.

PDB1@ORCL> insert /*+ APPEND */ into compressed_2
  2  select * from source_data;

364496 rows created.

PDB1@ORCL> commit;

Commit complete.

PDB1@ORCL> select bytes/1048576
  2  from user_segments 
  3  where segment_name = 'COMPRESSED_2';

BYTES/1048576
-------------
    13

PDB1@ORCL> update compressed_2
  2  set owner=owner;

364496 rows updated.

PDB1@ORCL> commit;

Commit complete.

PDB1@ORCL> select bytes/1048576
  2  from user_segments
  3  where segment_name = 'COMPRESSED_2';

BYTES/1048576
-------------
    13

PDB1@ORCL> update compressed_2
  2  set owner=substr(owner||owner,1,30);

364496 rows updated.

PDB1@ORCL> select bytes/1048576
  2  from user_segments
  3  where segment_name = 'COMPRESSED_2';

BYTES/1048576
-------------
    29

PDB1@ORCL> 
PDB1@ORCL> select table_name, pct_free, compression, compress_for
  2  from user_tables
  3  where table_name like 'COMPRESS%'
  4  order by 1;

TABLE_NAME    PCT_FREE COMPRESS
------------------------------ ---------- --------
COMPRESS_FOR
------------------------------
COMPRESSED_1    0 ENABLED
BASIC

COMPRESSED_2          10 ENABLED
ADVANCED


PDB1@ORCL> 


Note the initial size of COMPRESSED_2 is slightly large because it starts with PCT_FREE=10.
The UPDATE does seem to take longer to run.  COMPRESS FOR OLTP preserves PCT_FREE at 10. But it does handle UPDATEs better than BASIC Compression. However, if you have no subsequent UPDATEs to the data, BASIC Compression (which does not need an additional licence when running Enterprise Edition) will suffice.

I urge you to *TEST* COMPRESS FOR OLTP with the different types of UPDATE operations that you actually have in your live database before you choose to implement it.

In 12c, COMPRESS FOR OLTP is called ROW STORE COMPRESS ADVANCED.  I presented the 11g style COMPRESS FOR OLTP syntax so that you could use it in 11g.
.
.
.


19 March, 2016

Compression -- 4 : RMAN (BASIC) Compression

BASIC Compression in RMAN is free with the Enterprise Edition. Advanced Compression with a specified algorithm requires the Advanced Compression Option Licence.

RMAN> backup as compressed backupset database root;

Starting backup at 19-MAR-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=48 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00003 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_9fxmvhl3_.dbf
input datafile file number=00001 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_9fxmx6s1_.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_9fxn0vgg_.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_9fxn0t8s_.dbf
channel ORA_DISK_1: starting piece 1 at 19-MAR-16
channel ORA_DISK_1: finished piece 1 at 19-MAR-16
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2016_03_19/o1_mf_nnndf_TAG20160319T081217_cgtjk2ms_.bkp tag=TAG20160319T081217 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:16
Finished backup at 19-MAR-16

Starting Control File and SPFILE Autobackup at 19-MAR-16
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/autobackup/2016_03_19/o1_mf_s_906884075_cgtjofh1_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 19-MAR-16

RMAN> list backup;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1       Full    385.78M    DISK        00:02:09     19-MAR-16      
        BP Key: 1   Status: AVAILABLE  Compressed: YES  Tag: TAG20160319T081217
        Piece Name: /u01/app/oracle/fast_recovery_area/ORCL/backupset/2016_03_19/o1_mf_nnndf_TAG20160319T081217_cgtjk2ms_.bkp
  List of Datafiles in backup set 1
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 3034354    19-MAR-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_9fxmx6s1_.dbf
  3       Full 3034354    19-MAR-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_9fxmvhl3_.dbf
  4       Full 3034354    19-MAR-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_9fxn0vgg_.dbf
  6       Full 3034354    19-MAR-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_9fxn0t8s_.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2       Full    17.20M     DISK        00:00:03     19-MAR-16      
        BP Key: 2   Status: AVAILABLE  Compressed: NO  Tag: TAG20160319T081435
        Piece Name: /u01/app/oracle/fast_recovery_area/ORCL/autobackup/2016_03_19/o1_mf_s_906884075_cgtjofh1_.bkp
  SPFILE Included: Modification time: 19-MAR-16
  SPFILE db_unique_name: ORCL
  Control File Included: Ckp SCN: 3034519      Ckp time: 19-MAR-16

RMAN> 
CDB$ROOT@ORCL> l
  1  select status, command_id, to_char(start_time,'DD-MON HH24:MI') Start_At, to_char(end_time,'DD-MON HH24:MI') End_At,
  2  trunc(input_bytes/1048576) Input_MB, trunc(output_bytes/1048576) Output_MB, input_type
  3  from v$rman_backup_job_details
  4* order by start_time
CDB$ROOT@ORCL> 
CDB$ROOT@ORCL> /

STATUS              COMMAND_ID                 START_AT         END_AT          INPUT_MB  OUTPUT_MB
------------------- ------------------------- ----------------- ------------- ---------- ----------
INPUT_TYPE
-------------
COMPLETED           2016-03-19T08:12:02       19-MAR 08:12      19-MAR 08:14        1807        402
DB FULL


CDB$ROOT@ORCL> 

Compare the INPUT_MB and OUTPUT_MB to see the compression achieved.

Default compression can also be configured with

RMAN> show compression algorithm;

RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default

RMAN> 
RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET;

new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1;
new RMAN configuration parameters are successfully stored

RMAN> 
RMAN> backup datafile 1;

Starting backup at 19-MAR-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_9fxmx6s1_.dbf
channel ORA_DISK_1: starting piece 1 at 19-MAR-16
channel ORA_DISK_1: finished piece 1 at 19-MAR-16
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2016_03_19/o1_mf_nnndf_TAG20160319T082704_cgtkdrdh_.bkp tag=TAG20160319T082704 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55
Finished backup at 19-MAR-16

Starting Control File and SPFILE Autobackup at 19-MAR-16
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/autobackup/2016_03_19/o1_mf_s_906884879_cgtkgk5d_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 19-MAR-16

RMAN> list backup of datafile 1;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1       Full    385.78M    DISK        00:02:09     19-MAR-16      
        BP Key: 1   Status: AVAILABLE  Compressed: YES  Tag: TAG20160319T081217
        Piece Name: /u01/app/oracle/fast_recovery_area/ORCL/backupset/2016_03_19/o1_mf_nnndf_TAG20160319T081217_cgtjk2ms_.bkp
  List of Datafiles in backup set 1
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 3034354    19-MAR-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_9fxmx6s1_.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3       Full    207.82M    DISK        00:00:55     19-MAR-16      
        BP Key: 3   Status: AVAILABLE  Compressed: YES  Tag: TAG20160319T082704
        Piece Name: /u01/app/oracle/fast_recovery_area/ORCL/backupset/2016_03_19/o1_mf_nnndf_TAG20160319T082704_cgtkdrdh_.bkp
  List of Datafiles in backup set 3
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 3035302    19-MAR-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_9fxmx6s1_.dbf

RMAN> 
CDB$ROOT@ORCL> l
  1  select status, command_id, to_char(start_time,'DD-MON HH24:MI') Start_At, to_char(end_time,'DD-MON HH24:MI') End_At,
  2  trunc(input_bytes/1048576) Input_MB, trunc(output_bytes/1048576) Output_MB, input_type
  3  from v$rman_backup_job_details
  4* order by start_time
CDB$ROOT@ORCL> /

STATUS              COMMAND_ID                START_AT          END_AT             INPUT_MB OUTPUT_MB
------------------- ------------------------- ----------------- ----------------- --------- ---------
INPUT_TYPE
-------------
COMPLETED           2016-03-19T08:12:02       19-MAR 08:12      19-MAR 08:14           1807       402
DB FULL

COMPLETED           2016-03-19T08:18:29       19-MAR 08:27      19-MAR 08:28            803       225
DATAFILE FULL


CDB$ROOT@ORCL> 
CDB$ROOT@ORCL> !du -sh /u01/app/oracle/fast_recovery_area/ORCL/backupset/2016_03_19/o1_mf_nnndf_TAG20160319T082704_cgtkdrdh_.bkp
208M /u01/app/oracle/fast_recovery_area/ORCL/backupset/2016_03_19/o1_mf_nnndf_TAG20160319T082704_cgtkdrdh_.bkp

CDB$ROOT@ORCL> 


Note the slight discrepancy in reported backuppiece size.  The LIST BACKUP shows it as 207.82MB,  the query on v$rman_backup_job_details dividing the output_bytes by 1048576 shows the size as 225MB (truncating to units) while the OS level "du -sh" command shows it as 208MB.
.
.
.

13 March, 2016

Compression -- 3 : Index (Key) Compression

Unlike Table Compression that uses deduplication of column values, Index Compression is based on the keys.  Key Compression is also called Prefix Compression.

This relies on repeated leading key values being eliminated.  Thus, for example, if the leading column of the composite index has frequently repeated values and because an Index is always an organised (sorted) structure, we find the repeated values appearing as if "sequentially".  Key Compression can eliminate the repeated values.

Thus, it becomes obvious that Index Key Compression is usable for
a.  A Composite Index of 2 or more columns
b.  Repeated appearances of values in the *leading* key columns
c.  Compression defined for a maximum of n-1 columns  (where n is the number of columns in the index).  That is, the last column cannot be compressed.
Note that a Non-Unique Index automatically has the ROWID appended to it, so Key Compression can be applied to all the columns defined.

Let's look at a few examples.

Starting with creating a fairly large table (that is a multiplied copy of DBA_OBJECTS)

PDB1@ORCL> create table target_data as select * from source_data where 1=2;

Table created.

PDB1@ORCL> insert /*+ APPEND */ into target_data select * from source_data;

364496 rows created.

PDB1@ORCL> commit;

Commit complete.

PDB1@ORCL> insert /*+ APPEND */ into target_data select * from source_data;

364496 rows created.

PDB1@ORCL> commit;

Commit complete.

PDB1@ORCL> insert /*+ APPEND */ into target_data select * from source_data;

364496 rows created.

PDB1@ORCL> commit;

Commit complete.

PDB1@ORCL> 
PDB1@ORCL> desc target_data
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(128)
 OBJECT_NAME                                        VARCHAR2(128)
 SUBOBJECT_NAME                                     VARCHAR2(128)
 OBJECT_ID                                          NUMBER
 DATA_OBJECT_ID                                     NUMBER
 OBJECT_TYPE                                        VARCHAR2(23)
 CREATED                                            DATE
 LAST_DDL_TIME                                      DATE
 TIMESTAMP                                          VARCHAR2(19)
 STATUS                                             VARCHAR2(7)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)
 NAMESPACE                                          NUMBER
 EDITION_NAME                                       VARCHAR2(128)
 SHARING                                            VARCHAR2(13)
 EDITIONABLE                                        VARCHAR2(1)
 ORACLE_MAINTAINED                                  VARCHAR2(1)

PDB1@ORCL> 


What composite index is a good candidate for Key Compression ?
*Not* an Index that begins with OBJECT_ID as that is a Unique value.

Let's compare two indexes (compressed and non-compressed) on (OWNER, OBJECT_TYPE, OBJECT_NAME).

PDB1@ORCL> create index target_data_ndx_1_comp on
  2  target_data (owner, object_type, object_name)  compress 2;

Index created.

PDB1@ORCL> exec dbms_stats.gather_index_stats('','TARGET_DATA_NDX_1_COMP');

PL/SQL procedure successfully completed.

PDB1@ORCL> select leaf_blocks
  2  from user_indexes
  3  where index_name = 'TARGET_DATA_NDX_1_COMP'
  4  /

LEAF_BLOCKS
-----------
       5629

PDB1@ORCL> 


PDB1@ORCL> drop index target_data_ndx_1_comp
  2  /

Index dropped.

PDB1@ORCL> create index target_data_ndx_2_nocomp on
  2  target_data (owner, object_type, object_name) ;

Index created.

PDB1@ORCL> exec dbms_stats.gather_index_stats('','TARGET_DATA_NDX_2_NOCOMP');

PL/SQL procedure successfully completed.

PDB1@ORCL> select leaf_blocks
  2  from user_indexes
  3  where index_name = 'TARGET_DATA_NDX_2_NOCOMP'
  4  /

LEAF_BLOCKS
-----------
       7608

PDB1@ORCL> 


Note the "compress 2" specification for the first index.  That is an instruction to compress based on the leading 2 columns.
Thus, the compressed index is 5,629 blocks but the normal, non-compressed index is 7,608 blocks.  We make a gain of 26% in the index size.

Why did I choose OWNER, OBJECT_TYPE as the leading columns ?  Because I expected a high level of repetition on these column names.


Note : I have not explored Advanced Index Compression available in 12.1.0.2
Advanced Index Compression tested in 12.1.0.2
.
.

06 March, 2016

COMPRESSION -- 2 : Compressed Table Partitions

A Partitioned Table can choose to have a mix of COMPRESS and NOCOMPRESS Partitions.

As in this case where the first 3 partitions are defined as COMPRESS and the last as NOCOMPRESS :  :

[oracle@localhost Hemant]$ sqlplus hemant/hemant@orcl

SQL*Plus: Release 12.1.0.2.0 Production on Sun Mar 6 23:20:19 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Sun Mar 06 2016 23:19:11 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> @create_SALESHIST
SQL> spool create_SALESHIST
SQL> 
SQL> drop table SALESHIST;

Table dropped.

SQL> 
SQL> alter session set "_partition_large_extents"=FALSE;

Session altered.

SQL> 
SQL> create table SALESHIST
  2   (
  3    PROD_ID NUMBER NOT NULL ,
  4    CUST_ID NUMBER NOT NULL ,
  5    TIME_ID DATE NOT NULL ,
  6    CHANNEL_ID NUMBER NOT NULL ,
  7    PROMO_ID NUMBER NOT NULL ,
  8    QUANTITY_SOLD NUMBER(10,2) NOT NULL ,
  9    AMOUNT_SOLD NUMBER(10,2) NOT NULL
 10   )
 11   NOCOMPRESS LOGGING
 12    TABLESPACE USERS
 13    PARTITION BY RANGE (TIME_ID)
 14   (
 15    PARTITION SALES_1998
 16    VALUES LESS THAN (TO_DATE('1999-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
 17        'NLS_CALENDAR=GREGORIAN'))
 18    SEGMENT CREATION IMMEDIATE
 19    COMPRESS BASIC NOLOGGING
 20    TABLESPACE SALES_1998 ,
 21    PARTITION SALES_1999
 22    VALUES LESS THAN (TO_DATE('2000-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
 23        'NLS_CALENDAR=GREGORIAN'))
 24    SEGMENT CREATION IMMEDIATE
 25    COMPRESS BASIC NOLOGGING
 26    TABLESPACE SALES_1999 ,
 27    PARTITION SALES_2000
 28    VALUES LESS THAN (TO_DATE('2001-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
 29        'NLS_CALENDAR=GREGORIAN'))
 30    SEGMENT CREATION IMMEDIATE
 31    COMPRESS BASIC NOLOGGING
 32    TABLESPACE SALES_2000 ,
 33    PARTITION SALES_2001
 34    VALUES LESS THAN (TO_DATE('2002-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
 35        'NLS_CALENDAR=GREGORIAN'))
 36    SEGMENT CREATION IMMEDIATE
 37    NOCOMPRESS NOLOGGING
 38    TABLESPACE SALES_2001 )
 39  /

Table created.

SQL> 
SQL> spool off
SQL> 
SQL> col partition_name format a30
SQL> select partition_name, compression, compress_for
  2  from user_tab_partitions
  3  where table_name = 'SALESHIST'
  4  order by partition_position;

PARTITION_NAME         COMPRESS COMPRESS_FOR
------------------------------ -------- ------------------------------
SALES_1998         ENABLED BASIC
SALES_1999         ENABLED BASIC
SALES_2000         ENABLED BASIC
SALES_2001         DISABLED

SQL> 
SQL> select compression, compress_for
  2  from user_tables
  3  where table_name = 'SALESHIST';

COMPRESS COMPRESS_FOR
-------- ------------------------------


SQL> 


The Compression attributes actually apply to the Segments so the Partition Segments have the definition but the Table, being segmentless, does not show the definition.

Note that I am still demonstrating BASIC Compression. So the compression is applied only on Direct Path INSERT.  As in :

SQL> insert /*+ APPEND */ into saleshist select * from sh.sales;

918843 rows created.

SQL> commit;

Commit complete.

SQL> 
SQL> select partition_name, tablespace_name, bytes/1024
  2  from user_segments
  3  where segment_name = 'SALESHIST'
  4  and segment_type = 'TABLE PARTITION'
  5  order by 1;

PARTITION_NAME         TABLESPACE_NAME        BYTES/1024
------------------------------ ------------------------------ ----------
SALES_1998         SALES_1998       3072
SALES_1999         SALES_1999       4096
SALES_2000         SALES_2000       4096
SALES_2001         SALES_2001      11264

SQL> 


This demonstrates that it is possible to
a) Have some Partitions defined as COMPRESSed and others as NOCOMPRESSed
b) Create different Partitions in different Tablespaces

Before the advent of (and still without using) the 12c ILM features, this was and is a method to manage historical data with compression and moving or placing data (i.e. the respective tablespace datafile(s)) in low-cost storage as desired.

Note : For the purposes of this demo, I used the parameter "_partition_large_extents"=FALSE.  Oracle's default Partition size since 11.2.0.2 has been 8MB for Table Partitions and that would have been excessively large for this demo.  If you are using Hybrid Columnar Compression and/or Exadata, Oracle advises against this.
.
.
.

05 March, 2016

Recent Blog Series on (SQL) Tracing

These have been my recent blog posts on (SQL) Tracing

1.  1.  Generating SQL Traces (own Session)  (Sep-15)

2.  2.  Generating SQL Traces  (another Session)  (Sep-15)

3.  3.  Tracing for Specific SQLs  (Oct-15)

4.  4.  Identifying a Trace File  (Oct-15)

5.  5.1.  Reading an SQL Trace  (Oct-15)

6.  5.2  Interpreting the SQL Trace Summary Level  (Oct-15)

7.  6.  Multiple Executions of the same SQL  (Nov-15)

8.  7.  SQL in PL/SQL  (Nov-15)

9.  8a.  Using SQL Trace for Performance Evaluations  (Nov-15)

10.  8b.  More Performance Evaluation from Trace File  (Nov-15)

11,  8c.  Still More Performance Evaluation from Trace File  (Nov-15)

12.  8d.  Full Table Scans  (Dec-15)

13.  9.  Advantages  (Dec-15)

14.  10.  Tracing DML  (Dec-15)

15.  10b.  More DML Tracing  (Dec-15)

16.  10c.  Query and DML (INSERT)  (Jan-16)

17.  11.  Tracing the Optimization of an SQL Statement  (Jan-16)

18.  11b.  Using DBMS_SQLDIAG to trace the Optimization of an SQL Statement  (Feb-16)

19.  12.  Tracing a Particular Process  (Feb-16)

.
.
.


Recent Blog Series on RMAN

These have been my recent blog posts on RMAN

1. 1  Backup Job Details  (Jun-15)

2. 2.  ArchiveLog Deletion Policy  (Jun-15)

3. 3.  The DB_UNIQUE_NAME in Backups to the FRA  (Jun-15)

4.  4.  Recovering from an Incomplete Restore  (Jul-15)

5.  4b. Recovering from an Incomplete Restore with OMF Files  (Jul-15)

6.  5.  Useful Keywords and SubClauses  (Jul-15)

7.  5b.  More Useful Keywords and SubClauses  (Jul-15)

8.  5c.  Some More Useful Keywords and SubClauses  (Jul-15)

9.  6.  Retention Policy and CONTROL_FILE_RECORD_KEEP_TIME  (Aug-15)

10.  7.  Recovery through RESETLOGS -- How are the ArchiveLogs Identified ?  (Aug-15)

11.  8.  Using a Recovery Catalog Schema  (Aug-15)

12.  9.  Querying the RMAN Views / Catalog  (Sep-15)

13.  10. VALIDATE  (Sep-15)

14.  Unused Block Compression and Null Block Compression  (Feb-16)


(This list does not include RMAN Blog Posts made before June 2015)
,
,
,

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016