PDA

View Full Version : ORACLE import file lacks object privileges



kestlert
09-11-2002, 05:10 AM
Hi there. New to Mondrian I'm about to setup on my PC (NT4.0). I have imported the .dmp file from mondrian-0.2-data.zip (after accidently downloading V1 data.zip). The import worked fine, but user foodmart has no object privileges on his own objects. I have ORACLE 8.1.7.0 installed here. Something wrong with export?

jhyde
09-11-2002, 05:18 AM
The latest data file is mondrian-data.zip, in the "mondrian data" package. Can you give that a try.

kestlert
09-11-2002, 05:42 AM
Hi,
I've downloaded mondrian-data.zip, but htis one doesn't contain a .dmp file. In background I started to run FoodMartData.sql for Oracle from mondrian-0.2-data.zip (but this may take a while). Is this SQL file compatible with mondrian 0.3?
For the import problem: I found some entries in ORACLE's metalink database about this problem which seems to be a bug of export utility, when doing full export as SYS or SYSDBA.
Thomas

kestlert
09-11-2002, 06:41 AM
Hi,
now I have loaded the SQL file (took about one hour on my fast PC).
But, sorry to say: You shouldn't include table names in quoutes if there is no urgent need (FoodMartData.sql).
SELECT * FROM customer -> ORA-00942: Tabelle oder View nicht vorhanden
SELECT * FROM "customer"; -> this works
ORACLE converts object names to UPPERCASE if not quoted. Its usual practice not to qoute except for critical object names (containing blanks, special chars, etc) - but who to hell really can't live without this?
Another issue: the SQL file is very long and does many INSERTs. Lucky me, my rollback segs where large enough, but often you will face the usual rollback problem (cannot extent RBS...). Adding COMMIT; after every 100th line would prevent this.
Thomas

jhyde
09-11-2002, 07:37 AM
You shouldn't include table names in quotes
> if there is no urgent need
> [...]
> ORACLE converts object names to UPPERCASE if not quoted.
I wanted the tables to be lowercase, so that it would expose bugs if we forgot to quote table names correctly in our code! (These days, a lot of people create their database schemas via graphical tools, and so mixed case and spaces are common in table names.)
By the way, was this your problem with FoodMart.dmp? Please confirm.
> Another issue: the SQL file is very long and does
> many INSERTs. Lucky me, my rollback segs
> where large enough, but often you will face the
> usual rollback problem (cannot extent RBS...).
> Adding COMMIT; after every 100th line would
> prevent this
I didn't want to include 'commit' statements, because I wanted FoodMartData.sql to be generic SQL. You can use the 'set autocommit 100' SQLPlus command.
I want FoodMart.dmp to be the primary import method for Oracle -- I am working to fix the problems you were experiencing -- but FoodMartTables.sql should be a fall-back option. I will document the 'autocommit' trick in install.html.

kestlert
09-15-2002, 08:27 PM
Julian,
>By the way, was this your problem with FoodMart.dmp? Please confirm.
YES, I have re-tested the import and the problem just was with lowercase/quoted names. The import works fine, sorry for making trouble.
I used the following statements:
create user foodmart identified by foodmart default tablespace users;
grant connect, resource to foodmart;
imp foodmart/foodmart file=FoodMart.dmp fromuser=foodmart touser=foodmart
>I didn't want to include 'commit' statements, ...
OK, I understand your intention.
Now I'm about setting up in Tomcat 4.0.3 ...
Thomas