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

On Jul 25, 2:06 pm, hpuxrac <johnbhur..._at_sbcglobal.net> 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
>
> What version of oracle exactly are the 2 instances operating on?
>
> What version of exp and imp did you use to move stuff between?- Hide quoted text -
>
> - Show quoted text -

My question really is after I did the grants (said succesful) :

  GRANT CREATE ANY VIEW TO "XXXX"
  GRANT UNLIMITED TABLESPACE TO "XXXX" DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','XXXX') does not show it. Is it a display problem or maybe I need to see it some other ways ?

Thank you in advance. Richard. Received on Mon Jul 28 2008 - 11:16:28 CDT