I don't get it. Are you saying that the tablespace name was "lost" after = an alter table move? I don't see that happening. SQL> select * from v$version ; BANNER ---------------------------------------------------------------- Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production PL/SQL Release 9.2.0.4.0 - Production CORE 9.2.0.3.0 Production TNS for Solaris: Version 9.2.0.4.0 - Production NLSRTL Version 9.2.0.4.0 - Production SQL> create table t (n number) tablespace users ; Table cr=E9=E9e. SQL> select table_name, tablespace_name from user_tables 2 where table_name =3D 'T' ; TABLE_NAME TABLESPACE_NAME ------------------------------ ------------------------------ T USERS SQL> alter table t move tablespace data ; Table modifi=E9e. SQL> select table_name, tablespace_name from user_tables 2 where table_name =3D 'T' ; TABLE_NAME TABLESPACE_NAME ------------------------------ ------------------------------ T DATA SQL> column object_name format a30 SQL> select a.object_name, c.name as tablespace_name 2 from user_objects a, sys.tab$ b, sys.ts$ c 3 where a.object_name =3D 'T' and a.object_type =3D 'TABLE' 4 and b.obj# =3D a.object_id 5 and c.ts# =3D b.ts# ; OBJECT_NAME TABLESPACE_NAME ------------------------------ ------------------------------ T DATA > -----Original Message----- > From: oracle-l-bounce@xxxxxxxxxxxxx > [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Mladen Gogala > Sent: vendredi, 20. f=E9vrier 2004 12:23 > To: Multiple recipients of list ORACLE-L > Subject: Annoying 9.2.0.4 "feature" >=20 >=20 > As every site, our has development and production databases. I've been > told to transport approximately 20GB of data between them and=20 > innocently > asked if I can finish that in an hour. Sure, heck, why not, I have a=20 > gigabit switch between 2 machines and I sure can copy 20GB in an hour. > So, I execute dbms_tts.transport_set_check and=20 > transport_set_violations > report one table in another tablespace being referenced by a=20 > foreign key. > No sweat, table isn't very big, I can move stuff around, and I do=20 > alter table move to the correct tablespace. Everything finishes OK. > I then re-execute dbms_tts.transport_set_check and=20 > transport_set_violations > report the very same table, again, being referenced by a=20 > foreign key, this > time in the tablespace SYSTEM!!!!! > I select tablespace_name from dba_tables where the table is=20 > the one that I=20 > just have moved and the tablespace name was EMPTY!=20 > Dba_segments was OK, > but in dba_tables, tablespace_name was empty and oracle=20 > assumed that it=20 > means "SYSTEM". I had to drop the table and re-create it,=20 > together with all > foreign keys pointing to it. What a pain! Eventually, it took me=20 > about an hour. What a pain! The "feature" is that DBA_TABLES=20 > and USER_TABLES > (actually, SYS.TAB$) are not correctly maintained after=20 > "alter table move". > The new tablespace is simply not entered. Oh, well, one can't=20 > 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@xxxxxxxxxxxxx > put 'unsubscribe' in the subject line. > -- > Archives are at //www.freelists.org/archives/oracle-l/ > FAQ is at //www.freelists.org/help/fom-serve/cache/1.html > ----------------------------------------------------------------- >=20 ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------