Oracle tips (Display sessions by user,application and Tablespace info)

Sometimes you are developing with kettle and you need to open several connections to the same database, in this case I will show how to check the amount of connections opened in Oracle. Below there are some useful queries:

-- Show sessions by user
select osuser, username, machine, program
from v$session
order by osuser
-- Show sessions by application
select program Application, count(program) Number_of_Sessions
from v$session
group by program
order by Number_of_Sessions desc

If you detect you have too many connections it is time to convert your transformations transactional, using this feature you will prevent collapse the pool of connections. (Check the option Make the transformation database transactional? )

Transactional database

Below I attach a query to display the size and free space on the different tablespaces.

-- Show Tablespaces
select b.tablespace_name, tbs_size SizeMb, a.free_space FreeMb
from  (select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space
from dba_free_space
group by tablespace_name) a,
(select tablespace_name, sum(bytes)/1024/1024 as tbs_size
from dba_data_files
group by tablespace_name) b
where a.tablespace_name(+)=b.tablespace_name;

Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s