In this example, all the tablespaces are locally managed; in addition, the DMARTS tablespace
has a larger block size to improve response time for data mart tables that are typically accessed
hundreds or thousands of rows at a time.
DBA_SEGMENTS
The data dictionary view DBA_SEGMENTS has one row for each segment in the database. This
view is not only good for retrieving the size of the segment, in blocks or bytes, but also for identifying
the owner of the object and the tablespace where an object resides:
SQL> select tablespace_name, count(*) NUM_OBJECTS,
2 sum(bytes), sum(blocks), sum(extents) from dba_segments
3 group by rollup (tablespace_name);
TABLESPACE_NAME NUM_OBJECTS SUM(BYTES) SUM(BLOCKS) SUM(EXTENTS)
---------------- ----------- ---------- ----------- ------------
DMARTS 2 67108864 4096 92
EXAMPLE 418 81068032 9896 877
SYSAUX 5657 759103488 92664 8189
SYSTEM 1423 732233728 89384 2799
UNDOTBS1 10 29622272 3616 47
USERS 44 11665408 1424 73
XPORT 1 134217728 16384 87
7555 1815019520 217464 12164
DBA_EXTENTS
The DBA_EXTENTS view is similar to DBA_SEGMENTS, except that DBA_EXTENTS drills down
further into each database object. There is one row in DBA_EXTENTS for each extent of each
segment in the database, along with the FILE_ID and BLOCK_ID of the datafile containing the extent:
SQL> select owner, segment_name, tablespace_name,
2 extent_id, file_id, block_id, bytes from dba_extents
3 where segment_name = 'AUD$';
Chapter 6: Monitoring Space Usage 173
OWNER SEGMENT_NAM TABLESPACE EXTENT_ID FILE_ID BLOCK_ID BYTES
----- -------------- ---------- ---------- ---------- ---------- ---------
SYS AUD$ SYSTEM 3 1 32407 196608
SYS AUD$ SYSTEM 4 1 42169 262144
SYS AUD$ SYSTEM 5 2 289 393216
SYS AUD$ SYSTEM 2 1 31455 131072
SYS AUD$ SYSTEM 1 1 30303 65536
SYS AUD$ SYSTEM 0 1 261 16384
In this example, the table AUD$ owned by SYS has extents in two different datafiles that
compose the SYSTEM tablespace.
Pages:
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315