Re: Space explanation
Date: Tue, 5 May 2020 14:15:58 -0500
Message-ID: <CAHSa0M0PFSq=v0mu6_SS-W=Gbdrup4QGc_-TDVthyq9pvHny2Q_at_mail.gmail.com>
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'
Elapsed: 00:00:00.02
Table truncated.
Elapsed: 00:00:00.03
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:
14:09:19 SQL> /
TABLE_NAME BLOCKS NUM_ROWS
------------------------ ---------- --------------------
....
WVCE 3444463 0
14:09:19 SQL>
14:09:20 SQL> truncate table WVCE ;
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
>
> - 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-lReceived on Tue May 05 2020 - 21:15:58 CEST