FAQ
Hi all,

Does anyone use the module Oracle Quoting ?

Do you have any reference regd this module, like : integration with
another modules, how to setup the module, documentation, process flow, links
and everything that can help me for studying it.

Thanks and Regards
Eriovaldo

Search Discussions

  • Varciasz at Apr 30, 2006 at 11:40 am
    Hello,



    I'm trying to refresh materialized view (Oracle 9i) and I have error

    that some privileges are needed but I don't have idea what more can be

    needed



    I'm trying to execute from User2:



    begin

    DBMS_MVIEW.REFRESH('sys.My_View','c');

    end;



    and Oracle gives me back error:



    begin

    *

    ERROR at line 1:

    ORA-01031: insufficient privileges

    ORA-06512: at "SYS.DBMS_SNAPSHOT", line 794

    ORA-06512: at "SYS.DBMS_SNAPSHOT", line 851

    ORA-06512: at "SYS.DBMS_SNAPSHOT", line 832

    ORA-06512: at line 2



    Table, materialized view and privileges are made in this way:



    create or replace table My_Table (aa integer primary key);

    create materialized view My_View as select * from My_Table;



    CREATE USER User2;

    IDENTIFIED BY ThisIsMySecretPassword;

    GRANT ALL ON My_View TO User2;

    GRANT SELECT ON My_Table TO User2;

    GRANT CREATE SESSION TO User2;

    GRANT ALTER ANY MATERIALIZED VIEW to User2;

    GRANT SELECT ANY TABLE to User2;

    GRANT CREATE ANY MATERIALIZED VIEW to User2

    GRANT DROP ANY MATERIALIZED VIEW to User2



    --END



    This table and materialized view have been made by user System



    I also try to give the same Roles that have System but this is still

    the same error.

    Refresh works fine executed by owner of this View



    What privileges are missing?





    Thanks for any help



    varciasz
  • Varciasz at Apr 30, 2006 at 7:56 pm

    This table and materialized view have been made by user System
    What about if it's owned by a regular user? This sounds like a very
    marginal case with a lot that could go wrong.
    Hi there,

    When user creates his own MView then there is no problem with refreshing it,
    but I still have no idea how refresh MView of other user

    FOR EXAMPLE:

    User "SYSTEM":

    CREATE USER User2

    IDENTIFIED BY ThisIsMySecretPassword;
    GRANT CREATE SESSION TO User2;
    GRANT ALTER ANY MATERIALIZED VIEW to User2;
    GRANT SELECT ANY TABLE to User2;
    GRANT CREATE ANY TABLE to User2;
    GRANT CREATE ANY MATERIALIZED VIEW to User2;
    GRANT DROP ANY MATERIALIZED VIEW to User2;
    ALTER USER "USER2" QUOTA UNLIMITED ON "SYSTEM";

    -- END OF SYSTEM

    User "USER2"

    create table My_Table (aa integer primary key);
    create materialized view My_View as select * from My_Table;
    begin

    DBMS_MVIEW.REFRESH('My_View','c');
    end;
    --END OF USER2

    This example works fine but when TABLE and MATERIALIZED VIEW are made by
    "SYSTEM" and user "USER2" trying to refresh it by:

    begin

    DBMS_MVIEW.REFRESH('sys.My_View','c');
    end;

    then some privileges are needed and error is shown:

    begin
    *
    ERROR at line 1:

    ORA-01031: insufficient privileges
    ORA-06512: at "SYS.DBMS_SNAPSHOT", line 794
    ORA-06512: at "SYS.DBMS_SNAPSHOT", line 851
    ORA-06512: at "SYS.DBMS_SNAPSHOT", line 832
    ORA-06512: at line 2

    I don't have any idea what privileges are needed because I already tried
    almost all of then. What is a meaning of this lines in error (794, 851,
    832)? Is there any way to track what privileges are missing?

    It not suppose to be so difficult! What's wrong with this Oracle?

    Thanks for any help

    varciasz
  • Stefan Knecht at Apr 30, 2006 at 9:00 pm
    From what I can see from your posting:

    begin

    DBMS_MVIEW.REFRESH('sys.My_View','c');
    end;

    You're creating the materialized view in schema SYSTEM, but try to refresh
    an mview in schema SYS - that cannot work.

    The error message you're getting doesn't seem to be accurate, though, you
    should be getting ora-23401 if that is the cause - I only tested on 10gr2,
    so 9i might react differently but I cannot test this right now.

    system_at_CENTRAL> grant create session, alter any materialized view to user1
    identified by user1;

    Grant succeeded.

    system_at_CENTRAL> create table t1 (x int primary key);

    Table created.

    system_at_CENTRAL> create materialized view v1 as select * from t1
    2;

    Materialized view created.

    system_at_CENTRAL> @conn user1/user1
    Connected.
    user1_at_CENTRAL> exec dbms_mview.refresh('SYSTEM.V1 ');

    PL/SQL procedure successfully completed.

    user1_at_CENTRAL>

    Either way, you really shouldn't be using SYS or SYSTEM for userdata.

    Stefan
  • Varciasz at Apr 30, 2006 at 11:35 pm
    Thanks for trying but this still doesn't work at all.
    You're creating the materialized view in schema SYSTEM, but try to refresh
    an mview in schema SYS - that cannot work.
    At first - all tables, MViews etc when I'm logged in as "SYSTEM" appears in
    SYS schema. That's why I used "SYS." instead "SYSTEM." I don't know how its
    work but it is like that.

    So after :

    EXEC dbms_mview.refresh('SYSTEM.V1 ');

    I have error from Oracle:

    *
    ERROR at line 1:
    ORA-23401: materialized view "SYSTEM"."V1" does not exist
    ORA-06512: at "SYS.DBMS_SNAPSHOT", line 794
    ORA-06512: at "SYS.DBMS_SNAPSHOT", line 851
    ORA-06512: at "SYS.DBMS_SNAPSHOT", line 832
    ORA-06512: at line 1

    And after
    EXEC dbms_mview.refresh('sys.V1 ');

    I have the same old error:
    *
    ERROR at line 1:
    ORA-01031: insufficient privileges
    ORA-06512: at "SYS.DBMS_SNAPSHOT", line 794
    ORA-06512: at "SYS.DBMS_SNAPSHOT", line 851
    ORA-06512: at "SYS.DBMS_SNAPSHOT", line 832
    ORA-06512: at line 1

    It looks like that this is not so simple ...



    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Stefan Knecht
    Sent: Sunday, April 30, 2006 11:00 PM
    To: oracle-l_at_freelists.org
    Subject: Re: Refresh materialized view by other user then owner

    From what I can see from your posting:

    begin
    DBMS_MVIEW.REFRESH('sys.My_View','c');
    end;

    You're creating the materialized view in schema SYSTEM, but try to refresh
    an mview in schema SYS - that cannot work.

    The error message you're getting doesn't seem to be accurate, though, you
    should be getting ora-23401 if that is the cause - I only tested on 10gr2,
    so 9i might react differently but I cannot test this right now.

    system_at_CENTRAL> grant create session, alter any materialized view to user1
    identified by user1;

    Grant succeeded.

    system_at_CENTRAL> create table t1 (x int primary key);

    Table created.

    system_at_CENTRAL> create materialized view v1 as select * from t1
    2 ;

    Materialized view created.

    system_at_CENTRAL> @conn user1/user1
    Connected.
    user1_at_CENTRAL> exec dbms_mview.refresh('SYSTEM.V1 ');

    PL/SQL procedure successfully completed.

    user1_at_CENTRAL>

    Either way, you really shouldn't be using SYS or SYSTEM for userdata.

    Stefan
  • Stefan Knecht at May 1, 2006 at 12:01 am
    What client are you using to connect to the database ? How exactly are you
    connecting to "SYSTEM" ? Do you have SYSDBA granted to SYSTEM and connect
    SYSTEM AS SYSDBA ?

    If possible, can you post the complete output of what you're trying to
    execute ?

    Stefan
    On 5/1/06, varciasz wrote:

    Thanks for trying but this still doesn't work at all.

    You're creating the materialized view in schema SYSTEM, but try to
    refresh
    an mview in schema SYS - that cannot work.
    At first - all tables, MViews etc when I'm logged in as "SYSTEM" appears
    in
    SYS schema. That's why I used "SYS." instead "SYSTEM." I don't know how
    its
    work but it is like that.

    So after :
    EXEC dbms_mview.refresh('SYSTEM.V1 ');

    I have error from Oracle:
    *
    ERROR at line 1:
    ORA-23401: materialized view "SYSTEM"."V1" does not exist
    ORA-06512: at "SYS.DBMS_SNAPSHOT", line 794
    ORA-06512: at "SYS.DBMS_SNAPSHOT", line 851
    ORA-06512: at "SYS.DBMS_SNAPSHOT", line 832
    ORA-06512: at line 1


    And after
    EXEC dbms_mview.refresh('sys.V1 ');

    I have the same old error:
    *
    ERROR at line 1:
    ORA-01031: insufficient privileges
    ORA-06512: at "SYS.DBMS_SNAPSHOT", line 794
    ORA-06512: at "SYS.DBMS_SNAPSHOT", line 851
    ORA-06512: at "SYS.DBMS_SNAPSHOT", line 832
    ORA-06512: at line 1





    It looks like that this is not so simple ...




    ________________________________________
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Stefan Knecht
    Sent: Sunday, April 30, 2006 11:00 PM
    To: oracle-l_at_freelists.org
    Subject: Re: Refresh materialized view by other user then owner


    From what I can see from your posting:


    begin
    DBMS_MVIEW.REFRESH('sys.My_View','c');
    end;
    You're creating the materialized view in schema SYSTEM, but try to refresh
    an mview in schema SYS - that cannot work.

    The error message you're getting doesn't seem to be accurate, though, you
    should be getting ora-23401 if that is the cause - I only tested on 10gr2,
    so 9i might react differently but I cannot test this right now.

    system_at_CENTRAL> grant create session, alter any materialized view to user1
    identified by user1;

    Grant succeeded.

    system_at_CENTRAL> create table t1 (x int primary key);

    Table created.

    system_at_CENTRAL> create materialized view v1 as select * from t1
    2;

    Materialized view created.

    system_at_CENTRAL> @conn user1/user1
    Connected.
    user1_at_CENTRAL> exec dbms_mview.refresh('SYSTEM.V1 ');

    PL/SQL procedure successfully completed.

    user1_at_CENTRAL>


    Either way, you really shouldn't be using SYS or SYSTEM for userdata.

    Stefan



    --
    http://www.freelists.org/webpage/oracle-l
  • Varciasz at May 1, 2006 at 12:36 am
    I'm using SQL *Plus Worksheet and logging into Database thru logging window
    after running "C:\oracle\ora92\bin\oemapp.bat worksheet" as SYSBDA
    (username: SYSTEM)



    In first mail I send whole code of creating "USER2", Table, Materialized
    View and refreshing it command used by "USER2"

    The only output shown by SQL *Plus Worksheet is an error that also entirely
    I put to this mail, unless you mean any other output then please clarify
    this



    SYSTEM has granted roles: DBA, AQ_ADMINISTRATION_ROLE





    From: Stefan Knecht
    Sent: Monday, May 01, 2006 2:01 AM
    To: varciasz
    Cc: oracle-l_at_freelists.org
    Subject: Re: Refresh materialized view by other user then owner



    What client are you using to connect to the database ? How exactly are you
    connecting to "SYSTEM" ? Do you have SYSDBA granted to SYSTEM and connect
    SYSTEM AS SYSDBA ?

    If possible, can you post the complete output of what you're trying to
    execute ?

    Stefan

    On 5/1/06, varciasz wrote:

    Thanks for trying but this still doesn't work at all.
    You're creating the materialized view in schema SYSTEM, but try to refresh
    an mview in schema SYS - that cannot work.
    At first - all tables, MViews etc when I'm logged in as "SYSTEM" appears in
    SYS schema. That's why I used "SYS." instead "SYSTEM." I don't know how its
    work but it is like that.

    So after :

    EXEC dbms_mview.refresh('SYSTEM.V1 ');

    I have error from Oracle:

    *
    ERROR at line 1:
    ORA-23401: materialized view "SYSTEM"."V1" does not exist
    ORA-06512: at "SYS.DBMS_SNAPSHOT", line 794
    ORA-06512: at " SYS.DBMS_SNAPSHOT", line 851
    ORA-06512: at "SYS.DBMS_SNAPSHOT", line 832
    ORA-06512: at line 1

    And after
    EXEC dbms_mview.refresh('sys.V1 ');

    I have the same old error:
    *
    ERROR at line 1:
    ORA-01031: insufficient privileges
    ORA-06512: at "SYS.DBMS_SNAPSHOT", line 794
    ORA-06512: at "SYS.DBMS_SNAPSHOT", line 851
    ORA-06512: at "SYS.DBMS_SNAPSHOT", line 832
    ORA-06512: at line 1

    It looks like that this is not so simple ...

    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Stefan Knecht
    Sent: Sunday, April 30, 2006 11:00 PM
    To: oracle-l_at_freelists.org
    Subject: Re: Refresh materialized view by other user then owner

    From what I can see from your posting:

    begin
    DBMS_MVIEW.REFRESH(' sys.My_View','c');
    end;
    You're creating the materialized view in schema SYSTEM, but try to refresh
    an mview in schema SYS - that cannot work.

    The error message you're getting doesn't seem to be accurate, though, you
    should be getting ora-23401 if that is the cause - I only tested on 10gr2,
    so 9i might react differently but I cannot test this right now.

    system_at_CENTRAL> grant create session, alter any materialized view to user1
    identified by user1;

    Grant succeeded.

    system_at_CENTRAL> create table t1 (x int primary key);

    Table created.

    system_at_CENTRAL> create materialized view v1 as select * from t1
    2;

    Materialized view created.

    system_at_CENTRAL> @conn user1/user1
    Connected.
    user1_at_CENTRAL> exec dbms_mview.refresh('SYSTEM.V1 ');

    PL/SQL procedure successfully completed.

    user1_at_CENTRAL>

    Either way, you really shouldn't be using SYS or SYSTEM for userdata.

    Stefan
  • Varciasz at May 1, 2006 at 3:13 pm
    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/***@My_DB 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
  • Stefan Knecht at May 1, 2006 at 6:42 pm
    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 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/***@My_DB 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

    --
    http://www.freelists.org/webpage/oracle-l
  • Stefan Knecht at May 1, 2006 at 9:01 am
    Ahh okay, then there is one part of the confusion we can identify:

    SYS is magical, SYS is different.If you connect AS SYSDBA, using any user,
    you get into the database as user SYS, and not the actual user you specified
    when connecting. And I tend to think that this is the root cause of your
    problems. You really, really, really should not use SYS for storing user
    data. The same goes for the SYSTEM tablespace, this is reserved for Oracle
    database internal data as well.

    Could you try issuing the following statements, when you're connected to the
    database AS SYSDBA and let me know if this works.

    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 users datafile 'c:\users.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 users;

    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);
    create materialized view a_mview 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;
    /

    Stefan
  • Dennis Williams at May 1, 2006 at 1:18 pm
    Eriovaldo,

    So this is part of Oracle E-Business?

    Dennis Williams
    On 4/30/06, Eriovaldo Andrietta wrote:

    Hi all,

    Does anyone use the module Oracle Quoting ?

    Do you have any reference regd this module, like : integration with
    another modules, how to setup the module, documentation, process flow, links
    and everything that can help me for studying it.

    Thanks and Regards
    Eriovaldo
    --
    http://www.freelists.org/webpage/oracle-l
  • Eriovaldo Andrietta at May 1, 2006 at 5:03 pm
    Hi Dennis,

    I think so, I am not sure, I don´t have informations from person that
    uses it daily as a tool for sales.
    So, I had read some material in the net, also Oracle documentation, but I
    is not enough to give a good idea how this module works.
    I would appreciate a lot if someone has a process flow regarding this tool.

    Regards
    Eriovaldo
    On 5/1/06, Dennis Williams wrote:

    Eriovaldo,

    So this is part of Oracle E-Business?

    Dennis Williams

    On 4/30/06, Eriovaldo Andrietta wrote:

    Hi all,

    Does anyone use the module Oracle Quoting ?

    Do you have any reference regd this module, like : integration with
    another modules, how to setup the module, documentation, process flow, links
    and everything that can help me for studying it.

    Thanks and Regards
    Eriovaldo
    --
    http://www.freelists.org/webpage/oracle-l
  • Stauffer, Robert G at May 1, 2006 at 6:26 pm
    Eriovaldo,

    Oracle Quoting is part of their E-Business Suite of applications. You can find the documentation on OTN using the navigation path below. It also includes the docs for all of their other E-Business Suite products.

    http://www.oracle.com/technology//index.html > Applications Technology > E-Business Suite Applications Technology > Documentation > Oracle Applications 11.5.10.2+ Online Documentation CD [which is the most recent version] > Documentation > Quoting

    There you'll find the the User's, Implementation, and API manuals.

    Bob Stauffer
    DBA
    D&E Communications
    Ephrata, PA, USA
    717-738-8737
    rstauffer@decommunications.com

    ________________________________

    From: oracle-l-bounce@freelists.org On Behalf Of Eriovaldo Andrietta
    Sent: Monday, May 01, 2006 13:04
    To: Dennis Williams
    Cc: oracle-l@freelists.org
    Subject: Re: DOUBTS REGD. ORACLE QUOTING

    Hi Dennis,

    I think so, I am not sure, I don´t have informations from person that uses it daily as a tool for sales.
    So, I had read some material in the net, also Oracle documentation, but I is not enough to give a good idea how this module works.
    I would appreciate a lot if someone has a process flow regarding this tool.

    Regards
    Eriovaldo

    On 5/1/06, Dennis Williams wrote:

    Eriovaldo,

    So this is part of Oracle E-Business?

    Dennis Williams

    On 4/30/06, Eriovaldo Andrietta wrote:

    Hi all,

    Does anyone use the module Oracle Quoting ?

    Do you have any reference regd this module, like : integration with another modules, how to setup the module, documentation, process flow, links and everything that can help me for studying it.

    Thanks and Regards

    Eriovaldo

    **DISCLAIMER
    This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of D&E except to the extent that it relates to their official business.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedApr 30, '06 at 9:24a
activeMay 1, '06 at 6:42p
posts13
users5
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase