Connect to an Oracle database using PL/SQL and export the data for backup and import recovery

Connect to an Oracle database using PL/SQL and export the data for backup and import recovery

This operation Baidu a search a large, today to organize the project of the past to backup a bit of the database, try to export the data for backup and import recovery; The following is the operation process;

1 Open Service

2 Configuring Monitoring

Locate the following file:

Notepad opens

In the Navigator drop-down menu, select: Add the database to the tree,

Then click OK

Then you can log in using PL/SQL;

3 Creating new tablespaces and new users

Log in using the system user, execute the following SQL statement to establish the tablespace and the new user, and authorize the user

CREATE TABLE Space

Create tablespace myyyjc datafile ' D:\app\Administrator\oradata\sunlichengdata\myyyjc.dbf ' size tenm autoextend on ;

Delete table space

--drop tablespace MYYYJC including contents and datafiles cascade constraints;

Create a new user

Create user SLC identified by SLC default tablespace MYYYJC;

Authorized

Grant connect,resource to SLC;

Grant dba to SLC;

Revoke authorization

--revoke dba from SLC;

4 exporting the original database structure and data

Then, the old user YYJC login system to export the database structure and data;

Tool à Export user object (U)

This step exports the SQL file---Build Table statement (including the storage structure).

Tool à export table;

This step is to export the DMP file---table structure and data;

Here, the PL/SQL tool contains three ways to export the Oracle table structure and data, three ways: Oracle export, SQL inserts, PL Developer where Oracle Export exported. DMP is a binary file that can be cross-platform and includes permissions that are efficient and widely used.

5 Importing the original database structure and data into a new table space

In order to verify that the exported two files can be used, login with the new user, import the data into the new table space to try;

Tool à import table;

Tool à import table;

I did not respond when I imported the table structure, possibly because . SQL The file contains a large segment of the ( Blob,clob,long ), cannot be exported. But after I continue importing the . DMP file, both the table and the data are in the lead;

Check it out: http://www.cnblogs.com/wuhuacong/archive/2012/03/09/2387680.html

The above noteworthy is that we export Oracle objects and data, the default is still the original Oracle database table space and database user name, if we want to specify a different tablespace and database user object in the new database server, then we need to replace the generated SQL script, and in the first step to specify a reasonable tablespace and database users; perhaps it may be the reason for the different table space, but the data is in the import, indicating that the exported backup data is available;

Connect to an Oracle database using PL/SQL and export the data for backup and import recovery