You Asked

I do have a question regarding the row archival,

we are having an application which is using connection pooling. Though we have temporary tables, it has its own data in sessions in application.

But if we issue a statement

EXECUTE IMMEDIATE ' ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ALL;'

from a pl/sql procedure. This impacts all the sessions for that oracle connection.

What's the solution for this

and Chris said...

If you want to hide the archived rows, you'll need to make them invisible again once the process is complete.

Do this with:

ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ACTIVE;

Rating

(3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, March 14, 2023 - 7:10 pm UTC

Hi Chris,

That is not what intended, there are more sessions for one oracle connection in an application.

The temporary tables would be having their own data for sessions.

But if we use this above statement for row archival visiblity, it affects all the sessions with in that
connection

March 15, 2023 - 12:33 pm UTC

I'm unclear how temporary tables relate to this problem.

The point still stands though: if you use a connection pool you need to enable/disable row archival visibility at the start/end of the process as appropriate.

Re: Oracle sessions and row archival visiblity true

Narendra, March 15, 2023 - 9:31 am UTC

@JITHESH

You need to run the SQL suggested by Chris, every time your application grabs a connection from connection pool.
That is a way to "reset" the database session to be used by next application thread.

March 15, 2023 - 12:33 pm UTC

Exactly

A reader, March 25, 2023 - 7:33 am UTC

yes, what you said is correct.

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.

Breadcrumb

Question and Answer