Refresh materialized view by other user then owner 2006-05-01 - By Stefan Knecht
Hi
you really shouldn't be needing a quota on SYSTEM... Try setting the user's
default tablespace instead:
create user user_a identified by user_a
quota unlimited on My_Tablespace
default tablespace My_Tablespace;
No users should have system as their default tablespace actually, my mistake
for not specifying this, did the test on 10g having the database-wide
default tablespace so I left it out and it still worked for me :)
Stefan
On 5/1/06, varciasz <varciasz@(protected)> wrote:
>
> Hello,
>
> Thanks, this example after few correction works OK.
> I really don't know what's wrong with schema SYS .
>
> Below full, working script.
> All changes:
> You forget to put Table and MView to created Tablespace,
> It was also needed to give
> quota unlimited on SYSTEM;
> to user_a. I don't know why and what for this is needed but without it
> Oracle shows that I don't have privileges on tablespace SYSTEM
>
>
>
>
>
> Connect system/***@(protected) AS SYSDBA
>
>
> -- we'll first create a tablespace to store the table / mview
> -- here you just have to replace the path to a valid folder on your
> computer,
> -- I'm assuming you're running Windows, please correct me if I'm wrong
> create tablespace My_Tablespace datafile 'c:\My_Tablespace.dbf' size 10M;
>
> -- we'll create a user that will own the table and materialized view
> create user user_a identified by user_a
> quota unlimited on My_Tablespace
> quota unlimited on SYSTEM;
>
> -- grant him the privileges necessary for this test
> grant create session, create table, create materialized view to user_a;
>
> -- create the user that will be able to refresh user_a's mview
> create user user_b identified by user_b;
>
> grant create session, alter any materialized view to user_b;
>
> -- now connect as user_a to create the objects
> connect user_a/user_a
>
> -- connected as user_a we create the table and the mview
> create table a_table (x int primary key)
> tablespace My_Tablespace;
> create materialized view a_mview tablespace My_Tablespace as select * from
> a_table;
>
> -- connect as user_b and refresh it
> connect user_b/user_b
> begin
> dbms_mview.refresh('USER_A.A_MVIEW','c');
> end;
> /
>
>
>
> THANK YOU VERY MUCH FOR HELP
>
>
> Best Regards
>
> varciasz
>
>
>
Hi<br><br>you really shouldn't be needing a quota on SYSTEM... Try setting the
user's default tablespace instead:<br><br>create user user_a identified by user
_a<br>quota unlimited on My_Tablespace<br>default tablespace My_Tablespace;
<br><br>No users should have system as their default tablespace actually, my
mistake for not specifying this, did the test on 10g having the database-wide
default tablespace so I left it out and it still worked for me :)<br>
<br>Stefan<br><br><br><br><br><div><span class="gmail_quote">On 5/1/06, <b
class="gmail_sendername">varciasz</b> <<a href="mailto:varciasz@(protected)"
>varciasz@(protected)</a>> wrote:</span><blockquote class="gmail_quote" style=
"border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding
-left: 1ex;">
Hello,<br><br>Thanks, this example after few correction works OK.<br>I really
don't know what's wrong with schema SYS .<br><br>Below full, working script.<br
>All changes:<br>You forget to put Table and MView to created Tablespace,
<br>It was also needed to give<br>
quota unlimited on SYSTEM;<br>to user_a. I don't know why and what for
this is needed but without it<br>Oracle shows that I don't have privileges on
tablespace SYSTEM<br><br><br><br>
<br><br>Connect system/***@(protected) AS SYSDBA<br><br><br>-- we'll first create a
tablespace to store the table / mview<br>-- here you just have to replace the
path to a valid folder on your<br>computer,<br>-- I'm assuming you're running
Windows, please correct me if I'm wrong
<br>create tablespace My_Tablespace datafile 'c:\My_Tablespace.dbf' size 10M;
<br><br>-- we'll create a user that will own the table and materialized view<br
>create user user_a identified by user_a<br>quota unlimited on My_Tablespace
<br>quota unlimited on SYSTEM;<br><br>-- grant him the privileges necessary for
this test<br>grant create session, create table, create materialized view to
user_a;<br><br>-- create the user that will be able to refresh user_a's mview
<br>create user user_b identified by user_b;<br><br>grant create session, alter
any materialized view to user_b;<br><br>-- now connect as user_a to create the
objects<br>connect user_a/user_a<br><br>-- connected as user_a we create the
table and the mview
<br>create table a_table (x int primary key)<br>
tablespace My_Tablespace;<br>create materialized view a_mview
tablespace My_Tablespace as select * from<br>a_table;<br><br>-- connect as user
_b and refresh it<br>connect user_b/user_b
<br>begin<br>dbms_mview.refresh('USER_A.A_MVIEW','c');<br>end;<br>/<br><br><br>
<br>THANK YOU VERY MUCH FOR HELP<br><br><br>Best Regards<br><br>varciasz<br><br>
<br></blockquote></div><br>
|
|
