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;
    /
Advertisements

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
/

Turn on Logging for All Indexes

This script will turn on logging for all indexes that have it currently turned off:

-- Turns on index logging where it's turned off
-- Does not include system indexes

set time on timing on serveroutput on pages 100 lines 32767

col owner format a30
col index_name format a30

SELECT owner, index_name
from dba_indexes
where logging = 'NO' and owner not in ('SYSTEM','DBSNMP','SYS')
/

declare
  ddl varchar2(32767);
begin
  for i in (
    SELECT owner, index_name from dba_indexes where logging = 'NO' and owner not in ('SYSTEM','DBSNMP','SYS')
  ) LOOP
    begin
      ddl := 'alter index '||i.owner||'.'||i.index_name||' logging'||'';
      DBMS_OUTPUT.PUT_LINE(ddl);

      begin
        execute immediate ddl;
      exception
        when others then
          null;
      end;
    end;
  end loop;
end;
/

SELECT owner, index_name
from dba_indexes
where logging = 'NO' and owner not in ('SYSTEM','DBSNMP','SYS')
/

 

Script to Kill Data Pump Jobs

Script to kill Data Pump jobs and clean up the master tables:

-- Kill expdp jobs and clean master tables

set time on timing on serveroutput on pages 100 lines 32767

col owner_name format a15
col job_name format a30
col operation format a15
col job_mode format a15
col state format a15
col attached_sessions format 99
SELECT owner_name, job_name, operation, job_mode, state, attached_sessions Sessions
FROM dba_datapump_jobs
ORDER BY 1,2
/

declare
  ddl varchar2(32767);
  jobid number;
begin
  for i in (
    SELECT o.status, o.object_id, o.object_type, o.owner,o.object_name FROM dba_objects o, dba_datapump_jobs j WHERE o.owner=j.owner_name AND o.object_name=j.job_name AND j.job_name NOT LIKE 'BIN$%'
  ) LOOP
    begin
      jobid := DBMS_DATAPUMP.ATTACH(i.object_name,i.owner);
      
      if jobid is not null then
        begin
          DBMS_DATAPUMP.STOP_JOB(jobid,1,1);
        exception
          when others then
            null;
        end;
      end if;

      ddl := 'drop table '||i.owner||'.'||i.object_name||'';
      DBMS_OUTPUT.PUT_LINE(ddl);
      begin
        execute immediate ddl;
      exception
        when others then
          null;
      end;
    end;
  end loop;
end;
/

SELECT owner_name, job_name, operation, job_mode, state, attached_sessions Sessions
FROM dba_datapump_jobs
ORDER BY 1,2
/

 

Deleting Oracle Data Pump Jobs

Script to delete all non-running Data Pump jobs:

-- Delete expdp master tables for jobs that
-- were interrupted and are now 'NOT RUNNING'

set time on timing on serveroutput on pages 100 lines 32767

col owner_name format a15
col job_name format a30
col operation format a15
col job_mode format a15
col state format a15
col attached_sessions format n99
SELECT owner_name, job_name, operation, job_mode, state, attached_sessions Sessions
FROM dba_datapump_jobs
ORDER BY 1,2
/

declare
  ddl varchar2(32767);
begin
  for i in (
    SELECT o.status, o.object_id, o.object_type, o.owner,o.object_name FROM dba_objects o, dba_datapump_jobs j WHERE o.owner=j.owner_name AND o.object_name=j.job_name AND j.job_name NOT LIKE 'BIN$%' and j.state = 'NOT RUNNING'
  ) LOOP
    begin
      ddl := 'drop table '||i.owner||'.'||i.object_name||'';
      DBMS_OUTPUT.PUT_LINE(ddl);
      begin
        execute immediate ddl;
      exception
        when others then
          null;
      end;
    end;
  end loop;
end;
/

SELECT owner_name, job_name, operation, job_mode, state, attached_sessions Sessions
FROM dba_datapump_jobs
ORDER BY 1,2
/