Shulou(Shulou.com)06/01 Report--

I have written about expdp and impdp before, and those who are interested can move to http://blog.itpub.net/30485601/viewspace-2151455/. Since expdp and impdp are available after oracle10g, if the database version is before 10g, then expdp and impdp cannot be used. But we can use exp and imp to achieve logical import and export functions, in fact, expdp and impdp itself is developed on the basis of exp and imp, different is that exp and imp is a client tool, you can operate on the client, and only need resource authority on it, so it is very convenient for daily data migration.

If you read expdp and impdp first, this article will feel much simpler and humble, because in terms of practicality, of course, the waves behind the Yangtze River push forward the waves. In fact, it depends on who it is compared with. Daniel is better than Daniel two years ago, but he is still a Daniel two years ago. Whether to use expdp or exp actually has something to do with the usage scenario. If you are a system administrator with DBA authority, then during the data migration, of course, I think I can assign myself a user who has access to the system directory and expdp, and you can enjoy the pleasure brought by expdp. If you are just an ordinary user and are assigned to you by others, for example, your database server is hosted, the hosting party has multiple libraries loaded on a library, and your library is only one of them managed by others, then the other party will not give you a high-authority user in general. All you have is connect and resource permissions. In this case, you can use exp. The key is that you do not have the permission to use expdp. Exp and imp are definitely more than sufficient for daily migration backups. After reading the text for a long time, I guess the spectators are relaxed. The old driver is about to start.

Preparation: if you need to do exp and imp on the client side, you need to download the full version of the client on the client side, and then configure the environment variable tnsnames.ora.

1. Exp

1. Export schemas

Exp qj/**@local file=C:\ Users\ QuJian\ Desktop\ qj.dmp log=C:\ Users\ QuJian\ Desktop\ qj.log owner=qj statistics=none

Explanation: both exp and imp are operating system commands that operate at the operating system level. Exp indicates that this is an exp export operation; qj/**, where qj is the user name and * * is the user password. For qj, if you only have resource permission, you can only export qj yourself. If you have exp_full_database, you will have the right to export the entire library. Of course, you can also export other schemas. @ local,local is tnsmames (network service name). You need to configure the environment variable TNS_NAME on the client side. You can find this tnsnames,tnsnames to record the address and service name of the database server through the path, and you can find the address of the database server through local. File indicates the location of the data export. The exported file uses the .dmp suffix, log indicates the export log, and the export log uses the .log suffix. Owner indicates the schema to be exported. If you have exp_full_database permission, you can also export other schema. Multiple schema are separated by commas (,), such as owner= (log). Because the client character set is inconsistent with the server character set, there will be an error that EXP-00091 is exporting problematic statistics, plus statistics=none can resolve this error. The normal export process and the export process without statistics=none are posted below:

As shown in the figure:

The process of normal export

Exp-00104 errors can only be resolved through expdp at present

The export without statistics=none is shown in the figure:

As you can see, there are a large number of exp-00091 errors.

2. Export some tables. I will take the export table as an example to list some commonly used parameters.

Tables

Exp qj/**@local file=C:\ Users\ QuJian\ Desktop\ t.dmp log=C:\ Users\ QuJian\ Desktop\ t.log tables= (T1 Magneto T2) statistics=none

Explanation: the tables parameter indicates which tables to export, separated by commas (,) between multiple tables

The result is shown in the figure:

Query

Exp qj/**@local file=C:\ Users\ QuJian\ Desktop\ t.dmp log=C:\ Users\ QuJian\ Desktop\ t.log tables= (T1 Magneto T2) query=\ "where object_id

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.