dba_extents
Name Null? Type
-------------------- -------- ---------------
OWNER VARCHAR2(30)
SEGMENT_NAME VARCHAR2(81)
PARTITION_NAME VARCHAR2(30)
SEGMENT_TYPE VARCHAR2(18)
TABLESPACE_NAME VARCHAR2(30)
EXTENT_ID NUMBER
FILE_ID NUMBER
BLOCK_ID NUMBER
BYTES NUMBER
BLOCKS NUMBER
RELATIVE_FNO NUMBER
Information about every extent in the database.
SQL> create table test_table(a number);
Table created.
SQL> select owner
2 , tablespace_name
3 , extent_id
4 , file_id
5 , bytes
6 from dba_extents
7 where segment_name = 'TEST_TABLE'
8 /
OWNER TABLESPACE_NAME EXTENT_ID FILE_ID BYTES
------- --------------- --------- ------- ----------
SYS ANDY 0 15 65536
One use of dba_extents is for seeing which tablespaces a particular user has objects in:
select tablespace_name
, ceil(sum(bytes) / 1024 / 1024) "MB"
from dba_extents
where owner like '&user_id'
group by tablespace_name
order by tablespace_name
/
For more useful DBA queries click here.
| 