按用户迁移数据库并变更用户名

1.     Install 10.2.0.1 db and install path p6810189

2.     Create db and configure listener

3.     Create user simplo、smt and tablespace

Create user smt identified by smt default tablespace SMT_TB;

Grant dba to smt_tb;

4.     Create user smphr and tables

Create user smphr identified by smphr;

Grant dba to smphr;

create table SMPHR.ORGNIZATION_OWNER(

  ORG_ID   INTEGER,

  USERNAME VARCHAR2(30),

  PASSWORD VARCHAR2(30),

  PRODLINE VARCHAR2(30));

Insert into SMPHR.ORGNIZATION_OWNER values(51,’SMT’,’smt’,’SMT’);

Commit;

5.     Move smp table from system to users

select 'alter table smp.'||table_name||' move to USERS' from dba_tables where owner='SMP' and tablespace_name='SYSTEM';

6.     Rebuild index

select 'alter index smp.'||index_name||' rebuild tablespace USERS online' from dba_indexes where owner='SMP' and tablespace_name='SYSTEM';

7.     Export schemas smp from 31(fab2)

Drop tablespace temp including contens and datafile;

Create directory dump_arch as ‘/ora102/archive’;

$ Expdp smp/ezsfcs directory=dumpdir dumpfile=dump_arch:smp1.dmp,dumpdir:smp2.dmp,dumpdir:smp3.dmp

Logfile=dumpdir:dump.log Schemas=smp filesize=85G parallel=4

8.     Copy dumpfile

ftp>

binary

prompt

9.     Noarchive log

Shutdown immediate;

Startup mount;

Alter database noarchivelog;

10.  Import schemas from  smp to smt

SQL>Create directory dumpdir as ‘/ora105/oradata/’;

SQL>Grant read,write on directory dumpdir to smt;

$impdp smt/smt directory=dumpdir dumpfile=smp1.dmp,smp2.dmp,smp3.dmp

Logfile=impdp_smt.log schemas=smp remap_schema=smp:smt

Remap_tablespace=USERS:SMT_TB parallel=4

11.  Check impdp log

Create error  job and compile invalid objects

12.  Archive log enable

Shutdown immediate;

Startup mount;

Alter database archivelog;

 

13.  FAB2 create temp tablespace

SQL>Create temporary tablespace temp tempfile ‘/ora102/oradata/STCSMES/temp01.dbf’ size 5120M  AUTOEXTEND  ON NEXT 100M MAXSIZE UNLIMITED;

SQL>Alter database default temporary tablespace temp;

SQL>Alter tablespace temp add datafile ‘/ora102/oradata/STCSMES/temp02.dbf’ size 5120 AUTOEXTEND  ON NEXT 100M MAXSIZE UNLIMITED;

SQL>Alter tablespace temp add datafile ‘/ora102/oradata/STCSMES/temp03.dbf’ size 5120AUTOEXTEND  ON NEXT 100M MAXSIZE UNLIMITED;

SQL>Alter tablespace temp add datafile ‘/ora102/oradata/STCSMES/temp04.dbf’ size 5120 AUTOEXTEND  ON NEXT 100M MAXSIZE UNLIMITED;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24849178/viewspace-708564/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24849178/viewspace-708564/