Re: Newbie Oracle DBA question - Why CREATE ANY VIEW not shown
Date: Tue, 29 Jul 2008 02:32:02 -0700 (PDT)
Message-ID: <4e3b9a23-c1ee-476c-a3cc-e7497daebb9a@34g2000hsf.googlegroups.com>
On 25 Jul, 01:12, 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, Richard? It works as expected on my system:
SYSTEM_at_db1> select * from v$version;
BANNER
Oracle Database 10g Release 10.2.0.1.0 - Production
PL/SQL Release 10.2.0.1.0 -
Production
CORE 10.2.0.1.0
Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 -
Production
SYSTEM_at_db1> grant create any view to TEST_USER;
Grant succeeded.
SYSTEM_at_db1> select
dbms_metadata.get_granted_ddl('SYSTEM_GRANT','TEST_USER') from dual;
DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','TEST_USER')
GRANT CREATE TRIGGER TO
"TEST_USER"
GRANT CREATE PROCEDURE TO
"TEST_USER"
GRANT CREATE SEQUENCE TO
"TEST_USER"
GRANT CREATE ANY VIEW TO
"TEST_USER"
GRANT CREATE VIEW TO
"TEST_USER"
GRANT CREATE TABLE TO
"TEST_USER"
GRANT CREATE SESSION TO
"TEST_USER"
SYSTEM_at_db1> grant unlimited tablespace to TEST_USER;
Grant succeeded.
SYSTEM_at_db1> select
dbms_metadata.get_granted_ddl('SYSTEM_GRANT','TEST_USER') from dual;
DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','TEST_USER')
GRANT CREATE TRIGGER TO
"TEST_USER"
GRANT CREATE PROCEDURE TO
"TEST_USER"
GRANT CREATE SEQUENCE TO
"TEST_USER"
GRANT CREATE ANY VIEW TO
"TEST_USER"
GRANT CREATE VIEW TO
"TEST_USER"
GRANT CREATE TABLE TO
"TEST_USER"
GRANT UNLIMITED TABLESPACE TO
"TEST_USER"
GRANT CREATE SESSION TO
"TEST_USER"
SYSTEM_at_db1>
HTH -g Received on Tue Jul 29 2008 - 04:32:02 CDT