Oracle exp with file size parameter

There is no de-compress option in imp utility when you import the data. From version 10i Oracle have new utility called impdp and expdp which have more functions and is faster. It would have been better, since exp and imp are being deprecated, to see examples of expdp or impdp instead or at least in addition to these examples. In these examples can we substitute expdp and impdp for exp and imp?

Someone can answer for me or give an idea how to accomplished that? Save my name, email, and website in this browser for the next time I comment. Notify me of followup comments via e-mail. All rights reserved Terms of Service. Even if you are a sysadmin or developer, when you have Oracle running in your environment, you might encounter a situation where you have to do some basic DBA tasks.

Ric February 2, , am. Wolfsrudel February 2, , am. Moreover, if you know how many dump files you will get as a result, you can pass file names as a value to the dumpfile parameter:. Burleson is the American Team Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals. Feel free to ask questions on our Oracle forum. This allows you to tune plan generation for queries in a nonproduction database using statistics from a production database.

Oracle9i Database Concepts. Specifies that the Export is a table-mode Export and lists the table names and partition and subpartition names to export. You can specify the following when you specify the name of the table:. All the tables in the relevant schema that match the specified pattern are selected for export, as if the respective table names were explicitly specified in the parameter.

Some operating systems, such as UNIX, require that you use escape characters before special characters, such as a parenthesis, so that the character is not treated as a special character. Some operating systems require that quotation marks on the command line be preceded by an escape character. The following are examples of how case-sensitivity can be preserved in the different Export modes. For example, if the parameter file contains the following line, Export interprets everything on the line after emp as a comment and does not export the tables dept and mydata:.

However, given the following line, the Export utility exports all three tables because emp is enclosed in quotation marks:. Some operating systems require single quotation marks rather than double quotation marks, or the reverse; see your Oracle operating system-specific documentation. Different operating systems also have other restrictions on table naming. You must use escape characters to get such characters in the name past the shell and into Export.

This includes all tables contained in the list of tablespaces and all tables that have a partition located in the list of tablespaces. Indexes are exported with their tables, regardless of where the index is stored. When specified as y , this parameter enables the export of transportable tablespace metadata. If you omit the password, Export will prompt you for it.

Certain parameters can conflict with each other. In each example, you are shown how to use both the command-line method and the parameter file method. In this example, an entire database is exported to the file dba.

User mode exports can be used to back up one or more database users. For example, a DBA may want to back up the tables of deleted users for a period of time. User mode is also appropriate for users who want to back up their own data or who want to move objects from one owner to another.

In this example, user scott is exporting his own tables. In table mode, you can export table data or the table definitions. If schemaname is not specified, Export defaults to the previous schema name from which an object was exported. If there is not a previous object, Export defaults to the exporter's schema. A nonprivileged user can export only dependent objects for the specified tables that the user owns. Exports in table mode do not include cluster definitions.

As a result, the data is exported as unclustered tables. Thus, you can use table mode to uncluster tables. In this example, pattern matching is used to export various tables for users scott and blake. In partition-level export, you can specify the partitions and subpartitions of a table that you want to export. Assume emp is a table that is partitioned on employee name.

There are two partitions, m and z. As this example shows, if you export the table without specifying a partition, all of the partitions are exported. As this example shows, if you export the table and specify a partition, only the specified partition is exported. Assume emp is a partitioned table with two partitions, m and z.

Table emp is partitioned using the composite method. Partition m has subpartitions sp1 and sp2, and partition z has subpartitions sp3 and sp4. As the example shows, if you export the composite partition m, all its subpartitions sp1 and sp2 will be exported.

If you export the table and specify a subpartition sp4 , only the specified subpartition is exported. Starting Export from the command line with no parameters initiates the interactive method. The command-line interactive method does not provide prompts for all Export functionality and is provided only for backward compatibility. When you invoke Export interactively, the response given by Export depends on what you enter at the command line.

Table shows the possibilities. Additionally, if you omit the password and allow Export to prompt you for it, you cannot specify the instance string as well. You can specify instance only with username. After Export is invoked, it displays the following prompts.

You may not see all prompts in a given Export session because some prompts depend on your responses to other prompts. Some prompts show a default answer. If the default is acceptable, press Enter. For example, if beth is a privileged user exporting in table mode, Export assumes that all tables are in the beth schema until another schema is specified. This section describes the different types of messages issued by Export and how to save them in a log file.

You can capture all Export messages in a log file, either by using the LOG parameter see LOG or, for those systems that permit it, by redirecting the Export output to a file. The Export utility writes a log of detailed information about successful unloads and any errors that may occur.

Refer to your Oracle operating system-specific documentation for information on redirecting output. Export does not terminate after recoverable errors. For example, if an error occurs while exporting a table, Export displays or logs an error message, skips to the next table, and continues processing. These recoverable errors are known as warnings. For example, if a nonexistent table is specified as part of a table-mode export, the Export utility exports all other tables.

Then it issues a warning and terminates successfully. Some errors are nonrecoverable and terminate the Export session. These errors typically occur because of an internal problem or because a resource, such as memory, is not available or has been exhausted. For example, if the catexp. If one or more recoverable errors occurs but Export is able to continue to completion, Export displays the following message:.

If a nonrecoverable error occurs, Export terminates immediately and displays the following message:. Oracle9i Database Error Messages and your Oracle operating system-specific documentation. Export provides the results of an export operation immediately upon completion. Depending on the platform, Export may report the outcome in a process exit code as well as recording the results in the log file. This enables you to check the outcome from the command line or script.

Table shows the exit codes that get returned for various results. Data is read from disk into a buffer cache, and rows are transferred to the evaluating buffer.

The data, after passing expression evaluation, is transferred to the Export client, which then writes the data into the export file. Direct path Export is much faster than conventional path Export because data is read from disk into the buffer cache and rows are transferred directly to the Export client.

The evaluating buffer is bypassed. The data is already in the format that Export expects, thus avoiding unnecessary data conversion. The data is transferred to the Export client, which then writes the data into the export file. Figure illustrates how data extraction differs between conventional path Export and direct path Export.

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. In versions of Export prior to 8. 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.

The following users are exempt from Virtual Private Database and Oracle Label Security enforcement regardless of the export mode, application, or utility used to extract data from the database:. This is a powerful privilege and should be carefully managed. Your exact performance gain depends upon the following factors:. This section describes factors to take into account when you use Export and Import across a network.

Because the export file is in binary format, use a protocol that supports binary transfers to prevent corruption of the file when you transfer it across a network. How to execute this? You have to create multiple database directory objects in the database as per your requirement. You will get following error when you specify multiple values to directory. LRM multiple values not allowed for parameter 'directory'.