Geeks With Blogs

Scott Van Vliet Once a Developer, Always a Developer

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.

Posted on Tuesday, January 23, 2007 10:01 PM ASP.NET , Oracle | Back to top


Comments on this post: How to Drop an Oracle Schema / User locked by ASP.NET

# re: How to Drop an Oracle Schema / User locked by ASP.NET
Requesting Gravatar...
Thank you so much for sharing this wonderful idea. This is a useful information as I was trying to figure out how the program works. -
Marla Ahlgrimm
Left by Lalaine Peter on Mar 19, 2015 10:15 PM

# re: How to Drop an Oracle Schema / User locked by ASP.NET
Requesting Gravatar...
Wow, cool post. I’d like to write like this too – taking time and real hard work to make a great article…
pha thai bang thuoc
Left by Susu on Jun 01, 2017 9:17 PM

# re: How to Drop an Oracle Schema / User locked by ASP.NET
Requesting Gravatar...
Câu hỏi về chi phí phá thai luôn được các chị em quan tâm, vậy phá thai hết bao nhiêu tiền?
Left by suga on Jun 21, 2017 7:24 PM

# re: How to Drop an Oracle Schema / User locked by ASP.NET
Requesting Gravatar...
Nhiều chị em còn có thắc về cách phá thai 1 tháng tuổi. Hãy đến với phòng khám phụ khoa thái hà chị em nhé!
Left by suga on Jun 21, 2017 7:26 PM

Your comment:
 (will show your gravatar)


Copyright © svanvliet | Powered by: GeeksWithBlogs.net