Exp-00008 Oracle Error 604 Encountered
Contents |
2005 - 10:45 pm UTC Category: Database – Version: oracle 7.3 Latest Followup You Asked Hi, I am having a problem regarding export. I want to export sets of
Exp 00008 Oracle Error 904 Encountered
tables repeatedly, but following error is occuring. ORA-00604 error occurred at recursive exp 00008 oracle error 6552 encountered SQL level num Actually I have to export various tables in the form of sets. Each set contains
Ora-01426: Numeric Overflow
42 selected tables.When I export any set the above error occurs. But, when I export in sets of 6 tables , no error occurs. More over, this error only occurs only when I perform export on a server called 'PCV'.And when i perform the same action on server called 'BACKUP',no error occur in any case. Actually PCV and BACKUP ,both are copies of each other,with slight differences in their init.ora file.The parameters which differ with each other are, PCV --parameters 1.db_files=20 2.db_block_buffers=200 3.shared_pool_size=6500000 4.open_cursors=100 5.log_buffer=8192 BACKUP ---parameters 1.db_files=35 2.db_block_buffers=3200 3.shared_pool_size=12000000 4.open_cursors=300 5.log_buffer=32768 There may be problem with the parameter difference. Please help me in this regard. Thanks. Jahazneb and we said... Well, that is not the entire message stack. the ora-604 is: 00604, 00000, "error occurred at recursive SQL level %s" An error occurred while processing a recursive SQL statement (a statement applying to internal dictionary tables). If the situation described in the next error on the stack can be corrected, do so; otherwise contact Oracle Support. There is a "next error on the stack" that describes the real failure. I'm going to guess here based on the init.ora parameters above -- it is ORA-01000 "maximum open cursors exceeded" exceeded. 100 is way too small. the smallest I use is 500 but I prefer 1000 myself in all cases. Adjust and retry the operation after changing and bouncing the instance (shutdown+startup). If you are worried that setting too high will "waste" stuff, see http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:288216032261 It'll only allocate them as it needs them (and if it needs them -- it isn't wasting anything, it needs them) Reviews Write a Review
check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. Results 1 to 6 of 6 Thread: error export database Tweet Thread Tools Show Printable Version Subscribe to this Thread… Search Thread Advanced Search Display Linear Mode Switch to Hybrid Mode Switch to Threaded Mode 12-01-03,05:54 #1 piwee View Profile View Forum Posts Registered User Join Date Nov 2003 Posts 4 Unanswered: error export database Hi, when https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:752825657640 I export the Oracle database I always get this error message at the end : . exporting referential integrity constraints . exporting synonyms . exporting views EXP-00008: ORACLE error 604 encountered ORA-00604: error occurred at recursive SQL level 1 ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at line 8 ORA-01436: CONNECT BY loop in user data EXP-00000: Export terminated unsuccessfully I use Oracle 8.1.5.0.2 running http://www.dbforums.com/showthread.php?972557-error-export-database on Novell Netware 5.1 sp6 I do a full export with a .ncf (like a .bat) command : load exp81 system/xxx parfile=oracle1:\oranw815\nlm\export.par The export.par file is : buffer = 30000 file = oracle1:dmp\export.dmp log = oracle1:dmp\export.log full = y consistent = y Can someone help me? thanks Reply With Quote 12-01-03,10:43 #2 Hings View Profile View Forum Posts Registered User Join Date Apr 2003 Location Minneapolis, MN Posts 273 It seems that you have one or more corrupted blocks. Try to run dbv against your file(s) and fix it. I guess it will help you out. Bhavin MS Computer Science OCP DBA 9i/8i Reply With Quote 12-01-03,23:42 #3 satish_ct View Profile View Forum Posts Visit Homepage Registered User Join Date Nov 2003 Location Bangalore, INDIA Posts 333 Hi, Export generates the list of objects to be exported at the beginning of the export for each user. If a table is dropped after this time, export will report an error like you encountered. These look like, perhaps, temporary tables. Does your application create temporary tables? Do you know what these tables are/were? SATHISH . Reply With Quote 12-02-03,03:50 #4 piwee View Profile View Forum Posts Registered User Join Date Nov 2003 Posts 4 Orig
EXP/IMP tools to accessDB Oracle Database 11g: Interactive QuickReference OracleTube@DeskRSS - Posts Tricks to restrict EXP/IMP tools to accessDB Posted: January 24, 2011 in tools Tags: https://oracletube.wordpress.com/2011/01/24/tricks-to-restrict-expimp-tools-to-access-db/ exp EXU8FUL export previlege 0 Traditional exp/imp tools are getting decommission soon http://www.iselfschooling.com/board/index.php?topic=470.0 .. In heterogeneous environments we still support interfaces. exp/imp are client tools so often found that many unwanted users uses to export data from production system. It sometimes degrade db performance. To prevent such scenario you can use below tricks to avoid unwanted access to oracle error production DB. Trick #1 Revoke SELECT privilege from EXU8FUL table. If connecting user have DBA, SELECT ANY DICTIONARY and EXP_FULL_DATABASE grants then trick will not work. GOTO DB server –> $ sqlplus “/ as SYSDBA” SQL> revoke select on EXU8FUL from public; Revoke succeeded. And, now try to exp from client machine. $ exp scott/tiger@oracle_db file=xp.dmp log=xp.log Export: 00008 oracle error Release 10.2.0.4.0 - Production on Mon Jan 24 16:02:05 2011 Copyright (c) 1982, 2007, Oracle. All rights reserved. EXP-00008: ORACLE error 942 encountered ORA-00942: table or view does not exist EXP-00024: Export views not installed,please notify your DBA EXP-00000: Export terminated unsuccessfully Trick #2: Write an oracle trigger on database level. To make trick manageable create an oracle table no_exp_imp_users as below. create table no_exp_imp_users ( block_user varchar2(30) not null ); create or replace trigger no_exp_imp after logon on database begin if ( user in (select block_user from no_exp_imp_users ) and ( upper(sys_context(‘userenv','module')) in (‘exp','imp') OR upper(sys_context(‘userenv','module')) in (‘EXP.EXE','IMP.EXE') ) then raise_application_error (-20999, ‘DOOMED! You are not allowed to run exp/imp'); end if; end; / Now insert record into no_exp_imp_users to prevent them to access it. insert into no_exp_imp_user values (‘SCOTT'); commit; C:\>exp scott/tiger@oracle_db file=xp.dmp log=xp.log Export: Release 10.2.0.4.0 - Production on Mon Jan 24 16:02:05 2011 Copyright (c) 1982, 2007, Oracle. All rights reserved. EXP-00056: ORACLE error 604 encountered ORA-00604: error occurred at recursive SQL level 1 ORA-20999: ‘DO
1 Hour 1 Day 1 Week 1 Month Forever Login with username, password and session length Home Help Login Register iSelfSchooling.com - Community of Sharing>General Category>Registerd and Subscribed Members. This is not a FORUM!!!!!>How to prevent the EXP-00080 and EXP-00079 error messages... Pages: [1] | Go Down « previous next » Send this topic | Print Author Topic: How to prevent the EXP-00080 and EXP-00079 error messages... (Read 6588 times) john2010 Guest How to prevent the EXP-00080 and EXP-00079 error messages... « on: May 03, 2010, 07:40:42 AM » www.iselfschooling.comWhile you are performing a full export, you export skips tables because the following error is encountered.-------------------------About to export specified tables via Direct Path ...EXP-00080: Data in table "ISELFSCHOOLING_TABLE" is protected. Using conventional mode.EXP-00079: Data in table "ISELFSCHOOLING_TABLE" is protected. Conventional path may only be exporting partial table.. . exporting table ISELFSCHOOLING_TABLE 1 rows exportedExport terminated successfully with warnings.-------------------------Reason: The warning is given because the table has a so called fine-grained access control policy (FGA) enabled. If fine-grained access control is enabled on a SELECT statement, then the Export may not export the entire table, because fine-grained access may rewrite the query.Only the user SYS or any user who has the EXEMPT ACCESS POLICY privilege, can select all rows.To ensure that the table has an enabled FGA Control policy:SQL> CONNECT system/managerSQL> SELECT owner||'.'||object_name "OBJECT_OWNER.NAME" FROM dba_policies WHERE object_name='ISELFSCHOOLING_TABLE'; ENA SEL OBJECT_OWNER.NAME --- --- ----------------------- YES YES owner.ISELFSCHOOLING_TABLETo solve this problem, do the following steps:Login to SQLPLUS as the SYSDBA user and grant the EXEMPT ACCESS POLICY privilege to the user that performs the EXP command. SQL> CONNECT / AS SYSDBAGrant the EXEMPT ACCESS POLICY privilege to the user that performs the EXP command. SQL> GRANT exempt access policy TO exp_user;Make sure that privilege was granted to EXP_USER. SQL> SELECT grantee FROM dba_role_privs WHERE granted_role IN (SELECT grantee FROM dba_role_privs WHERE granted_role='EXP_FULL_DATABASE');Now, you should be able to perform export without errors.# exp exp_user/password FILE=iselfschooling_table.dmp \ LOG=iselfschooling_table.log \ TABLES=owner.ISELFSCHOOLING_TABLE DIRECT=yNote: Rows in table