Delete Record From A Partitioned Table When One Of The Tablespaces Is Offlined (Doc ID 1345904.1)

Last updated on FEBRUARY 02, 2017

Applies to:

Oracle Server - Enterprise Edition - Version: 11.1.0.7 and later   [Release: 11.1 and later ]
Information in this document applies to any platform.

Goal

Suppose , We have a partitioned table SALES_LIST with total 4 partitions ( SALES_WEST, SALES_EAST , SALES_CENTRAL, SALES_OTHER) and one partition(SALES_WEST) is in one tablespace TEST_RO and rest 3 partitions( SALES_EAST , SALES_CENTRAL, SALES_OTHER) are in other tablespace TEST_RW. Now we want to delete a row from this table at table level,not partition level when Tablespace TEST_RO is offline but we are not able to do it. and getting below error


SQL> delete from sales_list where SALES_AMOUNT = 2 and SALESMAN_ID =2;

ERROR at line 1:
ORA-00376: file 11 cannot be read at this time
ORA-01110: data file 11: '/app/oracle/oradata/test/test_ro.dbf'



Example:




SQL> create tablespace TEST_RO datafile '/app/oracle/oradata/test/test_ro.dbf' size 100m;

Tablespace created.



SQL> create tablespace TEST_RW datafile '/app/oracle/oradata/test/test_rw.dbf' size 100m;

Tablespace created.




SQL> CREATE TABLE sales_list
  (salesman_id NUMBER(5),
  sales_state VARCHAR2(20),
sales_amount NUMBER(10))
   PARTITION BY LIST(sales_state)
(
  PARTITION sales_west VALUES('California', 'Hawaii') tablespace test_ro,
  PARTITION sales_east VALUES ('New York', 'Virginia', 'Florida') tablespace test_rw,
  PARTITION sales_central VALUES('Texas', 'Illinois') tablespace test_rw,
PARTITION sales_other VALUES(DEFAULT) tablespace test_rw
  );

Table created.

SQL> create index sales_list_idx on sales_list(sales_state) local;

Index created.

SQL> SELECT * FROM SALES_LIST;

SALESMAN_ID SALES_STATE SALES_AMOUNT
----------- -------------------- ------------
1 California 1
2 New York 2
3 Illinois 3
4 SANJAY 4





SQL> select TABLE_OWNER,TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME from dba_tab_partitions where TABLE_NAME='SALES_LIST';

TABLE_NAME PARTITION_NAME TABLESPACE_NAME
------------------ ------------------------- ------------------------------
SALES_LIST SALES_WEST TEST_RO
SALES_LIST SALES_EAST TEST_RW
SALES_LIST SALES_CENTRAL TEST_RW
SALES_LIST SALES_OTHER TEST_RW


and the local index is partitioned to below partition respectively:



SQL> SELECT INDEX_OWNER,INDEX_NAME,PARTITION_NAME,TABLESPACE_NAME FROM DBA_IND_PARTITIONS WHERE INDEX_NAME='SALES_LIST_IDX';

INDEX_NAME PARTITION_NAME TABLESPACE_NAME
------------------------ ------------------------------ ------------------------------
SALES_LIST_IDX SALES_WEST TEST_RO
SALES_LIST_IDX SALES_EAST TEST_RW
SALES_LIST_IDX SALES_CENTRAL TEST_RW
SALES_LIST_IDX SALES_OTHER TEST_RW


Now , we want to delete rows from SALES_EAST partition of SALES_LIST table by below command when test_ro tablespace is offlined.


SQL> alter tablespace test_ro offline;

Tablespace altered.


SQL> delete from sales_list where SALES_AMOUNT = 2 and SALESMAN_ID =2;


ERROR at line 1:
ORA-00376: file 11 cannot be read at this time
ORA-01110: data file 11: '/app/oracle/oradata/test/test_ro.dbf'

Solution

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