Create table across dblink gives ORA-01536

From: sPh <sphealey_at_worldnet.att.net>
Date: 6 Aug 2004 07:51:42 -0700
Message-ID: <a1e85ad4.0408060651.6d531d49@posting.google.com>

SQL> select * from v$version;
Oracle8 Enterprise Edition Release 8.0.5.2.1 - Production PL/SQL Release 8.0.5.2.0 - Production
CORE Version 4.0.5.0.0 - Production
TNS for 32-bit Windows: Version 8.0.5.0.0 - Production NLSRTL Version 3.3.2.0.0 - Production

---

I have the following view on database R:

   CREATE OR REPLACE FORCE VIEW SWDATA.SW_BOL_INFO    (PRO_NUMBER, ID, TRAILER_ID, SEAL_ID, CUSTOMER_ID,     SHIPTO_ID, OCN_ID, TRANSACTION_DATE)    AS

   select  bs.pro_number,
           bl.id,
           it.trailer_id,
           it.seal_id,
           bs.customer_id,
           bs.shipto_id,
           it.ocn_id,
           it.transaction_date
   from    inventory_trans it,
           bol bl,
           bol_shipto bs
   where   it.type = 'SHP'
   and     it.trailer_id = bl.trailer_id
   and     it.seal_id = bl.seal_id
   and     bl.id = bs.bol_id

   with read only;

The inventory_trans table in particular is quite large and this view cuts down the size by 90%. The view has excellent performance at database R.

In database L I have a dblink to R and a synonym SW_D_BOL_INFO to the view at database R.

When I try to create a table at L by selecting from the view at R, I get the following error:

   SQL> create table t99

     2    as
     3      select * from sw_d_bol_info
     4        where transaction_date > sysdate - 240;

   create table t99
   *
   ERROR at line 1:
   ORA-01536: space quota exceeded for tablespace ''    ORA-02063: preceding line from DCMS_SWDATA

By walking it up, I found that the error starts around 200 days of data, which represents about 500,000 records.

Both schemas involved have plenty of tablespace quota left - about 10 times the amount needed to run the CREATE TABLE query at R. And both have unlimited quota on their respective temporary data tablespaces.

Any idea what would be the cause of this? I would gladly increase any necessary quotas, but I don't know exactly what tablespace '' means!

sPh Received on Fri Aug 06 2004 - 09:51:42 CDT