Shulou(Shulou.com)05/31 Report--

This article introduces you to the example analysis of impdp/expdp, the content is very detailed, interested friends can refer to, hope to be helpful to you.

Create tablespace C_MAIN DATAFILE'D:\ oracle\ product\ 10.2.0\ oradata\ orcl\ main.dbf' SIZE 1024m

I. exp / imp backup and restore

You can use plsql to back up and restore this oracle database. You can also use the command line method of cmd for backup and restore.

The steps are as follows:

Open the cmd window and change to d:\ oracle10\ product\ 10.1.0\ db_1\ bin\ (the folder directory of the executable under the oracle installation directory).

Invoke the command:

Backup:

Exp user/pass@database file=F:\ dump\ 20120521.dmp owner= (user)

Log=F:\ dump\ user-exp.log

Reduction

Imp newuser/pass@databasefile=F:\ dump\ 20120521.dmp fromuser=user touser=newuser log=F:\ dump\ user-imp.log

two。 Using expdp, impdp backup and restore

Log in to the linux system and enter the sql command line with sqlplus "/ as sysdba"

2.1 View the catalog:

SQL > select * from dba_directories

2.2. specify the dump_dir directory: (directory maintenance command:)

SQL > create directory dump_dir as'/ oracle/backup';// Establishment

Drop directory dump_dir;// deletion

2.3. Authorize the user to operate on the directory:

SQL > grant read,write on directory dump_dir to user_name

2.4 perform command operations:

Backup:

Expdp user/pass DIRECTORY = dump_dir DUMPFILE=user.dmp SCHEMAS=user logfile=user-expdp.log version=10.2.0.1.0 (need to import the version of the machine: used to import the high-version export to the lower-version oracle database)

Restore:

Impdp newuser/pass DIRECTORY=dump_dir DUMPFILE=user.dmp REMAP_SCHEMA=user:newuser logfile=user-impdp.log table_exists_action=replace

The REMAP_SCHEMA parameter is: source scheme: destination scheme.

Third, the problems encountered:

3.1processing object type scheme_export/table/statistics/table_statistics . After the execution, there was a fake death here, and it stopped on this screen for a long time.

You can add a parameter EXCLUDE=TABLE_STATISTICS. I left it for a while and came back to execute it.

3.2ORA-39213: Metadata processing is not available solution:

SQL > execute dbms_metadata_util.load_stylesheets

=

Online reference:

For reference, the parameters of http://www.cnblogs.com/lanzi/archive/2011/01/06/1927731.html impdp and expdp are very detailed.

a. Create a dblink to the server

Create database link link_name connect to username identified by password using 'connect_string'; / / username and password are server-side

B.conn / as sysdba

Create or replace directory dir as' directory'

Grant read,write on directory dir to username

C.expdp username/password directory=dir network_link=link_name. / / the username here uses the user who created the dblink, and the directory is also created by the target database.

4. If you want to import a database directly without generating a dmp file, the principle is similar to 2. Use impdp with network_link directly, so you can impdp directly, bypassing the expdp step.

And the article:

Http://tech.it168.com/db/o/2007-03-14/200703140943314.shtml

Quote

To learn about impdp, please first learn about export. I have taken notes on export before: oracle

Expdp of 10g data pump. These two notes may only be useful to programmers and are usually used to import data from the official zone into the test area, which may not be very helpful to database administrators, who use most of these tools for data backup.

Impdp can load data into the entire database, specific scenarios (that is, users), specific tablespaces, or specific tables, or transfer tablespaces to the database. Impdp supports network mode operations, that is, loading data from another database.

Import data using impdp

1. Specify the directory where the dump file (the file where the exported data is stored) is stored

SQL > connect sys/password as sysdba

SQL > create directory dump_dir as'd:\ dump'

2. Use the impdp command

Start-- > run-- > cmd

Impdp username/password parameter1 [, parameter2, … .]

Where username represents the user name, password represents the user's password, and parameter1 and parameter2 represent parameters. There are nearly 40 types of parameters, and you can use the command impdp help=y to see the meaning of all parameters and parameters. Here are some common parameters needed to import databases and tables, which have exactly the same meaning as in expdp.

Content: specifies what to import. Syntax format: content= {all | data_only | metadate_only}. Where all means to import object definitions and their data, data_only means to import only object data, and metadate_only means to import only object definitions. Default is all

Directory: specify the default location where the dump and log files are located (that is, the file location where the data is saved). Syntax format: directory=direcotry_object. Direcotry_object is a directory object created by the create directory statement.

Dumpfile: specifies the name of the dump file. Syntax format: dumpfile= [direcotry_object:] file_name [, … .] . Direcotry_ object defaults to the directory object specified by the above directory parameter. The default name of file_name is expdat.dmp. If the import file is too large, you can specify multiple file names, such as direcotry_object:mydb_%U.dmp. "% U" is a placeholder and a file with the same name will not appear in this way.

Logfile: specifies the name of the log file. Syntax format: logfile= [direcotry_object:] file_name. Direcotry_ object defaults to the directory object specified by the directory parameter. File_name log file, default name is export.log.

Exclude: objects to be excluded on import. Syntax format: exclude=object_type [: name_clause] [, … .] . Object_type represents object types, such as table, view, index, function, preocedure,name_clause means to use SQL statements to specify filter criteria for object names, such as table: "like 'emp%'" to exclude any tables that start with emp.

Include: the objects to be included on import. Syntax format: include = object_type [: name_clause] [, … .] . The meaning of object_type and name_clause is the same as that of the exclude parameter. Include and exclude cannot be used at the same time. When an object is included, all related objects are also included.

Network_link: specifies the database link name when the grid is imported. Syntax format: network_link=database_link. Database_link represents the remote database link name.

Query: specifies the where condition for importing data. Syntax format: query= [schema.] [table_name:] query_clause. Where schema represents the scheme name, which is the user name, table_name represents the table name, and query_clause represents the conditional clause, that is, the where clause.

Tables: specifies the import table. Syntax format: tables= [schema.] table_name [,... .] . Schema represents the scheme name, and table_name represents the table name

Full: specifies whether to import the entire database. Syntax format: full= {y | n}. Y indicates that the database import is performed, and the n table is not executed. The default is n.

Parfile: specifies the import parameter file name. Write the parameters required by the impdp command in a file that you specify when you execute the impdp command instead of specifying various parameters

Import instance

By the way, write down the expdp command for easy comparison. In order not to repeat the previous export note, let's take importing data from a remote database into a local database as an example.

Export remote database

1. Estimate the dump file size

If the database is large, this process may take a long time. Before exporting the database, it is best to estimate the size of the dump file (the file to which it is exported) to determine the appropriate filesize parameter values and to determine whether there is enough disk space. Use syntax:

Expdp username/password@234DB full=y estimate_only=y nologfile=y

Where estimate_only=y means that only the disk space occupied by the dump file is estimated and no export operation is performed; @ 234DB represents the remote database, which is defined in the tnsnames.ora file.

two。 Export database

Expdp username/password@234DB directory=dump_dir dumpfile=mydb_%U.dmp logfile=mydb.log filesize=200m full=y

Where dump_dir represents the directory of the exported file, which is located in the remote machine because the remote database is exported here;% U is a placeholder, representing the two digits of 01q99, and execution will create files such as MYDB_21.DMP.

3. Import the entire database

Impdp username/password directory=dump_dir dumpfile=mydb_%U.dmp full=y

Where dump_dir represents the directory of the input and output files. Here, the files exported remotely are copied to the local machine.

Import remote table

1. Export tabl

Expdp username/password@234DB directory=dump_dir dumpfile=mytable.dmp tables=schemas.emp

Where schemas represents the scheme (that is, the user) and emp represents the remote database name. The dump file in this way will be saved in the operating system of the remote server. If you want to save the dump file to the local machine, use the following syntax:

Expdp username/password directory=dump_dir dumpfile=mytable.dmp tables=schemas.emp network_link=to234db

Where username/password is the user name and password of the local database, and to234db is the database link of the local database to the remote database. In this way, the data can be saved to the local machine, but it will generate network traffic.

two。 Import tabl

Impdp username/password directory=dump_dir dumpfile=mytable.dmp tables=schemas.emp

The schemas should be the same as the remote user name, that is, if you do not have this user locally, you must first create a user, such as:

Grant connect,resource to schemas identified by password . Emp is the table to be imported. If the table to be imported already exists in the local schemas scheme, you can specify the tables_exists_action parameter. The format is tables_exists_action= {skip | append | truncate | replace}. These four parameter values mean to ignore the table, append the record to the table, delete the table data before appending the record, and replace the existing table with a new table.

This is the end of the sample analysis on impdp/expdp. I hope the above content can be of some help to you and learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.