Calculating Total Instance Memory Footprint

Since the PGA (controlled by the ‘pga_aggregate_target’ setting) is outside of the SGA, this query will calculate the combined totals.  The amount of free RAM on the DB host should exceed this calculated total:

  with counts as (
    select round(sum(value)/1024/1024) total from v$sga
    union all
    select value/1024/1024 from v$parameter where name = 'pga_aggregate_target')
  select sum(total) from counts;

 

Advertisements

Oracle Sequence Close to Maximum

This SQL identifies Oracle sequences that are above 90% used:

select sequence_owner "Owner", sequence_name "Name", last_number "Last", max_value "Max", last_number/max_value*100 "Pct Used" 
from dba_sequences
where sequence_owner not in ('SYS')
and last_number/max_value*100 < 101 and last_number/max_value*100 > 90
order by "Pct Used" desc

 

Gather Latest Captured Bind Values

-- Show the last binds used for a SQL ID
undefine SQL_ID
accept SQL_ID char prompt 'Enter SQL ID: '
column "Bind" format a8
column "Data Type" format a15
column "Bind Value" format a50

select distinct name "Bind", datatype_string "Data Type", value_string "Bind Value" from DBA_HIST_SQLBIND where sql_id = '&SQL_ID' and (last_captured = (select max(last_captured) from DBA_HIST_SQLBIND where sql_id = '&SQL_ID') or last_captured is null)
order by 1
/

 

Oracle Logical LOB Corruption

Attempting to export LOB rows that contain logical corruption results in a strange error:

ORA-31693: Table data object "MYSCHEMA"."MYTABLE" failed to load/unload and is being skipped due to error:
ORA-29913: error in executing MYCOLUMN callout
ORA-01555: snapshot too old: rollback segment number with name "" too small
ORA-22924: snapshot too old

The empty rollback segment is a sign that this error isn’t related to the UNDO tablespace sizing or the ‘undo_retention’ setting according to Metalink Doc ID 452341.1.  Gathering the corrupt ROWIDs can be tricky.  One way is to create a new table that matches the corrupt table and move every row out and back into the original table, noting the ROWIDs that fail. This assumes a primary key is in place:

create table myschema.mytable_new;
truncate table myschema.mytable_new;
set serveroutput on
declare
  ddl varchar2(32767);
begin
  for i in (
    select rowid, mypk from myschema.mytable
  ) LOOP
    begin
      ddl := 'insert into myschema.mytable_new select * from myschema.mytable where mypk='||i.mypk||'';
      begin
        execute immediate ddl;
      exception
        when others then
          DBMS_OUTPUT.PUT_LINE('Bad insert into myschema.mytable_new: ' || i.rowid);
      end;

      ddl := 'delete from myschema.mytable where mypk='||i.mypk||'';
      begin
        execute immediate ddl;
      exception
        when others then
          DBMS_OUTPUT.PUT_LINE('Bad delete from myschema.mytable: ' || i.rowid);
      end;

      ddl := 'insert into myschema.mytable select * from myschema.mytable_new where mypk='||i.mypk||'';
      begin
        execute immediate ddl;
      exception
        when others then
          DBMS_OUTPUT.PUT_LINE('Bad insert into myschema.mytable: ' || i.rowid);
      end;

      ddl := 'delete from myschema.mytable_new where mypk='||i.mypk||'';
      begin
        execute immediate ddl;
      exception
        when others then
          DBMS_OUTPUT.PUT_LINE('Bad delete from myschema.mytable_new: ' || i.rowid);
      end;

      commit;
    end;
  end loop;
end;
/

Once the bad rows have been identified, they can either be deleted by ROWID or the LOB portion can be emptied:

  update myschema.mytable set mycolumn = empty_clob()
  where rowid in ('AAAv96ABAAAMr0aAAL', 'AAAv96ABAAAMr0aAAO', 'AAAv96ABAAAMr0iAAB', 'AAAv96ABAAAMr0iAAH', 'AAAv96ABAAAMr0iAAK', 'AAAv96ABAAAMr0qAAA', 'AAAv96ABAAAMr0qAAB', 'AAAv96ABAAAMr0qAAG', 'AAAv96ABAAAMr0qAAH', 'AAAv96ABAAAMr0qAAJ', 'AAAv96ABAAAMr0yAAB', 'AAAv96ABAAAMr0yAAC', 'AAAv96ABAAAMr0yAAD', 'AAAv96ABAAAMr0yAAE', 'AAAv96ABAAAMr0yAAF','AAAv96ABAAAMr0yAAG', 'AAAv96ABAAAMr0yAAH', 'AAAv96ABAAAMr0yAAI', 'AAAv96ABAAAMr1CAAD', 'AAAv96ABAAAMr1CAAE', 'AAAv96ABAAAMr1CAAH', 'AAAv96ABAAAMr1CAAI', 'AAAv96ABAAAMr1CAAQ', 'AAAv96ABAAAMr1SAAC', 'AAAv96ABAAAMr1SAAD', 'AAAv96ABAAAMr1SAAE', 'AAAv96ABAAAMr1SAAF', 'AAAv96ABAAAMr1SAAG', 'AAAv96ABAAAMr1SAAH', 'AAAv96ABAAAMr1SAAI', 'AAAv96ABAAAMr1SAAJ');
commit;

Testing the data pump or export will validate the corruption is gone.

Reporting CPU/PSU Patch Levels

The tables that contain CPU/PSU patching information are different in Oracle 11g and 12c.  Here’s a bit of SQL that captures the information for the last patch applied in either version:

    set serveroutput on
    declare
      v_psu char(32767);
      v_sql char(32767);
      v_cnt number;
    begin
      select count(*) into v_cnt from dba_views where owner||'.'||view_name = 'SYS.DBA_REGISTRY_SQLPATCH';

      if v_cnt = 1 then
        v_sql := 'select to_char(ACTION_TIME,' || ''''||'DD-MON-YY HH:MI:SS' || '''' || ') || ' || ''''|| ' ' || '
''' || ' || description from SYS.DBA_REGISTRY_SQLPATCH where bundle_series like ' || '''' || 'DBRU%' ||'''' || ' a
nd action_time = (select max(action_time) from SYS.DBA_REGISTRY_SQLPATCH where bundle_series like ' || '''' || 'DB
RU%' || '''' || ')';
      else
        v_sql := 'select to_char(ACTION_TIME,' || '''' || 'DD-MON-YY HH:MI:SS' || '''' || ') || ' || '''' || ' ' |
| '''' || ' || comments from SYS.REGISTRY$HISTORY where comments like ' || '''' || 'PSU%' || '''' || ' and action_
time = (select max(action_time) from SYS.REGISTRY$HISTORY where comments like '|| '''' || 'PSU%' || '''' || ')';
      end if;

      execute immediate v_sql into v_psu;
      dbms_output.put_line(v_psu);
    exception
      when others then
        dbms_output.put_line('');
    end;
    /

Display Current Oracle Temp Usage

 

set lines 132
set pages 40
col username format a20
col used format 999,999,999
col tablespace format a20
col sid format 999999999
col serial format 999999999
col "Used (Bytes)" format 999,999,999,999
col "Used (MB)" format 999,999,999.99

SELECT 
  UserName,
  Tablespace,
  sid,
  serial#,
  Used AS "Used (Bytes)",
  Used / 1024 / 1024 AS "Used (MB)"
FROM 
  (SELECT 
     tu.username UserName,
     tu.TABLESPACE Tablespace,
     s.sid,
     s.serial#,
     SUM (ts.block_size * tu.blocks) Used
   FROM 
     v$tempseg_usage tu, 
     v$session s, 
     dba_tablespaces ts
   WHERE 
     tu.session_addr = s.saddr
   AND
     ts.TABLESPACE_NAME = tu.TABLESPACE
   GROUP BY 
     tu.username,
     tu.TABLESPACE,
     s.sid,
     s.serial#)
ORDER BY Used DESC
/

SELECT 
  tablespace AS "Tablespace",
  SUM (ts.block_size * tu.blocks) / 1024 / 1024 AS "Used (MB)"
FROM 
  v$tempseg_usage tu, dba_tablespaces ts
WHERE 
  ts.TABLESPACE_NAME = tu.TABLESPACE
GROUP BY 
  tablespace
ORDER BY 
  "Used (MB)" DESC
/