Datafile High Water Mark - How to shink a datafile.

Script to show how much a datafile can be shrunk to free up space.

set verify off
column file_name format a50 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings  format 999,990 heading "Poss.|Savings"
break on report
compute sum of savings on report
column value new_val blksize
select value from v$parameter where name = 'db_block_size';
/
select file_name,
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
       ceil( blocks*&&blksize/1024/1024) currsize,
       ceil( blocks*&&blksize/1024/1024) -
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
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(+) order by savings desc
/

                                                   Smallest
                                                       Size  Current    Poss.
FILE_NAME                                             Poss.     Size  Savings
-------------------------------------------------- -------- -------- --------
/u01/app/oracle/oradata/prod/users20.dbf             19,858   29,568    9,710
/u01/app/oracle/oradata/prod/users19.dbf             23,809   32,768    8,959
/u01/app/oracle/oradata/prod/users18.dbf             25,944   32,768    6,824
/u01/app/oracle/oradata/prod/users21.dbf             10,544   15,104    4,560
/u01/app/oracle/oradata/prod/users16kb03.dbf         23,065   27,506    4,441
/u01/app/oracle/oradata/prod/users22.dbf              4,833    6,400    1,567
/u01/app/oracle/oradata/prod/users23.dbf                 19    1,024    1,005
/u01/app/oracle/oradata/prod/users24.dbf                 27    1,024      997
/u01/app/oracle/oradata/prod/sysaux01.dbf            10,687   11,200      513

Follow this up with:
alter datafile <'datafile name'> resize <amount greater than smallest size possible>;

AUTOTRACE - Tuning SQL in SQLPLUS

+ set timing on

SQL> set autotrace off 
SQL> set autotrace on 

SQL> set autotrace on explain 
SQL> set autotrace on statistics 
SQL> set autotrace on explain statistics 

SQL> set autotrace traceonly 
SQL> set autotrace traceonly explain 
SQL> set autotrace traceonly statistics 
SQL> set autotrace traceonly explain statistics 

SQL> set autotrace off explain 
SQL> set autotrace off statistics 
SQL> set autotrace off explain statistic

shortcuts

SQL> set autot off 
SQL> set autot on 

SQL> set autot on exp 
SQL> set autot on stat 
SQL> set autot on exp stat 

SQL> set autot trace 
SQL> set autot trace exp 
SQL> set autot trace stat 
SQL> set autot trace exp stat 

SQL> set autot off exp 
SQL> set autot off stat 
SQL> set autot off exp stat