How do i determine how big a database is and how much space i have lef 3

[mancub]

I have just inherited a database, how do i determine how large it is and how much space has been used? Thanks in advance.

Mancub

 

[SantaMufasa]

Mancub,

There are two very different definitions for "database" in the Database Universe:

In the Oracle World, "database" refers to all of the objects (e.g., tables, indexes, et cetera) owned by all of the Oracle user/schemas (e.g., "SYS", "SYSTEM", "DBSNMP", "OUTLN", "<your login>", "<my login>", et cetera) that make up a single Oracle installation.

In the Non-Oracle World, "database" is roughly equivalent to an Oracle user/schema, for example, for a single Oracle instance/installation, there may be 30 Oracle user/logins/schemas. That would mean if the same "logical" structure existed in a Sybase, SQL Server, DB2, MySQL, EnterpriseDB, et cetera installation, then the installation would contain 30 "databases".

So, when you say, "...how do i determine how large (an Oracle database) is and how much space has been used?" which meaning of "database" should I infer?...1) The collective amount of space that all of the database objects for a single Oracle database instance consume, or 2) the various amounts of space that individual schemas in the database instance consume?

Let us know, because the code to show those respective values obviously differ.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[Welshbird]

I have a wee script I run in SQL*Plus that tells me how much space each of my tables takes and how much free space I have allocated to me.

If this helps you you are very welcome to it!

select a.table_name, b.bytes /1048576 as MB, sum(b.bytes/1048576) over () as Total
from user_tables a, user_segments b
where a.table_name = b.segment_name
order by a.table_name

This of course, as SantaM rightly states will not tell you ALL of the tables or the whole space used; just in your own individual schema.

(I leave this in my OracleBin and then just call it as @space whenever I need to know what is available, and also as a quick way of showing me my own user table names in order)

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[Turkbear]

Hi willif,
your statement

I leave this in my OracleBin

reminds me of a technique I learned to handle my custom sql scripts while leaving the Oracle directories untouched..
I create a directory called MyCode and place all my custom code there..I then add an entry to the SqlPath key in the registry ( HKLM_Software_Oracle) so that SqlPlus will look there ( as well as its standard locations, also in that Key value)..SqlPath is a PATH statement and can have multiple values separated by semi-colons.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Welshbird]

That's a good idea..... I might do that from now on.

('ave a star on me)

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[Turkbear]

Hi,
Thanks...We like to keep the Oracle directory structure
exactly like it was installed - we even go so far as to use separate disks for the software, log files, init<sid> (s) and startup/shutdown scripts.






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[SantaMufasa]

Another method (which I use exclusively) for redirecting SQL*Plus to look in the "D:\MyCode" directory without getting into the registry is to:

<right-click>SQL*Plus icon -> Properties -> "Start in:" D:\MyCode.

This is usually less hassle/more straightforward than changing registry values.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[Turkbear]

Hi,
Yes, that is an option ( and safer than registry editing, in some cases) - I assume that the existing SqlPath entry would handle finding the Oracle supplied 'standard' sql scripts?






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Welshbird]

that would normally sound good, but I have no icoons on my desktop or anywhere else really.

Maybe I can do it from the menu??

(I try and stop my PC feeling like a PC - ha. Gotta love being at home with my Mac)

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[mancub]

Thank mufasaa for making that clear. I question was in regards to "all of the objects (e.g., tables, indexes, et cetera) owned by all of the Oracle user/schemas (e.g., "SYS", "SYSTEM", "DBSNMP", "OUTLN", "<your login>", "<my login>", et cetera) that make up a single Oracle installation." Looking forward to hear from all you gurus.


mancub

 

[SantaMufasa]

Mancub,

It sounds like what you want, then, is a script that will total up the database consumption for all schemas. The following script displays the disk consumption for all database data files, by tablespace. It discloses the current consumption of each file, the maximum size for each file, and the remaining space available (provided that you have enough free space on the file system).

I store this code a script named, "Freespace.sql" script.

set echo on
REM **************************************************************
REM David L. Hunt (file author) distributes this and other 
REM files/scripts for educational purposes only, to illustrate the 
REM use or application of various computing techniques. Neither the 
REM author nor Dasages, LLC, makes any warranty regarding this 
REM script's fitness for any industrial application or purpose nor is 
REM there any claim that this or any similarly-distributed scripts 
REM are error free or should be used for any purpose other than
REM illustration.
REM **************************************************************
set echo off
set linesize 180
set pagesize 40
set heading on
col fname heading "Filename" format a60
col fnum heading "#" format 999
col ts heading "Tablespace|Name" format a15
col tb heading "Total|File Size" format 999,999,999,999
col used heading "Bytes Used" like tb
col free heading "Bytes Free" like tb
col percentfree heading "Pct|Free" format 999
col autoext heading "Auto|Extend" format a6
break on report
compute sum of tb used free on report
spool TablespaceUsage.txt
select    substr(tablespace_name,1,15) ts
    ,d.file_id fnum
    ,d.bytes tb
    ,decode(d.bytes,0,0,d.bytes-nvl(freebytes,0)) used
    ,decode(d.bytes,0,0,nvl(freebytes,0)) free
    ,(nvl(freebytes,0)/d.bytes)*100 percentfree
    ,decode(e.file#,null,'No','Yes') autoext
    ,''''||substr(file_name,1,55)||'''' fname
from     sys.dba_data_files d
    ,(select file_id,sum(bytes) freebytes
        from sys.dba_free_space
        group by file_id) f
    ,sys.filext$ e
    ,v$datafile v
where    d.file_id=f.file_id(+)
  and    d.file_id=e.file#(+)
  and    v.file#=d.file_id
order by tablespace_name,creation_time
/
spool off
prompt
prompt Wrote spool file "TablespaceUsage.txt".
prompt

Tablespace                      Total                                    Pct Auto
Name               #        File Size       Bytes Used       Bytes Free Free Extend Filename
--------------- ---- ---------------- ---------------- ---------------- ---- ------ --------------------------------------------------
DATA1              4      104,857,600       43,581,440       61,276,160   58 Yes    'D:\DHUNT\ORACLEDATA\DBDATA\DHUNT\DHUNTDATA01.DBF'
RBS                2       73,400,320       27,394,048       46,006,272   63 Yes    'D:\DHUNT\ORACLEDATA\DBDATA\DHUNT\DHUNTRBS01.DBF'
SYSTEM             1      142,606,336      135,118,848        7,487,488    5 Yes    'D:\DHUNT\ORACLEDATA\DBDATA\DHUNT\DHUNTSYS01.DBF'
TEMP               3       10,485,760            8,192       10,477,568  100 Yes    'D:\DHUNT\ORACLEDATA\DBDATA\DHUNT\DHUNTTEMP01.DBF'
                     ---------------- ---------------- ----------------
sum                       331,350,016      206,102,528      125,247,488
**************************************************************************************************************************************

Let us know if this shows what you want.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[mancub]

Thanks Mufasaa you did it again.