Mark D Powell wrote:
> 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 --
> The semantics get quite convoluted once dblinks are involved. The table
> was being created in the local schema, not the remote. The size of the
> table was ~100 MB when all was done. In order to make that happen, I
> had to raise the quota on the REMOTE user's schema to 512 MB. If anyone
> could help me with that one I would be obliged!
>
> sPh
Version 8.0.5 is terribly out of date, as you probably know, so don't
expect much.
Try an "EXPLAIN PLAN" to see what part of your distributed query is
running at the remote site. IIRC, a user's designated temporary
tablespace and a tablespace of type TEMPORARY are not the same thing.
Try putting the WHERE clause directly in the remote view, or experiment
with running the CTAS without the view, but instead directly against
inventory_trans_at_dblink, bol_at_dblink, etc. (or synonyms for them).
--Mark Bole
Received on Sun Aug 08 2004 - 14:13:39 CDT
