oracle database size?

Question

my client has has oracle database. i calculated to find the actual data occupies around 4 tb while the whole database size comes at around 12 tb . I have no further access or help to figure out what can be the reason for the add-on.

what can be the possible set of reasons? poor maintenance? views?

Answer 1

How did you calculate the actual data size as 4TB ?

I assume the 12TB are the sizes of the actual database files - assuming you use operating system files rather than the Oracle ASM mechanism.

Or are you looking at the actual size of drives occupied by the database ? If your SAN or NAS is set to use RAID 0+1, then you will need double the spindles for mirroring. If you use RAID 5, then you will need up to 30% more spindles.



You should also look inside the database and look for how much space is actually used within each tablespace. It may just be that the DBA allocated them too large, in which case the available space may be reserved for future growth.



If the 4TB are just the base table data, you need to also account for the indexes. Again, check the total storage for all schema objects (tables and indexes mostly) and see how much space they use.



Some other answers mention that views might be a cause for the extra space. This is not true: views occupy no space (they are really just stored queries). *Materialized* views will occupy space. They essentially represent copies of other data.



My bottom line advice is: talk to the customer's DBA and ask him/her for explanations. I can't conceive a customer with a database that size that does not know how storage is used.

Answer 2

There's a lot more in a database than data. Blank text fields can occupy the same length as filled ones (blank and null aren't the same thing). Table overhead, system tables, views, stored procedures, database overhead. Poor maintenance would only explain old, unused data, views, procedures, tables still hanging around.

Answer 3

Actually when you add data-files in a table-space it is set to be auto extendable. So when you check database size using sum of data-files, redo logs and control files it gives different results.



To find used space within the database.



SQL>select sum(bytes/1024/1024/1024) as SIZE from dba_extents;



Read more: http://indialots.blogspot.com/2014/08/size-of-oracle-database.html#ixzz392Bbcnez

Answer 4

Views are definitely a possible culprit. Another thing you may want to look at is logging. Logging can increase your DB size by a great deal and may even slow down performance if the logs get too large. It's certainly worth a look.



Look at the size of your logs, and if that doesn't explain the difference, try replicating the DB without the Views.

Answer 5

I was thinking the views could be one, is it possible that the database saves views like a browser cahce, I'm not that experienced in database management but it could be some hidden files for operating purposes.

Answer 6

What actually do u mean by size of table? The dimension(no of rows n cols)? Usually size of the table is the number of tuples present in that table...In that case query "select count(primary key field) from tablename" will give the no of tuples present... Plz clarify