Home » RDBMS Server » Server Administration » how to find used space of a tablespace
how to find used space of a tablespace [message #240900] Sun, 27 May 2007 08:13 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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
Re: how to find used space of a tablespace [message #240905 is a reply to message #240900] Sun, 27 May 2007 09:27 Go to previous messageGo to next message
rolex.mp
Messages: 161
Registered: February 2007
Senior Member
thanks

in the case of temporary tablespace , if i wanna know the usedspace and freespace available in it which dictionary should i query ?
Re: how to find used space of a tablespace [message #240906 is a reply to message #240903] Sun, 27 May 2007 09:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
dba_data_files gives you the whole file size.
There is a difference between dba_extents and dba_segments when you use SEGMENT_SPACE_MANAGEMENT=AUTO due to bitmaps.

Use dba_segments, is it much faster than dba_extents.

Regards
Michel
Re: how to find used space of a tablespace [message #240907 is a reply to message #240905] Sun, 27 May 2007 09:30 Go to previous message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For temporary tablespace use V$TEMP_EXTENT_POOL.

Regards
Michel
Previous Topic: Oracle Text
Next Topic: diff B/W DBA and APPS DBA
Goto Forum:
  


Current Time: Tue Dec 06 08:08:25 CST 2016

Total time taken to generate the page: 0.04920 seconds