Re: Space explanation

Truncate has 2 options: DROP STORAGE and REUSE STORAGE. I am not sure what is the default on your version.
On Tue, 2020-05-05 at 14:15 -0500, Ram Raman 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:
> > truncatealter table compactalter table movedrop tablemkfsIt 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 GogalaDatabase ConsultantTel: (347) 321-1217Email:
> > gogala.mladen_at_gmail.com
>
>

-- 
Mladen GogalaDatabase ConsultantTel: (347) 321-1217Email:gogala.mladen_at_gmail.com


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