Rao_S
asked on
Oracle, tablespace
how do i find -- the true free space within the used portion of the tablespace? After a delete which delets not only the data but also lob data..
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
i read about the 'shrink' and then i got confused because thee is a pre requisite that the tablespace be 'assm' and i dont knoe how the tbspace is defined on our system..
ASKER
this query shows the true free space..in the temp tbspace... is there a query imilar to find the true used/free space...in a tbspace..?
-- To report true free space within the used portion of the TEMPFILE:
SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;
-- To report true free space within the used portion of the TEMPFILE:
SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thank you...the links given by Jacobfw!!
Free for additional rows in the same table or free for other objects in the same tablespace?
If you delete rows from a table all you do is free up space for additional rows for that table. Are you wanting to calculate how many rows can be inserted into the freed up space fbefore another Extent is allocated?