Home » RDBMS Server » Server Administration » Finding the size of database (Oracle 10g)
Finding the size of database [message #299220] Sun, 10 February 2008 21:03 Go to next message
kaustubh
Messages: 26
Registered: June 2007
Junior Member
Hi,
I want to know, how can I find the size of the database. Some articles on internet suggest to query the table "dba_segments" while some suggest querying "dba_data_files", "dba_temp_files" and "sys.v_$log".

Method 1: Using dba_segments:
=============================

select sum(bytes/1024)from dba_segments;


Method 2: Using "dba_data_files", "dba_temp_files" and "sys.v_$log"
====================================================================
select a.data_size+b.temp_size+c.redo_size "total_size"
from ( select sum(bytes) data_size from dba_data_files ) a,( select nvl(sum(bytes),0) temp_sizefrom dba_temp_files ) b,
( select sum(bytes) redo_size from sys.v_$log ) c


Could you please let me know which one of the above is more correct and will give me the exact size of the database?

Also let me know how can I query the size of only the data (i.e. no redo logs, temp files etc)

Thanks and regards,
Kaustubh Kane.
Re: Finding the size of database [message #299227 is a reply to message #299220] Sun, 10 February 2008 23:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Define "size of database" and "size of only the data".

Regards
Michel
Re: Finding the size of database [message #299230 is a reply to message #299220] Sun, 10 February 2008 23:46 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
size of a database =size of physical files.
size of data=size occupied in physical files.

Now there are two things
one is your data is growing
second is your datafiles are growing.
In the first case,datagrows inside the datafiles,hence you can use dba_segments,dba_extents to see how much data has been ocupied.
To know whats the size of datafiles,query dba_data_files.

To know the size of database,add datafiles+controlfiles+tempfiles+archived files+redolog files
Re: Finding the size of database [message #299246 is a reply to message #299230] Mon, 11 February 2008 00:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
size of a database =size of physical files.
size of data=size occupied in physical files.

This is your interpretation may be not what OP thinks.

Regards
Michel
Re: Finding the size of database [message #299874 is a reply to message #299246] Wed, 13 February 2008 08:16 Go to previous messageGo to next message
kaustubh
Messages: 26
Registered: June 2007
Junior Member
Hi,
size of the database and size of only data are just my terms.

size of the database means the total size of the contents in all the tables, indexes, views, redo, undo etc.

size of only data means the size of the contents in tables only.

I want to know, how can I tell what is the amount of data I currently have in my database.

If somebody asks me what is the size of data in the database, then what and how should I query in the database.



Regards,
Kaustubh Kane
Re: Finding the size of database [message #299877 is a reply to message #299874] Wed, 13 February 2008 08:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
total size of the contents in all the tables, indexes, views, redo, undo etc

sum(bytes) from dba_segments

Quote:
the size of the contents in tables only.

Do you mean size of used blocks or size of data inside the used blocks?

Regards
Michel
Re: Finding the size of database [message #300155 is a reply to message #299877] Thu, 14 February 2008 07:44 Go to previous messageGo to next message
kaustubh
Messages: 26
Registered: June 2007
Junior Member
Hi,

Thanks for your reply.

Can you please tell me what is difference between size of used blocks and size of data inside the used blocks?


Regards,
Kaustubh Kane
Re: Finding the size of database [message #300161 is a reply to message #300155] Thu, 14 February 2008 08:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There may be free space inside blocks.
There is also Oracle overhead, block header for instance.

Regards
Michel
Re: Finding the size of database [message #300690 is a reply to message #300161] Sun, 17 February 2008 19:15 Go to previous messageGo to next message
kaustubh
Messages: 26
Registered: June 2007
Junior Member
Hi,
Ya, i want to know size of data insed used blocks.
Re: Finding the size of database [message #300759 is a reply to message #300690] Mon, 18 February 2008 00:56 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Gather statistics for your tables and use appropriate columns that are in dba_tables.

Regards
Michel
Previous Topic: Thread 1 cannot allocate new log, sequence... and Database hanged
Next Topic: critical patch update(CPUJAN2008)
Goto Forum:
  


Current Time: Sun Dec 11 02:42:36 CST 2016

Total time taken to generate the page: 0.11194 seconds