Re: Create table across dblink gives ORA-01536

sphealey_at_worldnet.att.net (sPh) wrote in message news:<a1e85ad4.0408060651.6d531d49_at_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

sPh, based on the information given the current oracle user is trying to create table t99 in their default tablespace as a CTAS from a remote view. The error messages make it appear that Oracle is attempting to create the table on DCMS_SWDATA. Since DDL over a link is not supported perhaps the messages are slightly messed up.

Verify the user has sufficient quota on their default tablespace and try adding a tablespace parameter to the CTAS statement.

HTH -- Mark D Powell -- Received on Fri Aug 06 2004 - 15:21:21 CDT