Re: Tablespace usage
Date: 3 Jun 92 13:36:17 GMT
Message-ID: <1254_at_usdsd1.DaytonOH.NCR.COM>
I have (re)posted a series of articles that deal with determining space usage
for an ORACLE database. We have found this information to be useful
in managing an ORACLE database space usage on a NCR Tower 700 running UNIX
V.3.
The actual SQL statements contain an error that may give invalid results in
the blocks used column of the report if a tablespace has multiple datafiles.
The
correct statement was listed earlier in the article but was mistyped when
listed in the query.
The part of the query where the typo occurs is listed below followed by the corrected statement:
ORIGINAL
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';
NEW
SELECT 'INSERT INTO temp_size_table',
' SELECT ','&'||'temp_var.' || segment_name ||'&'||'temp_var',
', COUNT( DISTINCT( SUBSTR( ROWID,1,8) || SUBSTR( ROWID,15,4))) blocks',
' FROM ', segment_name, ';'
FROM user_segments
WHERE segment_type = 'TABLE';
This causes the script to count blocks with the same block number twice if
they
occur in separate files instead of counting them once.
Newsgroups: comp.databases.oracle
Subject: Table Space Usage
Message-ID: <1737_at_iris.mincom.oz.au>
Date: 22 Apr 92 03:33:12 GMT
Organization: Mincom, Brisbane, Australia Lines: 267
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 ---
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: 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
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
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
-- Stephen A. Lindeman E-mail:Steve.A.Lindeman_at_DaytonOH.NCR.COM Data Services Division Phone: (513) 445-3688 USG DSD Systems Architecture Fax: (513) 445-4176Received on Wed Jun 03 1992 - 15:36:17 CEST