Interval Partitioning Does Not Inherit Logging (Doc ID 959116.1)

Last updated on FEBRUARY 02, 2017

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

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms