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

Oracle Patching and Development Insights

Survive Pathing and Better understand bug fixes and merge patches.  

The insiders have lots of great tips here..

YouTube Link

Opatch Slow Checking Patch Prerequisites

Is your Opatch session stuck on verifying "prerequisite checks"?

Verifying environment and performing prerequisite checks

See the full article here:

Binary patching is slow because of the inventory

https://mikedietrichde.com/2022/05/10/binary-patching-is-slow-because-of-the-inventory/

UPDATE:

OPatch 12.2.0.1.37+ Introduces a New Feature to Delete Inactive Patches in the ORACLE_HOME/.patch_storage Directory (Doc ID 2942102.1)

Index monitoring - v$object_usage returns no rows

Cause: v$object_usage view is specific to the currently connected user.

Solution:  Either connect as the object owner, or create the following view as sys

create or replace view V$ALL_OBJECT_USAGE
  (OWNER,
   INDEX_NAME,
   TABLE_NAME,
   MONITORING,
   USED,
   START_MONITORING,
   END_MONITORING
  )
  as
  select u.name,
         io.name,
         t.name,
         decode(bitand(i.flags, 65536), 0, 'NO', 'YES'),
         decode(bitand(ou.flags, 1), 0, 'NO', 'YES'),
         ou.start_monitoring, 
         ou.end_monitoring
    from sys.user$ u,
         sys.obj$ io, 
         sys.obj$ t, 
         sys.ind$ i, 
         sys.object_usage ou
   where i.obj# = ou.obj#
     and io.obj# = ou.obj#
     and t.obj# = i.bo#
     and u.user# = io.owner#
/

MEMORY USED BY SQL STATEMENT

select machine,to_char(SQL_EXEC_START,'DD-MON-YY HH24:MI:SS')            runtime,
       pga_allocated 
  from dba_hist_active_sess_history 
 where sql_id = '&sql_id';


EXAMPLE OUTPUT:

MACHINE                   RUNTIME                 PGA_ALLOCATED

------------------------- -------------------- ----------------
MYPCNAME                  13-JUL-16 16:18:40        807,153,664
MYPCNAME                  13-JUL-16 16:18:40        329,003,008
MYPCNAME                  13-JUL-16 16:18:40        274,280,448
MYPCNAME                  13-JUL-16 16:18:40        215,887,872
MYPCNAME                  13-JUL-16 16:18:40        204,615,680

Top 10 - Biggest Tables and Indexes

Top 10 - Biggest Tables and Indexes

set lines 170
set pages 20
col owner      format a20
col object     format a30
col type       format a6
col tablespace format a20
col size_in_gb format 999,999.9
select * 
  from (select owner           owner,
               segment_name    object,
               segment_type    type,
               tablespace_name tablespace,
               round(bytes/1024/1024/1024,1) size_in_gb
          from dba_segments 
         where owner not in ('SYS','SYSTEM')
         order by bytes desc
       ) 
 where rownum < 11
 order by size_in_gb desc;