Oracle PL/SQL: Dropping All Objects In An Oracle Schema

This blog post provides a simple piece of PL/SQL which will delete every object owned by the currently logged in user.

Here is the PL/SQL block;


declare
  v_ItemCount integer;
begin
  SELECT count(*)
    INTO v_ItemCount
    FROM ALL_OBJECTS AO
   WHERE AO.OWNER = USER
     AND AO.OBJECT_TYPE NOT IN ('INDEX')
     AND AO.OBJECT_NAME NOT LIKE 'BIN$%';
  while (v_ItemCount > 0) loop
    for v_Cmd in (SELECT 'drop ' || AO.OBJECT_TYPE || ' ' || AO.OBJECT_NAME ||
                         DECODE(AO.OBJECT_TYPE,
                                'TABLE',
                                ' CASCADE CONSTRAINTS',
                                '') as DROPCMD
                    FROM ALL_OBJECTS AO
                   WHERE AO.OWNER = USER
                     AND AO.OBJECT_TYPE NOT IN ('INDEX')
                     AND AO.OBJECT_NAME NOT LIKE 'BIN$%') loop
      begin
        execute immediate v_Cmd.dropcmd;
      exception
        when others then
          null; -- ignore errors
      end;
    end loop;
    SELECT count(*)
      INTO v_ItemCount
      FROM ALL_OBJECTS AO
     WHERE AO.OWNER = USER
       AND AO.OBJECT_TYPE NOT IN ('INDEX')
       AND AO.OBJECT_NAME NOT LIKE 'BIN$%';
  end loop;


  execute immediate 'purge recyclebin';
end;

The SQL was written against Oracle 10g (hence the "purge recyclebin" at the bottom and the exclusion of objects already in the recycle bin from the "drop" loop). You can monitor progress (and check to see if the code has stalled) by running the count SQL in another editor attached to the same schema.

Clearly a piece of SQL you should be very careful with!

Deploying an ASP.NET Website to a Remote Server Running IIS 6

Probably the most important thing I can do first is to describe the environment I'm using; we have a global Active Directory forest with local domain controllers in each country. We have Windows 2003 servers running IIS 6 in both test and production - Developers (like myself) have a normal user account and a "Domain Administrator" account for working with remote servers. We use Kerberos for authentication (which requires a slight change to the process, see later).

The website itself has been generated using Visual Studio 2008. It has a single label (lblOutput) on the "Default.aspx" page and in the pages "on_load" event handler the following code;

        lblOutput.Text = "
" +
    "
" +
    "

Page.User.Identity.Name:	" + Page.User.Identity.Name + "
System.Security.Principal.WindowsIdentity.GetCurrent().Name:	" + System.Security.Principal.WindowsIdentity.GetCurrent().Name + "
Request.ServerVariables[LOGON_USER]:	" + Request.ServerVariables["LOGON_USER"] + "

";

Put simply this just reads the currently logged in user, the user running the IIS server, and the server variable for the logged in user and displays them on the web page (very simple). It's a good test of what we're doin because the user running the web server will be changed by this process.

This document is a fairly specific guide for the company I work for so we can do the process repeatedly, feel free to make suggestions to improve it and to skip any "unnecessary" steps that don't fit into your organisation.

Step by Step Guide

1. Logon (Remote Desktop) to the machine acting as your webserver and make sure you are connecting as as an Administrator
2. Open Windows Explorer and go to "C:\Websites"
3. Create a directory with your website name, it's best not to use spaces or any special characters as we're going to use this for the names of the Website in IIS and the Applicaiton Pool.
4. Open IIS Manager
5. Right-click "Application Pools" and choose "New > Application Pool"
6. Call the Pool AP + the directory name you used under "C:\Websites" (henceforth just "directory name")
7. Choose "User default settings ..."
8. Click "OK"
9. Right-click the newly created Application Pool and select "Properties"
10. Select the "Identity" tab, choose "Configurable" and enter the windows account the website should be running under
11. Click "OK"
12. Right-click the "Websites" and select "New > Website"
13. Enter the same name as you used for a directory
14. Set the TCP Port to a random value not currently in use for any of the other sites
15. Select the directory you just created under "C:\Websites" as the location for the new site
16. Choose "Run Scripts" and "Read" for permissions
17. Click "Finish"
18. Right-click the newly created Web Site and select "Properties"
19. Go to "ASP.NET" tab and select the correct version (if needed)
20. Go to "Home Directory" tab and change the "Application Pool" to the application pool you just created
21. Go to "Directory Security" tab and click on "Edit" under "Authentication and acccess control"
22. Disable "Enable anonymous access"
23. Click "OK" twice
24. Open up Computer Management, expand "Local Users and Group", then "Groups"
25. Open the IIS_WPG group and add in the Windows account you are using to run the application pool

You are now ready to begin testing, open your web browser on a different machine and see if you can connect.

Troubleshooting
"Service Unavailable" error message in the browser
Check to make sure that the windows account you are using for the application pool is actually able to login (i.e. the account isn't locked, you've entered the correct username and password).

If you refresh the application pools in IIS Manager if the application pool has "crashed" you will see the icon change.

Multiple "username/password" dialogs followed by "you are not authorized to view this page" errors
The chances are if you're seeing this you are using Kerbaros for authentication (like us). An easy way to tell is to download Firefox and try and access the site using that - it should accept your authentication and take you to the site. This is one of the very rare cases where Firefox will actually work and IE won't.

In order to fix this problem you need to create a Service Principle Name (SPN) for the username/ server combination. Remember to create a fully qualified SPN as well as a shortcode (i.e. server and server.domain.forest.org) - you are likely to need both and it makes it a lot easier to have both.

Opening Additional Mailboxes in Outlook 2007

A simple step-by-step guide for end users on how to open additional mailboxes (assuming you have been granted the appropirate permissions) using Outlook 2007 and Microsft Exchange Mailboxes in a Corporate Environment.

Open Outlook 2007 as you would normally

Access the “Tools” menu and select “Account Settings ...”

Ensure that “Microsoft Exchange” is selected and click the button marked “Change...” just above and to the right of it.

Click on “More Settings ...” at the bottom right.

Select the “Advanced” tab (second from the left).

Click on the “Add...” button in the “Mailboxes” group at the top.

Enter the email address for the mailbox you wish to add and click “OK” on this dialog.

Providing you have entered the details correctly the new mailbox will be displayed under “Open these additional mailboxes” in the dialog. If you have additional mailboxes to add you can click on “Add ...” again and repeat the previous step.

When you’ve added all the mailboxes you require click “OK”.

The “Next” button at the bottom right will now be available on this dialog. Click “Next”.

Click “Finish”.

Click “Close”.

The shared mailbox will now be available.