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

Home -> Community -> Usenet -> c.d.o.misc -> Re: Tablespace storage summary

Re: Tablespace storage summary

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 10 Jun 1999 15:06:35 GMT
Message-ID: <376dd3f8.14691064@newshost.us.oracle.com>


A copy of this was sent to whuang98_at_my-deja.com (if that email address didn't require changing) On Thu, 10 Jun 1999 13:48:19 GMT, you wrote:

>I am also interested in this script, but I have a quick question for
>Thomas. I noticed that you used dba_temp_files table, but I cannot find
>this one in data dictionary. Do I have to run some sql files other than
>catalog.sql and catproc.sql to get this table? Or it is a typo.
>
>Thanks a lot!
>

bummer, forgot about that.

dba_temp_files is new with Oracle8i -- i just updated my script. It is structurally the same as dba_data_files.

In an 8.0 database or less either

if you do #2 above, you'll have a script that works in 7.x on up. In 8i, if you don't query that view -- you'll not get a list of your 'true' temporary tablespaces...

>Wei
>
>In article <3760a4c2.2605216_at_newshost.us.oracle.com>,
> tkyte_at_us.oracle.com wrote:
>> A copy of this was sent to "Ted Starr" <starrt_at_cyberback.com>
>> (if that email address didn't require changing)
>> On Wed, 9 Jun 1999 15:57:41 -0500, you wrote:
>>
>> >I need to write a query that will summarize table space storage
>> >in something similar to the following format:
>> >
>> >TABLESPACE USED FREE
>> > ts_name ####
>####
>> > owner_1 ####
>> > owner_2 ####
>> >
>> > ts_name_2 ####
>####
>> > owner_3 ####
>> > owner_4 ####
>> >
>> >I can get the tablespace_name, used, and free by joining
>> >dba_extents (or segments) with dba_free_space, but how
>> >do I get the used for each owner by tablespace at the same
>> >time?
>> >
>> >TIA,
>> >Ted
>> >
>>
>> Here is one (designed for sqlplus)
>>
>>
>------------------------------------------------------------------------
>------
>> -- free.sql
>> --
>> -- This SQL Plus script lists freespace by tablespace
>>
>------------------------------------------------------------------------
>------
>>
>> column dummy noprint
>> column pct_used format 999.9 heading "%|Used"
>> column name format a16 heading "Tablespace Name"
>> column Kbytes format 999,999,999 heading "KBytes"
>> column used format 999,999,999 heading "Used"
>> column free format 999,999,999 heading "Free"
>> column largest format 999,999,999 heading "Largest"
>> break on report
>> compute sum of kbytes on report
>> compute sum of free on report
>> compute sum of used on report
>>
>> column order_by_col1 noprint
>> column order_by_col2 noprint
>>
>> select nvl(b.tablespace_name,nvl(a.tablespace_name,'UNKOWN'))
>order_by_col1,
>> chr(0) order_by_col2,
>> nvl(b.tablespace_name,nvl(a.tablespace_name,'UNKOWN')) name,
>> kbytes_alloc kbytes,
>> kbytes_alloc-nvl(kbytes_free,0) used,
>> nvl(kbytes_free,0) free,
>> ((kbytes_alloc-nvl(kbytes_free,0))/kbytes_alloc)*100 pct_used,
>> nvl(largest,0) largest
>> from ( select sum(bytes)/1024 Kbytes_free,
>> max(bytes)/1024 largest,
>> tablespace_name
>> from sys.dba_free_space
>> group by tablespace_name ) a,
>> ( select sum(bytes)/1024 Kbytes_alloc,
>> tablespace_name
>> from sys.dba_data_files
>> group by tablespace_name
>> union all
>> select sum(bytes)/1024 Kbytes_alloc,
>> tablespace_name
>> from sys.dba_temp_files
>> group by tablespace_name ) b
>> where a.tablespace_name (+) = b.tablespace_name
>> union all
>> select tablespace_name order_by_col1,
>> owner order_by_col2,
>> ' ' || owner,
>> sum(bytes)/1024 kbytes,
>> to_number(null) used, to_number(null) free, to_number(null)
>pct_used,
>> to_number(null) largest
>> from dba_segments
>> group by tablespace_name, owner , ' ' || owner
>> order by 1, 2
>> /
>> -------------------------- eof ---------------------------------
>>
>> creates a report that looks like:
>>
>> %
>> Tablespace Name KBytes Used Free Used
>Largest
>> ---------------- ------------ ------------ ------------ ------
>------------
>> ARIA 6,144 104 6,040 1.7
>6,040
>> WEB$ARIA 96
>> CERTIFICATION 222,208 221,680 528 99.8
> 216
>> CERT 215,744
>> CERT_ADMIN 5,928
>> CIVTECH 1,024 360 664 35.2
> 664
>> CIVTECH 352
>> CTXSYS 12,288 8,896 3,392 72.4
>2,680
>> CTXSYS 5,784
>> PRESIDENTS 3,104
>> DES2 22,528 21,704 824 96.3
> 824
>> REPOS 21,696
>> INTRANET_UPLOAD 1,024 8 1,016 .8
>1,016
>> .....
>>
>> So, you get a tablespace (eg: aria) and how much space is allocated to
>it
>> (kbytes), how much is used in it (used), how much is left unallocated
>(free),
>> the %used, and the largest free contigous set of blocks (biggest
>extent you
>> could allocate in the tablespace).
>>
>> under the tables is an indented list of users who have allocated
>objects in the
>> tables and (in kbytes) how much storage they've allocated...
>>
>> See http://www.oracle.com/ideveloper/ for my column 'Digging-in to
>Oracle8i'...
>> Mirrored (and more current) at http://govt.us.oracle.com/~tkyte/
>>
>> Current article is "Fine Grained Access Control", added June 8'th
>>
>> Thomas Kyte tkyte_at_us.oracle.com
>> Oracle Service Industries Reston, VA USA
>> --
>> Opinions are mine and do not necessarily reflect those of Oracle
>Corporation
>>
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.

See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'... Mirrored (and more current) at http://govt.us.oracle.com/~tkyte/

Current article is "Fine Grained Access Control", added June 8'th  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA
--
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Jun 10 1999 - 10:06:35 CDT

Original text of this message

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