The
dbms_space unused_space procedure is used to return information
about space that is un-used by an object (where an object is a
table, an index or a cluster). This procedure only works with
objects that reside in a tablespace with an Automatic Segment Space
Management (ASSM) storage option (bitmap freelists as opposed to
one-way linked-list freelists).
The procedure
dbms_space unused_space
is also useful for locating objects that are wasting space. The
example below demonstrates the complete operation for checking the
space not used in a determined table.
In this example, a new table is created and its
unused space is checked with the procedure
unused_spaceof package
dbms_space.
<
Code
2.18 - dbms_space_unused_space.sql
conn pkg/pkg#123
create table tab_dbms_unused_space
tablespace
users
as
select
*
from
dba_objects;
--Checking the unused space
set serveroutput on
declare
tt_blk
number;
tt_bytes
number;
unu_blk
number;
unu_bytes
number;
last_ext_file_id number;
last_ext_blk_id
number;
last_used_blk
number;
begin
dbms_space.unused_space(
segment_owner
=> 'pkg',
segment_name
=> 'tab_dbms_unused_space',
segment_type
=> 'table',
total_blocks
=> tt_blk,
total_bytes
=> tt_bytes,
unused_blocks
=> unu_blk,
unused_bytes
=> unu_bytes,
last_used_extent_file_id
=> last_ext_file_id,
last_used_extent_block_id => last_ext_blk_id,
last_used_block
=> last_used_blk);
dbms_output.put_line('object_name = freelist_t');
dbms_output.put_line('-----------------------------------');
dbms_output.put_line('Total Number of blocks = ' || tt_blk);
dbms_output.put_line('Total unused blocks
= ' || unu_blk);
end;
/
OBJECT_NAME = FREELIST_T
-----------------------------------
Total Number of blocks = 1152
Total unused blocks
= 112
Now let's delete
some rows from this table and then execute the
alter table xxx
shrink space commandis
executed. After this, some extents are freed to be used again and
the unused space can be rechecked.
delete from
tab_dbms_unused_space;
68936 rows deleted
/
0 rows deleted
commit
/
Commit complete
--Checking the number of extents
select
count(*),
bytes
from
dba_extents
where
segment_name = 'tab_dbms_unused_space'
and
owner='pkg'
group by
bytes;
COUNT(*)
BYTES
---------- ----------
8
1048576
16
65536
--Freeing up extents that was deleted
alter table
tab_dbms_unused_space
enable row movement;
Table altered
alter table
tab_dbms_unused_space shrink space;
Table altered
--Check the number of extents again (now there is
just one extent because the table is empty after the delete command)
select
count(*),
bytes
from
dba_extents
where
segment_name = 'tab_dbms_unused_space
and
owner='pkg'
group by bytes;
COUNT(*)
BYTES
---------- ----------
1
65536
--Checking the unused space again (now we can see
just 4 unused and 4 used blocks.
-- It depends of db_clock_size and the storage
type of tablespace)
set serveroutput on
declare
tt_blk
number;
tt_bytes
number;
unu_blk
number;
unu_bytes
number;
last_ext_file_id number;
last_ext_blk_id
number;
last_used_blk
number;
begin
dbms_space.unused_space(
segment_owner
=> 'pkg',
segment_name
=> 'tab_dbms_unused_space',
segment_type
=> 'table',
total_blocks
=> tt_blk,
total_bytes
=> tt_bytes,
unused_blocks
=> unu_blk,
unused_bytes
=> unu_bytes,
last_used_extent_file_id
=> last_ext_file_id,
last_used_extent_block_id => last_ext_blk_id,
last_used_block
=> last_used_blk);
dbms_output.put_line('object_name = freelist_t');
dbms_output.put_line('-----------------------------------');
dbms_output.put_line('Total Number of blocks = ' || tt_blk);
dbms_output.put_line('Total unused blocks
= ' || unu_blk);
end;/
OBJECT_NAME = FREELIST_T
-----------------------------------
Total Number of blocks = 8
Total unused blocks
= 4
As we have illustrated, the
unused_spaceprocedure shows the space
that is not used below the HWM in any table or index
segment.
The HWM represents the border between the blocks
that are stored (lines still are in the blocks) or had previously
stored rows (deleted lines) and the blocks that have never stored
rows (a fresh empty data block acquired from the freelist).
The identified blocks have never been used
usefully by the segments and therefore, can be set free for use when
needed
|
|
 |
|
Inside the DBMS Packages
The DBMS packages form the foundation of
Oracle DBA functionality. Now, Paulo Portugal writes a landmark book
Advanced Oracle DBMS Packages: The Definitive Reference.
This is a must-have book complete with a code
depot of working examples for all of the major DBMS packages.
Order directly from Rampant and save 30%.
|
|
|
| |
|
Burleson is the American Team
Note:
This Oracle
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.
Feel free to ask questions on our
Oracle forum.
Verify
experience!
Anyone
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
publish
their Oracle
qualifications.
Errata?
Oracle technology is changing and we
strive to update our BC Oracle support information. If you find an error
or have a suggestion for improving our content, we would appreciate your
feedback. Just
e-mail:
 and include the URL for the page.
Copyright © 1996 - 2020
All rights reserved by
Burleson
Oracle ®
is the registered trademark of Oracle Corporation.
|
|
