As part of our configuration management process with our offshore team, we often have to recreate our database schema in Oracle by dropping the schema owner and recreating that user as part of the create/import scripts delivered with a build. However, when trying to perform this task in our onshore Development, QA or UAT environments we are faced with an issue where the user could not be dropped due to the fact that said user was currently connected:
DROP USER <user_to_drop> CASCADE
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected
However, when checking the V$SESSION system table, the user was not listed as being connected or having an active session with the database. Perplexed, we looked around at some other clues, and determined that while the user did not have an active session with the database, its connection was still valid and pooled by the ASP.NET Web process. Thus, we recycled the AppPool in which the application was hosted and – viola! The drop succeeded.
We've now automated this process and recycle the AppPool programmatically. Below is the Web service used to list the available AppPools and individually recycle them.
using System;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.DirectoryServices;
using System.Collections.Generic;
[WebService(Namespace = "http://tempuri.org")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
public class AppServerService : System.Web.Services.WebService
{
public AppServerService ()
{
}
private const string AppPoolDirectoryEntryPath = "IIS://localhost/W3SVC/AppPools";
[WebMethod]
public List<string> GetAppPoolNames()
{
List<string> appPoolList = new List<string>();
using (DirectoryEntry appPoolEntry =
new DirectoryEntry(AppPoolDirectoryEntryPath))
{
foreach (DirectoryEntry appPool in appPoolEntry.Children)
{
appPoolList.Add(appPool.Name);
appPool.Close();
appPool.Dispose();
}
}
return appPoolList;
}
[WebMethod]
public AppPoolRestartResponse RecycleAppPool(string appPoolName)
{
if (appPoolName == null)
{
throw new ArgumentNullException("appPoolName");
}
AppPoolRestartResponse response = new AppPoolRestartResponse();
using (DirectoryEntry appPool = new DirectoryEntry(
String.Format("{0}/{1}", AppPoolDirectoryEntryPath, appPoolName)))
{
try
{
appPool.Invoke("Recycle");
response.Successful = true;
response.Message = String.Format(
"Application Pool \"{0}\" was recycled succesfully at {1:HH:mm:ss}.",
appPool.Name, DateTime.Now);
}
catch (Exception e)
{
response.Successful = true;
response.Message = String.Format(
"Application Pool \"{0}\" failed to recycle: \"{1}\".",
appPool.Name, e.Message);
}
}
return response;
}
/// <summary>
///
/// </summary>
public class AppPoolRestartResponse
{
private bool _successful;
private string _message;
public bool Successful
{
get { return _successful; }
set { _successful = value; }
}
public string Message
{
get { return _message; }
set { _message = value; }
}
}
}
Note that in order for this Web service to work correctly, you must be authenticated to the Web service as an local administrator of the machine, or enable impersonation in Web.config for a user with the appropriate privelleges.