Newbie Oracle DBA question - Why CREATE ANY VIEW not shown
Richard
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
Richard
> 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')
Any one care to help out a newbie DBA ? Thanks. Richard.
joel garry
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.
hpuxrac
What version of oracle exactly are the 2 instances operating on?
What version of exp and imp did you use to move stuff between?
Richard
> On Jul 24, 8:12 pm, Richard <RSL...@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')
>
> - 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.
DA Morgan
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
damo...@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
gazzag
> 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')
What version of Oracle, Richard? It works as expected on my system:
SYSTEM@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@db1> grant create any view to TEST_USER;
Grant succeeded.
SYSTEM@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@db1> grant unlimited tablespace to TEST_USER;
Grant succeeded.
SYSTEM@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@db1>
HTH
-g
Richard
This just bawl me over -- why I can't get the privilleges to show, is
it an output issue, set line limit ? This is the actuall screen dump
( replaced actual pw and customer name ). Platform is 64 bit solaris.
Waiting for your expertise opinions. Thank you.
-bash-3.00$ sqlplus /nolog
SQL*Plus: Release 9.2.0.8.0 - Production on Tue Jul 29 15:18:11 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> connect sys/MYPASSWD as sysdba
Connected.
SQL> select
dbms_metadata.get_granted_ddl('SYSTEM_GRANT','MY_CUSTOMER') from dual;
DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','MY_CUSTOMER')
--------------------------------------------------------------------------------
GRANT SELECT ANY DICTIONARY TO "MY_CUSTOMER"
GRANT CREATE ANY SNAPSHOT TO "MY_CUSTOMER"
SQL> grant create any view to "MY_CUSTOMER"
2 ;
Grant succeeded.
SQL> grant unlimited tablespace to "MY_CUSTOMER";
Grant succeeded.
SQL> select
dbms_metadata.get_granted_ddl('SYSTEM_GRANT','MY_CUSTOMER') from dual;
DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','MY_CUSTOMER')
--------------------------------------------------------------------------------
GRANT SELECT ANY DICTIONARY TO "MY_CUSTOMER"
GRANT CREATE ANY SNAPSHOT TO "MY_CUSTOMER"
SQL> select * from v$version
2 ;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
PL/SQL Release 9.2.0.8.0 - Production
CORE 9.2.0.8.0 Production
TNS for Solaris: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production
Richard
> This just bawl me over -- why I can't get the privilleges to show, is
> it an output issue, set line limit ? This is the actuall screen dump
> ( replaced actual pw and customer name ). Platform is 64 bit solaris.
> Waiting for your expertise opinions. Thank you.
>
> -bash-3.00$ sqlplus /nolog
>
> SQL*Plus: Release 9.2.0.8.0 - Production on Tue Jul 29 15:18:11 2008
> Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
>
> SQL> connect sys/MYPASSWD as sysdba
> Connected.
>
> SQL> select
> dbms_metadata.get_granted_ddl('SYSTEM_GRANT','MY_CUSTOMER') from dual;
>
> DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','MY_CUSTOMER')
> GRANT SELECT ANY DICTIONARY TO "MY_CUSTOMER"
> GRANT CREATE ANY SNAPSHOT TO "MY_CUSTOMER"
>
> SQL> grant create any view to "MY_CUSTOMER"
> 2 ;
>
> Grant succeeded.
>
> SQL> grant unlimited tablespace to "MY_CUSTOMER";
>
> Grant succeeded.
>
> SQL> select
> dbms_metadata.get_granted_ddl('SYSTEM_GRANT','MY_CUSTOMER') from dual;
> DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','MY_CUSTOMER')
> GRANT SELECT ANY DICTIONARY TO "MY_CUSTOMER"
> GRANT CREATE ANY SNAPSHOT TO "MY_CUSTOMER"
>
> SQL> select * from v$version
> 2 ;
>
> BANNER
> ----------------------------------------------------------------
> Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
> PL/SQL Release 9.2.0.8.0 - Production
> CORE 9.2.0.8.0 Production
> TNS for Solaris: Version 9.2.0.8.0 - Production
> NLSRTL Version 9.2.0.8.0 - Production
Help ?
gazzag
>
> Help ?
>
I don't have a 9.2.0.8 system to test on, I'm afraid. But I have a
couple of questions:
1. What is the actual issue here? There are other ways of finding
the information you require without using DBMS_METADATA.
2. Is an upgrade to 10.2.0.x out of the question?
HTH
-g
Richard
Is there another way to see what privilleges have been granted ? I am
afraid if I dont check it run system turns over to users it will not
work because of privilleges.
Thank you in advance. --Richard
DA Morgan
Go to Morgan's Library at www.psoug.org and look up:
OBJECT PRIVILEGES
and
SYSTEM PRIVILEGES
The SQL statements you need are there.