Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Oracle 9i, a better way to use dbms_space

Oracle 9i, a better way to use dbms_space

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
60

20 rows selected.
Elapsed: 00:00:00.02



The code is as follows ...




--
/
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

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).
Received on Thu Mar 27 2003 - 12:03:42 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US