Active 2 years, 7 months ago. Viewed 3k times. Add a comment. Active Oldest Votes. The Overflow Blog. Podcast Making Agile work for data science. Stack Gives Back In full database mode, all grants on a table are exported.
In user mode, only those granted by the owner of the table are exported. System privilege grants are always exported.
Displays a description of the Export parameters. Default: y. Specifies a filename to receive informational and error messages. If you specify this parameter, messages are logged in the log file and displayed to the terminal display.
Default: none. Indicates that the export is a user-mode export and lists the users whose objects will be exported. If the user initiating the export is the database administrator DBA , multiple users can be listed. 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.
Specifies a filename for a file that contains a list of Export parameters. For more information about using a parameter file, see Invoking Export and Import. This parameter enables you to select a subset of rows from a set of tables when doing a table mode export. For example, the following statement will unload rows in both emp and bonus that match the query:. If a table is missing the columns specified in the QUERY clause, an error message will be produced, and no rows will be exported for the offending table.
You cannot determine from the contents of the export file whether the data is the result of a QUERY export. Specifies the length, in bytes, of the file record. If you do not define this parameter, it defaults to your platform-dependent value for buffer size.
The highest value is 64 KB. It does not affect the operating system file block size. Oracle Database Concepts. Oracle Database Administrator's Guide for more information about resumable space allocation.
The value for this parameter identifies the statement that is resumable. The value of the parameter specifies the time period during which an error must be fixed. If the error is not fixed within the timeout period, execution of the statement is terminated. Specifies the type of database optimizer statistics to generate when the exported data is imported.
However, the precalculated optimizer statistics will not be used at export time if a table has columns with system-generated names. 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:. Table-level export lets you export entire partitioned or nonpartitioned tables.
If a table in the list is partitioned and you do not specify a partition name, all its partitions and subpartitions are exported. 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.
Partition-level Export lets you export one or more specified partitions or subpartitions within a table. By default, table names in a database are stored as uppercase. If you have a table name in mixed-case or lowercase, and you want to preserve case-sensitivity for the table name, you must enclose the name in quotation marks.
The name must exactly match the table name stored in the database. 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.
Table names specified on the command line cannot include a pound sign, unless the table name is enclosed in quotation marks. Similarly, in the parameter file, if a table name includes a pound sign, the Export utility interprets the rest of the line as a comment, unless the table name is enclosed in quotation marks. F or 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:. 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. See Invoking Export and Import for more information. The user's guide for your Oracle Net protocol for information about specifying a connect string for Oracle Net. The following formula gives an approximation of the buffer size that inserts a given array of rows:.
If the buffer cannot hold the longest row in a table, Import attempts to allocate a larger buffer. For DATE columns, two or more rows are inserted at once if the buffer is large enough.
Specifies whether Import should commit after each array insert. By default, Import commits only after loading each table, and Import performs a rollback when an error occurs, before continuing with the next object. If a table has nested table columns or attributes, the contents of the nested tables are imported as separate tables. Therefore, the contents of the nested tables are always committed in a transaction distinct from the transaction used to commit the outer table.
Specifies whether or not Import should compile packages, procedures, and functions as they are created. For example, packages that are used to build domain indexes are compiled when the domain indexes are created.
Specifies whether or not table constraints are to be imported. The default is to import constraints. If you do not want constraints to be imported, you must set the parameter value to n. Note that primary key constraints for index-organized tables IOTs and object tables are always imported. Specifies whether or not the existing datafiles making up the database should be reused. Note that the export file contains the datafile names used in each tablespace. Specifies that Import should display a progress meter in the form of a period for n number of rows imported.
Default: expdat. Specifies the names of the export files to import. You need not be the user who exported the export files; however, you must have read access to the files. A comma-delimited list of schemas to import. The parameter enables you to import a subset of schemas from an export file containing multiple schemas for example, a full export dump file or a multischema, user-mode export dump file. Only the name of the object is affected. The user that you specify with TOUSER must exist in the target database prior to the import operation; otherwise an error is returned.
Create objects in the importer's schema regardless of the presence of or absence of the FROMUSER schema on import if the export file is a single-schema, user-mode export dump file created by an unprivileged user. Specifies whether to import the entire export dump file.
By default, the Import utility imports any object grants that were exported. If the export was a user-mode export, the export file contains only first-level object grants those granted by the owner.
Displays a description of the Import parameters. Default: n. Specifies how object creation errors should be handled. Note that only object creation errors are ignored; other errors, such as operating system, database, and SQL errors, are not ignored and may cause processing to stop. You might want to import data into tables that already exist in order to use new storage parameters or because you have already created the table in a cluster.
Also, objects dependent on tables, such as indexes, grants, and constraints, will not be created. Specifies whether or not to import indexes. System-generated indexes such as LOB indexes, OID indexes, or unique constraint indexes are re-created by Import regardless of the setting of this parameter. If indexes for the target table already exist at the time of the import, Import performs index maintenance when data is inserted into the table.
When this parameter is specified, index-creation statements for the requested mode are extracted and written to the specified file, rather than used to create indexes in the database. No database objects are imported. The file can then be edited for example, to change storage parameters and used as a SQL script to create the indexes. This step imports the database objects while preventing Import from using the index definitions stored in the export file. Specifies a file to receive informational and error messages.
If you specify a log file, the Import utility writes all information to the log in addition to the terminal display. Specifies a filename for a file that contains a list of Import parameters. For more information about using a parameter file, see Parameter Files. The SQL statements contained in the export are displayed in the order in which Import will execute them.
It is important to understand how they affect each other. A value of y means that Import will skip building indexes that were set to the Index Unusable state by either system or user. Other indexes not previously set to Index Unusable continue to be updated as rows are inserted. This parameter enables you to postpone index maintenance on selected index partitions until after row data has been inserted.
You then have the responsibility to rebuild the affected index partitions after the Import. Specifies what is done with the database optimizer statistics at import time.
Import database optimizer statistics only if they are not questionable. If they are questionable, recalculate the optimizer statistics. Do not import the database optimizer statistics. Instead, recalculate them on import. Oracle Database Concepts for more information about the optimizer and the statistics it uses.
Specifies whether or not to import any general Streams metadata that may be present in the export dump file. Specifies whether or not to import Streams instantiation metadata that may be present in the export dump file.
Specify y if the import is part of an instantiation in a Streams environment. Specifies that the import is a table-mode import and lists the table names and partition and subpartition names to import. Table-mode import lets you import entire partitioned or nonpartitioned tables. If a table in the list is partitioned and you do not specify a partition name, all its partitions and subpartitions are imported. All the tables whose names match all the specified patterns of a specific table name in the list are selected for import.
A table name in the list that consists of all pattern matching characters and no partition name results in all exported tables being imported. As the export file is processed, each table name in the export file is compared against each table name in the list, in the order in which the table names were specified in the parameter. To avoid ambiguity and excessive processing time, specific table names should appear at the beginning of the list, and more general table names those with patterns should appear at the end of the list.
Although you can qualify table names with schema names as in scott. The following are examples of how case-sensitivity can be preserved in the different Import modes.
Similarly, in the parameter file, if a table name includes a pound sign, the Import utility interprets the rest of the line as a comment, unless the table name is enclosed in quotation marks. F or example, if the parameter file contains the following line, Import interprets everything on the line after emp as a comment and does not import the tables dept and mydata:. However, given the following line, the Import utility imports all three tables because emp is enclosed in quotation marks:.
You must use escape characters to get such characters in the name past the shell and into Import. If there is more than one tablespace in the export file, you must specify all of them as part of the import operation. When you import a table that references a type, but a type of that name already exists in the database, Import attempts to verify that the preexisting type is, in fact, the type used by the table rather than a different type that just happens to have the same name.
To do this, Import compares the type's unique identifier TOID with the identifier stored in the export file. Import will not import the table rows if the TOIDs do not match. In some situations, you may not want this validation to occur on specified types for example, if the types were created by a cartridge installation.
If you do not specify a schema name for the type, it defaults to the schema of the importing user. For example, in the first preceding example, the type typ1 defaults to scott. It has no effect on table types. The output of a typical import with excluded types would contain entries similar to the following:.
Specifies a list of user names whose schemas will be targets for Import. The user names must exist prior to the import operation; otherwise an error is returned. If multiple schemas are specified, the schema names are paired. The following example imports scott' s objects into joe 's schema, and fred 's objects into ted' s schema:.
When specified as y , instructs Import to import transportable tablespace metadata from an export file. In each example, you are shown how to use both the command-line method and the parameter file method. Some examples use vertical ellipses to indicate sections of example output that were too long to include.
In this example, an entire database is exported to the file dba. Information is displayed about the release of Export you are using and the release of Oracle Database that you are connected to.
Status messages are written out as the entire database is exported. A final completion message is returned when the export completes successfully, without warnings. In this example, user scott is exporting his own tables. Then, status messages similar to the following are shown:. 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. This section gives some examples of import sessions that show you how to use the parameter file and command-line methods.
The examples illustrate the following scenarios:. In this example, using a full database export file, an administrator imports the dept and emp tables into the scott schema. Information is displayed about the release of Import you are using and the release of Oracle Database that you are connected to. This example illustrates importing the unit and manager tables from a file exported by blake into the scott schema. In this example, a database administrator DBA imports all tables belonging to scott into user blake' s account.
This section describes an import of a table with multiple partitions, a table with partitions and subpartitions, and repartitioning a table on different columns. In this example, emp is a partitioned table with three partitions: P1 , P2 , and P3. In a partition-level Import you can specify the specific partitions of an exported table that you want to import. In this example, these are P1 and P3 of table emp:. This example demonstrates that the partitions and subpartitions of a composite partitioned table are imported.
This example assumes the emp table has two partitions based on the empno column. This example repartitions the emp table on the deptno column. The Export and Import utilities are the only method that Oracle supports for moving an existing Oracle database from one hardware platform to another. You will need this information later in the process. Move the dump file to the target database server. Before importing the dump file, you must first create your tablespaces, using the information obtained in Step 1.
Otherwise, the import will create the corresponding datafiles in the same file structure as at the source database, which may not be compatible with the file structure on the target system.
This section describes the different types of messages issued by Export and Import and how to save them in a log file. You can capture all Export and Import messages in a log file, either by using the LOG parameter or, for those systems that permit it, by redirecting the output to a file.
A log of detailed information is written about successful unloads and loads and any errors that may have occurred. Export and Import do 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 warning s. 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 or Import 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. When an export or import completes without errors, a message to that effect is displayed, for example:. If one or more recoverable errors occurs but the job continues to completion, a message similar to the following is displayed:.
If a nonrecoverable error occurs, the job terminates immediately and displays a message stating so, for example:. Export and Import provide the results of an operation immediately upon completion. Depending on the platform, the outcome may be reported in a process exit code and the results recorded 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. Table Exit Codes for Export and Import. This section describes factors to take into account when using 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. For example, use FTP or a similar file transfer protocol to transmit the file in binary mode. Transmitting export files in character mode causes errors when the file is imported.
With Oracle Net, you can perform exports and imports over a network. For example, if you run Export locally, you can write data from a remote Oracle database into a local export file. If you run Import locally, you can read data into a remote Oracle database. For the exact syntax of this clause, see the user's guide for your Oracle Net protocol.
The following sections describe the globalization support behavior of Export and Import with respect to character set conversion of user data and data definition language DDL. The Export utility always exports user data, including Unicode data, in the character sets of the Export server. Character sets are specified at database creation. If the character sets of the source database are different than the character sets of the import database, a single conversion is performed to automatically convert the data to the character sets of the Import server.
If the export character set has a different sorting order than the import character set, then tables that are partitioned on character columns may yield unpredictable results. For example, consider the following table definition, which is produced on a database having an ASCII character set:.
To obtain the desired results, the owner of partlist must repartition the table following the import. If the export file's character set is different than the import user session character set, then Import converts the character set to its user session character set. Import can only perform this conversion for single-byte character sets. This means that for multibyte character sets, the import file's character set must be identical to the export file's character set.
A final character set conversion may be performed if the target database's character set is different from the character set used by the import user session. To minimize data loss due to character set conversions, ensure that the export database, the export user session, the import user session, and the import database all use the same character set.
Some 8-bit characters can be lost that is, converted to 7-bit equivalents when you import an 8-bit character set export file. Most often, this is apparent when accented characters lose the accent mark.
During character set conversion, any characters in the export file that have no equivalent in the target character set are replaced with a default character. The default character is defined by the target character set. The three interrelated objects in a snapshot system are the master table, optional snapshot log, and the snapshot itself.
The tables master table, snapshot log table definition, and snapshot tables can be exported independently of one another. Snapshot logs can be exported only if you export the associated master table. You can export snapshots using full database or user-mode export; you cannot use table-mode export. The snapshot log in a dump file is imported if the master table already exists for the database to which you are importing and it has a snapshot log.
As a result, each ROWID snapshot's first attempt to do a fast refresh fails, generating an error indicating that a complete refresh is required. After you have done a complete refresh, subsequent fast refreshes will work properly. In contrast, when a primary key snapshot log is exported, the values of the primary keys do retain their meaning upon import.
FSAL is a browser-less solution which allows you to run your Oracle Forms applications without needing to use a web browser. Once all prerequisites have been met, you may proceed with setting up your Forms and Reports environment to use FSAL:.
Open up formsweb. Locate your configuration section for your applications. The name of your application section will be surrounded by brackets such as [app].
You can define the variable before running a SQL statement, or you specify that the statement prompts for a variable value at the time that the SQL statement is run. Because the variable has already been defined, you are not prompted to enter a value.
Skip Headers. Starting and Exiting SQL Command Line To start SQL Command Line from the operating-system command prompt, enter the following: sqlplus When prompted, enter the username and password of the user account schema that you want to access in the local database.
All rights reserved.
0コメント