Re: Newbie Oracle DBA question - Why CREATE ANY VIEW not shown
From: DA Morgan <damorgan_at_psoug.org>
Date: Mon, 28 Jul 2008 16:00:14 -0700
Message-ID: <1217286008.432229@bubbleator.drizzle.com>
>> 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 -
Date: Mon, 28 Jul 2008 16:00:14 -0700
Message-ID: <1217286008.432229@bubbleator.drizzle.com>
Richard wrote:
> 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.
SQL> select DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','UWCLASS') FROM dual;
DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','UWCLASS')
GRANT MANAGE SCHEDULER TO "UWCLASS"
GRANT CREATE JOB TO "UWCLASS"
GRANT SELECT ANY DICTIONARY TO "UWCLASS"
GRANT ADMINISTER DATABASE TRIGGER TO "UWCLASS"
GRANT CREATE OPERATOR TO "UWCLASS"
GRANT CREATE TYPE TO "UWCLASS"
GRANT CREATE TRIGGER TO "UWCLASS"
GRANT CREATE PROCEDURE TO "UWCLASS"
GRANT CREATE DATABASE LINK TO "UWCLASS"
GRANT CREATE SEQUENCE TO "UWCLASS"
GRANT CREATE VIEW TO "UWCLASS"
GRANT CREATE SYNONYM TO "UWCLASS"
GRANT CREATE CLUSTER TO "UWCLASS"
GRANT CREATE TABLE TO "UWCLASS"
GRANT CREATE SESSION TO "UWCLASS"
SQL>
-- Daniel A. Morgan Oracle Ace Director & Instructor University of Washington damorgan_at_x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.orgReceived on Mon Jul 28 2008 - 18:00:14 CDT