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.
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.