DanBrother的部落格

Problem:
Oracle Data Pump Import Data encounters ORA-31684: Object type USER:"TESTUSER" already exists

Solution:
Add "exclude=user" within the impdp command

Problem:  (version of Oracle 8i)
EXP-00008: ORACLE error 4031 encountered 
ORA-04031: unable to allocate 4032 bytes of shared memory ("shared pool","oracle/aurora/rdbms/DbmsJavaSYS","joxlod: in ehe","ioc_allocate_pal")
EXP-00000: Export terminated unsuccessfully
Solution:
Increase java_pool_size to as lease 20M and rebounce the instance.

Problem:
IMP Tables encountered:  -bash: syntax error near unexpected token `
imp scott/tiger fromuser=wang touser=scott tables=(emp,dept) file=/data/scott_tab.dmp log=/data/scott_tab.log
Solution:  (need to add

Problem:
Encountered EXP-00056 & ORA-31600 invalid input value EMIT_SCHEMA
in Oracle 9.2.0.8
Solution:
@?/rdbms/admin/catexp.sql

shut immediate
startup migrate

** When  a database is started in MIGRATE mode, the following ALTER SYSTEM commands will be set automatically:
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET "_SYSTEM_TRIG_ENABLED"=FALSE SCOPE=MEMORY;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0 SCOPE=MEMORY;
ALTER SYSTEM SET AQ_TM_PROCESSES=0 SCOPE=MEMORY;
ALTER SESSION SET NLS_LENGTH_SEMANTICS=BYTE;
**************************************************************************************

When the rollback segment tablespace (RBS) was convered from dictionary managed to local managed in Oracle8i, exporting the full database might produce the error of EXP-00003: no storage definition found for segment(2,2).
If this is the case, you can convert the RBS back to the dictionary-managed tablespace by executing the SQL command:
exec dbms_space_admin.tablespace_migrate_from_local('RBS');
Then re-run the export command:
SET ORACLE_SID=testdb
exp sys/dba full=y direct=y buffer=100000000 file=d:\export\testdb.dmp log=d:\export\testdb.log

|
EXP-00091 Exporting questionable statistics:
Solution:
exp owne=scott statistics="none"  ....

EXP-00056: ORACLE error 904 encountered ORA-00904: "SYS"."DBMS_EXPORT_EXTENSION"."FUNC_INDEX_DEFAULT": invalid identifier
Solution:
conn / as sysdba
shut immediate
startup migrate
@?/rdbms/admin/catpatch.sql
shut immediate
startup

 
ORACLE9i Export Errors: exp-00056 & oracle error 942 encountered :
Solution:
@?/rdbms/admin/catexp.sql
@?/rdbms/admin/utlrp.sql

-- Create Database Directory
create directory mydump_dir as '/home/orale/mydump'
-- Grant Directory Privileges
grant read,write on DIRECTORY mydump_dir to SCOTT;
-- Query Permissions Granted to the Directory
select * from dba_tab_privs where table_name = UPPER('mydump_dir');
-- Revoke Directory Privileges
revoke read,write on DIRECTORY mydump_dir from SCOTT;
-- Drop the Directory
drop DIRECTORY mydump_dir;
---------------------------------------------------------------------------------------------------------------------
read -s MYPWD
-- Datapump Export (Full Database)

expdp system/$MYPWD@testdb full=y directory=mydump_dir dumpfile=testdb_dump_full.dmp logfile=testdb_dump_full.log
-- Datapump Export (Schema with Data)
expdp system/$MYPWD@testdb directory=mydump_dir schemas=scott dumpfile=scott_with_data_exp.dmp logfile=scott_with_data_exp.log
-- Datapump Export (Schema without Data , Metadata Only)
expdp system/$MYPWD@testdb directory=mydump_dir schemas=scott dumpfile=scott_without_data_exp.dmp logfile=scott_without_data_exp.log content=METADATA_ONLY
-- Datapump Export (Tables & Sequences)
cat dan_objects.par
>>
include=table:"LIKE 'DAN!_TAB!_%' ESCAPE '!'"
include=sequence:"LIKE 'DAN!_TAB%!_SEQ' ESCAPE '!'"
expdp danbrother/$MYPWD@testdb directory=mydump_dir parfile=dan_objects.par  
dumpfile=DANBROTHER_OBJECTS.dmp logfile=DANBROTHER_OBJECTS.log

-- Datapump Export (SQL Query)
cat dan_query.par
>>
query=TEST_TAB:"where orderno in ('100001','100002','100005','100010')"
expdp danbrother/$MYPWD@testdb directory=mydump_dir tables=TEST_TAB parfile=dan_query.par
dumpfile=DISCARDED_ORDERS.dmp logfile=DISCARDED_ORDERS.log