Oracle DBA database Daily Maintenance knowledge

The DBA will periodically check the connection to 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 "hang dead" connections may require manual cleanup by the DBA.

The first thing to say is that the system tables provided by different versions of the database are different, and you can view the tables provided by that version of the database based on the data dictionary.

Like this:

SELECT * FROM DICT where table_name like '%session% ';

You can identify some tables and then get session information based on those tables.

Like this is querying the session that is currently being manipulated:

SELECT SID, serial#, STATUS, USERNAME, SCHEMANAME, osuser,terminal, MACHINE,

Program, A.name from V$session S, audit_actions A WHERE s.command = a.action;

First, view the connection of the database

The DBA will periodically check the connection to 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 "hang dead" connections may require manual cleanup by the DBA.

The following SQL statement lists the sessions established by the current database:

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

From V$session;

which

The ID number of the SID conversation (session);

The serial number of the serial# session, together with the SID, to uniquely identify a session;

USERNAME the user name to establish the session;

Program This session is using what tools to connect to the database;

Status of the current session, active indicates that the session is performing certain tasks, inactive that the current session is not performing any action;

If the DBA is to manually disconnect a session, execute:

Alter system kill session ' sid,serial# ';

Note that in the example above, a session with Sid 1 to 7 (username column null) is a background process for Oracle, and do not take any action on those sessions.

Ii. Common Commands

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 #修改连接

1:oracle how to see which users are in total

SELECT * from All_users;

2: View the current number of Oracle connections

How do I view the current number of Oracle connections? Just use the following SQL statement to query.

SELECT * FROM v$session where username isn't null

Select Username,count (username) from V$session where username are not NULL GROUP by username #查看不同用户的连接数

Select COUNT (*) from V$session #连接数

Select Count (*) from v$session where status= ' ACTIVE ' #并发连接数

Show parameter Processes #最大连接

Alter system SET process

3: Lists the sessions established by the current database:

Select Sid,serial#,username,program,machine,status from V$session;