Re: Newbie Oracle DBA question - Why CREATE ANY VIEW not shown

From: joel garry <joel-garry_at_home.com>
Date: Fri, 25 Jul 2008 10:47:39 -0700 (PDT)
Message-ID: <ed433f95-d75d-423f-8bfc-1ef56007d616@a21g2000prf.googlegroups.com>


On Jul 25, 7:27 am, Richard <RSL..._at_gmail.com> wrote:
> On Jul 24, 8:12 pm, Richard <RSL..._at_gmail.com> wrote:
>
>
>
>
>
> > I am just starting out as Oracle DBA, been on other RDBMS. I finished
> > exp/imp a schema from from another oracle instance. Everything looked
> > good except:
>
> >  select dbms_metadata.get_granted_ddl ('SYSTEM_GRANT','XXXX') from
> > dual;
>
> > DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','XXXX')
> > ---------------------------------------------------------------------------­­-----
>
> >   GRANT SELECT ANY DICTIONARY TO "XXXX"
>
> >   GRANT CREATE ANY SNAPSHOT TO "XXXX"
>
> > on my target db, and from the original db, I got:
>
> > SQL> select dbms_metadata.get_granted_ddl ('SYSTEM_GRANT','XXXX') from
> > dual;
>
> > DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','XXXX')
> > ---------------------------------------------------------------------------­­-----
>
> >   GRANT SELECT ANY DICTIONARY TO "XXXX"
>
> >   GRANT CREATE ANY SNAPSHOT TO "XXXX"
>
> >   GRANT CREATE ANY VIEW TO "XXXX"
>
> >   GRANT UNLIMITED TABLESPACE TO "XXXX"
>
> > Before I started the import (with fromuser=XXXX touser=XXXX) , I
> > granted (from sys as sysdba I think),
>
> >   GRANT SELECT ANY DICTIONARY TO "XXXX";
> >   GRANT CREATE ANY SNAPSHOT TO "XXXX";
> >   GRANT UNLIMITED TABLESPACE TO "XXXX";
> >   grant select any dictionary to "XXXX";
>
> > and was succesfully granted. I didnt run the dbms-get-ddl immediately.
>
> > Why "CREATE ANY VIEW" and "UNLIMITED TABLESPACE" not shown up ?
>
> > ----------------------------------------------- part 2
> > ----------------------------------
>
> > By the way data compared with source looked good after imp. Same
> > counts and everything although I didnt check every user tables. I did
> > get this on the log output of imp:
>
> > IMP-00017: following statement failed with ORACLE error 20001:
> >  "DECLARE  SREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := '30383131';
> > SREC.MAX"
> >  "VAL := '796E69656C73656E'; SREC.EAVS := 0; SREC.CHVALS := NULL;
> > SREC.NOVALS"
> >  " :=
> > DBMS_STATS.NUMARRAY(250369961492205000000000000000000000,63050733528527"
> >  "5000000000000000000000); SREC.BKVALS := DBMS_STATS.NUMARRAY(0,1);
> > SREC.EPC "
> >  ":= 2;
> > DBMS_STATS.SET_COLUMN_STATS(NULL,'"UNUSUAL_ACTIVITY_LOG"','"CHANGED_B"
> >  "Y_NAME"', NULL ,NULL,NULL,80,.0125,1552,srec,3,6); END;"
> > IMP-00003: ORACLE error 20001 encountered
> > ORA-20001: Invalid or inconsistent input values
> > ORA-06512: at "SYS.DBMS_STATS", line 3425
> > ORA-06512: at line 1
>
> > About to enable constraints...
> > Import terminated successfully with warnings.
>
> > Is it only bad statistics ?  nothing to worry about right and I can
> > run compute statistics on the user tables to fix ?  The export run did
> > say something about "questionable statistics".
>
> > Your answer is most appreciated.  --Richard
>
> Any one care to help out a newbie DBA ?  Thanks. Richard.

We're all very caring people here :-)

Here's an into to the group: http://www.dbaoracle.net/readme-cdos.htm

My reaction to the first question is "gee, I oughta know that, but I don't, so I'll keep quiet, 'cause I've never thought to look." I wouldn't know how many others have the same reaction.

As far as statistics, there is an imp parameter that says whether to recalculate them, you should be familiar enough with your data to know if you need to. When in doubt, recalculate after an import. Exactly how depends on your data volume, version, and so on. The subject of recalculating statistics generates volumes of debates. You must read the concepts and admin manuals to start.

As a DBA, you should know how to use metalink to research problems. I went there, chose "all sources" in the quick find drop-down, and put DBMS_STATS.STATREC in the search box. Just looking at the headings of what was found, I'm thinking you should state your version(s), as you should on all posts. Also, searching on NUMARRAY finds some fair amount of bugs, perhaps for your situation you need to just not try to import statistics.

Welcome to the between world of rocks and hard places! :-)

jg

--
@home.com is bogus.
$ oerr ora 6512
06512, 00000, "at %sline %s"
// *Cause:   Backtrace message as the stack is unwound by unhandled
//           exceptions.
// *Action:  Fix the problem causing the exception or write an
exception
//           handler for this condition. Or you may need to contact
your
//           application administrator or DBA.
Received on Fri Jul 25 2008 - 12:47:39 CDT

Original text of this message