OraFAQ Forum: Server Administration

Home » RDBMS Server » Server Administration » Disk Space usage

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Disk Space usage [message #53136]

Wed, 28 August 2002 20:28

P
Messages: 29
Registered: May 2002

Junior Member

Does anyone know how/or if it is easily possible to get disk space usage via sqlplus??

Report message to a moderator

Re: Disk Space usage [message #53138 is a reply to message #53136]

Thu, 29 August 2002 00:23

sai sreenivas jeedigunta
Messages: 370
Registered: November 2001

Senior Member

hi,
u can go for Sum(blocks) from the tables like dba_datafiles, dba_tablespaces etc ...
through which you can get the Filled size and left space..I am sorry i dont Remember the Exact queru now...check the structure of the tables...

bye for now
SAI

Report message to a moderator

Re: Disk Space usage [message #53148 is a reply to message #53136]

Thu, 29 August 2002 06:05

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

test this,
i am not sure!.
and this script considers only the oracle datafiles in the disk

SQL> ed
Wrote file afiedt.buf

  1   SELECT substr(d.file_name,2,5) disk,
  2      trunc(sum(TS."total_size_b"/1048576))  total_size_mb,
  3      trunc(sum(NVL(FS."free_size_b"/1048576, 0)))  free_size_mb,
  4      trunc(sum(NVL(US."used_size_b"/1048576, 0)))  used_size_mb,
  5      trunc(100 * trunc(sum(NVL(US."used_size_b"/1048576, 0)))    /trunc(sum(TS."total_size_b"/104857
  6  --    trunc((sum(NVL(Round( 100 * US."used_size_b" / TS."total_size_b", 2 ), 0))/100)) AS "used
  7   --   ,  T.extent_management
  8   --   , T.allocation_type
  9   FROM dba_tablespaces T,dba_data_files D,
 10      (SELECT tablespace_name,
 11         Round(Sum(Bytes), 0) AS "total_size_b"
 12       FROM dba_data_files
 13       GROUP BY tablespace_name) TS,
 14      (SELECT tablespace_name,
 15         Round(Sum(Bytes), 0) AS "free_size_b"
 16       FROM dba_free_space
 17       GROUP BY tablespace_name) FS,
 18      (SELECT tablespace_name,
 19         Round(Sum(Bytes), 0) AS "used_size_b"
 20       FROM dba_segments
 21       GROUP BY tablespace_name) US
 22   WHERE T.tablespace_name = TS.tablespace_name
 23   AND T.tablespace_name = FS.tablespace_name (+)
 24   AND T.tablespace_name = US.tablespace_name (+)
 25   and t.tablespace_name = d.tablespace_name
 26*  group by substr(d.file_name,2,5)
SQL> /

DISK       TOTAL_SIZE_MB FREE_SIZE_MB USED_SIZE_MB USED_PERCENTAGE
---------- ------------- ------------ ------------ ---------------
apps/                  5            3            1              20
prod1               4224         2996         1227              29
prod2               5028         4293          735              14
prod3               3355         2004         1350              40
prod4               2706         2165          540              19
prod5               2905         2574          330              11
prod6               4207         3763          444              10
prod7               4150         2241         1908              45
prod8               3950         3574          375               9
prod9                115          111            3               2
u01/o                665          604           60               9
u02/o                800          787           12               1
u03/o                860          847           12               1

13 rows selected.

Report message to a moderator

Re: Disk Space usage [message #53164 is a reply to message #53136]

Thu, 29 August 2002 16:18

P
Messages: 29
Registered: May 2002

Junior Member

Sorry I was not clear in my question. I would like to know things like how big my hard drive is, how much space is utilised any files on it etc. Like doing something similar to df -k through sqlplus. I am only interested in the big picture I have written scripts for everything else to be monitored. It is so I can monitor disk utilization without having to write scripts for my 40 odd different db's with different o/s

Report message to a moderator

Previous Topic:	Clearing archived log records form Oracle database
Next Topic:	ORA error

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Sep 19 12:45:56 CDT 2024