Return-Path: X-Original-To: oracle-l@orafaq.com Delivered-To: oracle-l@orafaq.com Received: from turing.freelists.org (turing.freelists.org [206.53.239.180]) by malta2546.startdedicated.com (Postfix) with ESMTPS id 995FA10031450E for ; Tue, 5 May 2020 21:36:15 +0200 (CEST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 6E200236FD; Tue, 5 May 2020 15:36:14 -0400 (EDT) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1588707374; bh=hs0Kzt5IlPlCUi0WGTDt1McsAbedgHKBAPwVJr5cho4=; h=From:Sender:Sender:From; b=lGSlCt0A+KGsTg0y43jfIFT3yqPZIdgCBMv14e0eqjxnijJD0rx5Aw9UqWhshJI0K vHztu/+s/TBij85rCTzLotc2UxF41v5V0TaIYXZ1+0qWVucImd0yRLNEqU/wBjjjkg upFZDrK8RjmnK40A258Cs+9RscXPq6/aOCZXsbOQ= X-Virus-Scanned: Debian amavisd-new at turing.freelists.org Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id rq1CHKCYhqBP; Tue, 5 May 2020 15:36:13 -0400 (EDT) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 883AA22CF5; Tue, 5 May 2020 15:35:24 -0400 (EDT) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1588707368; bh=hs0Kzt5IlPlCUi0WGTDt1McsAbedgHKBAPwVJr5cho4=; h=From:Sender:Sender:From; b=cSy1mImAf7dKBV5Dq74QQkCsTSEkTriB2iJ51Iohb8TFDcJyCDmjGZ2yCOffVC0ZF 6H5vQXPdZ4oXEniigsk5VKY41rl0HTLq1V9tEZcwTMv2X/ZS0Kfgb9H+nNr36kkpIM 0XKVQb4YdpCeeNcMj7BwUF5x65C0OS9AngHCrLXs= Received: with ECARTIS (v1.0.0; list oracle-l); Tue, 05 May 2020 15:34:39 -0400 (EDT) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 5A6BF2240E for ; Tue, 5 May 2020 15:34:39 -0400 (EDT) Authentication-Results: turing.freelists.org; dkim=pass (2048-bit key; unprotected) header.d=gmail.com header.i=@gmail.com header.b="rkbeJyeb"; dkim-atps=neutral Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id yab-zLMToerQ for ; Tue, 5 May 2020 15:34:39 -0400 (EDT) Received: from mail-qk1-f175.google.com (mail-qk1-f175.google.com [209.85.222.175]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (No client certificate requested) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTPS id 1D2A12277C for ; Tue, 5 May 2020 15:34:39 -0400 (EDT) Received: by mail-qk1-f175.google.com with SMTP id s9so3617423qkm.6 for ; Tue, 05 May 2020 12:34:39 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to; bh=H6vYmDVsZzRgJqeIf3JcCdYadB77CAvb97E9wHLCtSc=; b=cezqIeyFFSz+4mAgJgfqoFfrHsj/Gmd5SHoz40KdEJyxHQgk12xQedF0XPSNXgF+3J 82m5w9DDeWrhLLPBdlIMR/4WANPePiBR25k+8SfRsHFuVZzbxvrYO+pDVxUkUFcKCPoO qygQ77mrKCAxBiBWVqm2WvR0J5YZHmwjDIrWXVmMxrt8is6Hb/Ku728COVTQyzJOdMLd uXB9MQZ6vSfKh+6T85UBAzsxraAbt1SOUaFoP5DozJwc9u0TVlrBBGBIt0WVFHzl0m6R 8y8eF/W6dJrRyJkZ/a1bBHocm9PHwgqkBYOBfIXR0R8Q3CS3A/tCe5TMLyI8JSQ94RoQ nlqA== X-Gm-Message-State: AGi0PuYLWz20zbNjIgR8Mt7Xv4mwtP/VGeHHDaPmI7fLv/iF/djaKtX8 x3fN7J005budOZCbrYayQ1DI/MpiFGBg47FALymx1Ja6NL8= X-Google-Smtp-Source: APiQypIKfpbmxpQLuM8/0XFPA4iSDm4qIsv7uC+GI0GXvvU3bBYsQT2k4zqLaPuJKg4KViRNjjUEl3NBMK1gIVhcOKA= X-Received: by 2002:a05:620a:16b0:: with SMTP id s16mr5293993qkj.204.1588707278064; Tue, 05 May 2020 12:34:38 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Jonathan Lewis Date: Tue, 5 May 2020 20:34:26 +0100 Message-ID: Subject: Re: Space explanation To: ORACLE-L Content-Type: multipart/alternative; boundary="000000000000f60e0505a4ebbc09" X-archive-position: 76850 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: jlewisoracle@gmail.com Precedence: normal Reply-To: jlewisoracle@gmail.com List-Help: List-Unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-Subscribe: List-Owner: List-post: List-Archive: X-list: oracle-l --000000000000f60e0505a4ebbc09 Content-Type: text/plain; charset="UTF-8" truncate doesn't reset the stats to zero. QL> select blocks, num_rows from user_tables; BLOCKS NUM_ROWS ---------- ---------- 1251 61768 1 row selected. SQL> truncate table t1; Table truncated. SQL> select blocks, num_rows from user_tables; BLOCKS NUM_ROWS ---------- ---------- 1251 61768 1 row selected. SQL> execute dbms_stats.gather_table_stats(user,'t1') PL/SQL procedure successfully completed. SQL> select blocks, num_rows from user_tables; BLOCKS NUM_ROWS ---------- ---------- 0 0 1 row selected. Did you collect stats between truncating and querying the stats the second time ? Regards Jonathan Lewis On Tue, May 5, 2020 at 8:17 PM 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 > 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@gmail.com >> > > > -- > > > --000000000000f60e0505a4ebbc09 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

truncate doesn't reset the stats t= o zero.

QL> select blocks, num_rows from user_t= ables;

=C2=A0 =C2=A0 BLOCKS =C2=A0 NUM_ROWS
---------- ----------=
=C2=A0 =C2=A0 =C2=A0 1251 =C2=A0 =C2=A0 =C2=A061768

1 row select= ed.

SQL> truncate table t1;

Table truncated.

SQL>= ; select blocks, num_rows from user_tables;

=C2=A0 =C2=A0 BLOCKS =C2= =A0 NUM_ROWS
---------- ----------
=C2=A0 =C2=A0 =C2=A0 1251 =C2=A0 = =C2=A0 =C2=A061768

1 row selected.

SQL> execute dbms_stats= .gather_table_stats(user,'t1')

PL/SQL procedure successfully= completed.

SQL> select blocks, num_rows from user_tables;
=C2=A0 =C2=A0 BLOCKS =C2=A0 NUM_ROWS
---------- ----------
=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A00 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A00

1 r= ow selected.


Did you collect stats = between truncating and querying the stats the second time ?

<= /div>
Regards
Jonathan Lewis


On Tue, May = 5, 2020 at 8:17 PM Ram Raman <vee= eraman@gmail.com> wrote:

Thanks all. TRUNCATE did no= t do it. Dropped and renamed it,=C2=A0we are good now. I am surprised I had= to make a copy of the table, drop original and rename the copy.

=C2=A0 1* =C2=A0select owner, table_name, blocks, num_rows from db= a_tables where tablespace_name =3D 'DBA'
14:09:19 SQL> /
<= br>TABLE_NAME =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 BLOCKS =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 NUM_ROWS
----------= -------------- ---------- --------------------
....
WVCE =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A034= 44463 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= 0

Elapsed: 00:00:00.02
14:09:19 SQL>
14:09:20 SQL> =C2= =A0truncate table WVCE ;

Table truncated.

Elapsed: 00:00:00.0= 3
14:09:29 SQL> =C2=A0select owner, table_name, blocks, num_rows from= dba_tables where tablespace_name =3D 'DBA';


TABLE_NAME = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 BLOCKS =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 NUM_ROWS
------------------------= ---------- --------------------
...
WVCE =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A03444463 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A00

<= /div>
O= n Tue, May 5, 2020 at 1:26 PM Mladen Gogala <gogala.mladen@gmail.com> wrote:
Delete or rollback do n= ot free space. There are ways to free space:
  • truncate
  • = alter table compact
  • alter table move
  • drop table
  • mkf= s
It is perfectly possible to have 32GB table consuming the = entire tablespace. That is known as "agile methodology" because i= t 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 o= f 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 fu= ll. Does anyone have an idea? There are couple of other tables in the table= space but they occupy just few 100 blocks. 12c

=C2= =A0 1 =C2=A0select table_name, NUM_ROWS, last_analyzed, blocks from dba_tab= les
=C2=A0 2* where table_name =3D upper('wvce')
11:14:17 SQL= > /

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_ROWS LAST_ANAL =C2=A0 =C2=A0 = BLOCKS
------------------------ -------------------- --------- ---------= -
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

Elapsed: 00:00:00.09
11:14:17= SQL>
11:14:28 SQL> select count(*) from WVCE ;

=C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 COUNT(*)
--------------------
=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A00

Elap= sed: 00:00:00.00
11:14:35 SQL>
--
=C2=A0<= /div>
Thanks
Ram
--
Mladen Gogala
Data= base Consultant
Tel: (347) 321-1217


--
=C2=A0

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