Re: Space explanation

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