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

Background: as a DBA,oracle database migration is a common thing, just recently I am also migrating a business system, the database is oracle, by the way.

Description: for ease of illustration, the old database is called An and the new database is B. The user and password are IRP/IRP, and B is a brand new environment.

Steps and ideas:

1. Export the data file from A.

Sqlplus / nologconn / as sysdbaEXP IRP/IRP BUFFER=64000 FILE=D:\ test.DMP log=D:\ test.log OWNER=IRP

two。 View the user default tablespace on machine A so that the same tablespace can be created on import

SQL > select username,default_tablespace from dba_users where username = 'IRP'; USERNAME DEFAULT_TABLESPACE-IRP IRP

3. View the tablespace used by the user

SQL > select DISTINCT owner, tablespace_name from dba_extents where owner like 'IRP'; OWNER TABLESPACE_NAME-IRP IRP

4. View the data file corresponding to the tablespace to create a data file of the right size on B.

SQL > select file_name,tablespace_name from dba_data_files where tablespace_name in 'IRP' FILE_NAME TABLESPACE_NAME-D:\ APP\ ADMINISTRATOR\ ORADATA \ ORCL\ IRP.DBF IRP if the user uses multiple tablespaces The query statement is as follows: select file_name,tablespace_name from dba_data_files where tablespace_name in ('IRP1','IRP2')

5. Check the tablespace of machine B to see if IRP exists (if it is a new server, this step is not required)

Select name from v$tablespace where name in 'IRP' cannot be found, which means there are no two tablespaces and need to be created.

6. Create tablespaces, users, and authorization on B

Create a large file tablespace CREATE BIGFILE TABLESPACE "IRP" DATAFILE'd:\ app\ oracle\ oradata\ orcl\ irp.dbf' SIZE 100m AUTOEXTEND ON NEXT 100m MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; create a default tablespace or CREATE TABLESPACE "IRP" DATAFILE'd:\ app\ oracle\ oradata\ orcl\ irp.dbf' SIZE 100m AUTOEXTEND ON NEXT 100m MAXSIZE 10000m LOGGING EXTENT MANAGEMENT LOCAL Create user CREATE USER "IRP" PROFILE "DEFAULT" IDENTIFIED BY "IRP" DEFAULT TABLESPACE "IRP" TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK; authorization GRANT "CONNECT" TO "IRP"; GRANT "RESOURCE" TO "IRP"; GRANT unlimited tablespace TO "IRP"

Note: BigFile tablespaces can only create one datafile data file, while SmallFile tablespaces can create up to 1024 data files

7. If the user already exists on server B, how to delete the user.

Check to see if the user SQL > select username from dba_users where username='IRP'; deletes the user and all the objects owned by the user drop user IRP cascade; to see if there are objects under the user Select object_type Count (*) from all_objects where owner='IRP' group by object_type OBJECT_TYPE COUNT (*)-1 SEQUENCE 3 2 PROCEDURE 5 3 LOB 139 4 PACKAGE 3 5 PACKAGE BODY 26 TRIGGER 1 7 TABLE 384 8 INDEX 426 9 FUNCTION 6

At this time, if the user is connecting, drop will make an error. You must first kill the user's session, and then drop user.

Build a statement that kills IRP user session and execute it, SELECT 'alter system kill session''| | SID | |','| SERIAL# | |''immediate;' FROM V$SESSION WHERE USERNAME='IRP';. The above statement is to build a statement to kill IRP user session, then copy these statements, paste them into sqlplus and execute them to kill IRP session.' ALTERSYSTEMKILLSESSION''' | | SID | |','| | SERIAL# | |''IMMEDIATE;'-alter system kill session' 9meme 42043' immediate; alter system kill session '10Power9137' immediate; alter system kill session' 72meme 17487' immediate; alter system kill session '84pr 3280' immediate Alter system kill session '91976' immediate; alter system kill session '100 13516' immediate; alter system kill session '111 immediate; alter system kill session' 115 4751 'immediate; alter system kill session' 120 10356 'immediate; alter system kill session' 211 4075 'immediate; alter system kill session' 216 48068 'immediate

8. Finally, import the data on B

Be sure to exit sqlplus when executing imp and execute it directly on the command line.

IMP test/test BUFFER=64000 FILE=D:\ test.DMP log=D:\ imptest.log FROMUSER=IRP TOUSER=IRP

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.