Re: Space explanation

As always Jonathan, you got the root of the issue. Thanks a lot. No, I did not collect stats after truncating. Learned one more thing new today. Come to think of it, it sounds obvious.

On Tue, May 5, 2020 at 2:36 PM Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:

>
> truncate doesn't reset the stats to zero.
>
> QL> select blocks, num_rows from user_tables;
>
> BLOCKS NUM_ROWS
> ---------- ----------
> 1251 61768
>
> 1 row selected.
>
> SQL> truncate table t1;
>
> Table truncated.
>
> SQL> select blocks, num_rows from user_tables;
>
> BLOCKS NUM_ROWS
> ---------- ----------
> 1251 61768
>
> 1 row selected.
>
> SQL> execute dbms_stats.gather_table_stats(user,'t1')
>
> PL/SQL procedure successfully completed.
>
> SQL> select blocks, num_rows from user_tables;
>
> BLOCKS NUM_ROWS
> ---------- ----------
> 0 0
>
> 1 row selected.
>
>
> Did you collect stats between truncating and querying the stats the second
> time ?
>
> Regards
> Jonathan Lewis
>
>
> On Tue, May 5, 2020 at 8:17 PM Ram Raman <veeeraman_at_gmail.com> wrote:
>
>>
>> Thanks all. TRUNCATE did not do it. Dropped and renamed it, we are good
>> now. I am surprised I had to make a copy of the table, drop original and
>> rename the copy.
>>
>> 1* select owner, table_name, blocks, num_rows from dba_tables where
>> tablespace_name = 'DBA'
>> 14:09:19 SQL> /
>>
>> TABLE_NAME BLOCKS NUM_ROWS
>> ------------------------ ---------- --------------------
>> ....
>> WVCE 3444463 0
>>
>> Elapsed: 00:00:00.02
>> 14:09:19 SQL>
>> 14:09:20 SQL> truncate table WVCE ;
>>
>> Table truncated.
>>
>> Elapsed: 00:00:00.03
>> 14:09:29 SQL> select owner, table_name, blocks, num_rows from dba_tables
>> where tablespace_name = 'DBA';
>>
>>
>> TABLE_NAME BLOCKS NUM_ROWS
>> ------------------------ ---------- --------------------
>> ...
>> WVCE 3444463 0
>>
>>
>> On Tue, May 5, 2020 at 1:26 PM Mladen Gogala <gogala.mladen_at_gmail.com>
>> wrote:
>>
>>> Delete or rollback do not free space. There are ways to free space:
>>>
>>> - truncate
>>> - alter table compact
>>> - alter table move
>>> - drop table
>>> - mkfs
>>>
>>> It is perfectly possible to have 32GB table consuming the entire
>>> tablespace. That is known as "agile methodology" because it forces the DBA
>>> to be agile and keep checking space.
>>> Regards
>>>
>>> On Tue, 2020-05-05 at 11:21 -0500, Ram Raman wrote:
>>>
>>> Hi
>>>
>>> I created a table for testing and inserting rows into it when the insert
>>> process failed as the tablespace reached maximum limit of 32G - that
>>> happened couple of days ago. However, there are no rows in the table, but
>>> space occupied shows as real high with the tablespace itself full. Does
>>> anyone have an idea? There are couple of other tables in the tablespace but
>>> they occupy just few 100 blocks. 12c
>>>
>>> 1 select table_name, NUM_ROWS, last_analyzed, blocks from dba_tables
>>> 2* where table_name = upper('wvce')
>>> 11:14:17 SQL> /
>>>
>>> TABLE_NAME NUM_ROWS LAST_ANAL BLOCKS
>>> ------------------------ -------------------- --------- ----------
>>> WVCE 0 03-MAY-20 3444463
>>>
>>> Elapsed: 00:00:00.09
>>> 11:14:17 SQL>
>>> 11:14:28 SQL> select count(*) from WVCE ;
>>>
>>> COUNT(*)
>>> --------------------
>>> 0
>>>
>>> Elapsed: 00:00:00.00
>>> 11:14:35 SQL>
>>> --
>>>
>>> Thanks
>>> Ram
>>>
>>> --
>>>
>>> Mladen Gogala
>>> Database Consultant
>>> Tel: (347) 321-1217
>>> Email:gogala.mladen_at_gmail.com
>>>
>>
>>
>> --
>>
>>
>>

--

--
http://www.freelists.org/webpage/oracle-l