Select T.username,count (*) from v$session T GROUP by T.username
//Find the sql_id value that a user is executing based on the user group session Select
* From V$session t where T.username = ' BUDGET ' and t.sql_id are NOT NULL
//lookup corresponding SQL_ID's corresponding SQL statement
select M.sql_text from V$session T, V$sqlarea m where t.username = ' BUDGET ' and t.sql_id = m.sql_id
//
SELECT * from V$sqlarea t where T. sql_id = ' G92sy7kwt6mrq '
//Query Deadlock object
SELECT * from V$locked_object
1. Modify Oracle Maximum Connection number method
A, to sysdba identity landing pl/sql or worksheet
B, query the current number of connections show
parameter processes;
C, change the number of systems connected
alter system set processes=1000 Scope=spfile;
D, creating pfile create
pfile from SPFile;
E, restart Oracle Services or restart Oracle Server
2, query Oracle CURSOR usage
SELECT * from v$open_cursor where user_name = ' traffic ';
3. Methods for querying Oracle sessions
SELECT * FROM V$session
Kill the Conversation:
method One :sql> SELECT pid,spid from v$process where ADDR in (select Paddr from v$session where username = ' SYS ');
PID SPID
---------- ------------
17 13657
Ps-ef |grep 13657
Kill-9 13657
Method Two :
Sql> Select sid,serial# from v$session WHERE SID in (SELECT distinct SID from V$mystat);
SID serial#
---------- ----------
1637 13894
Alter system disconnect session ' 1637,13894 ' immediate;
This will prompt: ora-00031: Mark the session to terminate ... But in fact the implementation was successful.
If you use Pl/sql to log on to the client side, you will be prompted: ora-00028: Your session has been terminated.
To view the SQL progress being executed:
SELECT SE. Sid,opname,trunc (sofar/totalwork * 100, 2) | | '% ' as Pct_work,elapsed_seconds elapsed,round (Elapsed_seconds * (TOTALWORK-SOFAR)/Sofar) Remain_time,sql_text
From V$session_longops SL, V$sqlarea SA, V$session SE
WHERE SL. Sql_hash_value = SA. Hash_value and SL. SID = SE. SID and Sofar!= totalwork
ORDER BY Start_time
SELECT a.tablespace_name table space name, round (total/1024/1024/1024,4) tablespace size gb,round (free/1024/1024/1024,4) Table space remaining size GB,
Round ((total-free)/1024/1024/1024,4) tablespace uses size Gb,round ((total-free)/total,4) *100 "Usage%"
from (SELECT Tablespace_name,sum (bytes) free from Dba_free_space
GROUP by Tablespace_name) A,
(SELECT tablespace_name,sum ( Bytes total from Dba_data_files GROUP by Tablespace_name) b
WHERE a.tablespace_name=b.tablespace_name ORDER BY 5 desc
force the job to stop Oracle executing:
1, view all jobs,
Select * from Dba_jobs;
2, View the running job;
Select * from dba_jobs_running;
3, according to the SID found corresponding session;
Select sid,serial# from v$session where sid= ' &sid ';
4, kill corresponding session;
Alter system kill session ' &sid,&serial ';
5, the job is placed as broken;
Exec dbms_job.broken (' &job ', true);
6, SYSDBA user rights Delete job;
Delete from dba_jobs where job= ' &job ';
Start database when---really helpless