Number of Oracle database connections view commands

Oracle databases sometimes have intermittent client-side connectivity failures that are similar to the following error:

ORA-12519, tns:no appropriate service handler found

It is possible that the current number of connections on the database has exceeded the maximum value that it can handle.

To view the maximum number of connections allowed for a database:

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

To view the current number of connections:

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

The V$process view contains all the process information that the current system Oracle is running. is often used to establish a link between the operating system process ID of an Oracle or service process and the database session.

To modify the maximum number of connections:

The code is as follows	Copy Code
alter system SET processes = Scope = SPFile;

To restart the database:

The code is as follows	Copy Code
Shutdown immediate; Startup

See which users are currently 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;

The maximum number of concurrent queries to query the database since it was started (Sessions_highwater records the maximum value that the database session ever reached):

The code is as follows	Copy Code
SELECT * FROM V$license

To 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 Oracle's concurrent connection number

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

3. View the number of connections for different users

The code is as follows	Copy Code
Select Username,count (username) from V$session where username are not null GROUP by username;

4. View all Users:

The code is as follows	Copy Code
SELECT * from All_users;

5, view the user or role system permissions (directly assigned to the user or role system permissions):

The code is as follows	Copy Code
SELECT * from Dba_sys_privs; SELECT * from User_sys_privs;

6. View the rights included in the role (see only the roles owned by the logged on user)

The code is as follows	Copy Code
SELECT * from Role_sys_privs;

7, view the 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 the user or role:

The code is as follows	Copy Code
SELECT * from Dba_role_privs; SELECT * from User_role_privs;

10, to see which users have SYSDBA or Sysoper system permissions (the query requires appropriate permissions)

The code is as follows	Copy Code
SELECT * from V$pwfile_users;

To modify the maximum number of connections allowed for a database:

The code is as follows	Copy Code
alter system SET processes = Scope = SPFile;

Viewing the number of cursors

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

Maximum number of connections allowed for the query database:

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

Maximum number of cursors allowed in the query database:

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 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;