Aw: Re: Space explanation
From: <rogel_at_web.de>
Date: Tue, 5 May 2020 21:35:17 +0200
Message-ID: <trinity-7bafcd7a-c875-4273-bc31-c988788a7a09-1588707316988_at_3c-app-webde-bap11>
SQL> alter table t compact;
alter table t compact
*
ERROR at line 1:
ORA-01735: invalid ALTER TABLE option
SQL> alter table t enable row movement;
--
http://www.freelists.org/webpage/oracle-l
Received on Tue May 05 2020 - 21:35:17 CEST
Date: Tue, 5 May 2020 21:35:17 +0200
Message-ID: <trinity-7bafcd7a-c875-4273-bc31-c988788a7a09-1588707316988_at_3c-app-webde-bap11>
not sure about
alter table compact
, I guess you mean
alter table shrink space compact
?
According to docs, that behaves a bit differently:
"The database does not readjust the high water mark and does not release the space immediately"
see:
SQL> create table t(v varchar2(2000));
Table created.
SQL> insert into t select rpad('a', 2000, 'a') from dual connect by level <= 1e4;
10000 rows created.
SQL> select dbms_xplan.format_number(bytes) from user_segments where segment_name='T';
DBMS_XPLAN.FORMAT_NUMBER(BYTES)
--------------------------------------------------------------------------------
28M
--------------------------------------------------------------------------------
28M
SQL> rollback;
Rollback complete.
SQL> select dbms_xplan.format_number(bytes) from user_segments where segment_name='T';
DBMS_XPLAN.FORMAT_NUMBER(BYTES)
--------------------------------------------------------------------------------
28M
--------------------------------------------------------------------------------
28M
SQL> select * from t;
no rows selected
SQL> alter table t shrink space compact;
alter table t shrink space compact
*
ERROR at line 1:
ORA-10636: ROW MOVEMENT is not enabled
alter table t shrink space compact
*
ERROR at line 1:
ORA-10636: ROW MOVEMENT is not enabled
SQL> alter table t compact;
alter table t compact
*
ERROR at line 1:
ORA-01735: invalid ALTER TABLE option
SQL> alter table t enable row movement;
Table altered.
SQL> alter table t shrink space compact;
Table altered.
SQL> select dbms_xplan.format_number(bytes) from user_segments where segment_name='T';
DBMS_XPLAN.FORMAT_NUMBER(BYTES)
--------------------------------------------------------------------------------
28M
--------------------------------------------------------------------------------
28M
SQL> alter table t shrink space;
Table altered.
SQL> select dbms_xplan.format_number(bytes) from user_segments where segment_name='T';
DBMS_XPLAN.FORMAT_NUMBER(BYTES)
--------------------------------------------------------------------------------
65536
--------------------------------------------------------------------------------
65536
SQL>
Of course, I like mkfs most.
Gesendet: Dienstag, 05. Mai 2020 um 20:26 Uhr
Von: "Mladen Gogala" <gogala.mladen_at_gmail.com>
An: veeeraman_at_gmail.com, "ORACLE-L" <oracle-l_at_freelists.org>
Betreff: Re: Space explanation
Von: "Mladen Gogala" <gogala.mladen_at_gmail.com>
An: veeeraman_at_gmail.com, "ORACLE-L" <oracle-l_at_freelists.org>
Betreff: Re: Space explanation
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:
HiI 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. 12c1 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>ThanksRam
--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
Email:gogala.mladen_at_gmail.com