Re: dropping a datafile from a tablespace

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 24 Jun 1999 15:41:30 GMT
Message-ID: <37775161.67779581@newshost.us.oracle.com>

A copy of this was sent to pauldb <luapdb_at_yahoo.com> (if that email address didn't require changing) On Thu, 24 Jun 1999 07:23:43 -0800, you wrote:

>Is there a way to check the 'high water' mark of a datafile
>as you were referring ?
>
>

something like:

SQL> l

  1  select file_name, hwm, blocks total_blocks, blocks-hwm+1 shrinkage_possible
  2  from dba_data_files a,
  3       ( select file_id, max(block_id+blocks) hwm
  4           from dba_extents
  5          group by file_id ) b

  6* where a.file_id = b.file_id
SQL> /

FILE_NAME                             HWM TOTAL_BLOCKS SHRINKAGE_POSSIBLE
------------------------------ ---------- ------------ ------------------
/d04/dbs/temporary.dbf              64592        97920              33329
/d04/dbs/tools.dbf                   3547         3584                 38
/d04/dbs/users.dbf                 142316       142336                 21

should do it I believe. haven't thoroughly tested it but it looks right....

the shrinkage is in blocks, multiply by block size to determine amount of space you should be able to free up.

>
>
>**** Posted from RemarQ - http://www.remarq.com - Discussions Start Here (tm) ****

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Jun 24 1999 - 10:41:30 CDT