Question posted by: Richard
(Guest)
on
July 25th, 2008 05:35 AM
This is highly unusual. I do this only out of desperation. I am DB2
DBA on unix and recent had to take on Oracle admin as well. This
question is really about Oracle export/import. Why I post here
( please allow me once ) because
I posted at the other Google Oracle main group and it's surprising
under-attended, very few recent posters and lots of spams. I ask here
because some of you would have been experienced Oracle DBA as well.
I am just starting out as Oracle DBA on Solaris 9i, been on DB2 UDB
LUW and MSSQL and Sybase. 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 original db, I got:
SQLselect 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. I didnt run the dbms-get-ddl to check - thinking
all was well - it said success after all.
then later i checked.
Why "CREATE ANY VIEW" and "UNLIMITED TABLESPACE" not shown up ?
----------------------------------------------- part 2
----------------------------------
By the way data compared with source looked good after import. 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(250369961492205000000000000000 000000,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 log
did
say something about "questionable statistics".
Your answer is most appreciated. --Richard
