Overcome Concurrency Limitations

Here is how you can solve the concurrency-limiting problem demonstrated in my test case using ROWDEPENDENCIES. The following code creates a new set of tables, opens two SQL*Plus sessions, and executes an anonymous block:

/* create the tables for the test case */
create table t1_rd (c1 number) rowdependencies pctfree 5;

create index idx_t1_rd on t1_rd(c1) pctfree 5;

/* now open 2 SQL*Plus sessions and cut-paste this code in both */

/* session 1*/
alter session set isolation_level=serializable;

begin
FOR i IN 1..10000
  LOOP
    insert into t1_rd values(i);
  END LOOP;
end;


/* session 2*/
alter session set isolation_level=serializable;

begin
FOR i IN 1..10000
  LOOP
    insert into t1_rd values(i);
  END LOOP;
end;

/* now in both sessions, execute the anonymous block*/

/* session 1 */
SQL>/

/* session 2*/

SQL>/
Here is the output from both sessions:

SQL> alter session set isolation_level=serializable;

Session altered.

SQL> begin
  2  FOR i IN 1..10000
  3    LOOP
  4      insert into t1_rd values(i);
  5    END LOOP;
  6  end;
  7  /

PL/SQL procedure successfully completed.

The code produced no errors. Now you can commit or rollback to end the transaction. After commit, you can see the rows inserted in the table.
Determine Which Rows Have Been Committed
You can use ROWDEPENDENCIES to determine which rows have been committed and which rows haven't been committed yet by the same session.

INSERT INTO t1_rd VALUES (100);

INSERT INTO t1_rd VALUES (101);

SELECT c1, ORA_ROWSCN FROM t1_rd WHERE ORA_ROWSCN IS NULL;

INSERT INTO t1 values (1000);

INSERT INTO t1 values (1001);

SELECT c1, ORA_ROWSCN FROM t1;

In tables with ROWDEPENDENCIES, the ORA_ROWSCN column is NULL for uncommitted rows.
With these techniques, you can improve application concurrency and avoid that dreaded ORA-8177 error.