How to prevent temporary table operations from generating archive logs in Oracle Database?

In my application, we dynamically create tables to store session-specific information. These tables are created at the beginning of an active session and dropped once the session is closed. Recently, the user base has grown exponentially, and we're now experiencing approximately 500k sessions per day, which results in 500k temporary tables being created and dropped daily.

Previously, Oracle generated around 10-12 GB of archive logs each day, but now, due to the increased session count and temporary table creation, the archive log size has skyrocketed to 450 GB per day. These temporary tables follow a specific naming pattern, starting with PUser_<session_id>.

The problem is that the temporary table operations are being logged in the archive logs, even though they are not necessary for recovery purposes.

Is there any way to prevent these temporary table operations from being included in Oracle’s archive logs?

I would appreciate any insights or solutions to reduce the archive log size caused by these temporary tables.

Solution

If your role information is already stored somewhere in the database, why not simply query it in place? Why store it elsewhere in a temporary location?

If you do need to load it elsewhere, a real table is the worst option, a global temporary table (GTT) is better, but PL/SQL collections is best.

PL/SQL collections when defined in packages are persistent for the lifetime of the session. The first touch of the package runs the instantiation code (after the BEGIN in the package body) which loads the associative array once-for-all, and from then on that won't be repeated.

Sample table:

CREATE TABLE my_roles (username varchar2(128), role_name varchar2(128));

INSERT INTO my_roles VALUES ('XYZ','ROLE1');
INSERT INTO my_roles VALUES ('XYZ','ROLE2');
COMMIT;

Package code:

CREATE OR REPLACE PACKAGE pkg_plsql_auth
AS
  TYPE role_tabtype IS TABLE OF Boolean INDEX BY varchar2(128);
  tab_roles role_tabtype;
END pkg_plsql_auth;
/
CREATE OR REPLACE PACKAGE BODY pkg_plsql_auth
AS
BEGIN
  FOR rec_role IN (SELECT *
                     FROM my_roles
                    WHERE username = USER)
  LOOP
    tab_roles(rec_role.role_name) := TRUE;
  END LOOP;
END pkg_plsql_auth;
/

Now test it, logged in as XYZ:

BEGIN
  IF pkg_plsql_auth.tab_roles.EXISTS('ROLE1') 
  THEN
    dbms_output.put_line('I have role1');
  ELSE
    dbms_output.put_line('I do not have role1');
  END IF;

Output:

I have role1
I do not have role3

This will keep it 100% in PGA memory (making it inappropriate for any serious data storage, but perfect for limited control information like this). That's actually better than a global temporary table (GTT) because GTTs create temporary segments in the temporary tablespace. While GTTs do avoid redo logging, they still involve I/O - their contents on are disk. PL/SQL collections, on the other hand, are simple mallocs from free memory and the associative array variety (INDEX BY ...) are optimized for ultra-fast element access, which would be appropriate if you know which role you are interested in each time.

The only problem with the above approach is that a user could manually add roles to their array. To get around that, you can move the array to inside the package and use accessor functions at the header level:

CREATE OR REPLACE PACKAGE pkg_plsql_auth
AS
  FUNCTION f_has_role(in_role_name IN varchar2) RETURN Boolean;
END pkg_plsql_auth;
/
CREATE OR REPLACE PACKAGE BODY pkg_plsql_auth
AS
  TYPE role_tabtype IS TABLE OF Boolean INDEX BY varchar2(128);
  tab_roles role_tabtype;

  FUNCTION f_has_role (in_role_name IN varchar2)
    RETURN Boolean
  AS
  BEGIN
    RETURN tab_roles.EXISTS(in_role_name);
  END f_has_role;
BEGIN
  FOR rec_role IN (SELECT *
                     FROM my_roles
                    WHERE username = USER)
  LOOP
    tab_roles(rec_role.role_name) := TRUE;
  END LOOP;
END pkg_plsql_auth;
/

Now test it, logged in as XYZ:

BEGIN
  IF pkg_plsql_auth.f_has_role('ROLE1') 
  THEN
    dbms_output.put_line('I have role1');
  ELSE
    dbms_output.put_line('I do not have role1');
  END IF;
  
  IF pkg_plsql_auth.f_has_role('ROLE3') 
  THEN
    dbms_output.put_line('I have role3');
  ELSE
    dbms_output.put_line('I do not have role3');
  END IF;  
END;

Output:

I have role1
I do not have role3

If you want to call this accessor function from SQL instead of from PL/SQL, you can change the return type from Boolean to some SQL type (varchar2, integer, whatever) and can call it in, say, a WHERE clause in order to return 0 rows if they are unauthorized.

But again, the better than best solution is simply to query the role privilege table itself directly and not bother copying it anywhere.