Shulou(Shulou.com)06/01 Report--

Original address: http://blog.itpub.net/24558279/viewspace-752293/

11. Query the number of connections in oracle

2select count (*) from v$session

32. Query the number of concurrent connections to oracle

4select count (*) from v$session where status='ACTIVE'

53. Check the number of connections for different users

6select username,count (username) from v$session where username is not null group by username

74. View all users:

8select * from all_users

95. View system permissions for users or roles (system permissions assigned directly to users or roles):

10select * from dba_sys_privs

11select * from user_sys_privs

126. View the permissions contained in the role (you can only view the roles owned by the logged-in user)

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. Check which users have sysdba or sysoper system permissions (appropriate permissions are required for query)

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 by the database

Modify the maximum number of connections:

Alter system set processes = 300scope = spfile

Restart the database:

Shutdown immediate

Startup

-- see which users are currently using data

SELECT osuser, a.userame.cputated timestamp executions1000000000 | |'s encoding, sql_fulltext,machine |

From v$session a, v$sqlarea b

Where a.sql_address = b.address order by cpu_time/executions desc

Number of select count (*) from v$session # connections

Select count (*) from v$session where status='ACTIVE' # concurrent connections

Show parameter processes # maximum connection

Alter system set processes = value scope = spfile; restart database # modify connection

SQL > Select count (*) from v$session where status='ACTIVE'

COUNT (*)

-

twenty

SQL > Select count (*) from v$session

COUNT (*)

-

one hundred and eighty seven

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 active,I SEE

SQL > select count (*) from v$session # connections

SQL > Select count (*) from v$session where status='ACTIVE' # concurrent connections

SQL > show parameter processes # maximum connection

SQL > alter system set processes = value scope = spfile; restart database # modify connection

Unix 1 user session corresponds to one operating system process

And windows is embodied in threads.

DBA should regularly check the connection of the database to see if the number of sessions established with the database is normal. If too many connections are established, it will consume the resources of the database. At the same time, some "dead" connections may need to be manually cleaned by DBA.

The following SQL statement lists the sessions currently established in the database:

Select sid,serial#,username,program,machine,status

From v$session

The output is as follows:

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 27 SYS SQLPLUS.EXE WORKGROUP\\ WORK3 ACTIVE

11 5 DBSNMP dbsnmp.exe WORKGROUP\\ WORK3 INACTIVE

Among them

The ID number of the SID session (session)

The serial number of the SERIAL# session, used with SID to uniquely identify a session

The user name of the session established by USERNAME

What tool does the PROGRAM session use to connect to the database?

STATUS the state of the current session. ACTIVE indicates that the session is performing certain tasks, and INACTIVE indicates that the current session has not performed any action.

If DBA wants to manually disconnect a session, execute:

Alter system kill session\ 'SID,SERIAL#\'

Sql statement SQL statement is as follows:

SELECT username, machine, program, status, COUNT (machine) AS

Number of connection

FROM v$session

GROUP BY username, machine, program, status

ORDER BY machine

Display the results (each person's machine will be different)

SCHNEIDER | WORKGROUD\ WANGZHENG | TOAD.exe | ACTIVE | 1

SCHNEIDER | WORKGROUP\ 597728AA514F49D | sqlplusw.exe | INACTIVE | 1

| | WWW-Q6ZMR2OIU9V | ORACLE.EXE | ACTIVE | 8 |

PUBLIC | INACTIVE | 0

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.