Oracle-L: Oracle 9i, a better way to use dbms

From: Jamadagni, Rajendra <Rajendra.Jamadagni_at_espn.com>
Date: Thu, 27 Mar 2003 10:03:42 -0800
Message-ID: <F001.0057470B.20030327100342@fatcity.com>

Hi all,

I was working on making dbms_space.space_usage for all my LMT tablespaces. The procedure is perfect, but I didn't want to wait too long till the pl/sql block finishes it work and dbms_output.put_line works its magic. So here is my take on the code ... if you want, feel free to use it.

It should work in 9i onwards, but I have tested this only on 9202. Sorry, this logic won't work for pre-9i versions.

An example is as follows ...

oraclei_at_dino-OLDNCS1> sys
SQL*Plus: Release 9.2.0.2.0 - Production on Thu Mar 27 12:55:25 2003 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected.
SQL> get test_space
  1 set line 200
  2 set trimspool on
  3 column nm format A30
  4 SELECT segment_name nm

  5        ,total_blocks
  6        ,full_blocks
  7        ,(fs1_blocks+fs2_blocks+fs3_blocks+fs4_blocks) partial_blocks
  8        ,unformatted_blocks

9 FROM TABLE(CAST(SYSTEM.Get_Free_Blocks('INTRSCH','') AS SYSTEM.SPACE$free_blocks_tbl))
10* WHERE full_blocks <> -1

SQL> 
SQL> set timing on
SQL> @test_space
NM                             TOTAL_BLOCKS FULL_BLOCKS PARTIAL_BLOCKS

UNFORMATTED_BLOCKS
------------------------------ ------------ ----------- --------------

INTRSCH_ACTUAL_UNIVS                     60           4             44
12
INTRSCH_BASE_DEMOS                       60           0             16
44
INTRSCH_CMP_ACTIMPS                       1           0              0
0
INTRSCH_CMP_EPRTGS                        1           0              0
0
INTRSCH_DAYPARTS                         60           0             16
44
INTRSCH_DAYPART_DAYS                     60           1             15
44
INTRSCH_DEMO_FORMULAS                    60           0             16
44
INTRSCH_ESP_ACTIMPS                   23752       23623            129
0
INTRSCH_ESP_EPRTGS                     2832        2805             27
0
INTRSCH_MONTH_SUMMARY                     1           0              0
0
INTRSCH_QH_CMP_IMPS                       1           0              0
0
INTRSCH_QH_ESP_IMPS                       1           0              0
0
INTRSCH_SAVED_EPS                       124           2            122
0
TMP_INTRSCH_DATA                          1           0              0
0
TMP_INTRSCH_DATES                       124           0             64
60
TMP_INTRSCH_EPRT                         60           0            124
0
TMP_INTRSCH_EPRT_SUMMARY                  1           0             80
44
TMP_INTRSCH_EXCL_SPORT                    1           0              0
0
TMP_INTRSCH_INCL_SPORT                  124           0             48
76
TMP_INTRSCH_TIMES                       124           0             64

20 rows selected.
Elapsed: 00:00:00.02

The code is as follows ...

Author: Raj Jamadagni
Use at your own risk
--
Run as system
--
function get_free_blocks
first parameter - owner
second parameter - Table name or patter to match
--
DROP TYPE SPACE$free_blocks_tbl / DROP TYPE SPACE$free_blocks / CREATE OR REPLACE TYPE SPACE$free_blocks AS OBJECT ( SEGMENT_OWNER VARCHAR2(30) ,SEGMENT_NAME VARCHAR2(30) ,SEGMENT_TYPE VARCHAR2(30) ,total_blocks NUMBER ,UNFORMATTED_BLOCKS NUMBER ,UNFORMATTED_BYTES NUMBER ,FS1_BLOCKS NUMBER ,FS1_BYTES NUMBER ,FS2_BLOCKS NUMBER ,FS2_BYTES NUMBER ,FS3_BLOCKS NUMBER ,FS3_BYTES NUMBER ,FS4_BLOCKS NUMBER ,FS4_BYTES NUMBER ,FULL_BLOCKS NUMBER ,FULL_BYTES NUMBER ,PARTITION_NAME VARCHAR2(30));

/
CREATE OR REPLACE TYPE SPACE$free_blocks_tbl AS TABLE OF SPACE$free_blocks;

/
GRANT ALL ON SPACE$free_blocks_tbl TO sys /
GRANT ALL ON SPACE$free_blocks TO sys
/
CREATE OR REPLACE FUNCTION Get_Free_Blocks

(p_own IN VARCHAR2, p_tab IN VARCHAR2) RETURN SPACE$free_blocks_tbl pipelined AS
--

CURSOR cur_main (c_own VARCHAR2, c_tbl VARCHAR2) IS SELECT owner, table_name, blocks

	  FROM DBA_TABLES
	 WHERE owner LIKE UPPER(c_own) || '%'
	   AND table_name LIKE UPPER(c_tbl) || '%'
	 ORDER BY owner, table_name;
  --
  nUnBlocks  	  NUMBER;
  nUnBytes		  NUMBER;
  nFS1Blocks	  NUMBER;
  nFS1Bytes		  NUMBER;
  nFS2Blocks	  NUMBER;
  nFS2Bytes		  NUMBER;
  nFS3Blocks	  NUMBER;
  nFS3Bytes		  NUMBER;
  nFS4Blocks	  NUMBER;
  nFS4Bytes		  NUMBER;
  nFullBlocks	  NUMBER;
  nFullBytes	  NUMBER;

--

BEGIN
  --
  FOR crec IN cur_main (p_own, p_tab)
  LOOP

exit condition EXIT WHEN cur_main%NOTFOUND; -- BEGIN dbms_application_info.set_module(crec.owner, crec.table_name); -- dbms_space.space_usage ( SEGMENT_OWNER => crec.owner ,SEGMENT_NAME => crec.table_name ,SEGMENT_TYPE => 'TABLE' ,UNFORMATTED_BLOCKS => nUnBlocks ,UNFORMATTED_BYTES => nUnBytes ,FS1_BLOCKS => nFS1Blocks ,FS1_BYTES => nFS1Bytes ,FS2_BLOCKS => nFS2Blocks ,FS2_BYTES => nFS2Bytes ,FS3_BLOCKS => nFS3Blocks ,FS3_BYTES => nFS3Bytes ,FS4_BLOCKS => nFS4Blocks ,FS4_BYTES => nFS4Bytes ,FULL_BLOCKS => nFullBlocks ,FULL_BYTES => nFullBytes ,PARTITION_NAME => NULL); EXCEPTION WHEN OTHERS THEN
- set to -1 to indicate error ... nUnBlocks := -1; nUnBytes := -1; nFS1Blocks := -1; nFS1Bytes := -1; nFS2Blocks := -1; nFS2Bytes := -1; nFS3Blocks := -1; nFS3Bytes := -1; nFS4Blocks := -1; nFS4Bytes := -1; nFullBlocks := -1; nFullBytes := -1; END; -- pipe ROW (SPACE$free_blocks( crec.owner ,crec.table_name ,'TABLE' ,crec.blocks ,nUnBlocks ,nUnBytes ,nFS1Blocks ,nFS1Bytes ,nFS2Blocks ,nFS2Bytes ,nFS3Blocks ,nFS3Bytes ,nFS4Blocks ,nFS4Bytes ,nFullBlocks ,nFullBytes, NULL)); END LOOP; -- FOR crec IN cur_main IF cur_main%isopen THEN CLOSE cur_main; END IF; -- IF cur_main%isopen THEN -- RETURN; -- END Get_Free_Blocks; / GRANT EXECUTE ON SYSTEM.get_free_blocks TO sys /

If you use it, your feedback or suggestions to enhance it are greatly appreciated. I am working on extending this to accomodate indexes as well, just don't have time that's all.

Raj

Rajendra dot Jamadagni at espn dot com
Any views expressed here are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !!

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Jamadagni, Rajendra
INET: Rajendra.Jamadagni_at_espn.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services


---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).

text/plain attachment: ESPN_Disclaimer.txt

Received on Thu Mar 27 2003 - 12:03:42 CST

Oracle 9i, a better way to use dbms_space