Managing Table and Session – Oracle

Posted on by Arpit Aggarwal

Listing database tables for a user, providing access and looking for session details in Oracle Database kept me busy last week, so thought of sharing some of the frequently used queries during a particular period of time, as follows:

List all tables accessible by current user

select tablespace_name, table_name, owner from all_tables;

List all tables owned by current user

select tablespace_name, table_name from user_tables;

List all tables accessible by current user and owned by a specific owner

select tablespace_name, table_name, owner from all_tables where owner = 'arpit';

Grant Access for a table to a specific user

GRANT SELECT, UPDATE, INSERT, DELETE ON "TABLE_OWNR"."EMPLOYEE_TABLE" TO "arpit";

Create Public Synonym for a table

create public synonym EMPLOYEE_TABLE for TABLE_OWNR.EMPLOYEE_TABLE;

List active / open / snipped connections in Oracle

select STATUS, count(STATUS) from V$SESSION where USERNAME='ARPIT' group by STATUS;

Kill Session in Oracle

ALTER SYSTEM KILL SESSION 'sid,serial#';

For example, if sid is 60 and serial number is 29882, then corresponding query will be:

ALTER SYSTEM KILL SESSION '60,29882';

How to know IDLE_TIME and CONNECT_TIME configured for a user profile?

select * from user_resource_limits user_resource where user_resource.resource_name in ('IDLE_TIME','CONNECT_TIME');