• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2659
  • Last Modified:

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
0
rgdba
Asked:
rgdba
  • 3
  • 3
1 Solution
 
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now