Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Hierchical wth null and holes

bpolarskiApr 6 2011 — edited Apr 7 2011
I am trying to mine the DBA_HIST_ACTIVE_SESS_HISTORY. The column that interrest me are 'sample_time','session_sid','session_serial#', 'blocking_session' and blocking_Session_serial#'.

The purpose is to list per sample time the blocking session tree (and related data here out of scope).
When the blocking last several minutes, all blocked session are sampled and the data is rather relevant
for troubleshooting, thought not absolute. But the data model is not easy for you may have holes.
I have reproduced a simplified version of the data pattern as
it is in DBA_HIST_ACTIVE_SESS_HISTORY and what I want to achieve.

-- grp stands for time_sample and it is the base grouping
-- sid  stands for session 
-- bsid stands for blocking session, this is a column of the SID row 
--         but reference the same type of object (session).
--         bsid itself may be present (or not) as a row.
-- We don't consider the serial in this simplified model

create table hist_table ( grp varchar2(1) , sid number, bsid number ) ;
insert into hist_table values ( 'A', 10, 100 ) ;
insert into hist_table values ( 'A',  20 , null ) ;
insert into hist_table values ( 'A', 30,  40  ) ;
insert into hist_table values ( 'A', 40,  50  ) ;
insert into hist_table values ('A', 50, null ) ;
insert into hist_table values ( 'B',  10, 100 ) ;
insert into hist_table values ( 'B',20 , null ) ;



SQL> select * from hist_table ;

G        SID       BSID
- ---------- ----------
A         10        100
A         20
A         30         40
A         40         50
A         50
B         10        100
B         20


We want : 

grp  Session
---   ------------------- 
  A    10
  A        100
  A    20
  A    30
  A       40
  A         50
  B   10
  B       100
  B   20



-- The difficulty is that blocking session 100 has no dedicated row.
-- Session 20 has no blocking session.
-- Sessions 30,40,50 are all linked in a hierarchic pattern.
This post has been answered by Frank Kulash on Apr 6 2011
Jump to Answer

Comments

Barbara Boehmer

Please see the following modification of what you posted.

SCOTT@orcl12c> CONN / AS SYSDBA

Connected.

SYS@orcl12c> -- set file watcher interval to one minute:

SYS@orcl12c> BEGIN

  2    DBMS_SCHEDULER.SET_ATTRIBUTE

  3       ('file_watcher_schedule',

  4        'repeat_interval',

  5        'freq=minutely; interval=1');

  6  END;

  7  /

PL/SQL procedure successfully completed.

SYS@orcl12c> -- create user with dba privileges:

SYS@orcl12c> CREATE USER upn_common IDENTIFIED BY upn_common

  2  /

User created.

SYS@orcl12c> GRANT DBA TO upn_common

  2  /

Grant succeeded.

SYS@orcl12c> CONNECT upn_common/upn_common

Connected.

UPN_COMMON@orcl12c> -- create table to insert results into:

UPN_COMMON@orcl12c> CREATE TABLE file_watcher_output

  2    (message VARCHAR2(4000))

  3  /

Table created.

UPN_COMMON@orcl12c> -- create procedure to insert results:

UPN_COMMON@orcl12c> CREATE OR REPLACE PROCEDURE ins_emp

  2  AS

  3  BEGIN

  4    INSERT INTO file_watcher_output (message)

  5    VALUES ('File has arrived ' || SYSTIMESTAMP);

  6    COMMIT;

  7  END;

  8  /

Procedure created.

UPN_COMMON@orcl12c> BEGIN

  2    -- create credential using operating system user and password (fill in your own):

  3    DBMS_SCHEDULER.CREATE_CREDENTIAL

  4       (credential_name     => 'local_credential',

  5        username          => '...',

  6        password          => '...');

  7    -- create file watcher:

  8    DBMS_SCHEDULER.CREATE_FILE_WATCHER

  9       (file_watcher_name   => 'test_file_watcher',

10        directory_path      => 'c:\my_oracle_files',

11        file_name          => 'file.txt',

12        credential_name     => 'local_credential',

13        enabled          => FALSE);

14    -- create job:

15    DBMS_SCHEDULER.CREATE_JOB

16       (job_name          => 'watchjob',

17        job_type          => 'stored_procedure',

18        job_action          => 'ins_emp',

19        start_date          => SYSTIMESTAMP,

20        queue_spec          => 'test_file_watcher',

21        end_date          => SYSTIMESTAMP+0.1,

22        auto_drop          => FALSE,

23        comments          => 'TESTING A PROCEDURE');

24    -- set attributes:

25    DBMS_SCHEDULER.SET_ATTRIBUTE

26       (name              => 'watchjob',

27        attribute          => 'logging_level',

28        value           => DBMS_SCHEDULER.LOGGING_OFF);

29    DBMS_SCHEDULER.SET_ATTRIBUTE

30       (name              => 'watchjob',

31        attribute          => 'max_run_duration',

32        value           => INTERVAL '1' MINUTE);

33    DBMS_SCHEDULER.SET_ATTRIBUTE

34       (name              => 'watchjob',

35        attribute          => 'schedule_limit',

36        value           => INTERVAL '1' MINUTE);

37    -- enable:

38    DBMS_SCHEDULER.enable ('test_file_watcher');

39    DBMS_SCHEDULER.enable ('watchjob');

40  END;

41  /

PL/SQL procedure successfully completed.

UPN_COMMON@orcl12c> -- write file (file must not exist previously):

UPN_COMMON@orcl12c> CREATE OR REPLACE DIRECTORY upncommon_dir AS 'c:\my_oracle_files'

  2  /

Directory created.

UPN_COMMON@orcl12c> declare

  2    filtyp utl_file.file_type;

  3  begin

  4    filtyp := utl_file.fopen ('UPNCOMMON_DIR', 'file.txt', 'W', NULL);

  5    utl_file.put_line (filtyp, 'File has arrived ' || SYSTIMESTAMP, TRUE);

  6    utl_file.fclose (filtyp);

  7  end;

  8  /

PL/SQL procedure successfully completed.

UPN_COMMON@orcl12c> -- wait long enough (may take more than one minute) for job to run:

UPN_COMMON@orcl12c> EXEC DBMS_LOCK.SLEEP (75)

PL/SQL procedure successfully completed.

UPN_COMMON@orcl12c> -- check for results:

UPN_COMMON@orcl12c> SELECT * FROM file_watcher_output

  2  /

MESSAGE

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

File has arrived 02-OCT-13 03.02.37.345000 PM -07:00

1 row selected.

UPN_COMMON@orcl12c> -- clean-up:

UPN_COMMON@orcl12c> EXEC DBMS_SCHEDULER.DROP_JOB ('WATCHJOB')

PL/SQL procedure successfully completed.

UPN_COMMON@orcl12c> EXEC DBMS_SCHEDULER.DROP_FILE_WATCHER ('test_file_watcher')

PL/SQL procedure successfully completed.

UPN_COMMON@orcl12c> EXEC DBMS_SCHEDULER.DROP_CREDENTIAL ('local_credential')

PL/SQL procedure successfully completed.

UPN_COMMON@orcl12c> DROP PROCEDURE ins_emp

  2  /

Procedure dropped.

UPN_COMMON@orcl12c> DROP TABLE file_watcher_output

  2  /

Table dropped.

UPN_COMMON@orcl12c> CONN / AS SYSDBA

Connected.

SYS@orcl12c> DROP USER upn_common CASCADE

  2  /

User dropped.

SYS@orcl12c> -- remove file:

SYS@orcl12c> HOST DEL c:\my_oracle_files\file.txt

1 - 1
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on May 5 2011
Added on Apr 6 2011
8 comments
1,815 views