Share an Oracle data import and export experience

Recently, there is a task to modify an older project, the branch does not have this project database-related backup, so you need to export the database from the formal environment backup out of the local deployment installation, before the other projects have also been the deployment and installation of this database, also wrote an operational documentation, But the writing is not so detailed, many scenes are not considered, after thinking about it or to re-write a document to facilitate the next database-related export operations, but also to help other first contact colleagues to quickly operate.

The development of project adoption is related to: Vs2008,oracle 11g,plsql.

The first step, the export of the database

A. Database user export

1. Querying all database Users

 SELECTWm_concat (t.username)  fromdba_users T WHERET.account_status= 'OPEN'  andT.default_tablespace not inch('SYSTEM','Sysaux')  andT.username not inch('SCOTT','EMULATION')

2. Export CREATE table space and data file script

 SELECT 'CREATE tablespace' ||T.tablespace_name||        'datafile"'E:\ORADATA\FSDB\' ||T.tablespace_name|| '01.DBF"'size 100M autoextend on;'    fromdba_tablespaces T  WHERET.tablespace_name not inch('SYSTEM','Sysaux','UNDOTBS1','TEMP','USERS','UNDOTBS2');

E:\ORADATA\FSDB\ Local database File storage directory

A script similar to the following is queried

CREATE ' E:\ORADATA\FSDB\TBS_TYUM_SMS01. DBF'on;

Tbs_tyum_sms Table Space Name

E:\ORADATA\FSDB\TBS_TYUM_SMS01. DBF-Created spatial table DBF file

3. Create an export file related actions

The file directory where the export file is created (this directory must exist) does not create a real directory in the operating system and is best created by administrators such as system.

Create or Replace  as ' E:\backup\xxxx ';

Second, view the Management Manager directory (and see if the operating system exists, because Oracle does not care if the directory exists, and if it does not exist, an error occurs)

Select *  from Dba_directories;

Third, give the Scott user permissions to operate in the specified directory, preferably by a system administrator.

Grant Read  on directory dpdataggdbto Scott;

4. Export by user

-Export by user

EXPDP System/[email protected] schemas=xxxx Dumpfile=xxdb20170307bak. DMP Directory=dpdataggdb Logfile=xxdb20170307bak.log

-exp All Export

Exp System/[email protected] file=e:\backup\xxxx\qzdb20170220_bak.dmp full=y

system/123456 Login DBA account password

Xxdb the name of the database that needs to be exported

Schemas User Collection

Dumpfile=xxdb20170307bak. DMP Exported Data DMP file

Directory=dpdataggdb the file directory created in the third section the exported file will generate the corresponding file under the directory.

Logfile=xxdb20170307bak.log Exported data log file

Second step, database import

1.01 opening the Oracle Databese Configuration Tool

1.02 Enter the Welcome page

1.03 choose to create a database, if you need to modify you can choose the metabase option

1.04 database Templates

1.05 fill in the database connection representation

1.06 management options to go directly to the next step

1.07 fill in the database login information, in order to manage easy to select the account you want to use the same management password

1.08 set the data file storage location, you can choose the default location, you can also set the storage location

1.09 set the recovery configuration to select the default data

1.10 database content, can skip

1.11 Initialization parameters, the main allocation of memory space, need to consider the current server above the available space, multiple databases need to average processing, after the adjustment of uppercase, character sets, link mode can choose the default options

1.12 Database Storage

1.13 Creating options

1.14 Click Finish to generate the database.

2. Open plsq or Toad log in with Sys DBA to create the first step the second operation exports

CREATE ' E:\ORADATA\FSDB\TBS_TYUM_SMS01. DBF'on;

..... You should export the creation statements for multiple spaces here

3. Create a file directory for importing exports

Create or Replace  as '  '

E:\app must be a directory that already exists.

To view a directory that already exists

Select *  from Dba_directories

Delete a file directory record

DROP DIRECTORY Ty_dump_dir

Use the SYS user to log in to the user you want to access to specify permissions to access the directory.

Grant Read  on  to Scott;

4. Import data

IMPDP System/[email protected] directory=ty_dump_dir Dumpfile=xxdb20170307bak. DMP Logfile=xxdb20170307bak. LOG full=y

IMPDP System/[email protected] schemas=xxxx Dumpfile=xxdb20170307bak. DMP Directory=dpdataggdb Logfile=xxdb20170307bak.log

Or

Imp system/[email protected] file=d:\bak.dmp full=y ignore=y

The relevant keywords here are not explained, you can refer to the Export file description

If the EXPDP is exported, you need to export exp with IMPDP, you need Imp or you will get an error when exporting.

All import and export operations here are window+r cmd under the cmd command operator

In fact, in the import and export process should also encounter some other problems, here because before the operation of the time there is no record, and if you encounter a problem, I will record here, but also hope that this article can bring help to the people in need. If there is anything wrong, I hope you can point it out. Thank you!

Links: Links

If you are interested, you can take a look.

Share an Oracle data import and export experience