Skip to Main Content
Forums

SQL & PL/SQL

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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Help in understanding dbms_space.space_usage

vpolasaMay 29 2014 — edited May 30 2014

Hi,

   Can someone please help me understand how below parameters are calculated for dbms_space.space_usage

fs1_blocks

fs2_blocks

fs3_blocks

fs4_blocks


I'm trying to use the below query, but it only give the percent of empty blocks as whole.

SELECT table_name,
       partition_name,
       blocks,
       empty_blocks,
       CEIL ( ( (empty_blocks / blocks) * 100)) perc
  FROM dba_tab_partitions
WHERE table_owner = '<schema_name>' AND empty_blocks <> 0;

Currently using: Windows 7

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

Thanks

Comments

Martin Preiss

as far as I can tell dbms_space.space_usage is not based on dictionary information but reads some of the tables' blocks and analyzes them, My statement is based on a SQL trace (event 10046) for a dbms_space.space_usage call: the trace file contains some single block reads (390 for a table with 72K blocks) - but no additional recursive queries (obviously the dictionary informations on the table were already in the rowcache).

Jonathan Lewis

Martin, (and OP)

It's reading the bitmap space management blocks for objects in ASSM tablespace

fs1 -> blocks with 0 to 25% free space

fs2 -> blocks with 25 to 50% free space

fs3 -> blocks with 50 - 75% free space

fs4 -> blocks with 75 -> 100% free space

FULL -> filled past the limit set by PCTFREE (and you might have set pctfree to something big like 60%, which means a block would be flagged full when it was actually 40% used)

For index blocks the indicators are different - they're either FULL (in use in the index, so locked in place) or fs2 (available for relocation).

Regards

Jonthan Lewis

Martin Preiss

Jonathan,

thank you for the clarification. I remembered your recent article on the different semantic for index blocks: http://jonathanlewis.wordpress.com/2013/12/17/dbms_space_usage/.

1 - 3
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jun 27 2014
Added on May 29 2014
3 comments
2,082 views