Re: Expdp a subset of fields from a table

I agree and, yes, we do RMAN backups, also. There are some types of internal corruption which RMAN at 10g won't detect, however, they are getting few and far between.

Thanks,
Rich

On Mon, Jun 29, 2009 at 12:54 PM, Robert Freeman <robertgfreeman_at_yahoo.com>wrote:

> Don't suppose you are doing RMAN backups? If you are, in most cases it
> should detect any corruption.
>
> RF
>
>
> Robert G. Freeman
> Oracle ACE
> Author:
> Oracle Database 11g RMAN Backup and Recovery (Oracle Press) - ON IT'S WAY
> SOON!
> OCP: Oracle Database 11g Administrator Certified Professional Study Guide
> (Sybex)
> Oracle Database 11g New Features (Oracle Press)
> Portable DBA: Oracle (Oracle Press)
> Oracle Database 10g New Features (Oracle Press)
> Oracle9i RMAN Backup and Recovery (Oracle Press)
> Oracle9i New Features (Oracle Press)
> Other various titles out of print now...
> Blog: http://robertgfreeman.blogspot.com
> The LDS Church is looking for DBA's. You do have to be a Church member in
> good standing. A lot of kind people write me, concerned I may be breaking
> the law by saying you have to be a Church member. It's legal I promise! :-)
> http://pages.sssnet.com/messndal/church/parachurch.pdf
>
> ------------------------------
> *From:* Rich <richa03_at_gmail.com>
> *To:* Michael McMullen <ganstadba_at_hotmail.com>
> *Cc:* Oracle-L Freelists <oracle-l_at_freelists.org>
> *Sent:* Monday, June 29, 2009 1:04:59 PM
>
> *Subject:* Re: Expdp a subset of fields from a table
>
> Hi William:
> "Your best option is to create a view and export the view."
> Hey, now that's creative. However, Oracle throws:
> ORA-39166: Object TEST_EXPDP_HIST_T2 was not found.
> ORA-31655: no data or metadata objects selected for job
>
> Michael et al,
> This table is only insert - no delete, no update.
> We also control when the inserts happen; the table is a sort of quasi
> partition using a view and more inexpensive disks. It's just part of our
> ILM.
> We have looked at delayed block cleanout and are working with Oracle to try
> prove whether that is or is not the issue.
>
> We have tried setting the event (1555 at errorstack level 3:10200),
> however, we don't get any trace from doing so - Oracle 10.2.0.4 on Linux
> x86_64.
> I did trace the session and it does show increasing values of:
> cleanouts only - consistent read gets 546440
> cleanout - number of ktugct calls 546440
> in v$sessstat during the export which confirms they are happening.
>
> We have tried analyze and it completed, however, the error (1555) persists.
> Using Oracle Notes 452341.1 and 787004.1 (faster) we have found no
> corruption in the LOB area.
> I think this is why Oracle is pressing for potential corruption in the
> table which is why I would like to try an expdp without the LOB.
>
>
> Thanks for your help,
> Rich
>
> On Mon, Jun 29, 2009 at 11:32 AM, Michael McMullen <ganstadba_at_hotmail.com>wrote:
>
>> I’ve always thought that performance and ora-1555 go hand in hand, slow
>> query on changing data, odds are you see ora-1555 but I also get these on my
>> lob tables even when there have been no data changes for hours. Look at
>> delayed block cleanout. You could always trace the session or set an event
>> on ora-1555 to make sure. It could even be an underlying index causing the
>> errors. Most of our data is bulk loaded so we usually do a combination of
>> undo_retention,fts on the data, analyze all data and analyze all indexes to
>> avoid the error. Delayed block cleanouts are supposed to be rare but I run
>> into it all the time.
>>
>>
>> ------------------------------
>>
>> *From:* oracle-l-bounce_at_freelists.org [mailto:
>> oracle-l-bounce_at_freelists.org] *On Behalf Of *Rich
>> *Sent:* Monday, June 29, 2009 1:33 PM
>> *To:* Blanchard, William
>> *Cc:* Oracle-L Freelists
>> *Subject:* Re: Expdp a subset of fields from a table
>>
>>
>>
>> Hi William,
>> I tried that:
>> $ORACLE_HOME/bin/expdp `/home/oracle/dbserver_dba` TRACE=480300
>> DIRECTORY=DPUMP_DIR_IMP1 \
>> DUMPFILE=HSR_TRANSLATION_HISTORY_TIER2_NO_LOB_`date +%d%b%Y`.expdp \
>> TABLES='HSR_TRANSLATION_HISTORY_TIER2' \
>> QUERY=HSR_TRANSLATION_HISTORY_TIER2\:\"select TRHS_PKEY, TRHS_SEQ,
>> TRAN_KEY, TRSP_KEY, TRHS_DESC, TRHS_DOC_URL, TRHS_DOC_SIZE,
>> TRHS_CREATED_DATE, TRHS_CONTENT_TYPE, TRHS_ZIPPED, BSTP_GUID,
>> TRHS_INHERITABLE from HSR_TRANSLATION_HISTORY_TIER2\" \
>> logfile=HSR_TRANSLATION_HISTORY_TIER2_NO_LOB_`date +%d%b%Y`_expdp.log
>>
>> I get ORA-00933: SQL command not properly ended
>>
>> Hi Michael,
>> It's not a performance issue, 1555 issue.
>> We checked for corruption in the LOB area to no avail and Oracle is saying
>> they think it might be the table segment, not the LOB segment.
>> I asked in the SR, however, thought I'd ask here, also.
>>
>>
>> On Mon, Jun 29, 2009 at 10:10 AM, Blanchard, William <
>> wblanchard_at_societyinsurance.com> wrote:
>>
>> Just include a select in your expdp command/parfile.
>>
>>
>>
>> QUERY = <schema>.<table>: SELECT …
>>
>>
>> ------------------------------
>>
>>
>>
>
>

--
http://www.freelists.org/webpage/oracle-l