An interpretation of the reason one cannot use SYS (or anyone connected as SYSDBA -- which becomes essentially SYS), is because a consistent export may not be obtained this way. I do not know the reason behind that, but it certainly explains your issue with the duplicate records. Also, it appears that any 'other' user with proper permissions is able to get a consistent export. So EXP_FULL_DATABASE, DBA etc will work for 'other' schemas, or indeed, depending what you wish to export, any permissions that allow that particular type of export -- except SYS / SYSDBA will create a consistent export. Therefore, yes SYSTEM will work as SYSTEM has the privileges... or any other user you create and give privileges. Personally, I use an admin account of my own creation for most tasks and not SYS or SYSTEM. IMP_FULL_DATABASE is for importing, (verses EXP_FULL_DATABASE). If you have a consistent export, you will have a consistent import. (note in older version of 'exp' there was a 'consistent' parameter... :) which is no longer part of 'expdp'). Joel Patterson Database Administrator 904 727-2546 -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of sreejith s Sent: Tuesday, October 11, 2011 4:10 AM To: aprilcsims@xxxxxxxxx Cc: ORACLE-L Subject: Re: expdp without password Hi, Came across this thread while searching through Oracle L archives. I have referred this note which clearly says one should not use SYS / SYSDBA for datapump export and import. However , neither this note, nor documentation mention clearly which user can be used for a consistent export and import ( Perhaps, I missed it ? ) . Suppose I have to export multiple schemas using "SCHEMAS" parameter in datapump export, I cannot take a schema level export.In this case which user can I use ? SYSTEM ? or any user with IMP_FULL_DATABASE privilege ? Also, I have noticed during our past import for a highly transactional database ( more than 1.5 TB) size, we usually get the following error during import and import terminates. *ORA-39014: One or more workers have prematurely exited. ORA-39029: worker 1 with process name "DW01" prematurely terminated ORA-31671: Worker process DW01 had an unhandled exception. ORA-12801: error signaled in parallel query server P000, instance clvxd02q:FLYVXS2 (2) ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found ORA-06512: at "SYS.KUPW$WORKER", line 1423 ORA-06512: at line 2 * On analysis, we found that there is a duplicate value for a primary key / unique index for one of the table which we are importing. We are using *FLASHBACK_TIME="to_TIMESTAMP(to_char(systimestamp,'DD-MM-YYYY HH24:MI:SS'), 'DD-MM-YYYY HH24:MI:SS')"* in our export parameter file. When we check the production database table, there is no duplicate .The workaround we did was EXCLUDE index & constraints first, delete the duplicates , then do import with INCLUDE=index & constraint. I am just wondering if the use of FLASHBACK_TIME along with SYSDBA could be the reason for not getting a consistent export. Any help / suggestions on this ? Best Regards, Sreejith On Tue, Sep 27, 2011 at 6:52 PM, April Sims <aprilcsims@xxxxxxxxx> wrote: > Using sysdba for exports is NOT recommended since 10g. Below is a direct > quote from MOS Note: *How to Connect AS SYSDBA when Using Export or Import > [ID 277237.1]* > "SYSDBA is used internally in the Oracle database and has specialized > functions. Its behavior is not the same as for generalized users. For > example, the SYS user cannot do a transaction level consistent read > (read-only transaction). Queries by SYS will return changes made during the > transaction even if SYS has set the transaction to be READ ONLY. Therefore > export parameters like CONSISTENT, OBJECT_CONSISTENT, FLASHBACK_SCN, and > FLASHBACK_TIME cannot be used. > Starting with Oracle10g, the export shows a warning that the export is not > consistent when the export is started with CONSISTENT=Y and connects to the > database with the user SYS (or as SYSDBA): > EXP-00105: parameter CONSISTENT is not supported for this user > > Note that Oracle automatically provides read consistency to a query so that > all the data that the query sees comes from a single point in time > (statement-level read consistency). For export this means that the export > of > table data is consistent. However, if a table contains nested tables, the > outer table and each inner table are exported as separate transactions. And > if a table is partitioned, each partition is exported as a separate > transaction. If a nested table or a partitioned table was updated during > the > export, the data that is exported while connected as the SYS schema could > be > inconsistent. > > Typically, there is no need to invoke Export or Import as SYSDBA, except in > the following situations: > - at the request of Oracle technical support; > - when exporting a transportable tablespace set with the old-style export > utility (Oracle9i and Oracle8i); > - when importing a transportable tablespace set with the old-style import > utility (Oracle10g, Oracle9i, and Oracle8i)." > > > On Tue, Sep 27, 2011 at 6:45 AM, <Joel.Patterson@xxxxxxxxxxx> wrote: > > > > > I was wondering if you can export, (expdp), without a password, (or > wallet > > etc.) similar to using other utilities such as 'Sqlplus / as sysdba', or > > 'rman target /'? > > > > I have a password script and can create a centralized password file > > (unencrypted), but since I have been able to do most everything else > without > > a password, I would rather not implement it just because of this issue. > > > > I've googled (with mixed results -- seen an answer that did not work). > Did > > not find it (an example) in the manual. > > > > Best Regards, > > > > > > -- > > //www.freelists.org/webpage/oracle-l > > > > > > > > > -- > April C. Sims > IOUG SELECT Journal Executive Editor > http://aprilcsims.wordpress.com > Twitter, LinkedIn > Oracle Database 11g – Underground Advice for Database Administrators > < > http://www.amazon.com/Oracle-Database-Underground-Advice-Administrators/dp/1849680000/ref=sr_1_1?ie=UTF8&s=books&qid72289339&sr=8-1#noop> > https://www.packtpub.com/oracle-11g-database-implementations-guide/book > OCP 8i, 9i, 10g, 11g DBA > Southern Utah University > aprilcsims@xxxxxxxxx > > -- > //www.freelists.org/webpage/oracle-l > > > -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l