Hi tektipsters!
I need a query that would get the minimum possible high water mark for a tablespace for resize.
Here's one query I got from the net:
query is fine but problem is it scans all datafiles from the database which is taking tooooo long before showing the result...
I need a query that I can only select single datafile and will give the result as fast as possible.
So,here's what I did...(by hitting directly at tables from view definitions + giving the file_name-->&1)
looks fine but not enough.. ;-)
I guess it still do a full scan on X$KTFBUE.
If I have one million record from it, then possibly I'll have to wait 4 to 6 mins just to get single result. costly.
I am trying to figure out how to get this run faster.
TOAD has this one right? Maybe as fast as TOAD. hehe!
I know there's a lot of living query out there better than this... ;-)
Please help...
Many thanks in advance,
sandtek
I need a query that would get the minimum possible high water mark for a tablespace for resize.
Here's one query I got from the net:
Code:
select 'alter database datafile ''' || file_name || ''' resize ' || ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) || 'm;' cmd
from dba_data_files a,
( select file_id,
max(block_id+blocks-1) hwm
from dba_extents
group by file_id
) b
where a.file_id = b.file_id(+)
and ceil(blocks*&&blksize/1024/1024)- ceil((nvl(hwm,1)* &&blksize)/1024/1024 ) > 0
/
I need a query that I can only select single datafile and will give the result as fast as possible.
So,here's what I did...(by hitting directly at tables from view definitions + giving the file_name-->&1)
Code:
select 'alter database datafile ''&&1'' resize '
|| nvl (ceil((((max(e.ktfbueblks+e.ktfbuebno-1))*&&blksize))/1024/1024), 1)
|| 'M;' cmd
from sys.x$ktfbue e,
sys.FILE$ f,
v$dbfile dbf
WHERE e.ktfbuesegtsn = f.ts#
AND e.ktfbuefno = f.relfile#
and dbf.file# = f.file#
and dbf.name = '&&1'
/
I guess it still do a full scan on X$KTFBUE.
If I have one million record from it, then possibly I'll have to wait 4 to 6 mins just to get single result. costly.
I am trying to figure out how to get this run faster.
TOAD has this one right? Maybe as fast as TOAD. hehe!
I know there's a lot of living query out there better than this... ;-)
Please help...
Many thanks in advance,
sandtek