Table Space Usage
Date: 22 Apr 92 03:33:12 GMT
Message-ID: <1737_at_iris.mincom.oz.au>
In response to this request to be able to determine the space usage in an Oracel database, I would like to (re)post the following article that came off the net some time ago. This is actually a dump of a thread that was running at the time, so there is some other ( possibly spurious ) info that has come along for the ride.
Hope it might prove helpful:
- ooo OOO 000 ---
From iris.mincom.oz.au!bunyip.cc.uq.oz.au!munnari.oz.au!uunet!infonode!tensmekl 13 Aug 91 20:54:31 GMT
Path: iris.mincom.oz.au!bunyip.cc.uq.oz.au!munnari.oz.au!uunet!infonode!tensmekl
From: tensmekl_at_infonode.ingr.com (Kermit Tensmeyer)
Newsgroups: comp.databases
Subject: Oracle Free Space or Space used - DBA
Keywords: Oracle DBA
Message-ID: <1991Aug13.205431.15306_at_infonode.ingr.com>
Date: 13 Aug 91 20:54:31 GMT
Organization: Intergraph Corp. Huntsville, AL
Lines: 30
As near as I can figure, there isn't a documented way in Oracle to figure the actual size of data that is being used in any given table. Granted.. You can derive information that tell how much space is allocated to a given table, you can tell how many and how big the extents are that the table uses within a tablespace. But nowhere, (and I have RTFM from Vol 1 - Vol 27), is it documented how to determine the actual size of the data being used in a given table. One alternative that was suggested has been to do an export and look at the export file created to see what the initial size for the table might be. **Beeep*** Thank you for playing - Try again. The views and table for export (expvew.sql) don't give any clues as to determing the current size. So come one, come all, how does one determine the size of data in a database table. Oracle answers preferred. -- Kermit Tensmeyer | Intergraph Corporation UUCP: ...uunet!infonode!tensmekl | One Madison Industrial Park INTERNET: tensmekl_at_infonode.com | Mail Stop LR23A2 AT&T: (205)730-8127 | Huntsville, AL 35807-4201
From iris.mincom.oz.au!bunyip.cc.uq.oz.au!munnari.oz.au!uunet!seismo!beno.CSS.GOV 15 Aug 91 03:11:37 GMT
Path: iris.mincom.oz.au!bunyip.cc.uq.oz.au!munnari.oz.au!uunet!seismo!beno.CSS.GOV
From: jean_at_beno.CSS.GOV (Jean Anderson)
Newsgroups: comp.databases
Subject: Re: Oracle Free Space or Space used - DBA
Message-ID: <49911_at_seismo.CSS.GOV>
Date: 15 Aug 91 03:11:37 GMT
Sender: usenet_at_seismo.CSS.GOV
Organization: SAIC Geophysics Divisions, San Diego, CA
Lines: 153
Nntp-Posting-Host: beno.css.gov
In <1991Aug13.205431.15306_at_infonode.ingr.com> (Kermit Tensmeyer)....
> As near as I can figure, there isn't a documented way in
> Oracle to figure the actual size of data that is being used
> in any given table. Granted.. You can derive information
It is documented, but it deserves some sort of convolution award.
ORACLE tags each record with a unique rowid that contains the datafile, the block number, and the rownumber. To find out how much space a table actually uses, count the number of unique blocks in that table's rowid. Since a single table could span multiple database files and the same block number could show up in both files, count the number of distinct block/file combinations (query is lifted from the ORACLE 6 DBA Guide, Chapter 6, page 10):
select count(distinct(substr(rowid,1,8)||substr(rowid,15,4)))
from <table>;
Mind you, it doesn't tell you "how occupied" that block is so partially filled blocks will get counted the same as 100% filled blocks. But it gets me within 5% of reality, which is close enough for what I need.
I am including a script below that was distributed at the last IOUG. Maurice verbally told me at that time it could be freely distributed, so I assume it's ok to post it here. If not, my apologies in advance.
Now, here's a puzzle I haven't been able to figure out. Does anybody know how to calculate how much space is actually occupied by an index?
- Jean
+----------------------------------------------------------------------------+ | Jean Anderson, DBA email: jean_at_seismo.css.gov | | SAIC Geophysics Division, Mailstop 12 or: jean_at_esosun.css.gov | | 10210 Campus Point Drive phone: (619)458-2727 | | San Diego, CA 92121 fax: (619)458-4993 | +----------------------------------------------------------------------------+ | Any opinions are mine, not my employer's. | +----------------------------------------------------------------------------+ REM %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% REM %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% REM %%% %%% REM %%% ACTUAL_SIZE.SQL %%% REM %%% %%% REM %%% This SQL script uses the following SYS view %%% REM %%% USER_SEGMENTS. It also uses a table named %%% REM %%% TEMP_SIZE_TABLE which contains the table name and %%% REM %%% the number of blocks in use. These views and table are %%% REM %%% used to compare the number of blocks allocated to a %%% REM %%% table to the actual number of blocks used by the same %%% REM %%% table's data. %%% REM %%% This script creates a second SQL script which %%% REM %%% is made up of inserts. These inserts select the %%% REM %%% tablename and block-in-use count to be used by the %%% REM %%% actual report select statement. This secondary file is %%% REM %%% named FILL_SIZE_TABLE.SQL and can be discarded as soon %%% REM %%% as the script has finished running. %%% REM %%% %%% REM %%% This script is designed to be run in an account %%% REM %%% with the RESOURCE privilege. %%% REM %%% %%% REM %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% REM %%% %%% REM %%% Oracle Version 6 IOUG Sept 1990 %%% REM %%% Author: Maurice C. Manton III %%% REM %%% %%% REM %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% REM %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% REM SET TERM OFF; TTITLE OFF; BTITLE OFF; SET PAGESIZE 0; SET VERIFY OFF; SET HEADING OFF; SET RECSEP OFF; REM CREATE TABLE temp_size_table( table_name CHAR(30), blocks NUMBER); REM SPOOL fill_size_table.sql REM SELECT 'INSERT INTO temp_size_table', ' SELECT ','&'||'temp_var.' || segment_name ||'&'||'temp_var', ', COUNT( DISTINCT( SUBSTR( ROWID,1,8))) blocks', ' FROM ', segment_name, ';' FROM user_segments WHERE segment_type = 'TABLE'; REM SPOOL OFF; REM DEFINE temp_var = ''''; START fill_size_table; HOST rm fill_size_table.sql; REM REM %%% %%% REM %%% PAGE BREAK %%% REM %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% REM %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% REM %%% PAGE: 2 %%% REM %%% ACTUAL_SIZE.SQL %%% REM %%% %%% REM %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% REM compute sum of blocks on report; compute sum of act_blocks on report; break on report; REM SET TERM ON; TTITLE ON; BTITLE OFF; REM SET VERIFY ON; SET HEADING ON; SET LINESIZE 80; SET PAGESIZE 60; SET NEWPAGE 0; REM COLUMN t_date NOPRINT new_value t_date; COLUMN user_id NOPRINT new_value user_id; REM SELECT sysdate t_date, user user_id FROM dual; REM COLUMN segment_name FORMAT A20 HEADING "SEGMENT|NAME"; COLUMN segment_type FORMAT A7 HEADING "SEGMENT|TYPE"; COLUMN extents FORMAT 999 HEADING "EXTENTS"; COLUMN bytes FORMAT A6 HEADING "BYTES"; COLUMN blocks FORMAT 9,999,999 HEADING "ORACLE|BLOCKS"; COLUMN act_blocks FORMAT 9,999,999 HEADING "ACTUAL|USED|BLOCKS"; COLUMN pct_block FORMAT 999.99 HEADING "PCT|BLOCKS|USED"; REM SPOOL &user_id._actual_to_allocated_space REM TTITLE LEFT t_date RIGHT 'PAGE: ' FORMAT 999 SQL.PNO SKIP 1 - CEN "&user_id ACTUAL VS ALLOCATED STORAGE REPORT" SKIP 2; REM SELECT segment_name, segment_type, extents, to_char( bytes/1024)||'K' bytes, a.blocks, b.blocks act_blocks, b.blocks/a.blocks*100 pct_block FROM sys.user_segments a, temp_size_table b WHERE segment_name = UPPER( b.table_name ); REM SPOOL OFF; REM REM DROP TABLE temp_size_table; REM REM %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% REM %%% END OF SCRIPT. %%% REM %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%exit
From iris.mincom.oz.au!bunyip.cc.uq.oz.au!munnari.oz.au!samsung!think.com!hsdndev!dartvax!L.Carl.Pedersen 15 Aug 91 21:07:23 GMT
Path: iris.mincom.oz.au!bunyip.cc.uq.oz.au!munnari.oz.au!samsung!think.com!hsdndev!dartvax!L.Carl.Pedersen
From: L.Carl.Pedersen_at_dartmouth.edu (L. Carl Pedersen)
Newsgroups: comp.databases
Subject: Re: Oracle Free Space or Space used - DBA
Message-ID: <1991Aug15.210723.9953_at_dartvax.dartmouth.edu>
Date: 15 Aug 91 21:07:23 GMT
References: <1991Aug13.205431.15306_at_infonode.ingr.com>
Sender: news_at_dartvax.dartmouth.edu (The News Manager)
Organization: Dartmouth College, Hanover, NH
Lines: 25
In article <1991Aug13.205431.15306_at_infonode.ingr.com> tensmekl_at_infonode.ingr.com (Kermit Tensmeyer) writes:
> But nowhere,
> (and I have RTFM from Vol 1 - Vol 27), is it documented
> how to determine the actual size of the data being used in
> a given table.
Right on. I have been moaning about this for years. I have asked support several times, and they always say something like "Oh, you can do that using ROWID. Just look on page something or other of the DBA Guide." And I say, "Yes, but that doesn't include chained blocks (for one thing)." and they say, "Oh, yeah, that's right. Let me get back to you on this." - and they never do.
The best way I have found to do this is trial and error, loading & reloading the table into tables with various different STORAGE clauses and looking at the resulting allocation. If MINEXTENTS is one, and more than one extent is allocated, then you know the thing is using all but the last extent - at least. I realize this is a crock.
I have always suspected that there is actually a good way to do this that gives an accurate result - but that very few people know what it is. There must be some way to find out about chained blocks. Maybe I'm dreaming. Any Oracle developers listening?
- ooo OOO ooo ---
Mark Stavar
Mincom
Juliette St
Brisbane Q Aust
Email: marks_at_jove.mincom.oz.au
Received on Wed Apr 22 1992 - 05:33:12 CEST