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.g et_granted_ddl ('SYSTEM_GRANT' ,'XXXX') from
dual;
DBMS_METADATA.G ET_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.g et_granted_ddl ('SYSTEM_GRANT' ,'XXXX')
from
dual;
DBMS_METADATA.G ET_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.STAT REC; BEGIN SREC.MINVAL := '30383131';
SREC.MAX"
"VAL := '796E69656C7365 6E'; SREC.EAVS := 0; SREC.CHVALS := NULL;
SREC.NOVALS"
" :=
DBMS_STATS.NUMA RRAY(2503699614 922050000000000 00000000000,630 50733528527"
"50000000000000 00000000); SREC.BKVALS := DBMS_STATS.NUMA RRAY(0,1);
SREC.EPC "
":= 2;
DBMS_STATS.SET_ COLUMN_STATS(NU LL,'"UNUSUAL_AC TIVITY_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 "questionab le statistics".
Your answer is most appreciated. --Richard
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.g et_granted_ddl ('SYSTEM_GRANT' ,'XXXX') from
dual;
DBMS_METADATA.G ET_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.g et_granted_ddl ('SYSTEM_GRANT' ,'XXXX')
from
dual;
DBMS_METADATA.G ET_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.STAT REC; BEGIN SREC.MINVAL := '30383131';
SREC.MAX"
"VAL := '796E69656C7365 6E'; SREC.EAVS := 0; SREC.CHVALS := NULL;
SREC.NOVALS"
" :=
DBMS_STATS.NUMA RRAY(2503699614 922050000000000 00000000000,630 50733528527"
"50000000000000 00000000); SREC.BKVALS := DBMS_STATS.NUMA RRAY(0,1);
SREC.EPC "
":= 2;
DBMS_STATS.SET_ COLUMN_STATS(NU LL,'"UNUSUAL_AC TIVITY_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 "questionab le statistics".
Your answer is most appreciated. --Richard
Comment