Message-ID: <eface1e6f5917d8100e832231c4dc39578116877.camel@gmail.com>
Subject: Re: Space explanation
From: Mladen Gogala <gogala.mladen@gmail.com>
To: veeeraman@gmail.com, ORACLE-L <oracle-l@freelists.org>
Date: Tue, 05 May 2020 14:26:40 -0400
In-Reply-To: <CAHSa0M3D1GTdwajgBu3MNPRSgxwLtjhtq0vgNVFi8OB7EU4ccg@mail.gmail.com>
References: 
 <CAHSa0M3D1GTdwajgBu3MNPRSgxwLtjhtq0vgNVFi8OB7EU4ccg@mail.gmail.com>
Content-Type: multipart/alternative; boundary="=-9BBybcDDaiNuXA3sPgcA"
User-Agent: Evolution 3.36.1-2
MIME-Version: 1.0
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-To: gogala.mladen@gmail.com
--=-9BBybcDDaiNuXA3sPgcA
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: 7bit

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 GogalaDatabase ConsultantTel: (347) 321-1217Email:gogala.mladen@gmail.com

--=-9BBybcDDaiNuXA3sPgcA
Content-Type: text/html; charset="utf-8"
Content-Transfer-Encoding: quoted-printable

<html dir=3D"ltr"><head></head><body style=3D"text-align:left; direction:lt=
r;" bgcolor=3D"#fcfcfc" text=3D"#3c3c3c" link=3D"#737373" vlink=3D"#3c3c3c"=
><div>Delete or rollback do not free space. There are ways to free space:</=
div><ul><li>truncate</li><li>alter table compact</li><li>alter table move</=
li><li>drop table</li><li>mkfs</li></ul><div>It is perfectly possible to ha=
ve 32GB  table consuming the entire tablespace. That is known as "agile met=
hodology" because it forces the DBA to be agile and keep checking space.</d=
iv><div>Regards</div><div><br></div><div>On Tue, 2020-05-05 at 11:21 -0500,=
 Ram Raman wrote:</div><blockquote type=3D"cite" style=3D"margin:0 0 0 .8ex=
; border-left:2px #729fcf solid;padding-left:1ex"><div dir=3D"ltr"><div>Hi<=
/div><div><br></div><div>I created a table for testing and inserting rows i=
nto it when the insert process failed as the tablespace reached maximum lim=
it 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 itsel=
f full. Does anyone have an idea? There are couple of other tables in the t=
ablespace but they occupy just few 100 blocks. 12c</div><div><br></div><div=
>&nbsp; 1 &nbsp;select table_name, NUM_ROWS, last_analyzed, blocks from dba=
_tables<br>&nbsp; 2* where table_name =3D upper('wvce')<br>11:14:17 SQL&gt;=
 /<br><br>TABLE_NAME &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp=
; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; NUM_ROWS LAST_ANAL &nbsp; &nbsp; BLOCK=
S<br>------------------------ -------------------- --------- ----------<br>=
WVCE &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; =
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0 03-M=
AY-20 &nbsp; &nbsp;3444463<br><br>Elapsed: 00:00:00.09<br>11:14:17 SQL&gt;<=
br>11:14:28 SQL&gt; select count(*) from WVCE ;<br><br>&nbsp; &nbsp; &nbsp;=
 &nbsp; &nbsp; &nbsp; COUNT(*)<br>--------------------<br>&nbsp; &nbsp; &nb=
sp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0<br><br>Elapsed: 00:00=
:00.00<br>11:14:35 SQL&gt;<br></div>-- <br><div dir=3D"ltr" class=3D"gmail_=
signature" data-smartmail=3D"gmail_signature"><div dir=3D"ltr"><div dir=3D"=
ltr"><div dir=3D"ltr"><div dir=3D"ltr"><div>&nbsp;</div><div>Thanks</div><d=
iv>Ram</div></div></div></div></div></div></div>
</blockquote><div><span><pre>-- <br></pre><div>Mladen Gogala</div><div>Data=
base Consultant</div><div>Tel: (347) 321-1217</div><div>Email:gogala.mladen=
@gmail.com</div><div></div></span></div></body></html>

--=-9BBybcDDaiNuXA3sPgcA--

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