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;

SQL Outlines or SQL Profiles

SQL OUTLINES or PROFILES

set lines 170
set pages 100
col created      format a14
col type         format a6
col status       format a10
col fm           format a3
col profile_name format a30
col sql_text     format a50
col comp_data    format a50
SELECT created,
       type,
       status,
       force_matching fm,
       profile_name,
       sql_text,
       comp_data
  FROM DBA_SQL_PROFILES PROF,
       DBMSHSXP_SQL_PROFILE_ATTR ATTR
 WHERE prof.name = attr.profile_name
 ORDER BY status, created desc;

Parallel Query Processes - Parent / Child Details

Parallel Process Details including Master/Slave relationships

SQL
set lines 200
set pages 100
col username   format a10
col qcslave    format a10
col slaveset   format a8
col program    format a30
col sid        format a5
col slvinst    format a7
col state      format a8
col waitevent  format a30
col qcsid      format a5
col qcinst     format a6
col reqdop     format 999
col actdop     format 999
col secelapsed format 999,999
SELECT DECODE(px.qcinst_id,NULL,username, ' - '||LOWER(SUBSTR(pp.SERVER_NAME,LENGTH(pp.SERVER_NAME)-4,4) ) ) USERNAME, 
       DECODE(px.qcinst_id,NULL, 'QC', '(Slave)') "QCSLAVE" ,
       TO_CHAR( px.server_set) SLAVESET, 
       s.program PROGRAM, 
       TO_CHAR(s.SID) SID,
       TO_CHAR(px.inst_id) SLVINST, 
       DECODE(sw.state,'WAITING', 'WAIT', 'NOT WAIT' ) STATE,
       CASE  sw.state WHEN 'WAITING' THEN SUBSTR(sw.event,1,30) ELSE NULL END WAITEVENT ,
       DECODE(px.qcinst_id, NULL ,TO_CHAR(s.SID) ,px.qcsid) QCSID,
       TO_CHAR(px.qcinst_id) QCINST, 
       px.req_degree REQDOP, 
       px.DEGREE ACTDOP,
       DECODE(px.server_set,'',s.last_call_et,'') SECELAPSED
  FROM gv$px_session px, 
       gv$session s, 
       gv$px_process pp, 
       gv$session_wait sw
 WHERE px.SID=s.SID (+)
   AND px.serial#=s.serial#(+)
   AND px.inst_id = s.inst_id(+)
   AND px.SID = pp.SID (+)
   AND px.serial#=pp.serial#(+)
   AND sw.SID = s.SID
   AND sw.inst_id = s.inst_id
 ORDER BY DECODE(px.QCINST_ID,  NULL, px.INST_ID,  px.QCINST_ID), 
          px.QCSID,
          DECODE(px.SERVER_GROUP, NULL, 0, px.SERVER_GROUP), 
          px.SERVER_SET, 
          px.INST_ID

Script to Find Unopened files in ASM

Find Unopened files in ASM


set pagesize 0
set linesize 200
col full_alias_path format a80

select * from (
select  x.gnum,x.filnum,x.full_alias_path,f.ftype from (
SELECT gnum,filnum,concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path
FROM (SELECT g.name gname, a.parent_index pindex, a.name aname,
            a.reference_index rindex,a.group_number gnum,a.file_number filnum
      FROM v$asm_alias a, v$asm_diskgroup g
      WHERE a.group_number = g.group_number)
START WITH (mod(pindex, power(2, 24))) = 0 CONNECT BY PRIOR rindex = pindex) x,
(select group_number gnum,file_number filnum, type ftype from v$asm_file order by group_number,file_number) f
where x.filnum != 4294967295
and x.gnum=f.gnum and x.filnum=f.filnum
MINUS
select x.gnum,x.filnum,x.full_alias_path,f.ftype
from ( select id1 gnum,id2 filnum from v$lock where type='FA' and (lmode=4 or lmode=2)) l,
(
SELECT gnum,filnum,concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path
FROM (SELECT g.name gname, a.parent_index pindex, a.name aname,
            a.reference_index rindex,a.group_number gnum,a.file_number filnum
      FROM v$asm_alias a, v$asm_diskgroup g
      WHERE a.group_number = g.group_number)
START WITH (mod(pindex, power(2, 24))) = 0 CONNECT BY PRIOR rindex = pindex
) x,
(select group_number gnum,file_number filnum, type ftype from v\$asm_file order by group_number,file_number) f
where x.filnum != 4294967295 and
x.gnum=l.gnum
and x.filnum=l.filnum
and x.gnum=f.gnum and x.filnum=f.filnum) q
order  by q.gnum,q.ftype
/

Sample Output
1      13460 +MYDG1/LEGACYDB1/DATAFILE/indx01.dbf          DATAFILE

1      12440 +MYDG2/LEGACYDB2/DATAFILE/temp01.dbf          TEMPFILE

Reading ASM Disk Header

How to Read an ASM Disk Header

As root,

/u01/app/11.2.0/grid/bin/kfed read /dev/mapper/mydisk1|egrep "(dsksize|provstr|dskname|grpname|fgname)"

kfdhdb.driver.provstr:  ORCLDISKMYDISK1 ; 0x000: length=15

kfdhdb.dskname:                 MYDISK1 ; 0x028: length=7

kfdhdb.grpname:               MYDISKGROUP ; 0x048: length=9

kfdhdb.fgname:                  MYDISK1 ; 0x068: length=7

kfdhdb.dsksize:                  524294 ; 0x0c4: 0x00080006

To list all ASM devices using blkid:

blkid|grep sd.*oracleasm|while read a b;do echo -n $a$b" scsi_id=";(echo $a|tr -d [:digit:]|tr -d [:]|cut -d"/" -f3|xargs -i scsi_id -g -s /block/{})done;

Rename ASM diskgroup (11.2)

Rename ASM diskgroup

$GRID_HOME/bin/renamedg phase=both verbose=true dgname=OLD_FRA newdgname=NEW_FRA asm_diskstring='ORCL:DISK01','ORCL:DISK02'

Important Note: This does not change the file names/locations in the controlfile. 

1. List all data/temp/control/redo files.

2. Relocate the controlfiles (search this blog for "Multiplexing Controlfiles"). 

3. Rename the diskgroup.

4. Rename datafiles, tempfiles, online/standby redo logfiles using "alter database rename file 'x' to 'y';" with the database in mount mode.

ORA-02030 v$lock v$session v$process v$rollname

Error

SQL> grant select on v$lock to oms;
grant select on v$lock to oms
                *
ERROR at line 1:
ORA-02030: can only select from fixed tables/views

Solution

select synonym_name,
       table_name 
  from dba_synonyms
 where synonym_name in ('V$LOCK','V$SESSION','V$PROCESS','V$ROLLNAME');

SYNONYM_NAME                   TABLE_NAME
------------------------------ ------------------------------
V$LOCK                         V_$LOCK
V$PROCESS                      V_$PROCESS
V$ROLLNAME                     V_$ROLLNAME
V$SESSION                      V_$SESSION


SQL> grant select on v_$lock to supersmartuser;
Grant succeeded.

Foreign Keys with Missing Indexes

--FK Constraint columns not indexed

set lines 160set pages 40col FKCons       format a30col SourceTable  format a30col SourceColumn format a30col TargetTable  format a30col TargetColumn format a30select scc.constraint_name  as FKCons,       scc.table_name       as SourceTable,       scc.column_name      as SourceColumn,       tcc.table_name       as TargetTable,       tcc.column_name      as TargetColumn        from all_cons_columns scc,        all_constraints sc,       all_cons_columns tcc where sc.constraint_name = scc.constraint_name    --Join Cons to Cons Cols   and sc.r_constraint_name = tcc.constraint_name  --Join source to target   and sc.constraint_type = 'R'                    --RI Constraints only   and scc.owner = '&SCHEMA'                           --Only OMS schema   and tcc.table_name||tcc.column_name 
       not in (select i.table_name||i.column_name                  from all_ind_columns i                where i.index_owner = '&SCHEMA')       --FKs not indexed order by scc.table_name/

I can only guess this was a problem in prior releases of the database.  According to this test in 12c, it doesn't seem possible.


SQL> create table source (sourceid number, constraint sourcepk primary key (sourceid));
Table created.

SQL> create table target (targetid number, constraint targetpk primary key (targetid));
Table created.

SQL> alter table target add constraint sourceidfk foreign key (targetid) references source (sourceid);
Table altered.

SQL> col table_name format a20
SQL> col index_name format a20
SQL> col column_name format a20

SQL> select table_name,index_name,column_name from all_ind_columns where index_owner = 'KEN';

TABLE_NAME           INDEX_NAME           COLUMN_NAME
-------------------- -------------------- --------------------
TARGET               TARGETPK             TARGETID
SOURCE               SOURCEPK             SOURCEID

SQL> drop index sourcepk;
drop index sourcepk
           *
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key

SQL> alter table source disable constraint sourcepk;
alter table source disable constraint sourcepk
*
ERROR at line 1:
ORA-02297: cannot disable constraint (KEN.SOURCEPK) - dependencies exist

AWR Interval and Retention

SQL
select
       extract( day from snap_interval) *24*60+
       extract( hour from snap_interval) *60+
       extract( minute from snap_interval ) "Snapshot Interval",
       extract( day from retention) *24*60+
       extract( hour from retention) *60+
       extract( minute from retention ) "Retention Interval"
from dba_hist_wr_control;

SAMPLE OUTPUT
Snapshot Interval Retention Interval
----------------- ------------------
               60              10080

Hangcheck Timer Not Needed for 11gR2 RAC

Hangcheck Timer FAQ (Doc ID 232355.1)

Do I need the hangcheck-timer with 11gR2 ?

Answer

-----------

The hangcheck-timer is not needed with 11gR2. This is documented in 'Oracle® Grid Infrastructure Installation Guide 11g Release 2 (11.2) for Linux' section 'Improved Input/Output Fencing Processes'.

Oracle 11gR2 Whatsnew Doc

Improved Input/Output Fencing Processes

Oracle Clusterware 11g release 2 (11.2) replaces the oprocd and Hangcheck processes with the cluster synchronization service daemon Agent and Monitor to provide more accurate recognition of hangs and to avoid false termination.

Identifying Corruption

IDENTIFYING CORRUPTION


Find Corrupt Segments
set linesize 140
col owner format a10
col segment_type format a10
col segment_name format a30
col partition_name format a15
col file# format 999
SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
       , greatest(e.block_id, c.block#) corr_start_block#
       , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
       , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) 
         - greatest(e.block_id, c.block#) + 1 blocks_corrupted
       , null description
    FROM dba_extents e, v$database_block_corruption c
   WHERE e.file_id = c.file#
     AND e.block_id <= c.block# + c.blocks - 1
     AND e.block_id + e.blocks - 1 >= c.block#
  UNION
  SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
       , header_block corr_start_block#
       , header_block corr_end_block#
       , 1 blocks_corrupted
       , 'Segment Header' description
    FROM dba_segments s, v$database_block_corruption c
   WHERE s.header_file = c.file#
     AND s.header_block between c.block# and c.block# + c.blocks - 1
  UNION
  SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
       , greatest(f.block_id, c.block#) corr_start_block#
       , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
       , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) 
         - greatest(f.block_id, c.block#) + 1 blocks_corrupted
       , 'Free Block' description
    FROM dba_free_space f, v$database_block_corruption c
   WHERE f.file_id = c.file#
     AND f.block_id <= c.block# + c.blocks - 1
     AND f.block_id + f.blocks - 1 >= c.block#
  order by file#, corr_start_block#
/

Generate Tablespace DDL

GENERATE TABLESPACE DDL

select dbms_metadata.get_ddl('TABLESPACE',tablespace_name) from dba_tablespaces;

or

select 'select dbms_metadata.get_ddl(''TABLESPACE'','''||tablespace_name||''') from dual;' from dba_tablespaces;

RMAN Backup File Order

RMAN BACKUP FILE ORDER

Question:
Assuming a single channel and filesperset=1, what order does RMAN choose to backup datafiles?

Answer:
The order of files is random. The File Names are read from V$DATAFILE view and due to performance bug, the ORDER BY FILE# clause is removed, hence the query can return file# in random order.

Reset Oracle Sequence in-place

HOW TO RESET A SEQUENCE IN-PLACE

  This avoids drop/create, and re-plumbing grants and synonyms.

  1. Set the "increment by" value to -nextval-1
  2. Increment the sequence
  3. Set the "increment by" value back to 1

SQL> select myschema.mysequence.nextval from dual;

   NEXTVAL

----------

1887203408

SQL> alter sequence myschema.mysequence increment by -1887203407;

Sequence altered.

SQL> select myschema.mysequence.nextval from dual;

   NEXTVAL

----------

         1

SQL> alter sequence myschema.mysequence increment by 1;

Sequence altered.

SQL> select myschema.mysequence.nextval from dual;

   NEXTVAL

----------

         2

SQL> alter system flush shared_pool;

System altered.