11. Query the number of connections to Oracle
2select Count (*) from v$session;
32. Querying the number of concurrent connections for Oracle
4select Count (*) from v$session where status= ' ACTIVE ';
53. View the number of connections for different users
6select Username,count (username) from V$session where username are not null GROUP by username;
74. View all Users:
8select * from All_users;
95. View user or role system permissions (System permissions that are directly assigned to a user or role):
10select * from Dba_sys_privs;
11select * from User_sys_privs;
126. View the roles (only the roles owned by the logged-in user) that contain the permissions
13select * from Role_sys_privs;
147. View User Object permissions:
15select * from Dba_tab_privs;
16select * from All_tab_privs;
17select * from User_tab_privs;
188. View all roles:
19select * from Dba_roles;
209. View the roles owned by the user or role:
21select * from Dba_role_privs;
22select * from User_role_privs;
2310. See which users have SYSDBA or Sysoper system permissions (requires appropriate permissions when querying)
24select * from V$pwfile_users; Select COUNT (*) from v$process--current number of connections Select value from v$parameter where name = ' processes '--the maximum number of connections allowed for the database To modify the maximum number of connections:
alter system SET processes = + scope = SPFile; To restart the database:
Shutdown immediate;
Startup --See which users are currently using the data
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;
Select COUNT (*) from V$session #连接数
Select COUNT (*) from v$session where status= ' ACTIVE ' #并发连接数
Show parameter Processes #最大连接 alter system SET processes = value scope = spfile; Restart database #修改连接 Sql> Select Count (*) from v$session where status= ' ACTIVE ';
COUNT (*)
----------
20
Sql> Select Count (*) from v$session;
COUNT (*)
----------
187
Sql> Show parameter processes;
NAME TYPE VALUE
------------------------------------ ----------- ----------
Aq_tm_processes integer 0
Db_writer_processes Integer 1
Gcs_server_processes integer 0
Job_queue_processes Integer 10
Log_archive_max_processes Integer 2
Processes integer 450
Sql>
Concurrency refers to active,i see Sql> Select COUNT (*) from V$session #连接数
Sql> Select Count (*) from v$session where status= ' ACTIVE ' #并发连接数
Sql> Show parameter Processes #最大连接
Sql> alter system SET processes = value scope = spfile; Restart database #修改连接
Unix 1 user session corresponds to an operating system process
While Windows embodies the thread The DBA will periodically check the connection of the database to see if the number of sessions established with the database is normal, and if too many connections are established, the resources of the database are consumed. At the same time, some "dead" connections may require the DBA to manually clean up.
The following SQL statement lists the session conditions established by the current database:
Select Sid,serial#,username,program,machine,status
From V$session;
The output is:
SID serial# USERNAME Program Machine STATUS
---- ------- ---------- ----------- --------------- --------
1 1 ORACLE. EXE WORK3 ACTIVE
2 1 ORACLE. EXE WORK3 ACTIVE
3 1 ORACLE. EXE WORK3 ACTIVE
4 1 ORACLE. EXE WORK3 ACTIVE
5 3 ORACLE. EXE WORK3 ACTIVE
6 1 ORACLE. EXE WORK3 ACTIVE
7 1 ORACLE. EXE WORK3 ACTIVE
8 SYS SQLPLUS. EXE Workgroup\\work3 ACTIVE
5 Dbsnmp Dbsnmp.exe Workgroup\\work3 INACTIVE
which
The ID number of the SID session;
The serial number of the serial# session, together with the SID, is used to uniquely identify a session;
USERNAME the user name of the session to be established;
Program This session is using what tools to connect to the database;
Status current state of this session, active indicates that the session is performing certain tasks, inactive indicates that the current session does not perform any action;
If the DBA is going to manually disconnect a session, execute:
Alter system kill session \ ' Sid,serial#\ ' The SQL statement SQL statement is as follows:
SELECT username, machine, program, status, COUNT (machine) as
Number of connections
From V$session
GROUP by username, machine, program, status
ORDER by Machine;
Show results (different on everyone's machine)
schneider| Workgroud\wangzheng| toad.exe| Active|1
schneider| workgroup\597728aa514f49d|sqlplusw.exe| Inactive|1
| www-q6zmr2oiu9v| ORACLE. exe| Active|8
public| | | Inactive|0 Query by host name Select COUNT (*) from v$session WHERE machine = ' DXMH '; ' DXMH ' for Host name Data Recovery statement CREATE TABLE Informationlaw_bak
As
SELECT * from Informationlaw as of TIMESTAMP to_timestamp (' 20121126 103435 ', ' yyyymmdd Hh24miss '); Search by machine name Select Username,machine,count (username) from V$session where username are not null GROUP by Username,machine; |
