Wherescape Red and Oracle Express - take 2

The Validate Privileges option in Wherescape Setup Administrator (WSA) is supposed to assist you in getting your admin user granted up and ready to go.

So based on its recommendations you go into the XE home page and tick the following:

• Create Database Link
• Create Materialized View
• Create Procedure
• Create Table
• Create View

and then run a little script as follows:

grant select any table to bob;
grant query rewrite to bob; --why the heck is this deprecated stuff here??
grant execute on sys.dbms_lock to bob;
grant select on sys.v_$session to bob;

and you should now be good to go, right?

Nope. If you haven't created any tablespaces and you don't select any on metadata creation, you will get a ton of errors related to availability of tablespace "USERS". Where did this come from? I didn't specify a tablespace anywhere and there doesn't appear to be a WSA default tablespace option, but WSA has gone and decided to plug it in the metadata creation sql.

The quick and dirty for getting round this is to give bob DBA rights. Thankfully this is just a tickbox away in XE.

Getting Oracle XE on Windows with Virtual PC to work

Pretty much every time I have installed Oracle XE on Windows without taking any preparatory steps, I have had:

• the home page go "page not found"
• "shared memory realm does not exist" errors in sqlplus
• port 8080 failed to come up in lsnrctl status/netstat -an find "TCP"
• inaccessible Oracle odbc dsns with "tns:no listener" errors

My main preparation steps before installing XE on a Windows VPC:

1. Install the Microsoft Loopback Adapter
2. Use a static IP address
3. Disable all other networking

If this doesn't get it working, forget about checking whether the services are up - they will be.

Instead open up a command prompt and enter the following:

sqlplus / as sysdba
startup;

And this gets rid of the various problems I experience. This is in no way comprehensive. Oracle seems to be happy to leave their customers to trawl google for laundry lists of symptoms and fixes compiled by various bloggers and tweakers.

This isn't a bad article (though not specific to XE).
http://www.dba-oracle.com/oracle9iAS_tips_windows_service.htm
Neither is this
http://psst0101.wordpress.com/2007/06/13/getting-oracle-10201-to-start-automatically/