public void KillRootBlockingSession(ConnStringBuilder connBuilder, List <SessionInfo> rootSessionList)
{
try
{
SqlConnectionStringBuilder connstrBuilder = new SqlConnectionStringBuilder();
connstrBuilder.DataSource = connBuilder.DataSource;
connstrBuilder.UserID = connBuilder.UserName;
connstrBuilder.Password = connBuilder.Password;
connstrBuilder.InitialCatalog = "master";
using (SqlConnection sqlConnection = new SqlConnection(connstrBuilder.ConnectionString))
{
sqlConnection.Open();
SqlCommand sqlCommand = sqlConnection.CreateCommand();
foreach (SessionInfo item in rootSessionList)
{
sqlCommand.CommandText = string.Format("kill {0}", item.spid);
sqlCommand.ExecuteNonQuery();
}
return;
}
}
catch
{
}
}
public bool CheckConnAndAuth(ConnStringBuilder connBuilder)
{
SqlConnectionStringBuilder connstrBuilder = new SqlConnectionStringBuilder();
connstrBuilder.DataSource = connBuilder.DataSource;
connstrBuilder.UserID = connBuilder.UserName;
connstrBuilder.Password = connBuilder.Password;
connstrBuilder.InitialCatalog = "master";
using (SqlConnection conn = new SqlConnection(connstrBuilder.ConnectionString))
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandTimeout = 6000;
//cmd.CommandText = @"SELECT 1 FROM syslogins t WHERE t.name=@loginName AND (t.sysadmin=1 OR t.serveradmin=1)";
//cmd.Parameters.Add("@loginName", SqlDbType.VarChar, 100);
//cmd.Parameters["@loginName"].Value = connBuilder;
cmd.CommandText = @"SELECT 1 FROM syslogins t WHERE t.name=SYSTEM_USER AND (t.sysadmin=1 OR t.serveradmin=1)";
conn.Open();
object userAth = cmd.ExecuteScalar();
if (userAth == null || userAth == DBNull.Value)
{
throw new Exception("用户需要拥有sysadmin或serveradmin的权限");
}
}
return(true);
}
public bool CheckConnAndAuth(ConnStringBuilder connBuilder)
{
OracleConnectionStringBuilder connstrBuilder = new OracleConnectionStringBuilder();
connstrBuilder.DataSource = connBuilder.DataSource;
connstrBuilder.UserID = connBuilder.UserName;
connstrBuilder.Password = connBuilder.Password;
//connstrBuilder.SelfTuning = false;
using (OracleConnection conn = new OracleConnection(connstrBuilder.ConnectionString))
{
OracleCommand cmd = conn.CreateCommand();
string s1 = @"
SELECT COUNT(1)
FROM (
SELECT 1
FROM user_sys_privs t
WHERE t.privilege = 'SELECT ANY DICTIONARY'
UNION ALL
SELECT 1
FROM user_role_privs r
JOIN role_sys_privs p ON r.GRANTED_ROLE = p.ROLE
WHERE p.privilege = 'SELECT ANY DICTIONARY'
) T ";
string s2 = @"
SELECT COUNT(1)
FROM (
SELECT 1
FROM user_sys_privs t
WHERE t.privilege = 'ALTER SYSTEM'
UNION ALL
SELECT 1
FROM user_role_privs r
JOIN role_sys_privs p ON r.GRANTED_ROLE = p.ROLE
WHERE p.privilege = 'ALTER SYSTEM'
) S ";
string sql = string.Format("SELECT ({0}) * ({1}) AS C FROM DUAL", s1, s2);
cmd.CommandText = sql;
conn.Open();
object userAth = cmd.ExecuteScalar();
if (userAth == null || userAth == DBNull.Value || Convert.ToInt32(userAth) == 0)
{
throw new Exception("用户需要[SELECT ANY DICTIONARY]和[ALTER SYSTEM]的权限");
}
}
return(true);
}
public void KillRootBlockingSession(ConnStringBuilder connBuilder, List <SessionInfo> rootSessionList)
{
try
{
OracleConnectionStringBuilder connstrBuilder = new OracleConnectionStringBuilder();
connstrBuilder.DataSource = connBuilder.DataSource;
connstrBuilder.UserID = connBuilder.UserName;
connstrBuilder.Password = connBuilder.Password;
using (OracleConnection conn = new OracleConnection(connstrBuilder.ConnectionString))
{
conn.Open();
OracleCommand cmd = conn.CreateCommand();
foreach (var item in rootSessionList)
{
cmd.CommandText = string.Format("alter system kill session '{0},{1}'", item.spid, item.kpid);
cmd.ExecuteNonQuery();
}
}
}
catch
{
}
}
public List <SessionInfo> GetSessionList(ConnStringBuilder connBuilder)
{
OracleConnectionStringBuilder connstrBuilder = new OracleConnectionStringBuilder();
connstrBuilder.DataSource = connBuilder.DataSource;
connstrBuilder.UserID = connBuilder.UserName;
connstrBuilder.Password = connBuilder.Password;
using (OracleConnection conn = new OracleConnection(connstrBuilder.ConnectionString))
{
OracleCommand cmd = conn.CreateCommand();
//cmd.CommandText = @"SELECT t.SID, t.SERIAL#, t.STATUS, t.EVENT, t.BLOCKING_SESSION, t.BLOCKING_SESSION_STATUS, t.USERNAME, t.MACHINE, t.PROGRAM, t.PROCESS, t.SECONDS_IN_WAIT, t.P1TEXT, t.P2TEXT, t.P3TEXT, t.P1, t.P2, t.P3, NVL(s1.SQL_ID, s2.SQL_ID) AS SQL_ID, NVL(s1.SQL_TEXT, s2.SQL_TEXT) AS SQL_TEXT FROM V$SESSION t LEFT JOIN V$SQL s1 ON t.SQL_ID = s1.SQL_ID AND t.SQL_CHILD_NUMBER = s1.CHILD_NUMBER LEFT JOIN V$SQL s2 ON t.PREV_SQL_ID = s2.SQL_ID AND t.PREV_CHILD_NUMBER = s2.CHILD_NUMBER WHERE t.TYPE = 'USER' AND t.SID != USERENV('SID')";
cmd.CommandText = @"
SELECT t.SID, t.SERIAL#, t.STATUS, t.EVENT, t.BLOCKING_SESSION, t.BLOCKING_SESSION_STATUS, t.USERNAME, t.MACHINE, t.PROGRAM, t.PROCESS, t.SECONDS_IN_WAIT /* , t.SQL_EXEC_START -- 11g */
, t.P1TEXT, t.P2TEXT, t.P3TEXT, t.P1, t.P2, t.P3, NVL(s1.SQL_ID, s2.SQL_ID) AS SQL_ID, NVL(s1.SQL_TEXT, s2.SQL_TEXT) AS SQL_TEXT
, NVL(s1.CPU_TIME, s2.CPU_TIME) AS CPU_TIME, NVL(s1.BUFFER_GETS, s2.BUFFER_GETS) AS BUFFER_GETS, NVL(s1.DISK_READS, s2.DISK_READS) AS DISK_READS, NVL(s1.ELAPSED_TIME, s2.ELAPSED_TIME) AS ELAPSED_TIME
FROM V$SESSION t
LEFT JOIN V$SQL s1 ON t.SQL_ID = s1.SQL_ID AND t.SQL_CHILD_NUMBER = s1.CHILD_NUMBER
LEFT JOIN V$SQL s2 ON t.PREV_SQL_ID = s2.SQL_ID AND t.PREV_CHILD_NUMBER = s2.CHILD_NUMBER
WHERE t.TYPE = 'USER' AND t.SID != USERENV('SID')";
cmd.CommandTimeout = 300;
List <SessionInfo> sessionList = new List <SessionInfo>(128);
conn.Open();
using (OracleDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
sessionList.Add(ConverToBockInfo(dr));
}
}
return(sessionList);
}
}
public List <SessionInfo> GetSessionList(ConnStringBuilder connBuilder)
{
SqlConnectionStringBuilder connstrBuilder = new SqlConnectionStringBuilder();
connstrBuilder.DataSource = connBuilder.DataSource;
connstrBuilder.UserID = connBuilder.UserName;
connstrBuilder.Password = connBuilder.Password;
connstrBuilder.InitialCatalog = "master";
using (SqlConnection sqlConnection = new SqlConnection(connstrBuilder.ConnectionString))
{
SqlCommand sqlCommand = sqlConnection.CreateCommand();
//sqlCommand.CommandText = "SELECT t.spid, t.kpid, t.blocked, t.status, t.lastwaittype, t.waitresource, t.waittime/1000 waittime, t.last_batch, DB_NAME(t.dbid) DbName, t.loginame, t.program_name, t.hostname, t.hostprocess, t.cmd, dc.text FROM master.sys.sysprocesses t OUTER APPLY master.sys.dm_exec_sql_text(t.sql_handle) dc WHERE t.spid >= 50 and t.spid != @@SPID";
sqlCommand.CommandText = @"
select t.spid, t.kpid, t.blocked, t.status, t.lastwaittype, t.waitresource, t.waittime/1000 AS waittime, t.last_batch
, DB_NAME(t.dbid) DbName, t.loginame, t.program_name, t.hostname, t.hostprocess, t.cmd, dc.text
, p.query_plan, tr.cpu_time, tr.logical_reads, tr.reads, tr.total_elapsed_time
from master.sys.sysprocesses t
outer apply master.sys.dm_exec_sql_text(t.sql_handle) dc
left join master.sys.dm_exec_requests tr on t.spid = tr.session_id
outer apply sys.dm_exec_query_plan(tr.plan_handle) p
where t.spid >= 50 and t.spid != @@SPID";
sqlCommand.CommandTimeout = 180;
List <SessionInfo> list = new List <SessionInfo>(128);
sqlConnection.Open();
using (SqlDataReader sqlDataReader = sqlCommand.ExecuteReader())
{
while (sqlDataReader.Read())
{
list.Add(ConverToBockInfo(sqlDataReader));
}
}
return(list);
}
}
