Re: Parent table RO -- can't write to child?

Here's a little test in 9iR2:

SQL> alter table emp move tablespace USERS; Table altered.

SQL> alter index pk_emp rebuild tablespace USERS; Index altered.

SQL> alter table dept move tablespace barney; Table altered.

SQL> alter index pk_dept rebuild tablespace BARNEY; Index altered.

SQL> alter tablespace barney read only;

So EMP is in USERS, read-write. DEPT is in BARNEY, read-only. DEPT is of course the parent table for EMP:

SQL> insert into emp (empno, ename, deptno)   2 values (4355,'SMITH',14);
insert into emp (empno, ename, deptno)
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.FK_DEPTNO) violated - parent key not found

So, let's try your little test: insert a record into the child table and see if the fact the parent is in read only tablespace causes problems:

SQL> insert into emp (empno, ename, deptno)   2 values (4354,'SMITH',10);

1 row created.

Nope. You are perfectly correct: when you insert a child record, we need to be able to check that the department number being assigned to the new record exists in the parent table. But it doesn't need to be modified, and there is zero harm in the parent table therefore being in read only tablespace.

Yet you have a problem... so what could it be? Well, since no version of Oracle was mentioned (nor a platform) it is conceivable that what I've just done is only true in 9i, and that earlier releases suffered from this problem. But I doubt it (especially since I remember demonstrating this in 8.0). It might be O/S specific (another thing you didn't mention), and so my test could work in Windows (which I use) but fail on some other platform. But I doubt it, since the 8.0 demo was done on Solaris.

You might not be using SQL*Plus. Quite often, when I write Access front-ends to applications, I can't be bothered working out which field has been modified on a form. So I just iterate through all the fields, and update the entire record. You *think* you've just updated a 'QUANTITY' field, but my form issues a SQL statement along the lines of 'update SALES set col1=field1, col2=field2, col3=field4,QUANTITY=field5;' etc). Possibly your application (which again you didn't provide details of) is doing that. So even though you *think* you're only inserting values in fields X, Y and Z, actually the application is saving fields A, B and C in a different table. Possible, but I can't tell from the information you provided.

Are you sure that you simply made the parent table's tablespace read only, and that it hasn't been taken offline in any way, shape or form? For example:

SQL> alter tablespace barney offline;
Tablespace altered.

SQL> insert into emp (empno, ename, deptno)   2 values (4359,'SMITH',10);
insert into emp (empno, ename, deptno)

            *
ERROR at line 1:
ORA-00376: file 14 cannot be read at this time ORA-01110: data file 14: 'D:\ORACLE\ORADATA\OEMREP\BARNEY1.DBF'

Incidentally, your comment about the index on the parent table being used for the check is very perceptive (especially in 9i, where multiple DML statements on the child table cause 256 values from the primary key of the parent table to be cached in memory):

SQL> alter tablespace barney online;
Tablespace altered.

SQL> alter index pk_dept rebuild tablespace users; Index altered.

SQL> alter tablespace barney offline;
Tablespace altered.

SQL> insert into emp (empno, ename, deptno)   2 values (4359,'SMITH',10);

1 row created.

So, with BARNEY offline, but the index in the USERS tablespace (which is still online, of course), the check on department 10 being a valid department uses the *index* alone, and thus succeeds, even when the parent table itself is unavailable for use.

Regards
HJR "Nobody Important" <CanTheMongoose_at_hotmail.com> wrote in message news:6d951625.0301171404.4d58126e_at_posting.google.com...
> I don't quite understand what's happening....
>
> I have two tables, foo and bar. Foo is in a read only tablespace, as
> are his indexes. Bar is in a read write tablespace, as are his
> indexes. Bar has a foreign key to foo.
>
> When I try to insert a record into bar, I get the messages:
>
> ORA-00372: file 73 cannot be modified at this time.
> ORA-01110: data file 73: '/u13/oradata/foobar/foo_idx.dbf'
>
> But I'm not modifying foo -- or if I am, I don't see how. Oracle
> should check to see that the value I'm trying to insert into bar
> actually exists in foo, and I can even see how it would use the index
> and not the table to do that. But I'm not modifying foo's index, just
> reading it.
>
> Can someone explain what I'm doing wrong? And perhaps a suggestion on
> how to do it right?
>
> Thank you,
> Eric
Received on Fri Jan 17 2003 - 16:27:52 CST