Skip to Main Content
  • Questions
  • Error with Read-Only partitions when inserting via SQL View

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Martin.

Asked: June 10, 2020 - 8:15 am UTC

Last updated: June 16, 2020 - 3:21 am UTC

Version: 18

Viewed 1000+ times

You Asked

Hello,
We had the following issue and we are seeking for your advise what can be done.
1) We have a very large tables (Finance applications, i.e. millions of lines) so we decided to use Partitions to improve performance and also to archive old data
2) We defined the old partitions as Read-Only to avoid changes - there should be no changes.
3) We are inserting new data into the partitioned table via the SQL View (application logic) with a current date - so it should be stored into read write partition
4) We get an error SQL Error: "ORA-14466: Data in a read-only partition or subpartition cannot be modified"
If we change the partitions to R/W, there is no issue. One R/O partition and the issue appears. Even if inserting is surely somewhere else.
If we change the SQL View name to a table name in insert statement (it is just restriction of columns), there is no issue with R/O partitions.

Are there any settings of DB or of the partitioned table which allow us the usage of R/O partitions despite the SQL views used for inserting?

Thanks


and Connor said...

Sorry - we need to see a test case, because I can't replicate your findings


SQL>
SQL> create table t
  2  ( d date,
  3    id int,
  4    data varchar2(20)
  5  )
  6  partition by range (d )
  7  (
  8    partition p1 values less than ( date '2018-01-01' ),
  9    partition p2 values less than ( date '2019-01-01' ),
 10    partition p3 values less than ( date '2020-01-01' ),
 11    partition p4 values less than ( date '2021-01-01' )
 12  );

Table created.

SQL>
SQL> insert into t
  2  select date '2017-01-01' + rownum, rownum, 'data'||rownum
  3  from dual connect by level <= date '2021-01-01' - date '2017-01-01' - 1;

1460 rows created.

SQL>
SQL> alter table t modify partition p1 read only;

Table altered.

SQL> alter table t modify partition p2 read only;

Table altered.

SQL>
SQL> create or replace
  2  view VW as
  3  select d,id from t
  4  where d > date '2020-01-01';

View created.

SQL>
SQL> delete from VW where id = 1100;

1 row deleted.

SQL> insert into VW values ( sysdate, 100);

1 row created.

SQL> insert into VW values ( date '2018-06-01', 100);
insert into VW values ( date '2018-06-01', 100)
            *
ERROR at line 1:
ORA-14466: Data in a read-only partition or subpartition cannot be modified.


SQL>
SQL>


Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks

Martin Heisler, June 11, 2020 - 11:55 am UTC

Hello, I would like to add the full details, but there is no way how to add files (the SQL definition is very long). I will create a new question with same content and attachments.

The test case does not work for me

David, June 11, 2020 - 1:09 pm UTC

Hi, when repeating the test case, I am getting ORA-14466 for first insert "insert into VW values ( sysdate, 100);" What might be wrong?
Connor McDonald
June 13, 2020 - 9:39 am UTC

Please send us your test case with full output exactly as we have done.

We don't have a crystal ball

here is the test case

David Manhal, June 15, 2020 - 12:03 pm UTC

SQL> set echo on
SQL> drop table t;

Table T dropped.

SQL> create table t
2 ( d date,
3 id int,
4 data varchar2(20)
5 )
6 partition by range (d )
7 (
8 partition p1 values less than ( date '2018-01-01' ),
9 partition p2 values less than ( date '2019-01-01' ),
10 partition p3 values less than ( date '2020-01-01' ),
11 partition p4 values less than ( date '2021-01-01' )
12 );

Table T created.

SQL>
SQL> insert into t
2 select date '2017-01-01' + rownum, rownum, 'data'||rownum
3 from dual connect by level <= date '2021-01-01' - date '2017-01-01' - 1;

1,460 rows inserted.

SQL>
SQL> alter table t modify partition p1 read only;

Table T altered.

SQL>
SQL> alter table t modify partition p2 read only;

Table T altered.

SQL>
SQL> create or replace
2 view VW as
3 select d,id from t
4 where d > date '2020-01-01';

View VW created.

SQL> delete from VW where id = 1100;

1 row deleted.

SQL> insert into VW values ( sysdate, 100);

Error starting at line : 29 in command -
insert into VW values ( sysdate, 100)
Error at Command Line : 29 Column : 13
Error report -
SQL Error: ORA-14466: Data in a read-only partition or subpartition cannot be modified.


Connor McDonald
June 16, 2020 - 3:21 am UTC

Looks like a version bug

19c
====

SQL>
SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

SQL>
SQL>  create table t
  2      ( d date,
  3        id int,
  4        data varchar2(20)
  5      )
  6      partition by range (d )
  7      (
  8        partition p1 values less than ( date '2018-01-01' ),
  9        partition p2 values less than ( date '2019-01-01' ),
 10       partition p3 values less than ( date '2020-01-01' ),
 11       partition p4 values less than ( date '2021-01-01' )
 12     );

Table created.

SQL>
SQL> insert into t
  2      select date '2017-01-01' + rownum, rownum, 'data'||rownum
  3      from dual connect by level <= date '2021-01-01' - date '2017-01-01' - 1;

1460 rows created.

SQL>
SQL> alter table t modify partition p1 read only;

Table altered.

SQL>
SQL> alter table t modify partition p2 read only;

Table altered.

SQL>
SQL>
SQL> create or replace
  2      view VW as
  3      select d,id from t
  4      where d > date '2020-01-01';

View created.

SQL>
SQL> delete from VW where id = 1100;

1 row deleted.

SQL>
SQL> insert into VW values ( sysdate, 100);

1 row created.



SQL>
SQL>

18c
====


SQL>
SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production

SQL>
SQL>  create table t
  2      ( d date,
  3        id int,
  4        data varchar2(20)
  5      )
  6      partition by range (d )
  7      (
  8        partition p1 values less than ( date '2018-01-01' ),
  9        partition p2 values less than ( date '2019-01-01' ),
 10       partition p3 values less than ( date '2020-01-01' ),
 11       partition p4 values less than ( date '2021-01-01' )
 12     );

Table created.

SQL>
SQL> insert into t
  2      select date '2017-01-01' + rownum, rownum, 'data'||rownum
  3      from dual connect by level <= date '2021-01-01' - date '2017-01-01' - 1;

1460 rows created.

SQL>
SQL> alter table t modify partition p1 read only;

Table altered.

SQL>
SQL> alter table t modify partition p2 read only;

Table altered.

SQL>
SQL>
SQL> create or replace
  2      view VW as
  3      select d,id from t
  4      where d > date '2020-01-01';

View created.

SQL>
SQL> delete from VW where id = 1100;

1 row deleted.

SQL>
SQL> insert into VW values ( sysdate, 100);
insert into VW values ( sysdate, 100)
            *
ERROR at line 1:
ORA-14466: Data in a read-only partition or subpartition cannot be modified.




Have a chat to Support - there might be patches available

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.