Oracle – View or Table to Show Idle Session Duration

From the documentation:

You can specify only unique, primary key, and foreign key constraints on views. However, you can define the view using the WITH CHECK OPTION clause, which is equivalent to specifying a check constraint for the view.

So it seems that there is no way to hint to the RDBMS that the underlying data (after the UNION ALL inline view is created) is NOT NULL.

This all seems a bit silly, given the UNION/UNION ALL/INTERSECT/MINUS statements all require matching datatypes!

Generally, aborting database queries and DML is not a good idea, and as @Raj already pointed out, killing sessions is also bad idea – actually it's even worse.

Your sessions may be running some transactions which consume undo space (in the undo tablespace of the user), hold locks on tables and occupy space (e.g. for row sorting) in the temporary tablespace. When you kill the session, all the work done by the transaction in-flight should be rolled back, all the undo segments and locks should be released. And while "light" sessions can terminate pretty quickly, others can take time before Oracle engine cleans everything up.

To answer your question we should know why you are killing your sessions.

• Is it because the user sessions consume a lot of resources (CPU) if you allow them more than 5 minutes to run?
• Is it because the users establish too much of sessions and idling?
• Is it because the newly established session starves for resources already used by other sessions and should have them available to run?
• Is it because you developed some cool software and you want to limit users to evaluate it only a few minutes before they are disconnected?

Probably you could improve your business logic so as to avoid killing sessions – doing computations on the server side, for example.

Or you could develop some RESTful web application which is run in the middle tier (application server) and performs queries on behalf of users while keeping a reasonable small number of connections to the database using connection pool. For example, you could write Java web application with servlets and JSP using Apache Tomcat JDBC Connection Pool (official documentation for Tomcat 8). You could also switch from dedicated server architecture in favor of shared server architecture.

If your software doesn't support connection pooling you could employ Database Resident Connection Pooling via OCI.

But if nothing I suggested to you meet your needs, you can implement your resource plan using Database Resource Manager, define directives which abort running queries or kill sessions which run or idle too long, and everything you need to implement this is described in chapter Managing Resources with Oracle Database Resource Manager in Oracle Database Administrator's Guide. The keywords are CANCEL_SQL, KILL_SESSION, MAX_EST_EXEC_TIME, MAX_IDLE_TIME.