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_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org]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_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
> -----------------------------------------------------------------
>=20
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 - 18:02:02 CST
