MIME-Version: 1.0
From: Ram Raman <veeeraman@gmail.com>
Date: Tue, 5 May 2020 11:21:55 -0500
Message-ID: <CAHSa0M3D1GTdwajgBu3MNPRSgxwLtjhtq0vgNVFi8OB7EU4ccg@mail.gmail.com>
Subject: Space explanation
To: ORACLE-L <oracle-l@freelists.org>
Content-Type: multipart/alternative; boundary="000000000000a4058e05a4e90ca7"
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-To: veeeraman@gmail.com
--000000000000a4058e05a4e90ca7
Content-Type: text/plain; charset="UTF-8"

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

--000000000000a4058e05a4e90ca7
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Hi</div><div><br></div><div>I created a table for tes=
ting and inserting rows into it when the insert process failed as the table=
space reached maximum limit of 32G - that happened couple of days ago. Howe=
ver, 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=
</div><div><br></div><div>=C2=A0 1 =C2=A0select table_name, NUM_ROWS, last_=
analyzed, blocks from dba_tables<br>=C2=A0 2* where table_name =3D upper(&#=
39;wvce&#39;)<br>11:14:17 SQL&gt; /<br><br>TABLE_NAME =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 NUM_R=
OWS LAST_ANAL =C2=A0 =C2=A0 BLOCKS<br>------------------------ ------------=
-------- --------- ----------<br>WVCE =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A00 03-MAY-20 =C2=A0 =C2=A03444463<br><br>Elap=
sed: 00:00:00.09<br>11:14:17 SQL&gt;<br>11:14:28 SQL&gt; select count(*) fr=
om WVCE ;<br><br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 COUNT(*)<br>----=
----------------<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=
 =C2=A0 =C2=A00<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_sig=
nature"><div dir=3D"ltr"><div dir=3D"ltr"><div dir=3D"ltr"><div dir=3D"ltr"=
><div>=C2=A0</div><div>Thanks</div><div>Ram</div></div></div></div></div></=
div></div>

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