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); } }