| number of allocated extents of a tablespace [message #60802] |
Sun, 07 March 2004 20:12  |
Vaibhav
Messages: 13
Registered: April 2001
|
Junior Member |
|
|
Hi !
How do we find the the number of allocated extents of a tablespace.
DBA_EXTENTS will give the no to extents allocated to a table,but where do we find information about the allocated extents of a tablespace.
Thanks in advance
Vaibhav
|
|
|
|
| Re: number of allocated extents of a tablespace [message #60811 is a reply to message #60802] |
Mon, 08 March 2004 03:57  |
Frank Naude
Messages: 4587
Registered: April 1998
|
Senior Member |
|
|
Try the following query:
SQL> SELECT file_id, block_id, bytes, owner, segment_name
2 FROM dba_extents
3 WHERE tablespace_name = 'USERS'
4 UNION ALL
5 SELECT file_id, block_id, bytes, 'FREE', 'SPACE'
6 FROM dba_free_space
7 WHERE tablespace_name = 'USERS'
8 /
FILE_ID BLOCK_ID BYTES OWNER SEGMENT_NAME
---------- ---------- ---------- -------- ------------------------------
4 9 65536 SCOTT DEPT
4 25 65536 SCOTT EMP
4 41 65536 SCOTT BONUS
4 49 65536 SCOTT SALGRADE
...
4 369 2228224 FREE SPACE
Best regards.
Frank
|
|
|
|
|
|
| Re: number of allocated extents of a tablespace [message #60851 is a reply to message #60830] |
Wed, 10 March 2004 01:47 |
Frank Naude
Messages: 4587
Registered: April 1998
|
Senior Member |
|
|
Hi,
No, "maxextents" is only a default value that will be used when createing segments (tables, indexes, etc.) in the tablespace.
According to the SQL Reference Guide:
"MAXEXTENTS: Specify the total number of extents, including the first, that Oracle can allocate for the object. The minimum value is 1 (except for rollback segments, which always have a minimum value of 2). The default value depends on your data block size."
Best regards.
Frank
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
