What is the use of this stored proc? It tells you how many blocks have free space for updates, right? But it does not tell you how much free space in each block. We canget free space info. From dba_free_space. Can you show how this proc can be of value to us? Another procs in this package is unused_space.If it reports 35 blocks. does it mean 35 blocks have neverhad data in it? It seems that it does not report any empty blocks above the high water mark Does it? how can we make sale.thanks, Tom. To Help Manage Space. THANKS
And We Said ...
Here is an example_space how to use dbms_space and how to interpret
Output. Basically Between The 2 Procedures Free Blocks and Unused Space, We'll
Be able to get:
Free blocks ... Number of Blocks on the freelist
Total Blocks ..... Total Blocks Allocated to the Table
Total Bytes ... Total Bytes Allocated to the Table
Unused block .... Blocks That Have Never Contained Data
Unused bytes ..... The Above in bytes
IT Does Not Tell You How Many Blocks Have Free Space for Updates. We can Tell
You How many blocks area Candidates for Inserts (They Are On The Free) and
Blocks on the freelist harve space for Updates - But - There Are Blocks in The
Table That Have Space for Updates But That Are Not On The Freelist. WE
Cannot see them in any report.
IT Does Not Tell you how much space is free in Each Block (Nothing Does,
Typically there thousands or hundreds of thousands of blocks in a table -
An Analysis of The Free Space BLOCK IS Not Practical. We can get an
Average Free Space But Not Block by Block.
This Report Does Show Blocks Above The High Water Mark. Unused Blocks areexactly the block.
You can get nurse of the information sales by this package package by =alyzing the
Table and use queries against user_tables and user_segments. the freeelist
Analysis is more detailed use this package askAS you can love at each freeelist
Independently.
BELOW is a procedure you can use to make use of dbms_space a little easier.
After That i create a Table and show how space is beking used in it at various
Operations. comments in bold explain the output.
Ops $ tkyte @ 8i> create or report
2 Procedure Show_Space
3 (p_segname in varchar2,
4 p_owner in varchar2 default user,
5 p_type in varchar2 default 'table')
6 as
7 l_free_blks number;
8
9 l_total_blocks number;
10 l_total_bytes number;
11 l_unused_blocks number;
12 l_unused_bytes number;
13 l_lastusedexTfileId Number;
14 l_lastusedextblockid number;
15 l_last_used_block number;
16 Procedure P (p_label in varcha2, p_num in number
17 IS
18 Begin
19 dbms_output.put_line (rPad (p_label, 40, '.') ||
20 p_num);
21 End;
22 Begin
23 dBMS_SPACE.FREE_BLOCKS
24 (segment_owner => p_owner,
25 segment_name => p_segname,
26 segment_type => p_type,
27 freeelist_group_id => 0,
28 free_blks => l_free_blks);
29
30 dBMS_SPACE.UNUSED_SPACE
31 (segment_owner => p_owner,
32 segment_name => p_segname, 33 segment_type => p_type,
34 Total_Blocks => L_Total_Blocks,
35 Total_Bytes => l_total_bytes,
36 unused_blocks => l_unused_blocks,
37 unused_bytes => l_unused_bytes,
38 last_used_extent_file_id => l_lastusedextFileId,
39 last_used_extent_block_id => l_lastusedextBlockId,
40 last_used_block => l_last_used_block);
41
42 p ('Free Blocks', L_Free_BLKs);
43 P ('Total Blocks', L_Total_Blocks);
44 p ('Total Bytes', L_Total_Bytes);
45 P ('unused blocks', l_unused_blocks);
46 P ('unused bytes', l_unused_bytes);
47 P ('Last Used Ext FileID', L_lastusedextFileId);
48 P ('last buy ext blockid', l_lastusedextblockid);
49 p ('Last Used Block', L_Last_USED_BLOCK);
50 End;
51 /
Procedure create.
Ops $ tkyte @ 8i>
Ops $ tkyte @ 8i> Create Table T (X Int, Y Char (2000) Default '*')
2 Storage (Initial 40K Next 40K Mineltents 5)
3 tablespace system;
Table created.
I create a table with> 1 extent to make it intending. I Also Put A
Char (2000) in There to make the minimum row length be 2000 bytes (chars always)
Take Their Max Space Right Away). This Just Makes My ROWS "BIG"
Ops $ tkyte @ 8i> INSERT INTO T (X) VALUES (1);
1 row created.
I create one row just to use a little space in the table
Ops $ tkyte @ 8i> Analyze Table T compute statistics;
Table analyzed.
Ops $ tkyte @ 8i> Compute Sum of Blocks on Report
Ops $ tkyte @ 8i> Break on report
Ops $ tkyte @ 8i> select extent_id, bytes, blocks2 from user_extents
3 where segment_name = 't'
4 and segment_type = 'table'
5 /
Extent_ID bytes Blocks
---------- --------------------
2 40960 5
3 81920 10
4 57344 7
0 40960 5
1 40960 5
------------
Sum 32
This Shows That there Are 32 blocks allocated in 5 extents to this table (as
EXPECTED)
Ops $ tkyte @ 8i> Clear Breaks
Ops $ tkyte @ 8i> Select Blocks, EMPTY_BLOCKS,
2 AVG_SPACE, NUM_FREELIST_BLOCKS
3 from User_Tables
4 where Table_name = 'T'
5 /
Blocks Empty_Blocks AVG_SPACE NUM_FREELIST_BLOCKS
---------- ---------------------------------------------------------------------------------------------------------------------------- -
1 30 6091 1
Since i analyzed the table, i Have Acccess to the Above information. You'll
Find that it Maps exactly to the data below. There Are A Total of 32 Blocks
Allocated to the Table (Below and as confirmed by user_extents Above). There
Are 30 EMPTY_BLOCKS (Above) / unused_blocks (below). Thase Are Blocks Above the
HWM. This Leaves 2 Blocks Unaccounted for - 1 Block Has Data IT, The Other
Has The Extent Map for the Table (The First Block of Each Table IS Used by The There
System itself).
Ops $ tkyte @ 8i> EXEC SHOW_SPACE ('T')
Free blocks ........................... 1
Total blocks ............................ 32
Total Bytes ............................. 262144
Unused blocks ......................... 30
Unused bytes ............................ 245760
Last Used Ext FileID .................... 1
Last Used Ext Blockid ................... 64816
Last Used Block .................................... 2PL / SQL Procedure SuccessFully Completed.
Ops $ Tkyte @ 8i> Insert INTO T (X)
2 SELECT ROWNUM
3 from all_Users
4 WHERE ROWNUM <50
5 /
49 rows create.
Ops $ tkyte @ 8i> commit;
COMMIT COMPLETE.
SO NOW WE HAVE 50 ROWS WITH 2K Each - I'm Using A 8k Blocksize SO I Expect
About 3 Rows / Block. That Means About 18 Blocks of Data Plus 1 for the system
= About 19 Blocks Should Be "Used" Now. Below I See That I Have
O 3 Blocks on the freelist. They Have More Space for New Inserts (They Have Not
Hit their pctused yet and may be inserted inst
O 12 unused blocks, leaving 20 blocks "ready". Since i Have 3 on the freeelist -
We probably used a little more the 18 for data - we buy 19 for the 50
Rows. We Have ONE for the system - all accounted for.
Ops $ tkyte @ 8i> EXEC SHOW_SPACE ('T')
Free blocks ........................... 3
Total blocks ............................ 32
Total Bytes ............................. 262144
Unused blocks ......................... 12
Unused bytes ............................ 98304
Last Used Ext FileID .................... 1
Last Used Ext Blockid ................... 64681
Last use block ....................... 5
PL / SQL Procedure SuccessFully Completed.
Ops $ tkyte @ 8i> delete from t;
50 rows deleded.
Ops $ tkyte @ 8i> commit;
COMMIT COMPLETE.
Now we can see what a delete does to our utilization.
Ops $ tkyte @ 8i> EXEC SHOW_SPACE ('T')
Free blocks ............................. 19
Total blocks ............................ 32
Total Bytes ............................. 262144
Unused blocks ......................... 12
Unused bytes ............................ 98304
Last used ext fileid .................... 1Last Used Ext Blockid .................. 64681
Last use block ....................... 5
PL / SQL Procedure SuccessFully Completed.
The Above Shows That The Delete Simply Put of Our Blocks on The Free List.
WE HAVE 19 Blocks on The Free List 12 Unused Blocks 1 System Block = 32
Blocks. All accountted for. Note That the hwm stayed the same - we don't have
31 Unused Blocks - We Have 12 as Before. The HWM for a Table Will Never
Decrease UnsS WE .....
Ops $ tkyte @ 8i> Truncate Table T;
Table truncated.
Ops $ tkyte @ 8i> EXEC SHOW_SPACE ('T')
Free blocks ........................... 0
Total blocks ............................ 32
Total Bytes ............................. 262144
Unused blocks ......................... 31
Unused bytes ............................ 253952
Last Used Ext FileID .................... 1
Last Used Ext Blockid ................... 64816
Last Used Block ....................... 1
PL / SQL Procedure SuccessFully Completed.
Truncate It. That Puts All of the blocks Below the hwm. Now we have 31
Unused blocks 1 system block = 32 blocks total. None on the free list snince
None of itm Have any Data.
REVIEWS
Bookmark Review | Bottom | TopTable Space Used for a Particular Table March 17, 2001 Reviewer: SpMurthy from Singapore Hi Tom,
Thanks for your reply it is more useful to me to know the table
Space.
Regards
Bookmark REVIEW | BOTTOM | TOPDBMS_SPACE USAGE MARCH 22, 2001 REVIEWER: B.N.SARMA from USA Tom,
Excellent, Nothing Less.
IT Would Have Been Nice Had you shown a select statement with autot on doing
FTS Upto HWM, Even if you have delted all the rows and the same with truncate.
IT Would Have Become a Good Notes.Your Explanation with Examples Makes Things Very Clear.
Why don't you write a book :-)
Regards
BN
Bookmark REVIEW | BOTTOM | Top March 23, 2001 Reviewer: Helena Markova from Bratislava, Slovakia
Bookmark REVIEW | BOTTOM | TOPDBMS_SPACE.FREE_SPACE MAY 09, 2001 Reviewer: D.c.l. from seattle, USA Right on. Awesome Grip of the Subject Matter.
Bookmark Review | Bottom | Top May 10, 2001 Reviewer: Vikram from Delhi, India Excellent
Bookmark Review | Bottom | Top August 21, 2001 Reviewer: k.v.s.raju from Sydney, Australia ITS Excellent
Bookmark REVIEW | BOTTOM | TOPDBMS_SPACE SEPTEMBER 19, 2001 REVIEWER: Jim from Ma VERY, VERY GOOD !!!
Bookmark REVIEW | BOTTOM | TOPERRORS IN SHOW_SPACE SEPTEMBER 20, 2001 REVIEWER: a Reader Tom, Tried Using Your Show_Space Procedure. It Compiled SUCCESSFULLY Button
Using it i get back Following Errors:
SQL> EXEC SHOW_SPACE ('T')
Begin Show_Space ('T'); END;
*
Error At Line 1:
ORA-00942: Table or view does not exist
ORA-06512: AT "sys.dbms_space", line 55
ORA-06512: AT "Tom.Show_Space", Line 22
ORA-06512: AT line 1
The Table T EXISTS Under Schema Tom and the show_space processure WAS Compiled
Under User Tom.
DBMSUTIL / PrVTUTIL ETC. Have All Been Run. What am i missing?
FOLLOWUP: Something Must Be Wrong - Give Me A Full Example Like This (That Shows IT
Works)
Ops $ Tkyte@ora817dev.us.racle.com> Create User A Identified by A;
User created.
Ops $ Tkyte@ora817dev.us.racle.com> Grant Create Session, Create Procedure,
Create Table to A;
Grant succeeded.
Ops $ TKYTE@ora817dev.us.racle.com> ALTER USER A Quota Unlimited on Use;
User altered.
Ops $ Tkyte@ora817dev.us.racle.com> Connect A / Aconnected.
a@ora817dev.us.racle.com> @showspace
Procedure create.
a@ora817dev.us.racle.com> Create Table T (x int) TABLESPACE USERS;
Table created.
a@ora817dev.us.racle.com> EXEC SHOW_SPACE ('T')
PL / SQL Procedure SuccessFully Completed.
Ops $ TKYTE@ora817dev.us.racle.com> Set ServerOutput on
Ops $ Tkyte@ora817dev.us.racle.com> EXEC SHOW_SPACE ('T');
Free blocks ........................... 0
Total Blocks ............................ 64
Total Bytes ........................... 524288
Unused blocks ......................... 63
Unused bytes ............................ 516096
Last Used Ext FileID .................... 7
Last Used Ext Blockid ................... 4809
Last Used Block ....................... 1
PL / SQL Procedure SuccessFully Completed.
SO, Do The Whole Create User / Install The Procedure / Run The Test and See IT
Reproduces. if not, Either You Were Not Logged in As Tom, Tom Did Not Own T,
