Frank van Bortel wrote:
> DA Morgan wrote:
>
>> Andrew Clark wrote:
>>
>>> DA Morgan <damorgan_at_x.washington.edu> wrote in
>>> news:1109866942.93326_at_yasure:
>>>
>>>> Your original hunch seems valid on its face.
>>>>
>>>> Try the following query:
>>>>
>>>> SELECT df.tablespace_name, SUM(df.bytes) TOTAL_SPACE,
>>>> SUM(fs.bytes) FREE_SPACE,
>>>> ROUND(((NVL(SUM(fs.bytes),0)/SUM(df.bytes))*100),2) PCT_FREE
>>>> FROM dba_free_space fs, dba_data_files df
>>>> WHERE df.tablespace_name = fs.tablespace_name (+)
>>>> GROUP BY df.tablespace_name
>>>> ORDER BY df.tablespace_name;
>>>>
>>>> These and other queries are available at http://www.psoug.org
>>>> click on Morgan's Library
>>>
>>>
>>>
>>>
>>> Thanks, that looks like some helpful stuff. On the tablespaces that I
>>> got an error 1659 on, here is the output of your second script:
>>>
>>> TABLESPACE_NAME TOTAL_SPACE FREE_SPACE PCT_FREE
>>> --------------- ----------- ---------- ----------
>>> ARDAT 209715200 16646144 7.94
>>> SDDAT 104857600 27459584 26.19
>>> SADAT 83886080 25034752 29.84
>>> SFIDX 131072000 0
>>> TIMEIDX 20971520 5177344 24.69
>>>
>>> So, obviously, SFIDX needs more space allocated to it. But why are
>>> the others failing when it looks like there is ample space?
>>>
>>> Andrew
>>
>>
>>
>> Free space does necessarily equate to usable. Any chance the
>> extent size be requested is larger than the free space available?
>> And is that free space fragmented such that no continguous space
>> is as large as what is being requested?
>>
>> My guess is that if you solve the problem with SFIDX you will be
>> fine. But why the separation of tablespaces for data and index?
>> Hopefully not following some mythological nonsense published a
>> decade ago. Also ... is this LMT or DMT?
>
>
> DMT, as I understand it, pct_increase=50...
Create a new tablespace, EXTENT MANAGEMENT LOCAL UNIFORM SIZE,
and PCT_INCREASE=0 (it should ALWAYS be 0). Then calculate an
appropriate PCTFREE and PCTUSED for each table, set PCTFREE to
0 for primary key indexes.
Then recreate the objects in the new tablespace and finally drop
the old.
Likely you have tons of wasted space and you will also see an
improvement in performance too: Possibly quite substantial.
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)
Received on Fri Mar 04 2005 - 08:58:35 CST
