Oracle Built−in Packages- P115: Ah, for the good old days of Version of PL /SQL! Life was so simple then. No stored procedures or functions and certainly no packages. You had your set of built−in functions, like SUBSTR and TO_DATE. You had the IF statement and various kinds of loops. With these tools at hand, you built your batch−processing scripts for execution in SQL*Plus, and you coded your triggers in SQL*Forms , and you went home at night content with a good day's work done. | Appendix A What s on the Companion Disk the defaults are correct. Be careful though because freelist_blocks does not do a set_segment to establish a context. If it is used alone . not in conjunction with total_blocks or unused_blocks it is best to specify a full segment context in the call. Q Q Why did I choose to not have freelist blocks call set segment to establish a segment context A A The unused_blocks function relies on segdata_rec to provide information about the current segment context loaded by the load_unused procedure. If the freelist_blocks function were to call set_segment to establish a context it would also have to call load_unused to keep segdata_rec in synch with the context. This would introduce significant and unnecessary overhead when only freelist information is desired. Here is an example of using the segspace package in a SQL Plus script to report on unused space in segments for a specific tablespace Filename on companion disk undefine tablespace_name set serveroutput on size 100000 set verify off DECLARE total_blocks NUMBER 0 unused_blocks NUMBER 0 BEGIN TABLESPACE UPPER tablespace_name FOR seg_rec IN SELECT segment_name segment_type owner FROM dba_segments WHERE tablespace_name UPPER tablespace_name LOOP total_blocks total_blocks unused_blocks unused_blocks END LOOP Total Blocks TO_CHAR total_blocks Unused Blocks TO_CHAR unused_blocks Pct Unused TO_CHAR ROUND unused_blocks total_blocks 100 END This is sample output from executing the script Enter value for tablespace_name LOAD_DATA TABLESPACE LOAD_DATA Total Blocks 9195 Unused Blocks 1300 Pct Unused 14 PL SQL procedure successfully completed. I like using segspace to probe space utilization within segments. With segspace I can obtain useful reports like the previous one with a few quick lines.
