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!
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.
Received on Thu Jun 10 1999 - 08:48:19 CDT
