Oracle does not come with the concept of schema level security. That is, one cannot create user A and user B where user B is granted the privilege to do anything in user A’s schema.

There is a workaround though. One can create a database DDL trigger as follows: 

CREATE OR REPLACE
TRIGGER prevent_alter_any
BEFORE ALTER ON database
BEGIN

     dbms_output.put_line('Checking..');

     IF SYS_CONTEXT('USERENV','SESSION_USER') != 'EDW' and ora_dict_obj_owner = 'EDW'

     THEN

          raise_application_error(-20901,'You do not have the privileges to perform the action');

     end if;

END prevent_alter_any;
/

This will prevent a user with the ALTER ANY table system privilege from being able to alter objects in the EDW schema.

By juggling the logic one can effectively allow ANY privileges on only a set of schemas for a particular user. 

Remember that this is still a workaround, and preferably typical users should, in general, not have powerful system privileges

Use this view as an example to create your own to see who has access to what:

create or replace view table_privs as
SELECT r.GRANTEE, r.owner, r.table_name, rle, granted_role, direct , r.grantor as role_grantor, d.grantor as direct_grantor, r.privilege, r.grantable
FROM
( 
SELECT rp.GRANTEE, 'Y' as RLE, rp.GRANTED_ROLE, tp.OWNER, table_name, grantor, privilege, grantable
FROM DBA_TAB_PRIVS tp, dba_role_privs rp
WHERE
 tp.GRANTEE = rp.GRANTED_ROLE
and tp.OWNER IN ('EDW','EDW_P','SECURE')
AND tp.GRANTEE NOT IN (SELECT USERname FROM DBA_USERS)) r
,
(SELECT GRANTEE, 'Y' as DIRECT, owner, table_name, grantor, privilege, grantable 
FROM DBA_TAB_PRIVS WHERE OWNER IN ('EDW','EDW_P','SECURE')
AND GRANTEE IN (SELECT USERname FROM DBA_USERS)) d
WHERE
r.grantee  = d.grantee (+)
and
r.table_name = d.table_name (+)
and
r.privilege = d.privilege (+)
UNION
SELECT d.GRANTEE, d.owner, d.table_name, rle, granted_role, direct , d.grantor as rg, d.grantor as dg, d.privilege, d.grantable
FROM
( 
SELECT rp.GRANTEE, 'Y' as RLE, rp.GRANTED_ROLE, tp.OWNER, table_name, grantor, privilege, grantable
FROM DBA_TAB_PRIVS tp, dba_role_privs rp
WHERE
 tp.GRANTEE = rp.GRANTED_ROLE
and tp.OWNER IN ('EDW','EDW_P','SECURE')
AND tp.GRANTEE NOT IN (SELECT USERname FROM DBA_USERS)) r
,
(SELECT GRANTEE, 'Y' as DIRECT, owner, table_name, grantor, privilege, grantable 
FROM DBA_TAB_PRIVS WHERE OWNER IN ('EDW','EDW_P','SECURE')
AND GRANTEE IN (SELECT USERname FROM DBA_USERS)) d
WHERE
d.grantee =  r.grantee (+)
and
 d.table_name = r.table_name (+)
and
d.privilege = r.privilege   (+)
ORDER BY GRANTEE, TABLE_NAME, PRIVILEGE;

In order to find blocked sessions execute: 

select ses.MODULE, ses.ACTION, ses.BLOCKING_SESSION, ses.* from v$session seswhere ses.BLOCKING_SESSION is not null; 

In order to find blocking sessions execute: 

select s.sid as blocking_sid, s.serial#  , p.pid, p.spid, p.PGA_USED_MEM, s.LOGON_TIME , s.LOCKWAIT, s.MODULE, s.ACTION, s.*
from v$session s, v$process p
where p.ADDR = s.paddr  and s.sid in (select ses.BLOCKING_SESSION from v$session ses  where ses.BLOCKING_SESSION is not null); 

In order to associate an actual user with one of these sessions, take note of the process id (pid) of the process associated with the session.

Log into Oracle Financials with an appropriate user and navigate to the user monitor screen. Enter query by example mode and enter the PID in the ‘Oracle Process’ screen.

Execute the query.

This will allow you to identify the user and take further action as appropriate.

Herman Scheepers

For the health and wealth part visit www.nofat.co.za or www.healthycash.co.za

On the Oracle side, here is a nice example of how to use lead/lag finctions to update redundant intra table previous/next row pointers:

declare
  n pls_integer :=1;
begin

  FOR c IN (select * from
               (
                  select p.CLIENT_CUR_KEY, p.RECORD_START_DATE, P.RECORD_STOP_DATE
                  ,lead(client_key,1)
                  over
                  (
                    partition by CLIENT_CUR_KEY order by RECORD_START_DATE
                  ) as ACTUAL_NEXT_CLIENT_KEY, p.NEXT_CLIENT_KEY
                  ,
                   lag(client_key,1)
                              over
                              (
                                partition by CLIENT_CUR_KEY order by RECORD_START_DATE
                              ) as ACTUAL_prev_CLIENT_KEy           
                  ,
                  p.CLIENT_KEY, p.PREV_CLIENT_KEY , p.EDW_LOAD_KEY
                  ,rowid
                  from edw_p.dim_pers_detail p
               )
            where   
              nvl(ACTUAL_NEXT_CLIENT_KEY,-1) != nvl(CLIENT_KEY,-2)
                or nvl(ACTUAL_prev_CLIENT_KEY,-1) != nvl(prev_CLIENT_KEY,-2)

  ) LOOP
 
    if c.ACTUAL_NEXT_CLIENT_KEY is not null then
      UPDate   edw_p.dim_pers_detail set
        NEXT_CLIENT_KEY = c.ACTUAL_NEXT_CLIENT_KEY
      where
        rowid = c.rowid
        and
         nvl(NEXT_CLIENT_KEY,-1) != c.ACTUAL_NEXT_CLIENT_KEY;
    end if;    
      
    if c.ACTUAL_prev_CLIENT_KEY is not null then
   
     UPDate edw_p.dim_pers_detail set
            PREV_CLIENT_KEY = c.ACTUAL_prev_CLIENT_KEy
          where
            rowid = c.rowid
            and
            nvl(PREV_CLIENT_KEY,-1) != c.ACTUAL_prev_CLIENT_KEY;
           
    end if;        
            

    n:=n+1;

    if n>1000 then
      commit;
      n:=1;
    end if;

  END LOOP;
 
end; 
/
commit;  

Herman Scheepers signing out for now 

Oracle expert performance tuning DBA and architect 

How to check that your KEEP cache is right-sized.

I recently wanted to reduce the KEEP cache on one of the databases I am working on since evidence indicated that increased PGA RAM would be more useful (slow hash joins aplenty and no easy way to avoid them, but that’s a story for a different day).

I ran the following query that I got from Oracle support:

select decode(pd.bp_id,1,'KEEP',2,'RECYCLE',3,'DEFAULT',
        4,'2K SUBCACHE',5,'4K SUBCACHE',6,'8K SUBCACHE',
         7,'16K SUBCACHE',8,'32KSUBCACHE','UNKNOWN') subcache,
         bh.object_name,bh.blocks
from x$kcbwds ds,x$kcbwbpd pd,(select /*+ use_hash(x) */ set_ds,
         o.name object_name,count(*) BLOCKS
         from obj$ o, x$bh x where o.dataobj# = x.obj
         and x.state !=0 and o.owner# !=0
         group by set_ds,o.name) bh 
where ds.set_id >= pd.bp_lo_sid
and ds.set_id <= pd.bp_hi_sid
and pd.bp_size != 0
and ds.addr=bh.set_ds;

And got result looking like this:

KEEP         DIM_AGREEMENT_C                12840                 

DEFAULT      DIM_PERS_DETAIL                31491                 

DEFAULT      TXPKR_PERSON_CONTRACT          19969                 

DEFAULT      TXPKE_CONTRACT                 8181                  

DEFAULT      MA_CLIENT_CONTACT_DETS_UC_C    2860                   

KEEP         DIM_AGREEMENT_C                6854                  

DEFAULT      SQL060602224851480             11333                 

I pasted the results into MS Excel and used the text to columns function in the data menu to format it nicely. Then I used the SUMIF conditional aggregation function to total all the blocks of objects in the KEEP cache and multiplied the result with the db block size to get the size of my actual KEEP cache.

This worked out to 770M with the specified db_keep_cache_size being 800M, so all was good in the land of KEEP.

A way, by the way (so to speak) to observe this is to employ SQL Plus as follows:

SQL> show parameters keep

NAME                                 TYPE        VALUE

------------------------------------ ----------- ----------

buffer_pool_keep                     string

control_file_record_keep_time        integer     32

db_keep_cache_size                   big integer 800M

Herman Scheepers signing  of from Cape Town

I was looking around the Web for a simple way to measure throughput at the instance  level, and by the time I was all Googled out I had not find anything suitable yet. I thus decided to roll my own.

It consists of using the difference in bytes shifted between to points in time to calculate the throughput during the interval so delineated.

Script to create the required tables:

--
-- IO_SAMPLES  (Table) 
--
CREATE TABLE XY90260.IO_SAMPLES
(
  ID                          NUMBER                NULL,
  IO_TIMESTAMP                DATE                  NULL,
  PHYSICAL_READ_IO_REQUESTS   NUMBER                NULL,
  PHYSICAL_WRITE_IO_REQUESTS  NUMBER                NULL,
  PHYSICAL_READ_TOTAL_MBR     NUMBER                NULL,
  PHYSICAL_WRITE_TOTAL_MBR    NUMBER                NULL,
  PHYSICAL_READ_TOTAL_BYTES   NUMBER                NULL,
  PHYSICAL_WRITE_TOTAL_BYTES  NUMBER                NULL
)
TABLESPACE USERS
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           );

--
-- IO_SAMPLES_PK  (Index)
--
--  Dependencies:
--   IO_SAMPLES (Table)
--
CREATE UNIQUE INDEX XY90260.IO_SAMPLES_PK ON XY90260.IO_SAMPLES
(ID)
TABLESPACE USERS
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           );

--
-- IO_SAMPLES_UK  (Index)
--
--  Dependencies:
--   IO_SAMPLES (Table)
--
CREATE UNIQUE INDEX XY90260.IO_SAMPLES_UK ON XY90260.IO_SAMPLES
(IO_TIMESTAMP)
TABLESPACE USERS
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           );

--
-- Non Foreign Key Constraints for Table IO_SAMPLES
--
ALTER TABLE XY90260.IO_SAMPLES ADD (
  CONSTRAINT IO_SAMPLES_PK
 PRIMARY KEY
 (ID)
    USING INDEX
    TABLESPACE USERS
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
               ));

ALTER TABLE XY90260.IO_SAMPLES ADD (
  CONSTRAINT IO_SAMPLES_UK
 UNIQUE (IO_TIMESTAMP)
    USING INDEX
    TABLESPACE USERS
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
               ));

ALTER TABLE XY90260.IO_SAMPLE_DELTAS ADD (
  CONSTRAINT IO_SAMPLE_DELTAS_FROM_FK
 FOREIGN KEY (ID_FROM)
 REFERENCES XY90260.IO_SAMPLES (ID),
  CONSTRAINT IO_SAMPLE_DELTAS_TO_FK
 FOREIGN KEY (ID_TO)
 REFERENCES XY90260.IO_SAMPLES (ID));

-
-- IO_SAMPLE_DELTAS  (Table)
--
--  Dependencies:
--   IO_SAMPLES (Table)
--
CREATE TABLE XY90260.IO_SAMPLE_DELTAS
(
  ID_FROM       NUMBER                              NULL,
  ID_TO         NUMBER                              NULL,
  IO_SPEED_MBS  NUMBER                              NULL
)
TABLESPACE USERS
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           );

--
-- IO_SAMPLE_DELTAS_UK  (Index)
--
--  Dependencies:
--   IO_SAMPLE_DELTAS (Table)
--
CREATE UNIQUE INDEX XY90260.IO_SAMPLE_DELTAS_UK ON XY90260.IO_SAMPLE_DELTAS
(ID_FROM, ID_TO)
TABLESPACE USERS
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           );

--
-- Non Foreign Key Constraints for Table IO_SAMPLE_DELTAS
--
ALTER TABLE XY90260.IO_SAMPLE_DELTAS ADD (
  CONSTRAINT IO_SAMPLE_DELTAS_UK
 UNIQUE (ID_FROM, ID_TO)
    USING INDEX
    TABLESPACE USERS
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
               ));

--
-- Foreign Key Constraints for Table IO_SAMPLE_DELTAS
--
ALTER TABLE XY90260.IO_SAMPLE_DELTAS ADD (
  CONSTRAINT IO_SAMPLE_DELTAS_FROM_FK
 FOREIGN KEY (ID_FROM)
 REFERENCES XY90260.IO_SAMPLES (ID));

ALTER TABLE XY90260.IO_SAMPLE_DELTAS ADD (
  CONSTRAINT IO_SAMPLE_DELTAS_TO_FK
 FOREIGN KEY (ID_TO)
 REFERENCES XY90260.IO_SAMPLES (ID));

The script to take a sample:

CREATE OR REPLACE procedure XY90260.take_io_sample as

  i       number;
  mx      number;
  s       number;
begin

select max(id) into mx from io_samples;

select nvl(max(id),1)+1 into i from io_samples;

INSERT INTO io_samples (
 id
 ,io_timestamp
,physical_read_io_requests
,physical_write_io_requests
,physical_read_total_mbr
,physical_write_total_mbr
,physical_read_total_bytes
,physical_write_total_bytes)
SELECT
i
,
sysdate
,
sum(decode(name,'physical read total IO requests',value,0)
  - decode(name,'physical read total multi block requests',value,0))
  ,
sum(decode(name,'physical write total IO requests',value,0)
  - decode(name,'physical write total multi block requests',value,0)) sw2
  ,
sum(decode(name,'physical read total multi block requests',value,0)) lr2
, sum(decode(name,'physical write total multi block requests',value,0)) lw2
, sum(decode(name,'physical read total bytes',value,0)) tbr2
, sum(decode(name,'physical write total bytes',value,0)) tbw2
FROM v$sysstat;

-- automatically calculate delta with most recent other sample

calc_io_sample_delta(mx,i,s);

dbms_output.put_line(s);

end;
/

The script to do the required scheduling:

BEGIN
  SYS.DBMS_SCHEDULER.CREATE_JOB
    (
       job_name        => 'XY90260.TAKE_IO_SAMPLE_JOB'
      ,start_date      => TO_TIMESTAMP_TZ('2009/12/20 14:12:19.340308 +02:00','yyyy/mm/dd hh24:mi:ss.ff tzh:tzm')
      ,repeat_interval => 'freq=hourly; byminute=0'
      ,end_date        => NULL
      ,job_class       => 'DEFAULT_JOB_CLASS'
      ,job_type        => 'PLSQL_BLOCK'
      ,job_action      => 'BEGIN take_io_sample; END;'
      ,comments        => 'Instance level IO sampling.'
    );
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'XY90260.TAKE_IO_SAMPLE_JOB'
     ,attribute => 'RESTARTABLE'
     ,value     => FALSE);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'XY90260.TAKE_IO_SAMPLE_JOB'
     ,attribute => 'LOGGING_LEVEL'
     ,value     => SYS.DBMS_SCHEDULER.LOGGING_RUNS);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
    ( name      => 'XY90260.TAKE_IO_SAMPLE_JOB'
     ,attribute => 'MAX_FAILURES');
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
    ( name      => 'XY90260.TAKE_IO_SAMPLE_JOB'
     ,attribute => 'MAX_RUNS');
  BEGIN
    SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
      ( name      => 'XY90260.TAKE_IO_SAMPLE_JOB'
       ,attribute => 'STOP_ON_WINDOW_CLOSE'
       ,value     => FALSE);
  EXCEPTION
    -- could fail if program is of type EXECUTABLE...
    WHEN OTHERS THEN
      NULL;
  END;
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'XY90260.TAKE_IO_SAMPLE_JOB'
     ,attribute => 'JOB_PRIORITY'
     ,value     => 3);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
    ( name      => 'XY90260.TAKE_IO_SAMPLE_JOB'
     ,attribute => 'SCHEDULE_LIMIT');
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'XY90260.TAKE_IO_SAMPLE_JOB'
     ,attribute => 'AUTO_DROP'
     ,value     => TRUE);

  SYS.DBMS_SCHEDULER.ENABLE
    (name                  => 'XY90260.TAKE_IO_SAMPLE_JOB');
END;
/

From which it obviously follows that the following query will give you instance throughput:

select
  to_char(n.io_timestamp,'YYYY-MM-DD:HH24:MI:SS') AS TS, IO_SPEED_MBS
  ,n.PHYSICAL_READ_IO_REQUESTS-prev.PHYSICAL_READ_IO_REQUESTS+n.PHYSICAL_WRITE_IO_REQUESTS-prev.PHYSICAL_WRITE_IO_REQUESTS
    as total_io_requests  
from
  io_samples       n,
  io_samples       prev, 
  io_sample_deltas d
where
  n.id = d.ID_TO
  AND
  prev.id = (select max(id) from io_samples where id < n.id)
order by
  n.io_timestamp;

With a bit of Excel magic you can produce a daily load profile such as: