As every site, our has development and production databases. I've been
told to transport approximately 20GB of data between them and innocently
asked if I can finish that in an hour. Sure, heck, why not, I have a
gigabit switch between 2 machines and I sure can copy 20GB in an hour.
So, I execute dbms_tts.transport_set_check and transport_set_violations
report one table in another tablespace being referenced by a foreign key.
No sweat, table isn't very big, I can move stuff around, and I do
alter table move to the correct tablespace. Everything finishes OK.
I then re-execute dbms_tts.transport_set_check and transport_set_violations
report the very same table, again, being referenced by a foreign key, this
time in the tablespace SYSTEM!!!!!
I select tablespace_name from dba_tables where the table is the one that I
just have moved and the tablespace name was EMPTY! Dba_segments was OK,
but in dba_tables, tablespace_name was empty and oracle assumed that it
means "SYSTEM". I had to drop the table and re-create it, together with all
foreign keys pointing to it. What a pain! Eventually, it took me
about an hour. What a pain! The "feature" is that DBA_TABLES and USER_TABLES
(actually, SYS.TAB$) are not correctly maintained after "alter table move".
The new tablespace is simply not entered. Oh, well, one can't have it all.
Needless to say, I'll report a TAR.
Please see the official ORACLE-L FAQ:
http://www.orafaq.com
To unsubscribe send email to: oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at
http://www.freelists.org/archives/oracle-l/
FAQ is at
http://www.freelists.org/help/fom-serve/cache/1.html
Received on Fri Feb 20 2004 - 15:21:21 CST
