| how to find used space of a tablespace [message #240900] |
Sun, 27 May 2007 08:13  |
rolex.mp
Messages: 161
Registered: February 2007
|
Senior Member |
|
|
how do i find the used space in a tablespace of a database ?
Should i query dba_extents to get max(bytes) grouped by tablespace or should i query dba_data_files to get max(bytes) grouped by tablespace ?
|
|
|
|
| Re: how to find used space of a tablespace [message #240902 is a reply to message #240900] |
Sun, 27 May 2007 08:28  |
saibal
Messages: 111
Registered: March 2007
Location: India
|
Senior Member |
|
|
1 select TABLESPACE_NAME, round(sum(bytes)/1024/1024,0) Meg from dba_segments
2* group by tablespace_name
system@DBASE-SQL>>/
TABLESPACE_NAME MEG
------------------------------ ----------
EXAMPLE 80
SYSAUX 275
SYSTEM 495
SYSTEM_MANAGED 88
UNDOTBS1 8
UNIFORM_SIZE 85
USERS 3
7 rows selected. |
|
|
|
| Re: how to find used space of a tablespace [message #240903 is a reply to message #240902] |
Sun, 27 May 2007 08:35 |
rolex.mp
Messages: 161
Registered: February 2007
|
Senior Member |
|
|
when i query sum(bytes) from dba_extents , dba_data_files ,dba_Segments table i get different data
foe eg
select sum(bytes),tablespace_name from dba_extents group by tablespace_name;
SUM(BYTES) TABLESPACE_NAME
---------- ------------------------------
9895936 APPS_UNDO_TBS
292683776 SYSAUX
3618439168 SYSTEM
select sum(bytes),tablespace_name from dba_data_files group by tablespace_name;
SUM(BYTES) TABLESPACE_NAME
---------- ------------------------------
558039040 APPS_UNDO_TBS
298975232 SYSAUX
3619487744 SYSTEM
select sum(bytes),tablespace_name from dba_segments group by tablespace_name
SQL> /
SUM(BYTES) TABLESPACE_NAME
---------- ------------------------------
9895936 APPS_UNDO_TBS
292749312 SYSAUX
3618439168 SYSTEM
which one is giving me the exact result ?
|
|
|
|
| Re: how to find used space of a tablespace [message #240904 is a reply to message #240903] |
Sun, 27 May 2007 08:49 |
saibal
Messages: 111
Registered: March 2007
Location: India
|
Senior Member |
|
|
querying DBA_SEGMENTS or DBA_EXTENTS should produce similar results, if not exactly the same. IN case also, the results are almost same. In my case:
system@DBASE-SQL>>select TABLESPACE_NAME , round(sum(bytes)/1024/1024,0) Meg
2 from dba_extents group by tablespace_name;
TABLESPACE_NAME MEG
------------------------------ ----------
EXAMPLE 80
SYSAUX 275
SYSTEM 495
SYSTEM_MANAGED 88
UNDOTBS1 10
UNIFORM_SIZE 85
USERS 3
7 rows selected.
I would not suggest querying the DBA_DATA_FILES view, because in my opinion, the values returned by this view are from a lower level--the file level, although I can't say that with any degree of certainty. You can choose either DBA_EXTENTS or DBA_SEGMENTS
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
