Oracle Database Migration

Export and Export user objects:

1. Plsql log in to the database

2. Query the default tablespace for the current user (take DEMO1 account as an example)

With DEMO1 user login plsql, in the window of the users branch to find the current user name DEMO1, and then click on the view, on the right of the default tablespace can see the user's Defaults table space (TBS_DEMO1).

3. Export all user objects of the DEMO1 User:

Under DEMO1 users, click Tools,export User Objects, the window that pops up on the right is set up as above, focusing on single file, and then selecting the path in the output file. Then click Export to import the user object file (the file is in SQL format).

Note: This export may not be successful or complete a class of hints, just need to see the next bar of the progress bar has been completed. Take a look at the resulting log file and finally the spoo off is done.

To export table data:

Select all Table objects in the right window, then click Output file below to choose the export path, then click Export to generate data files (file format is DMP format)

Second, import

Log in to SQL with the SYS account on the new database server

1. Create a new user tablespace (the name of the table space should be the same as the export, take tbs_demo1 as an example)

Create tablespace tms_demo1 datafile ' D:\ORACLE11GR2\ORADATA\PCILAB\DATA. DBF ' size 10g

Autoextend on next 1g MaxSize Unlimited

EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

The tablespace is unique to Oracle and can be imagined as a box that holds the database, specifying the size (in this case, 10g), and the path xx for the entity file. DBF customization (this example is D:\ORACLE11GR2\ORADATA\PCILAB\DATA. DBF), all data tables will be placed in the table space. (If you want to know where other tablespace paths are in the current instance, you can query with select * from Dba_data_files)

2. Create a new user and specify its default tablespace (user name needs to be the same as the user name that was previously exported, for example, DEMO1. ）

CREATE USER DEMO1 identified by DEMO1123

DEFAULT tablespace Tbs_demo1

Temporary tablespace TEMP;

New user DEMO1, password DEMO1123, default tablespace tbs_demo1, default temp table space temp.

3, grant the User DBA authority (time may not be able to import user objects and user data, so it is best to use the grant DBA to DEMO1; grant it dba authority, after the user object is finished, then use revoke DBA from DEMO1; Reclaim dba authority. ）

Basic rights granted to the user

Grant

Connect

ALTER SESSION,

CREATE any JOB,

CREATE any TABLE,

CREATE any VIEW,

CREATE CLUSTER,

CREATE DATABASE LINK,

CREATE Indextype,

CREATE JOB,

CREATE OPERATOR,

CREATE PROCEDURE,

CREATE SEQUENCE,

CREATE SESSION,

CREATE synonym,

CREATE TABLE,

CREATE TRIGGER,

CREATE TYPE,

CREATE VIEW,

DEBUG any PROCEDURE,

DEBUG CONNECT SESSION,

EXECUTE any PROCEDURE,

INSERT any TABLE,

MANAGE SCHEDULER,

SELECT any DICTIONARY,

SELECT any TABLE,

Under Any TABLE,

UNLIMITED Tablespace,

UPDATE any TABLE

to Pbs_demo1;

1. Import User objects

After logging into the DEMO1 account, execute the exported SQL script directly

2. Import Table Data

After logging into the DEMO1 account, select Import Tables under Tools and select the previously exported DMP file, then press Import.

Oracle Database Migration