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@xxxxxxxxx>
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@xxxxxxxxx
