Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Used space? or the whole space that allocated for table?

unknown-879931Jun 30 2014 — edited Jul 22 2014

Hello Experts

I am trying to find out where can I reach the actual used space and allocated space for tables. I ave just checked the Oracle documents for DBA_SEGMENTS table. It is written like the following for BLOCKS and BYTES column.

BYTESNUMBER Size, in bytes, of the
segment
BLOCKSNUMBER Size, in Oracle blocks, of the
segment

I am wondering that does BLOCKS column is shown HWM or the actual used blocks? Because when I added new rows to the table then I deleted them. However the values of BLOCKS columns does not change. Does it show HWM?

What should I do If I want to learn only the acual used blocks?


SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL> drop table my_tab2;

Table dropped.

SQL> create table my_tab2(id number);

Table created.

SQL> select bytes, blocks from dba_segments where lower(segment_name) = 'my_tab2';

no rows selected

SQL> insert into my_tab2 values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> select bytes, blocks from dba_segments where lower(segment_name) = 'my_tab2';

     BYTES     BLOCKS
---------- ----------
     65536          8

SQL> insert into my_tab2 select rownum from all_objects;

75419 rows created.

SQL> commit;

Commit complete.

SQL> select bytes, blocks from dba_segments where lower(segment_name) = 'my_tab2';

     BYTES     BLOCKS
---------- ----------
   1048576        128

SQL> delete from my_tab2;

75420 rows deleted.

SQL> commit;

Commit complete.

SQL> select bytes, blocks from dba_segments where lower(segment_name) = 'my_tab2';

     BYTES     BLOCKS
---------- ----------
   1048576        128

SQL>

Thanks in advance.

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 19 2014
Added on Jun 30 2014
29 comments
8,823 views