My Oracle Support Banner

Interval Partitioning Does Not Inherit Logging (Doc ID 959116.1)

Last updated on FEBRUARY 03, 2019

Applies to:

Oracle Server - Enterprise Edition - Version: 11.1.0.7 to 11.2.0.2 - Release: 11.1 to 11.2
Information in this document applies to any platform.
***Checked for relevance on 02-Aug-2011***

Symptoms

INTERVAL PARTITIONING : DOES NOT INHERIT LOGGING

Testcase :

-- Tablespace created with NOLOGGING

create tablespace TEST_TBS datafile '<datafile_name>' size 50m nologging;

-- Confirm Tablespace NOLOGGING status

select logging, force_logging from dba_tablespaces a where tablespace_name = 'TEST_TBS';

-- Create table with NOLOGGING in tablespace TEST_TBS

create table newsales
( prod_id number(6) not null
, time_id date not null
) NOLOGGING tablespace test_tbs
partition by range (time_id)
interval (numtodsinterval(1,'DAY'))
( partition p_before_1_jan_2005 values
less than (to_date('01-01-2005','dd-mm-yyyy')));

-- Confirm NOLOGGING for first partition P_BEFORE_1_JAN_2005

select table_name, partition_name, c.tablespace_name, c.logging from user_tab_partitions c
where table_name ='NEWSALES';

-- Force to create new partitions automatically with following inserts

insert into newsales values (11160,17450,to_date('01-jan-2005','dd-mon-yyyy'),'I',9999,19,798) ;
insert into newsales values (1340,33710,to_date('02-jan-2005','dd-mon-yyyy'),'S',9999,16,1264) ;
insert into newsales values (25270,65880,to_date('05-jan-2005','dd-mon-yyyy'),'I',9999,5,210) ;
insert into newsales values (1615,73480,to_date('05-jan-2005','dd-mon-yyyy'),'I',9999,8,96) ;
insert into newsales values (1900,84910,to_date('06-jan-2005','dd-mon-yyyy'),'I',9999,42,378) ;
insert into newsales values (8085,37900,to_date('09-jan-2005','dd-mon-yyyy'),'S',9999,1,68) ;
insert into newsales values (755,26590,to_date('09-jan-2005','dd-mon-yyyy'),'I',9999,11,132) ;
insert into newsales values (10,68060,to_date('09-jan-2005','dd-mon-yyyy'),'P',9999,28,4900) ;
insert into newsales values (13425,109310,to_date('10-jan-2005','dd-mon-yyyy'),'I',9999,1,68) ;
insert into newsales values (1955,65190,to_date('10-jan-2005','dd-mon-yyyy'),'S',9999,28,1512) ;

-- Check the LOGGING status of newly created partitions fron user_tab_partitions
-- LOGGING column will be YES for all the partitions except P_BEFORE_1_JAN_2005

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
  Symptoms
  Cause
  Solution
  References

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.