SQL Developer: New worksheet, new session?

SQL Developer loading. Yesterday.

Default Behaviour

The out of the box behaviour of SQL Developer is that SQL worksheets share the same connection.

What does that mean?

If you perform a insert, update or delete in one worksheet without issuing a commit, the result(s) can be viewed in another shared worksheet. This can be illustrated by the following example.

After launching SQL Developer and connecting to a database, I start two new SQL worksheets

The next two screen shots show that for both SQL worksheets that the table t is empty

Next using using the first worksheet, an insert into table t operation is performed. No commit has been issued.

Navigating to the second worksheet and querying the table again and the newly inserted row can be seen.

Unshared Connections

It is possible to open a SQL Worksheet with a dedicated database connection. In SQL Developer this is called an unshared connection.

From an existing worksheet the keyboard shortcut on Windows is CTL + SHIFT + N. This will only work from a shared worksheet. It is not possible to open a new unshared worksheet from an existing unshared worksheet.

The following screenshots demonstrates how an unshared connection differs from a shared connection.

Two shared SQL Worksheets: hr and hr~1 are started along with and one unshared: hr (Unshared). In the first worksheet I perform an insert operation which has not been committed.

Switching to the second shared worksheet, the uncommitted record is shown after querying the table.

Moving to the unshared connection and querying the table no row is returned.

The unshared worksheet is working as expected as a new session will not see uncommitted records.

Acknowledgements

Jeff Smith, the Oracle Product manager for SQL Developer has a superb blog where he shares many SQL Developer hints and tips. One of the posts
Opening Unshared Worksheets was the inspiration and source for this post.

The incremental commit Anti-pattern

This is a far more opinionated post than usual. It is not meant to inflammatory, my goal for this post is to have something tangible that I can point to the next time an Oracle Developer thinks they want or need to “do” incremental commits.

In this post an incremental commit is defined as code which commits a transaction but the cursor is kept open.  They are also known as a fetch across commit.

I encounter incremental commits in PL/SQL code that issues a commit inside a  loop such as the examples below:

Example

...
CURSOR customers_cur
IS
  SELECT columns
    FROM some_tables;
BEGIN
  FOR i in customers_cur
  LOOP
    -- doing something interesting with the row
    COMMIT; -- ARGH!
  END LOOP;
END;

The commit may be decorated with variations of:

IF rows_processed > some_arbitary_number
THEN 
  COMMIT;

Or

IF mod(customers_cur%rowcount, v_commit_limit) = 0 
THEN 
  COMMIT;

Why are they an Anti-Pattern?

They introduce side effects that the Developer is not aware of, usually a self inflicted ORA-01555 Snapshot too old exception.  I will come back to this in the final part of this post.

Why are Incremental commits used?

Over the years I have had many conversations with other Oracle Developers regarding the problems incremental commits cause. The common explanation I have heard for the introduction of incremental commits is that the developer didn’t want to “blow” the rollback segments.

I disagree with this. You should never commit inside a loop. You should commit when your transaction is complete and only then. If your rollback segments are too small to support your transactions then you need to work with your DBA and get them resized.

ORA-01555 Snapshot too old

I am going to spend the remainder of this post explaining why you will see this error when you perform an incremental commit. It will not be a deep dive into all the nuances of this exception just it’s relevance to incremental commits. The best explanation for ORA-01555 is this AskTom post which originally started some 18 years old. Much of what follows is distilled from this thread.

An ORA-01555 occurs when the database is unable to obtain a read consistent image. In order to obtain this information the database uses the rollback segment but if that information has been overwritten then the database can not use it and the ORA-01555 is raised. So what causes this information to be overwritten? In the context of incremental commits the fuse is lit when a commit is issued…

Here is the steps leading to this error taken from Oracle Support  Note:40689.1

1. Session 1 starts query at time T1 and Query Environment 50

2. Session 1 selects block B1 during this query

3. Session 1 updates the block at SCN 51

4. Session 1 does some other work that generates rollback information.

5. Session 1 commits the changes made in steps ‘3’ and ‘4’.
(Now other transactions are free to overwrite this rollback information)

6. Session 1 revisits the same block B1 (perhaps for a different row).

Now, Oracle can see from the block’s header that it has been changed and it is later than the required Query Environment (which was 50). Therefore we need to get an image of the block as of this Query Environment.

If an old enough version of the block can be found in the buffer cache then we will use this, otherwise we need to rollback the current block to generate another version of the block as at the required Query Environment.

It is under this condition that Oracle may not be able to get the required rollback information because Session 1’s changes have generated rollback information that has overwritten it and returns the ORA-1555 error.

I have marked the key point – 5. By issuing a commit you are saying I have finished with this data, other transactions feel free to reuse it. Except you haven’t finished with it and when you really need it, it will have been overwritten.

Edge Cases?

I am not aware of any edge cases that require incremental commits. If you know of any please let me know via the comments.

Acknowledgements:

This post would not have been possible without the help from the following sources:

AskTom question Snapshot too old

Stackoverflow Question Commit After opening cursor

New book: Oracle Incident Response and Forensics: Preparing for and Responding to Data Breaches

Looks like the first technical book for 2018 I will be reading has already be decided. Pete Finnigan has a new book coming out in early January.

Oracle-Incident-Response-and-Forensics-Book

More details from the publishers, Apress can be found here.  At the time of this post there was no preview available. Fortunately the listing on Amazon does so you can check out the contents here.

I will add a further post once I have read it.

Is there more than one reason to use PL/SQL WHERE CURRENT OF ?

I can’t remember using the PL/SQL construct where current of  in code I have written so along with my usual article style of how use to use this feature, this is also an experiment to find out if there are any other reasons to use where current of.

The code examples in this post have been developed using the Oracle Database App Development VM for Oracle VirtualBox which at the time of this post was using Oracle database 12.1.0.2. (Although nothing in this post is 12c specific)

First I create a table that can be used for the experiment


create table t
as
select *
from all_objects
/

With the table created, I then use it in the following example which I will explain below.  At this point I would like to say that I appreciate the example could be rewritten as a single SQL statement however I wanted to concentrate on illustrating the use of where current of  without an overtly complicated example where the point of this post is lost in a forest of code.


declare

  cursor c 
  is 
  select t.object_name
    from t
   where t.object_type in ('PACKAGE', 'PACKAGE BODY')
     and t.owner = 'APEX_040200'
     for update of object_name; 

  ln_count pls_integer := 1;

begin

  for i in c
  loop
    
    update t
       set t.object_name = 'X - ' || ln_count
     where current of c;
 
    ln_count := ln_count + 1;
 
  end loop;

  commit;  

end;

At line 3 I declare a cursor that identifies the records of interest, which are the package specifications and bodies owned by the APEX user.  Note the use of the for update clause. This is mandatory when using where current of . If you remove it and try to run the example you will see the following error message

PLS-00404: cursor 'C' must be declared with FOR UPDATE to use with CURRENT OF

Moving on to the execution section,  for each iteration of the cursor for loop,  the object name is updated (to something not very useful!) and you can see at line 20 what I think is the primary benefit of using where current of is that instead of repeating the where clause used by the cursor I have used where current of c. So when the cursor has to change to return different records the only change that needs to be made is to the cursor.

The final part of the example is once the loop completes I release the lock on the table by committing the changes.

The primary benefit of where current of is the reduction in duplicated code which is a good thing but am I missing anything else? Please use the comments to let me know.

Summary

In this post I have demonstrated how to use the PL/SQL construct where current of along with what I believe is it’s primary benefit, the reduction of duplicated code.

Acknowledgements

The inspiration for this post came from Steven Feuerstein’s book, Oracle PL/SQL Programming  5th Edition.

 

The auditing capabilities of Flashback Data Archive in Oracle 12c.

The goal of this post is to build a prototype that demonstrates the auditing capabilities of Flashback Data Archive in Oracle 12c.

Although the documentation mentions that Flashback Data Archive provides the ability to track and store transactional changes to a table I was struggling to see how this functionality actually worked and what audit information was available hence my need to build something that I could test drive.

This post is not a deep dive into Flashback Data Archive for that I would refer you to the landing page of Flashback Data Archive within the documentation.

The prototype was developed using the pre-built developer app vm for virtual box which at the time of writing used Oracle database version 12.1.0.2.

The goal of the demo is show how a table is audited for changes, the audit will show who changed the record and when the change was made.

Creating the prototype is split into the following steps:

  1. Create a new user (optional).
  2. Creation of a table that you wish to audit changes to.
  3. Creation of the Flashback Data Archive objects.
  4. Creation of a new context and the dependent package.
  5. Executing DML statements against the table of interest.
  6. Viewing the audit information.

Create a new user (optional).

Logging on as a user with SYSDBA role, create a new user called: fda_user


create user fda_user identified by fda_user;
alter user fda_user quota unlimited on users;

fda_user is then given the following grants:


grant create table to fda_user;
grant alter user to fda_user;
grant alter session to fda_user;
grant create session to fda_user;
grant create any context to fda_user;
grant create procedure to fda_user;

Creation of a table that you wish to audit changes to.

For this prototype I am using a copy of the employees table owned by the HR user. The command below was run using whilst logged in with the SYSDBA role.


create table fda_user.employees as select * from hr.employees;

Creation of the Flashback Data Archive objects.

Still logged in as a user with SYSDBA role create a new tablespace and allow the fda_user to utilise it.


create tablespace fda2 datafile 'FDA2.dbf' size 1M autoextend on next 1M;

alter user fda_user quota unlimited on fda2; 

The next step is to create the Flashback Data Archive. In this example the audit information will be kept for 1 year and then automatically purged.


create flashback archive fda_one_year_data tablespace fda2 quota 1G retention 1 year;

Next the fda_user is granted permissions in order to utilise Flashback Data Archive.


grant flashback archive on fda_one_year_data to fda_user;

grant execute on dbms_flashback_archive to fda_user;

In the file step with the sysdba role, the table that is to be audited, employees is pointed to the Flashback Data Archive that was created earlier.


alter table fda_user.employees flashback archive fda_one_year_data;

Creation of a new context and the dependent package.

Log in to the database as the fda_user create the following context and PL/SQL package spec and body. This will used to assign who made the changes to the Employees table what those changes where.


CREATE OR REPLACE CONTEXT employee_context USING employee_ctx_api;


CREATE OR REPLACE PACKAGE employee_ctx_api
IS
  PROCEDURE set_value (p_name  IN VARCHAR2,
                       p_value IN VARCHAR2);

END employee_ctx_api;
/

CREATE OR REPLACE PACKAGE BODY employee_ctx_api
IS
  PROCEDURE set_value (p_name IN VARCHAR2,
                       p_value IN VARCHAR2)
  IS
  BEGIN
    dbms_session.set_context('employee_context', p_name, p_value);
  END set_value;

END employee_ctx_api;
/

For the sake of keeping the example on track the context and dependent package has been created in the same schema but in a production environment both the context and package would live in a different schema.

With this step, the required set up and configuration of Flashback Data Archive is now complete.

Executing DML statements against the table of interest.

In the following statements, a call is made to dbms_session and the package used by our context to record who is making the change and the action they are undertaking. This is then followed by the insert, update or delete statement.

begin

  dbms_session.set_identifier('Ian');

  employee_ctx_api.set_value('action','Insert');

  insert into employees
  (
    employee_id,
    first_name,
    last_name,
    email,
    phone_number,
    hire_date,
    job_id,
    salary,
    commission_pct,
    manager_id,
    department_id
  )
  values
  (
    9999,
    'William',
    'Bodie',
    'bodie@ci5.com',
    '01-123-456',
    TO_DATE('01-JAN-1978', 'DD-MON-YYYY'),
    'SH_CLERK',
    '20000',
    NULL,
    149,
    80
  );

  commit;

end;


begin

 dbms_session.set_identifier('Ian');

 employee_ctx_api.set_value('action','Update');

 update employees e
    set e.salary = 100009
  where e.employee_id = 9999;

 commit;

end;
begin

 dbms_session.set_identifier('Ian');

 employee_ctx_api.set_value('action','Delete');

 delete from employees
 where employee_id = 9999;

 commit;

end;

Viewing the audit information

With committed changes to the Employees table the audit information can be viewed using a Flashback Table Query such as the following:


SELECT employee_id,
       salary,
       versions_starttime,
       versions_endtime,
       versions_operation vo,
       DBMS_FLASHBACK_ARCHIVE.get_sys_context(versions_xid, 'USERENV','SESSION_USER') AS session_user,
       DBMS_FLASHBACK_ARCHIVE.get_sys_context(versions_xid, 'USERENV','CLIENT_IDENTIFIER') AS client_identifier,
       DBMS_FLASHBACK_ARCHIVE.get_sys_context(versions_xid, 'employee_context','action') AS action
FROM   employees
       VERSIONS BETWEEN TIMESTAMP SYSTIMESTAMP-(1/24) AND SYSTIMESTAMP
WHERE  employee_id = 9999
ORDER BY versions_startscn;

Reviewing the query results shows a record being inserted, updated and finally deleted along with who did it (session_user and client_identifier columns), when it was done (versions_starttime column) and what was done (action column). Although this last column is not really useful as the information is already provided by the versions_operation (vo column) in the screenshot but is included as an example of what can be recorded.

fda_results

Summary

In this post I have shown how you can build a prototype to see if the Audit capabilities of Flashback Data Archive would work with your existing application.

Acknowledgements

The inspiration for this post came from two sources. First was Connor McDonald’s superb presentation on Flashback Data Archive at the 2015 UKOUG conference and Tim Hall’s fantastic and generous site

DBMS_UTILITY.FORMAT_ERROR_BACKTRACE and the perils of the RAISE statement

Since dbms_utility.format_error_backtrace has been available from Oracle 10g this isn’t another what it ishow to use it post. Instead I will be taking this opportunity to discuss a nuance of dbms_utility.format_error_backtrace that you may not be aware of.

dbms_utility.format_error_backtrace reports the call stack back to the exception or the last raise statement which if you are not aware of could catch you out.

A quick word on the examples that follow, I am not proposing you should use this functionality in its raw format as I have done here. I believe that for production code calls to dbms_utility.format_error_backtrace should be encapsulated within your exception handling package.

The following code creates 3 procedures. procedure a calls b and b calls c. Within procedure c an exception is raised. There is no intermediate exception handling and the exception passes back to procedure a which makes a call to dbms_utility.format_errror_backtrace.

create or replace procedure a
is
begin
 dbms_output.put_line('In procedure a');
 b();
exception
 when no_data_found then
 dbms_output.put_line(dbms_utility.format_error_backtrace);
end a;
/

create or replace procedure b
is
begin
 dbms_output.put_line('In procedure b');
 c();
end b;
/

create or replace procedure c
is
begin
 dbms_output.put_line('In procedure c');
 RAISE NO_DATA_FOUND;
end c;
/

When procedure a is run, the following results are observed:

begin
   a();
end;

In procedure a
In procedure b
In procedure c
ORA-06512: at "HR.C", line 5
ORA-06512: at "HR.B", line 5
ORA-06512: at "HR.A", line 5

The error stack shows the exception started on line 5 (line 24) . Excellent, when things go wrong we can track the problem down to the line number! However procedure b needs to change and the developer, Gary Careless makes the required enhancements, one of the changes is the introduction of a new exception handling block that includes a RAISE statement.

The amended version of procedure b is now:

create or replace procedure b
is
begin
 dbms_output.put_line('In procedure b');
 c();
 -- lots of new functionality here
exception
 -- new exception handler for the new functionality
 when others then
 -- do something here and re-raise the exception
 RAISE;
end b;
/

Now when procedure a is run again we see the following:

begin
   a();
end;

In procedure a
In procedure b
In procedure c
ORA-06512: at "HR.B", line 11
ORA-06512: at "HR.A", line 5

Oh no! we have lost the line number where the exception occurred. Instead of displaying that the exception was raised by line 5 of procedure c as it did in the first example, the callstack reports the exception location is now at the the location of the RAISE statement within procedure b!

To get the accurate output from dbms_utility.format_error_backtrace you need to call it

  • In the exception section that first raised the exception

or

  • Call it from the outermost PL/SQL block and ensure that the intermediate routines do not raisere raise the exception.

The location of exception handling within an application is a project wide decision which should be agreed before development begins. However if your error handling package includes a call to dbms_utility.format_error_backtrace then you should keep this nuance in mind.

Using INSERT ALL with related tables

In this article I will demonstrate a method where the INSERTALL statement can be used to insert rows into two tables that are related by a foreign key constraint.

The following example was developed and tested using the Pre-Built Oracle Database App Development VM which at the time of this post used Oracle Database 12.1.0.2.0  & SQL Developer 4.0.3.16.  The example was created within the standard HR schema.

First I create two tables:


CREATE TABLE parent_tbl (the_pk    NUMBER PRIMARY KEY,
                         object_id NUMBER);

CREATE TABLE child_tbl (the_pk      NUMBER PRIMARY KEY,
                        the_fk      NUMBER,
                        object_name VARCHAR2(30));

Next I add a foreign to define the relationship between these tables and two sequences used to populate the primary keys:


ALTER TABLE child_tbl
ADD CONSTRAINT child_tbl_fk1 FOREIGN KEY (the_fk)
   REFERENCES parent_tbl (the_pk);

CREATE SEQUENCE parent_tbl_seq;

CREATE SEQUENCE child_tbl_seq START WITH 500;

Next I ensure that the foreign key relationship is working as expected by trying to insert a record into the child table with value for the_fk column that doesn’t exist in parent_tbl:


INSERT INTO child_tbl
(the_pk,
the_fk,
object_name)
VALUES
(child_tbl_seq.nextval,
999,
'SomeObject');

Attempting to run this statement results in the expected error message:

SQL Error: ORA-02291: integrity constraint (HR.CHILD_TBL_FK1) violated - parent key not found

With the tables and relationship between them in place I can now demostrate how to use INSERTALL to insert information from user_objects into the parent and child tables.


INSERT ALL
INTO parent_tbl(the_pk, object_id) 
VALUES (parent_tbl_seq.nextval, 
        object_id)
INTO child_tbl(the_pk, the_fk, object_name) 
VALUES (child_tbl_seq.nextval,
        parent_tbl_seq.currval,
        object_name)
SELECT uo.object_id,
       uo.object_name
FROM user_objects uo
/

Lines 2 – 4 insert into the parent table, note the use of the sequence to populate the primary key.

Lines 5 – 8 insert into the child table, the important part (and the focus of this article) is to understand how the foreign key column, the_fk is populated. It uses currval of the sequence that was used to populate the parent table.

Thanks go to Tom Kyte for coming up with this elegant solution.

Summary

In this article I have demonstrated how to use INSERTALL to insert into two tables that are related by a foreign key.

Acknowledgements

The idea for this article came from Tom Kyte’s answer to this Ask Tom question.