I just had to do a quick demo with the default Discoverere Video Store tutorial and ran into a bit of a problem with the create scripts that I thought I’d warn the world about.
Basically in the new database release 10gR2, they no longer support setting a QUOTA on temporary tablespaces. So instead of tossing a warning and letting a script continue they are throwing an ORA-30041 error. Many 3rd party and even some of Oracle’s tools (Discoverer) have this built into the create user scripts.
Heres the steps that led me to the problem and also how I worked around it. Hopefully this will save some others from the pain in the future.
Step 1: Connect to the database as a database user and create an EUL now
Step 2: I selected Yes to the previous screen and selected the create EUL button.
Step 3: I selected to create a new user
This is where the Oracle scripts need to be changed, and since they are embedded into Discoverer (from what I could tell), I am unable to change them. But the error didn’t pop up until later.
Step 4: I selected the Users tablespace for the default and TEMP for the temporary.
Step 5: The errror
So the fix was pretty simple, I just went into the database and dropped the user I had just created and ran the following script to create the user by hand. This is what I do normally but for some reason was being lazy today.
CREATE USER "DISCO_EUL" IDENTIFIED BY "DISCO_EUL"
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT;
GRANT CREATE DATABASE LINK TO DISCO_EUL;
GRANT CREATE MATERIALIZED VIEW TO DISCO_EUL;
GRANT CREATE PROCEDURE TO DISCO_EUL;
GRANT CREATE SEQUENCE TO DISCO_EUL;
GRANT CREATE SESSION TO DISCO_EUL;
GRANT CREATE SYNONYM TO DISCO_EUL;
GRANT CREATE TABLE TO DISCO_EUL;
GRANT CREATE TRIGGER TO DISCO_EUL;
GRANT CREATE TYPE TO DISCO_EUL;
GRANT CREATE VIEW TO DISCO_EUL;
GRANT GLOBAL QUERY REWRITE TO DISCO_EUL;
GRANT QUERY REWRITE TO DISCO_EUL;
GRANT CREATE SEQUENCE TO DISCO_EUL;
GRANT CREATE SESSION TO "DISCO_EUL";
GRANT CREATE USER TO DISCO_EUL;
GRANT GRANT ANY PRIVILEGE TO DISCO_EUL;
GRANT ALTER USER TO DISCO_EUL;
GRANT "CONNECT" TO "DISCO_EUL";
GRANT "RESOURCE" TO "DISCO_EUL";
So I restarted the process to create the EUL, instead this time I selected to user a user that was already created.
And of course, this time it was successful…
When I went to install the VIDEO5 data I received the same error and used the same work around to solve the problem. OK, thats enough for today.
Also, I haven’t forgotten to analyze the data differences between the two geocoders, I just realized they were in 2 different coordinate systems and I’m putting together a bigger article on how to do the conversions.