Copying a user data in an Oracle database

Blog.sina.com.cnsblog_68c266e701016dy3.html copies all the tables of task users in the oracle database on one computer (also known as computer A) to the oracle database on another computer (also known as computer B. Note: In order not to modify other items such as programs,

The http://blog.sina.com.cn/s/blog_68c266e701016dy3.html copies all the tables of the task users in the oracle database on one computer (also known as computer A) to the oracle database on another computer (also known as computer B. Note: In order not to modify other items such as programs,

Http://blog.sina.com.cn/s/blog_68c266e701016dy3.html

Copy all the tables of the task users in the oracle database on one computer (also known as computer A) to the oracle database of another computer (also known as computer B. Note: In order not to modify other items such as programs, the tablespaces and users created in oracle of computer B should be the same as those in computer A (in oracle Database, the user name is task and the password is task, tablespace is users ).

1. Preparations before Replication

1.1 first Log On As sys or system

After oracle10g is installed, Go to Start-all programs-find the relevant Startup Program of oracle10g. Double-click SQL Plus to log on, or use PLSQL Developer to log on. Here we use the graphic interface PLSQL Developer, which is easier to operate.

1.2 create A tablespace (because the oracle database on computer A uses the users tablespace, you do not need to create A tablespace here. If oracle in computer A does not use the tablespace that comes with the system, you need to create it .)

Before creating A tablespace, query the size of the kby tablespace in computer.

Selecttablespace_name, sum (bytes)/1024/1024 from dba_data_files wheretablespace_name = 'kby'Group bytablespace_name;

The query result is 1-5.

-5 results of table space size Query

It can be seen that all the table spaces of KBY are 100 M, but there are still 99 M idle. Create a tablespace in the oracle database of computer B. At the same time, find the KBY Tablespace under the Tablespace folder in the Objects on the left.

Createtablespace kby datafile 'd: \ task \ kby. dbf' size 10 M autoextend onnext 10 m maxsize 100 m extent management local;

1.3 create a new user task and establish a relationship with the tablespace

CreateuserTask identified by cloud default tablespaceusers;

After the creation is successful, the User created is displayed, and the TASK User is found in the User Users folder in the Objects on the left.

1.4 grant user authorization

Grantconnect, resource, dba to task;

If you enter the preceding command in the Command window, the Grantsucceeded prompt is displayed.

2. Export all tables of the task user in the database.

Method 1: Open the DOS command window and enter the following command. The running results are 1-9 and 1-10.

ExpTask/task @ orcl file = c: \ task. dmp full = y

-9

-10

The task. dmp file is displayed in the c: \ task folder.

Method 2: export using PLSQL Developer

Note:

1. expuserid = password of the system user/system user @ server name. The owner is used to specify the scheme for executing the export operation.

2. query the database service name:

Method 1: select valuefrom v $ parameter where name = 'service _ name ';
Method 2: show parameter service_name;

Method 3: select name fromv $ database;

Method 3: query in the parameter file. Check the connection to tnsnames. ora. There is a SID, and SID is the service name.

Here, we use the second Method and system to log on, as shown in figure 1-12.

Figure 1-12 server name query

3. Import files exported from computer.

Method 1: Enter the doscommand window and enter the command. The running result is 1-14.

Imptask/task @ orcl file = c: \ task. dmp full = y

-14

After the import is complete, log on to the user task. In Objects, you can see that there are many tables in the tables folder of the task user, which is the same as that of the Computer Aoracle database task user. The import is successful.

Method 2: Like Export, you can use PLSQLDeveloper to import data. log on to the task user, and click tools> importtables> Oracle Export to select options to import the task. dmp file.

Note: If you have any questions, you can ask questions!