Christoph Bohl's Oracle Blog

How To Set Database To Noarchivelog

2013-03-11T13:42:00.000+01:00

# sqlplus /nolog
SQL> connect / as sysdba

The db must be mounted EXCLUSIVE and not open for operation

SQL> shutdown immediate;
SQL> startup mount;

Check the log mode of the database

SQL> select log_mode from v$database;

Set it to noarchivelog mode

SQL> alter database noarchivelog;

Set db open for user operation

SQL> alter database open;

Activate and De-Activate Granted Roles

2013-01-17T12:18:00.000+01:00

Check the roles that are enabled in your session:

select * from session_roles;

Using the command SET ROLE you can set only specific roles to active. Set all active:

set role all;

Disable specific roles:

set role all except role1, role2;

Datapump: Re-Importing TYPEs

2012-11-15T07:10:00.000+01:00

When duplicating a schema which contains TYPE objects using expdp/impdp, you will encounter the following error:

Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
ORA-39083: Object type TYPE failed to create with error:
ORA-02304: invalid object identifier literal

This is because Oracle tries to create the type object in the target schema with the same object identifier (OID) as it has in the source schema. But an OID must be unique within a database.

Add the following parameter to your impdp command to make sure new OIDs are generated upon import:

transform=oid:n

Since: Oracle 10gR2
Source: Oracle Support Doc ID 351519.1

Stop Datapump Job Using PL/SQL API

2012-11-14T13:21:00.001+01:00

Check the status of all current datapump jobs:

select *
from   dba_datapump_jobs
;

Using the PL/SQL API, attach to the job and stop it:

set serveroutput on
set lines 100
--/
declare
   handle number;
begin
  -- format: dbms_datapump.attach('','');
   handle := dbms_datapump.attach('SYS_IMPORT_SCHEMA_01','SYS');
   dbms_datapump.stop_job (handle,1,0);
end;
/

In a previous post, I showed how to achieve this with the command line utility: killing a datapump job.

Column Dependencies

2012-11-13T12:50:00.000+01:00

When looking for information about column dependencies, I stumbled over this article: DBA_DEPENDENCY_COLUMNS. The script below is copied from this source and made available here for easier copying :-) If you run it as sysdba, you'll have a new view DBA_DEPENDENCY_COLUMS. Requires Oracle 11g.

create view dba_dependency_columns as
select d.u_name                                        owner
   , d.o_name                                          name
   , decode  
     ( d.o_type#  
     , 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER'  
     , 4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE', 7, 'PROCEDURE'  
     , 8, 'FUNCTION', 9, 'PACKAGE', 10, 'NON-EXISTENT' 
      , 11, 'PACKAGE BODY', 12, 'TRIGGER' 
      , 13, 'TYPE', 14, 'TYPE BODY', 22, 'LIBRARY' 
      , 28, 'JAVA SOURCE', 29, 'JAVA CLASS' 
      , 32, 'INDEXTYPE', 33, 'OPERATOR' 
      , 42, 'MATERIALIZED VIEW', 43, 'DIMENSION' 
      , 46, 'RULE SET', 55, 'XML SCHEMA', 56, 'JAVA DATA' 
      , 59, 'RULE', 62, 'EVALUATION CONTXT' 
      , 92, 'CUBE DIMENSION', 93, 'CUBE' 
      , 94, 'MEASURE FOLDER', 95, 'CUBE BUILD PROCESS' 
      , 'UNDEFINED' 
      )                                                 type 
    , nvl2( d.po_linkname, d.po_remoteowner, d.pu_name) referenced_owner 
    , d.po_name                                         referenced_name 
    , decode 
      ( d.po_type# 
      , 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER' 
      , 4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE', 7, 'PROCEDURE' 
      , 8, 'FUNCTION', 9, 'PACKAGE', 10, 'NON-EXISTENT' 
      , 11, 'PACKAGE BODY', 12, 'TRIGGER' 
      , 13, 'TYPE', 14, 'TYPE BODY', 22, 'LIBRARY' 
      , 28, 'JAVA SOURCE', 29, 'JAVA CLASS' 
      , 32, 'INDEXTYPE', 33, 'OPERATOR' 
      , 42, 'MATERIALIZED VIEW', 43, 'DIMENSION' 
      , 46, 'RULE SET', 55, 'XML SCHEMA', 56, 'JAVA DATA' 
      , 59, 'RULE', 62, 'EVALUATION CONTXT' 
      , 92, 'CUBE DIMENSION', 93, 'CUBE' 
      , 94, 'MEASURE FOLDER', 95, 'CUBE BUILD PROCESS' 
      , 'UNDEFINED' 
      )                                                 referenced_type 
    , d.po_linkname                                     referenced_link_name 
    , c.name                                            referenced_column 
    , decode(bitand(d.d_property, 3), 2, 'REF', 'HARD') dependency_type 
 from ( select obj# 
             , u_name 
             , o_name 
             , o_type# 
             , pu_name 
             , po_name 
             , po_type# 
             , po_remoteowner 
             , po_linkname 
             , d_property 
             , colpos 
          from sys."_CURRENT_EDITION_OBJ" o 
             , sys.disk_and_fixed_objects po 
             , sys.dependency$ d 
             , sys.user$ u 
             , sys.user$ pu 
         where o.obj# = d.d_obj# 
           and o.owner# = u.user# 
           and po.obj# = d.p_obj# 
           and po.owner# = pu.user# 
           and d.d_attrs is not null 
         model 
               return updated rows 
               partition by 
               ( po.obj#        obj# 
               , u.name         u_name 
               , o.name         o_name 
               , o.type#        o_type# 
               , po.linkname    po_linkname 
               , pu.name        pu_name 
               , po.remoteowner po_remoteowner 
               , po.name        po_name 
               , po.type#       po_type# 
               , d.property     d_property 
               ) 
               dimension by (0 i) 
               measures (0 colpos, substr(d.d_attrs,9) attrs) 
               rules iterate (1000) 
                     until (iteration_number = 4 * length(attrs[0]) - 2) 
               ( colpos[iteration_number+1] 
                 = case bitand 
                        ( to_number 
                          ( substr 
                            ( attrs[0] 
                            , 1 + 2*trunc((iteration_number+1)/8) 
                            , 2 
                            ) 
                          ,'XX' 
                          ) 
                        , power(2,mod(iteration_number+1,8)) 
                        ) 
                   when 0 then null 
                   else iteration_number+1 
                   end 
               ) 
      ) d 
    , sys.col$ c 
where d.obj# = c.obj#
   and d.colpos = c.col#
;

grant select on dba_dependency_columns to select_catalog_role
;

create public synonym dba_dependency_columns for dba_dependency_columns
;

Check Installed Features And Options

2012-11-09T09:35:00.001+01:00

To see a list of installed features and their status:

select *
from   dba_registry
order by comp_name
;

To see which options are available:

select * 
from   v$option
order by parameter
;

Check Default Tablespaces

2012-11-08T07:34:00.000+01:00

Check default tablespaces of the database:

select * 
from   database_properties
where  property_name like '%TABLESPACE'
order by property_name
;

And the users:

select username
,      default_tablespace
,      temporary_tablespace
from   dba_users
order by username
;

VARCHAR2 column length in a Multi Byte Characterset Database

2012-11-01T11:11:00.001+01:00

When migrating data from a single-byte characterset database into a multi-byte characterset database, you will very likely encounter problems with column lenghts.

Oracle uses by default BYTE length sematics for VARCHAR2 columns.

As many characters need more than one byte in a multi-byte characterset database, the field lengths in BYTE will not be sufficient in many cases.

It will therefore be preferable in most cases to use CHAR length semantics in your multi-byte characterset database. This can be configured either on system or session level:

ALTER SYSTEM SET NLS_LENGTH_SEMANTICS=CHAR;
ALTER SESSION SET NLS_LENGTH_SEMANTICS=CHAR;

This way, you do not have to specify CHAR in each and every VARCHAR2 column definition.

SQL Monitoring in 11g

2012-08-07T15:05:00.000+02:00

With Oracle 11g, especially R2, Oracle is making it very easy to get an overview of the SQL statements (both running and finished) and how they perform. See all cursors:

select dbms_sqltune.report_sql_monitor_list(type=>'HTML', report_level=>'ALL') from dual;

Show detailed information for any given SQL ID:

select dbms_sqltune.report_sql_monitor(type=>'ACTIVE', sql_id=>'SQL ID') monitor_report from dual;

There are many options available, such as reporting on the last statement executed in the session, etc.

select dbms_sqltune.report_sql_monitor(type=>'ACTIVE') monitor_report from dual;

--set echo off timing off pagesize 0 linesize 1000 trimspool on trim on
--set long 2000000 longchunksize 2000000
select dbms_sqltune.report_sql_monitor
       (session_id=>sys_context('userenv','sid')
       ,session_serial=>dbms_debug_jdwp.current_session_serial
       ,report_level=>'ALL'
       ,type=>'TEXT'
       ) as monitor_report
from   dual
;

Oracle Exadata Database Machine Tutorials

2012-06-12T00:24:00.000+02:00

Our education partner, Skillbuilders, is pleased to announce that their first series of Oracle Exadata Tutorials are now available. The series consists of the following eleven sessions, all delivered by SkillBuilders Oracle Certified Master John Watson:

About the Exadata Hardware
What Makes the DB Machine Special
Smart Scan in Theory
How Smart Scan Functions - Demo
Smart Scan in Practice
Making Smart Scan Work - Demo
HCC in Theory
HCC performance and Compression Ratio - Demo
HCC Limitations and Best Practices
HCC Compression Degradation Issue - Demo
Exadata is Good But Not Easy

11g Optimizer Statistics

2012-06-04T13:49:00.002+02:00

Earlier this year, Oracle posted two excellent white papers about Optimizer Statistics:

They are worth reading!

Generate Optimizer Trace Files

2012-02-27T10:23:00.000+01:00

After starting this post about generating optimizer trace files, I found that Greg Rahn has already written a perfect overview of all methods to achieve this here.

Upgrading to 11g: Optimizer Changes

2012-02-14T08:18:00.001+01:00

When migrating to Oracle to a newer version, there is and will always be an impact on the performance of existing queries. Hence, it is wise to perform a full performance regression test with your software against the new version.

Oracle has published a good white paper which explains the new features of the Optimizer in 11g and lists important pre- and post-upgrade tasks. Check it out here: Upgrading from Oracle Database 10g to 11g: What to expect from the Optimizer.

Lock / Hang Analysis

2011-10-21T09:24:00.000+02:00

When I have to analyze database lock or even hang issues, I use the following SQL statements, scripts and references:

-- all sessions involved in blocking
-- - blocked sessions
select * 
from   v$session ses
where  ses.blocking_session is not null
union
-- - blockers
select * 
from   v$session ses
where  sid in ( select blocking_session from v$session where blocking_session is not null )
;

-- find root blocker
select 'alter system kill session '''||sid||','||serial#||''' immediate;' kill_db_cmd
,      'kill -9 '||process as kill_os_cmd
,      s.*
from   v$session s
where  sid in ( 
   select blocking_session
   from   v$session
              )
and    blocking_session is null              
--and    username = 'DBUSER'
--and    osuser = 'osuser'
;

-- ddl lock information
select dlk.session_id
,      ses.serial#
,      ses.username
,      ses.schemaname
,      ses.machine
,      ses.logon_time
,      dlk.owner
,      dlk.name
,      dlk.type
,      dlk.mode_held
,      dlk.mode_requested
,      'alter system kill session '''||ses.sid||','||ses.serial#||''' immediate;'
from   dba_ddl_locks dlk
  inner join v$session ses
          on dlk.session_id = ses.sid
where (mode_held <> 'Null' or mode_requested <> 'None')
order by dlk.session_id    
;

-- library cache lock information
select distinct ses.ksusenum as sid
,      ses.ksuseser          as serial#
,      ses.ksuudlna          as username
,      ses.ksusemnm          as module
,      obj.kglnaown          as object_owner
,      obj.kglnaobj          as object_name
,      lck.kgllkcnt          as lock_count
,      lck.kgllkmod          as lock_mode
,      lck.kgllkreq          as lock_req
,      swt.state             as wait_state
,      swt.event             as wait_event
,      swt.seconds_in_wait   as sec_in_wait
from   x$kgllk lck        -- library object locks (both held and requested)
,      x$kglob obj        -- objects
,      x$ksuse ses        -- sessions
,      v$session_wait swt -- session wait
where  lck.kgllkreq > 0   -- a lock is requested
and    obj.kglhdadr = lck.kgllkhdl
and    lck.kgllkuse = ses.addr
and    swt.sid = ses.indx
order by swt.seconds_in_wait desc
;

sta ?/rdbms/admin/utllockt
--WAITING_SESSION   LOCK_TYPE         MODE_REQUESTED MODE_HELD      LOCK_ID1          LOCK_ID2
------------------- ----------------- -------------- -------------- ----------------- -----------------
--524               None
--   511            Transaction       Exclusive      Exclusive      1835044           8310

-- it might also be of interest to look into ash
-- history, query coordinator, etc
select * 
from   v$active_session_history
where  time_waited > 0
;

-- displays a row for each lock or latch that is being held, and one row for each outstanding request for a lock or latch.
--  http://download.oracle.com/docs/cd/B14117_01/server.101/b10755/statviews_2210.htm
select * 
from   dba_lock_internal
;

-- all locked objects
select oracle_username
,      os_user_name
,      session_id
,      locked_mode
,      object_name
,      object_type
from   v$locked_object lob
,      dba_objects obj
where  lob.object_id = obj.object_id
order by oracle_username
,        os_user_name
,        session_id
;

-- enqueues
select sid
,      event
,      chr(bitand(p1,-16777216)/16777215)|| chr(bitand(p1, 16711680)/65535) "type"
,      mod(p1,16) as "mode" 
,      p2
,      p3 
from   v$session_wait 
where  event like 'enq%'
; 

-- cumulated statistics on enqueues
select * 
from   v$enqueue_statistics
;


-- see who accesses an object
select *
from   v$access
where  object like 'BLA'
;

-- oradebug dump systemstate
-- - shows complete dump of all processes including locks held/requested
-- - dump created in user dump dest
-- - connect as sysdba
oradebug setmypid
oradebug unlimit
oradebug dump systemstate 266
oradebug tracefile_name

Links / References:

MOS: How to Find which Session is Holding a Particular Library Cache Lock [ID 122793.1]
http://orainternals.wordpress.com/2009/06/02/library-cache-lock-and-library-cache-pin-waits/
http://www.evdbt.com/enqwaits.sql
http://www.perfvision.com/papers/09_enqueues.ppt

Oracle Performance Diagnostic Guide

2011-08-16T16:01:00.004+02:00

The Oracle way of identifying database hang and performance issues: Oracle Support Note 390374.1.

Check Database Character Set

2011-06-21T07:12:00.004+02:00

There are several possible ways to find out the database character set:

select value
from   nls_database_parameters
where  parameter = 'NLS_CHARACTERSET'
;

select property_value
from   database_properties
where  property_name = 'NLS_CHARACTERSET'
;

select value$
from   sys.props$
where  name = 'NLS_CHARACTERSET'
;

Exadata - finally!

2011-06-20T08:45:00.002+02:00

Just connected to an exadata database! An awesome feeling!

We are doing a POC on a X2-2 half rack. The first results are extremely promising!

Get Explain Plan From AWR

2011-06-06T17:01:00.003+02:00

To get an explain plan from the AWR for a statement that ran in the past, use the queries below:

select *
from   dba_hist_sqltext
where  sql_text like '%SQL Text you are looking for%'
;

select *
from   table(dbms_xplan.display_awr('<sql_id>',null,null,'ALL'))
;

Explain The Explain Plan

2011-05-10T12:52:00.002+02:00

Oracle has published a good introduction to reading execution plans on OTN.

Show Temporary Space Usage

2011-03-21T13:00:00.002+01:00

Need to know how much temp space is in use? By which session/SQL command?

Show the current temporary space usage:

-- current temp space usage
select tablespace
,      contents
,      segtype
,      used_mb
,      round(used_mb/1024) used_gb
from   (
  select tsu.tablespace
  ,      tsu.contents
  ,      tsu.segtype
  ,      round((tsu.blocks * tsp.block_size / 1024 / 1024)) used_mb
  from   v$tempseg_usage tsu
    inner join dba_tablespaces tsp
            on tsu.tablespace = tsp.tablespace_name
    )
;

Show the current temporary space usage per tablespace:

-- current temp space usage per tablespace
select tablespace
,      size_mb
,      used_mb
,      size_mb - used_mb as free_mb
,      round(size_mb/1024) size_gb
,      round(used_mb/1024) used_gb
,      round(size_mb/1024) - round(used_mb/1024) as free_gb
,      round(nvl((size_mb - used_mb)/size_mb * 100, 0)) pct_free
from   (
  select tsu.tablespace
  ,      df.size_mb
  ,      round((tsu.blocks * tsp.block_size / 1024 / 1024)) used_mb
  from   (
            select tablespace
            ,      sum(blocks) blocks
            from   v$tempseg_usage
            group by tablespace
         ) tsu
    inner join (
                  select tablespace_name
                  ,      round(sum(bytes)/1014/124) size_mb 
                  from   dba_temp_files
                  group by tablespace_name
                  union all
                  select tablespace_name
                  ,      round(sum(bytes)/1014/124) size_mb 
                  from   dba_data_files
                  group by tablespace_name
               ) df
            on tsu.tablespace = df.tablespace_name
    inner join dba_tablespaces tsp
            on tsu.tablespace = tsp.tablespace_name
    )
;

Show the current temporary space usage per session:

-- temp space usage per session
select ses.sid
,      ses.serial#
,      ses.username
,      ses.osuser
,      ses.process
,      ses.machine
,      ses.module
,      ses.program
,      tsu.tablespace
,      tsu.contents
,      tsu.segtype
,      round(tsu.blocks * tsp.block_size / 1024 / 1024) as used_mb
,      round(tsu.blocks * tsp.block_size / 1024 / 1024 / 1024) as used_gb
,      sql.sql_fulltext
from   v$tempseg_usage tsu
  inner join v$session ses
          on tsu.session_addr = ses.saddr
  inner join dba_tablespaces tsp
          on tsu.tablespace = tsp.tablespace_name
  left outer join v$sql sql
          on tsu.sql_id = sql.sql_id
;

Find Tracefile Name

2011-02-17T10:36:00.002+01:00

The SQL below shows the tracefile name and location for the current session:


select ud.value || '/' || 
       dn.value || '_ora_' || 
       p.spid || 
       nvl2(p.traceid,  '_' || p.traceid, null ) ||
       '.trc'  "Trace File"
from   v$session s
  inner join v$process p
          on s.paddr = p.addr
  cross join (
     select value 
     from   v$parameter
     where  name = 'user_dump_dest'
             ) ud
  cross join (
     select value 
     from   v$parameter
     where  name = 'db_name'
             ) dn
where s.audsid = sys_context('userenv','sessionid')
;

With Oracle 11g, the tracefile is available directly in v$process:


select p.tracefile 
from   v$session s 
  inner join v$process p
          on s.paddr = p.addr
where  sys_context('userenv','sessionid') = s.audsid
;

Note that there seems to be a bug in pre Oracle 11g releases. v$process.traceid returns to the tracefile_identifier you have set for your session on all non-background processes... Therefore the result of the query below is wrong for pre 11g releases.


alter session set tracefile_identifier = 'mytraceid';

select ud.value || '/' || 
       dn.value || '_ora_' || 
       p.spid || 
       nvl2(p.traceid,  '_' || p.traceid, null ) ||
       '.trc'  "Trace File"
from   v$session s
  inner join v$process p
          on s.paddr = p.addr
  cross join (
     select value 
     from   v$parameter
     where  name = 'user_dump_dest'
             ) ud
  cross join (
     select value 
     from   v$parameter
     where  name = 'db_name'
             ) dn

Determine The Owner Of A Package

2011-01-07T09:07:00.002+01:00

Sometimes it is useful to know the owner of a PL/SQL Package at runtime.

Hm, why would we not know which owner a package belongs to? There are multiple reasons. A utility package might be installed in several schemata, or in development, every developer might have his own copy of the application schemata.

One way to find the owner, is by inspecting the PL/SQL call stack. The latest entry on the stack references the current PL/SQL object and its owner.

Example of a call stack:


----- PL/SQL Call Stack -----
  object      line  object
  handle    number
name
3e218c390        13  package body PKG_OWNER.PKG_DEFINER_RIGHTS
3e5e67860         6  package body PKG_OWNER2.PKG_REAL

Why don't we just use sys_context('userenv', 'current_schema')? The current schema is not always the package owner. If you are using a PL/SQL package with invoker rights (authid current_user), it shows the caller.

OK, let's start with the preparation for a simple example. We'll create two database users first, pkg_owner (which will own the PL/SQL packages) and pkg_caller (which will invoke them).


create user pkg_owner identified by pkg_owner;
grant connect, create procedure to pkg_owner;

create user pkg_caller identified by pkg_caller;
grant connect to pkg_caller;

Next, we create to packages, pkg_definer_rights with definer rights and pkg_invoker_rights with invoker rights in the pkg_owner schema. Both have a function to retrieve the current schema and another function to get the owner from the callstack.


-- create the test packages in the pkg_owner schema
connect pkg_owner/pkg_owner;
--/
create or replace package pkg_definer_rights
is
  -- get current schema always returns the owner for AUTHID DEFINER packages
  function get_current_schema return varchar2;
  -- the call stack always shows the correct owner, no matter what AUTHID is set to
  function get_owner_by_callstack return varchar2;

end;
/
--/
create or replace package body pkg_definer_rights
is

  function get_current_schema return varchar2 
  is
  begin
    return sys_context('userenv', 'current_schema');
  end;

  function get_owner_by_callstack return varchar2
  is
  begin
    -- the first occurrence of the pattern . in the call stack
    --   is . of the least recently called package (i.e. this one)
    -- therefore take the . string out of the call stack and
    --   then take just the first part before the dot as the owner
    return regexp_replace(regexp_substr(dbms_utility.format_call_stack
                                       ,'[A-Z][A-Z0-9_$#]*\.[A-Z][A-Z0-9_$#]*',1,1,'i'
                                       )
                         ,'([A-Z][A-Z0-9_$#]*)\.([A-Z][A-Z0-9_$#]*)','\1',1,1,'i'
                         );
  end;

end;
/

--/
create or replace package pkg_invoker_rights
authid current_user
is

  -- get current schema returns the current schema, not the package owner for 
  --   AUTHID CURRENT_USER packages
  function get_current_schema return varchar2;
  -- the call stack always shows the correct owner, no matter what AUTHID is set to
  function get_owner_by_callstack return varchar2;

end;
/
--/
create or replace package body pkg_invoker_rights
is

  function get_current_schema return varchar2 
  is
  begin
    return sys_context('userenv', 'current_schema');
  end;

  function get_owner_by_callstack return varchar2
  is
  begin
    -- the first occurrence of the pattern . in the call stack
    --   is . of the least recently called package (i.e. this one)
    -- therefore take the . string out of the call stack and
    --   then take just the first part before the dot as the owner
    return regexp_replace(regexp_substr(dbms_utility.format_call_stack
                                       ,'[A-Z][A-Z0-9_$#]*\.[A-Z][A-Z0-9_$#]*',1,1,'i'
                                       )
                         ,'([A-Z][A-Z0-9_$#]*)\.([A-Z][A-Z0-9_$#]*)','\1',1,1,'i'
                         );
  end;

end;
/

grant execute on pkg_definer_rights to pkg_caller;
grant execute on pkg_invoker_rights to pkg_caller;

OK, we're prepared. So let's test them:


-- connect as caller to invoke the test packages
connect pkg_caller/pkg_caller;
--
select pkg_owner.pkg_definer_rights.get_current_schema definer_current_schema
,      pkg_owner.pkg_invoker_rights.get_current_schema invoker_current_schema
,      pkg_owner.pkg_definer_rights.get_owner_by_callstack definer_owner_by_callstack
,      pkg_owner.pkg_invoker_rights.get_owner_by_callstack invoker_owner_by_callstack
from   dual
;


DEFINER_CURRENT_SCHEMA:        PKG_OWNER
INVOKER_CURRENT_SCHEMA:        PKG_CALLER
DEFINER_OWNER_BY_CALLSTACK:    PKG_OWNER
INVOKER_OWNER_BY_CALLSTACK:    PKG_OWNER

As expected, the function pkg_invoker_rights.get_current_schema does not return the package owner, but the current schema. It can be changed using alter session set current_schema=schema.


alter session set current_schema = pkg_owner;
select pkg_owner.pkg_definer_rights.get_current_schema definer_current_schema
,      pkg_owner.pkg_invoker_rights.get_current_schema invoker_current_schema
,      pkg_owner.pkg_definer_rights.get_owner_by_callstack owner_by_callstack
,      pkg_owner.pkg_invoker_rights.get_owner_by_callstack invoker_owner_by_callstack
from   dual
;


DEFINER_CURRENT_SCHEMA:        PKG_OWNER
INVOKER_CURRENT_SCHEMA:        PKG_OWNER
DEFINER_OWNER_BY_CALLSTACK:    PKG_OWNER
INVOKER_OWNER_BY_CALLSTACK:    PKG_OWNER

Yes, the invoker current schema now reflects our alter session command.

If we'd like to include the code of the function get_owner_by_callstack in a common utility package and not include it in each and every PL/SQL Unit we are developing, does our example still work?

To find out, we create a package pkg_real in a new schema, pkg_owner2. This package is a mock up for one of our actual application package that want to make use of our utility to show the package owner:


create user pkg_owner2 identified by pkg_owner2;
grant connect, create procedure to pkg_owner2;

-- grant permissions to pkg_owner2
connect pkg_owner/pkg_owner;
grant execute on pkg_definer_rights to pkg_owner2;

-- create an invoking package
connect pkg_owner2/pkg_owner2;

create or replace package pkg_real
is
  function real_function return varchar2;
end;
/
create or replace package body pkg_real
is
  function real_function return varchar2
  is
  begin
    return pkg_owner.pkg_definer_rights.get_owner_by_callstack;
  end;
end;
/

grant execute on pkg_real to pkg_caller;

OK, now we test our package:


-- call the real one
connect pkg_caller/pkg_caller
--
select pkg_owner2.pkg_real.real_function
from   dual
;


PKG_REAL_OWNER
--------------
PKG_OWNER

Is this correct? No! The owner is pkg_owner2, not pkg_owner... What went wrong? Let's look at the call stack:


----- PL/SQL Call Stack -----
  object      line  object
  handle    number
name
3e218c390        13  package body PKG_OWNER.PKG_DEFINER_RIGHTS
3e5e67860         6  package body PKG_OWNER2.PKG_REAL

Of course! The top most entry in the call stack is now the utility function, not the real application package that we're interested in. So we change the method to get the second line in the call stack which will be the actual package that uses the utility function:


connect pkg_owner/pkg_owner
--
create or replace package body pkg_definer_rights
is

  function get_current_schema return varchar2 
  is
  begin
    return sys_context('userenv', 'current_schema');
  end;

  function get_owner_by_callstack return varchar2
  is
  begin
    -- the first occurrence of the pattern . in the call stack
    --   is . of the least recently called package (i.e. this one)
    -- therefore take the . string out of the call stack and
    --   then take just the first part before the dot as the owner
    return regexp_replace(regexp_substr(dbms_utility.format_call_stack
                                       ,'[A-Z][A-Z0-9_$#]*\.[A-Z][A-Z0-9_$#]*',1,2,'i'
                                       )
                         ,'([A-Z][A-Z0-9_$#]*)\.([A-Z][A-Z0-9_$#]*)','\1',1,1,'i'
                         );
  end;

end;
/

And we try again:


-- try again
connect pkg_caller/pkg_caller
--
select pkg_owner2.pkg_real.real_function
from   dual
;


REAL_FUNCTION
-------------
PKG_OWNER2

OK, that looks better :-)

Dynamic Memory Components

2010-12-03T12:10:00.003+01:00


-- version 10g
show sga;
-- sga dynamic components (ASMM, SGA_TARGET)
select component
,      current_size/1024/1024 "CURRENT_SIZE"
,      min_size/1024/1024 "MIN_SIZE"
,      user_specified_size/1024/1024 "USER_SPECIFIED_SIZE"
,      last_oper_type "TYPE" 
from   v$sga_dynamic_components
;
-- sga dynamic free memory (to be allocated by oracle)
select current_size/1024/1024 "Current Size MB"
from   v$sga_dynamic_free_memory
;
-- sga resize operations
select component
,      oper_type
,      oper_mode
,      initial_size/1024/1024 "INITIAL"
,      TARGET_SIZE/1024/1024 "TARGET"
,      FINAL_SIZE/1024/1024 "FINAL"
,      status 
,      TO_CHAR(end_time, 'YYYY-MM-DD HH24:MI:SS') "DATE" 
from   v$sga_resize_ops
;


-- version 11g
show sga;
-- memory dynamic components (ASMM, MEMORY_TARGET)
select component
,      current_size/1024/1024 "CURRENT_SIZE"
,      min_size/1024/1024 "MIN_SIZE"
,      user_specified_size/1024/1024 "USER_SPECIFIED_SIZE"
,      last_oper_type "TYPE" 
,      TO_CHAR(last_oper_time, 'YYYY-MM-DD HH24:MI:SS') "DATE" 
from   v$memory_dynamic_components
;
-- memory resize operations
select component
,      oper_type
,      oper_mode
,      initial_size/1024/1024 "INITIAL"
,      TARGET_SIZE/1024/1024 "TARGET"
,      FINAL_SIZE/1024/1024 "FINAL"
,      status 
,      TO_CHAR(end_time, 'YYYY-MM-DD HH24:MI:SS') "DATE" 
from   v$memory_resize_ops
;

Manually Recompile Objects

2010-11-10T16:16:00.004+01:00

Sometimes one needs to manually recompile objects in the database. The query below generates the commands required. It does however not take care of any sequencing that might be required.

I have posted tips for "automatic" re-compilation here.


select 'alter '||
          case when object_type = 'PACKAGE BODY' then 'PACKAGE'
               when object_type = 'TYPE BODY' then 'TYPE'
               when (object_type = 'SYNONYM' and owner = 'PUBLIC') then 'PUBLIC SYNONYM'
            else object_type 
          end||' '||
          case when (object_type = 'SYNONYM' and owner = 'PUBLIC') then null
            else owner||'.' 
          end||
          object_name||' compile'||
          case when object_type in ('PACKAGE BODY','TYPE BODY') then ' BODY'
            else null 
          end||';'
from   dba_objects
where  status != 'VALID'
and    owner like 'T136291%'
;

Killing A Datapump Job

2010-11-03T15:07:00.003+01:00

If you need to kill a datapump import or export job (impdp/expdp), it is not sufficient to just kill the operating system process.

The oracle job will still be pending. It can be stopped by using the PL/SQL API or the impdp/expdp utilities.

Here is an example of how to attach to the job and stop it immediately.


expdp 'userid="/ as sysdba"' attach=<NAME_OF_JOB>
Export> stop_job=immediate
Are you sure you wish to stop this job ([y]/n): y

Here's how to find running impdp/expdp jobs:


select * 
from   dba_datapump_jobs
;