oracle aide

September 24, 2007

DBMS_MVIEW.REFRESH fails with ORA-01031

Filed under: mview, replication — Tags: , , — oracleaide @ 8:15 pm

Problem statement:

  • user_a owns a materialized view (mv_demo);
  • user_b wants to refresh the mv_demo mview in the user_a schema;
  • dbms_refresh fails with ORA-01031: insufficient privileges.
  • (more…)

    Passed 1z0-042 – OCA exam

    Filed under: 1z0-042, oca — oracleaide @ 2:19 pm

    Thanks to:

  • The Book: Oracle Database 10g OCP Certification All-In-One Exam Guide
  • The Practice Test software from SelfTest
  • (more…)

    September 20, 2007

    ORA-00600: internal error code, arguments: [qerpfAllocateR]

    Filed under: ora-600 — oracleaide @ 7:49 pm

    This error is not available on Metalink yet.
    Getting rid of a nested ROWNUM function in the WHERE clause solved the problem.
    Anyone is curious to see the SQL statement? IMHO it is too intense too publish online.

    September 19, 2007

    “Create materialized view” fails on a remote view.

    Filed under: fun, replication — oracleaide @ 4:22 pm

    An attempt to create a materialized view fails if the master object is a remote view.
    The error is ORA-00942, table or view does not exist.
    This happens when the remote view is not owned by the database link user. In my case:

    create database link demo_db_link connect to MVADMIN identified by MVADMIN
    


    (more…)

    September 17, 2007

    “…Do not create the recovery catalog in the SYS schema.”

    Filed under: rman — oracleaide @ 7:51 pm

    If I try to connect to the RMAN catalog as SYSDBA I get many encouraging errors, like:
        RMAN-04004: error from recovery catalog database:
        ORA-28009: connection as SYS should be as SYSDBA or SYSOPER

    Why? Probably because:
    “…When RMAN connects to the recovery catalog database, it does not use the SYSDBA role. So, when you are using SQL*Plus to diagnose connection problems to the recovery catalog database, you must enter the catalog connect string exactly as it was entered into RMAN. Do not also specify AS SYSDBA.”

    RMAN> connect catalog sys/password@127.0.0.1/tako

    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-04004: error from recovery catalog database: ORA-28009: connection as SYS
    should be as SYSDBA or SYSOPER


    RMAN> connect catalog sys/password@127.0.0.1/tako as sysdba

    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-00558: error encountered while parsing input commands
    RMAN-01009: syntax error: found "as": expecting one of: "newline, ;"
    RMAN-01007: at line 1 column 41 file: standard input


    As usual – reading manuals helps.

    Blog at WordPress.com.