CTAS using distributed query

From: Sindu <sindu_at_bigfoot.com>
Date: Mon, 05 Feb 2001 02:19:24 -0800
Message-ID: <F001.002AA6FD.20010205020035@fatcity.com>

Hi lists,
I have a very strange situation here.

In db A, I have table T1.
In db B, I have table T2.

I'm trying to create table T3 (CTAS) on db B based on join query between T1 & T2. We tuned the query to use remote view (because T1 is a very huge table, while T2 is small table), so we create view on db A:

create view T1T2_view as select ... from t1, t2_at_dbB where ...

and then we can do in db B:
select * from T1T2_view_at_dbA

This works fine, but when I tried to:
create table T3 as select * from T1T2_view_at_dbA, it returns no rows. I also tried precreating T3, and then:
insert into T3 as select * from T1T2_view_at_dbA, no rows inserted either. but I can select * from T1T2_view_at_dbA and I have rows returned.

Finally I'm using SQLPlus
copy from xxx/yyy_at_dba create t3 using select * from t1t2_view it works fine.

Why can't I use CTAS? Anybody has similar problem? We're running 8.0.5.2.1 on Solaris 2.6.

ps. I also have another tables/views similar to above setup, and I can CTAS on it, but not on the above table only.

regards,
Sindu

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Sindu
  INET: sindu_at_bigfoot.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Mon Feb 05 2001 - 04:19:24 CST