Command for viewing the number of oracle database connections

This article summarizes a variety of operations on the number of connections in the oracle database, including viewing the number of connections, modifying the number of connections, and configuring the number of connections.

Sometimes the client connection fails intermittently in the oracle database, and the following error is reported:

ORA-12519, TNS: no appropriate service handler found

It may be that the current number of connections in the database has exceeded the maximum number it can process.

View the maximum number of connections allowed by the database:

The Code is as follows:	Copy code
Select value from v $ parameter where name = 'processs '; Show parameter processes;

 

View the current number of connections:

The Code is as follows:	Copy code
Select count (*) from v $ process;

The v $ process view contains information about all processes running in oracle. It is often used to establish a connection between the operating system process ID of an oracle or service process and a database session.

 
Modify the maximum number of connections:

The Code is as follows:	Copy code
Alter system set processses = 300 scope = spfile;

 
Restart the database:

The Code is as follows:	Copy code
Shutdown immediate; Startup;

 

Check which users are using the data:

The Code is as follows:	Copy code
SELECT osuser, a. username, cpu_time/executions/1000000 |'s ', SQL _fulltext, machine From v $ session a, v $ sqlarea B Where a. SQL _address = B. address order by cpu_time/executions desc;

 

Query the maximum number of concurrent queries since the database was started (sessions_highwater records the maximum number of database sessions that have been reached ):

The Code is as follows:	Copy code
Select * from v $ license

View the number of oracle user database connections

1. query the number of oracle connections

The Code is as follows:	Copy code
Select count (*) from v $ session;

2. query the number of concurrent connections of oracle

The Code is as follows:	Copy code
Select count (*) from v $ session where status = 'active ';

3. view the number of connections of different users

The Code is as follows:	Copy code
Select username, count (username) from v $ session where username is not null group by username;

4. View All Users:

The Code is as follows:	Copy code
Select * from all_users;

5. view system permissions of users or roles (system permissions assigned to users or roles ):

The Code is as follows:	Copy code
Select * from dba_sys_privs; Select * from user_sys_privs;

6. view the permissions contained in a role (only roles owned by login users can be viewed)

The Code is as follows:	Copy code
Select * from role_sys_privs;

7. View user object permissions:

The Code is as follows:	Copy code
Select * from dba_tab_privs; Select * from all_tab_privs; Select * from user_tab_privs;

8. view all roles:

The Code is as follows:	Copy code
Select * from dba_roles;

9. view the roles owned by a user or role:

The Code is as follows:	Copy code
Select * from dba_role_privs; Select * from user_role_privs;

10. Check which users have sysdba or sysoper system permissions (the corresponding permissions are required for query)

The Code is as follows:	Copy code
Select * from V $ PWFILE_USERS;

 

Modify the maximum number of connections allowed by the database:

The Code is as follows:	Copy code
Alter system set processses = 300 scope = spfile;

 

View the number of cursors

The Code is as follows:	Copy code
Select * from v $ open_cursor Where user_name =''

 

Query the maximum number of connections allowed by the database:

The Code is as follows:	Copy code
Select value from v $ parameter where name = 'processs '; Or: show parameter processes;

 

Query the maximum number of allowed database cursors:

The Code is as follows:	Copy code
Select value from v $ parameter where name = 'open _ cursors'

 

View oracle version

The Code is as follows:	Copy code
Select banner from sys. v _ $ version;

Displays the number of cursors opened by the user "SYSTEM" for each session in descending order.

The Code is as follows:	Copy code
Select o. sid, osuser, machine, count (*) num_curs from v $ open_cursor o, v $ session s where user_name = 'system' and o. sid = s. sid group by o. sid, osuser, machine order by num_curs desc;