how much space?
i need to know:
1. size of a database.
2. size of a schema in the database.
3. space occupied by all objects in the schema.
4. free space left.
what is the sql query for this?
Leave a comment...
- 3 Comments
- which database release? locally managed or dict managed tbsp?
1) the database size is technically the sum total of the datafile sizes.
select sum(bytes) from dba_data_files;
2-3) The schema "size" is the sum of the space occupied by all of it's objects.
select sum(bytes) from dba_segments where owner=?
4) In the database?
select sum(bytes) from dba_free_space;
In the "schema"? connected as the user/schema you are interested in...
select sum(bytes) from user_free_space;
A#1; Sat, 23 Feb 2008 14:20:00 GMT
- >>1) the database size is technically the sum total of the datafile sizes.
That depend, isn't it be the total size of extent ?
Nicolas.#2; Sat, 23 Feb 2008 14:21:00 GMT
- > That depend, isn't it be the total size of extent ?
That would be the amount of space "allocated" to the tablespaces but not technically the "size" of the database. It all depends on what you want to know. If you were to take an OS backup of the database, you would have a set of files sized equivalent to the dbfs. The OS can't see the "extents". If your sys admin were to ask you how big is your db, you would have to give him the sum totals of your dbfs because extents are irrelevent outside of oracle. If YOU want to know the amount of space "allocated" to the database's tablespaces then you could add up the extents.
SELECT SUM(bytes) FROM DBA_EXTENTS;
A#3; Sat, 23 Feb 2008 14:22:00 GMT