RE: Simple DBA Type Question

From: David Barbour <DBarbour_at_connectsouth.com>
Date: Mon, 21 Aug 2000 10:13:31 -0500
Message-Id: <10596.115103@fatcity.com>

Peter,

Here's a script I got off this list a couple of years ago. With modifications, it has served me quite well.
> ----------script to get list of datafiles------------------
>
> select substr(FILE_NAME,1,50) file_name,
> substr(d.FILE_ID,1,7) file_id,
> substr(d.TABLESPACE_NAME,1,12) tablespace_name,
> substr(d.BYTES,1,13) file_size,
> substr(nvl(sum(e.BYTES),0),1,13) bytes_used,
> substr(round(nvl(sum(e.BYTES),0) / (d.BYTES), 4) * 100,1,6)
> per_used,
> substr(d.BYTES - nvl(sum(e.BYTES),0),1,13) bytes_free
> from DBA_EXTENTS e, DBA_DATA_FILES d
> where d.FILE_ID = e.FILE_ID (+)
> group by FILE_NAME, d.TABLESPACE_NAME, d.FILE_ID, d.BYTES, STATUS
> order by d.TABLESPACE_NAME, d.FILE_ID
>
> ------script to get list of tablespaces and usage level ----------
>
> set feedback off;
>
> create or replace view ORASNAP_DDF
> as
> select TABLESPACE_NAME,
> sum(BYTES) BYTES
> from dba_data_files
> group by TABLESPACE_NAME;
>
> create or replace view ORASNAP_DFS
> as
> select TABLESPACE_NAME,
> sum(BYTES) BYTES
> from dba_free_space
> group by TABLESPACE_NAME;
>
> prompt
> prompt
>
> select substr(a.TABLESPACE_NAME,1,18) TABLESPACE_NAME,
> substr(a.BYTES,1,12) bytes_allocated,
> substr(a.BYTES-b.BYTES,1,12) bytes_used,
> substr(round(((a.BYTES-b.BYTES)/a.BYTES)*100,2),1,9) per_used,
> substr(b.BYTES,1,12) bytes_free,
> substr(round((1-((a.BYTES-b.BYTES)/a.BYTES))*100,2),1,9) per_free
> from sys.orasnap_ddf a, sys.orasnap_dfs b
> where a.TABLESPACE_NAME=b.TABLESPACE_NAME
> order by ((a.BYTES-b.BYTES)/a.BYTES) desc;
>
> drop view ORASNAP_DDF;
>
> drop view ORASNAP_DFS;
>
> set feedback 6;
>
> prompt
> prompt
>
> ---------------script to get init.ora parameters -------------------
>
> select substr(NAME,1,38) name,
> substr(VALUE,1,40) value,
> substr(ISDEFAULT,1,6) isdefault,
> substr(ISSES_MODIFIABLE,1,6) issue_modifiable,
> substr(ISMODIFIED,1,6) ismodified
> from sys.v_$parameter
> order by NAME
> /
> ---------------------------------------------------------------
> Regards,

David A. Barbour
Oracle DBA - ConnectSouth
512-681-9438
dbarbour_at_connectsouth.com

-----Original Message-----
From: Peter Hazelton [mailto:peterhazelton_at_hotmail.com] Sent: Monday, August 21, 2000 9:45 AM
To: Multiple recipients of list ORACLE-L Subject: Simple DBA Type Question

Hi guys

Real quick question here! Using SQL plus, how can I determine the physical size of my database? If that cannot be done, is there a way to see the size of individual tables?

Thanks a lot

Peter

---

Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com

-- 
Author: Peter Hazelton
  INET: peterhazelton_at_hotmail.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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

Received on Mon Aug 21 2000 - 10:13:31 CDT