Oracle Export -- Conventional path

I have a very interesting situation with Oracle export. We do a schema level export and it appears like it chooses conventional path on certain tables. I've checked the table and none of them have columns such as long, blob, clob etc. All of them have one of the following -- char, date, number, varchar2. What makes the export utility choose the conventional path even when direct path is specified? Below are the parameters we use to export data:
owner=<schema_name>
direct=y
recordlength=65535
buffer=1073741824
compress=n
triggers=n
statistics=none

Any help/insights is greatly appreciated.

Thank you
RG
rgdbaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
is there anything else special about the table ?

Can you post the create tablescript for it and its tablespace?
0
Naveen KumarProduction Manager / Application Support ManagerCommented:
how did you find export is doing a conventional path ?
0
Naveen KumarProduction Manager / Application Support ManagerCommented:
i presume that you are using either parameter file or command line options to say direct=y when invoking oracle export.

Restrictions for Direct Path Exports
------------------------------------
To invoke a direct path Export, you must use either the command-line method or a parameter file. You cannot invoke a direct path Export using the interactive method.

To use direct path Export, specify the DIRECT=y parameter on the command line or in the parameter file. The default is DIRECT=n, which extracts the table data using the conventional path.

Additionally, be aware that the Export parameter BUFFER applies only to conventional path Exports. For direct path Export, use the RECORDLENGTH parameter to specify the size of the buffer that Export uses for writing to the export file.

In versions of Export prior to 8.1.5, you could not use direct path Export for tables containing objects and LOBs. If you tried to, their rows were not exported. This behavior has changed. Rows in tables that contain objects and LOBs will now be exported using conventional path, even if direct path was specified. Import will correctly handle these conventional path tables within direct path dump files.

also refer to the below url for more information :

http://download.oracle.com/docs/cd/B10501_01/server.920/a96652/ch01.htm#1006735

Thanks
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

rgdbaAuthor Commented:
There is nothing special about these tables, atleast I don't find anything special. None of them have the data types that would force the export utility to use conventional path. Number of columns in these tables range from 3 to 200. Same with row counts they range from zero to several millions (> 100 mil). I wanted to know if there is an undocumented feature/switch in export that forces it to pick conventional path. I can't imagine it being random.
RG
0
Naveen KumarProduction Manager / Application Support ManagerCommented:
how did you find export is doing a conventional path ?
0
rgdbaAuthor Commented:
Because it shows up in the export log saying that it is using conventional path export!!
0
rgdbaAuthor Commented:
One of our DBAs found the cause for this behavior. It was because these tables had unused columns and in 9i, tables with unused columns are switched to conventional export by Oracle. Below is the Metalink ID that explains this behavior.

Metalink ID: Note:262927.1

RG
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.