Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

tablespace HWM resize

Status
Not open for further replies.

sandtek

Programmer
Nov 20, 2007
13
SG
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:
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
/
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)

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'
/
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top