数据库迁移脚本

在50.6上导dmp文件步骤

su - oracle

imp file=/tmp/sps_dev_20150701.dmp log=/tmp/imp.log grants=N indexes=Y rows=Y full=Y userid=SXZQ/SXZQ@ORCL

查看对象select count(*) from user_objects

查看表总数select count(*) from user_tables

查看表空间使用情况

SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,

(B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE"

FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C

WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME; 

exp spsv601/spsv106@spststdb_v6 file=D:\gxyjdb20130813.dmp owner=(spsv601)

--1. 当前表空间，文件最大限制为4G

create   tablespace SPS_DATA        

datafile   'D:\oracle\product\10.2.0\oradata\orcl\SPS_DATA.DBF'   size   3000M    

autoextend   on    

next   200M    

maxsize   12000M;

--2.归档表空间

create   tablespace SPS_DATAARC        

datafile   'D:\oracle\product\10.2.0\oradata\orcl\SPS_DATAARC.DBF'   size   1000M    

autoextend   on    

next   200M    

maxsize   12000M;

--3.历史表空间

create   tablespace SPS_DATAHIS        

datafile   'D:\oracle\product\10.2.0\oradata\orcl\SPS_DATAHIS.DBF'   size   1000M    

autoextend   on    

next   200M    

maxsize   12000M;

--4.索引表空间

create   tablespace SPS_INDX        

datafile   'D:\oracle\product\10.2.0\oradata\orcl\SPS_INDX.DBF'   size   1000M    

autoextend   on    

next   200M    

maxsize   12000M;

--5.索引归档表空间

create   tablespace SPS_INDXARC        

datafile   'D:\oracle\product\10.2.0\oradata\orcl\SPS_INDXARC.DBF'   size   1000M    

autoextend   on    

next   200M    

maxsize   12000M;

--6.索引历史表空间

create   tablespace SPS_INDXHIS        

datafile   'D:\oracle\product\10.2.0\oradata\orcl\SPS_INDXHIS.DBF'   size   1000M    

autoextend   on    

next   200M    

maxsize   12000M;

--7.临时表空间

--8.索引历史表空间

create   tablespace DATA        

datafile   'D:\oracle\product\10.2.0\oradata\orcl\DATA.DBF'   size   1000M    

autoextend   on    

next   200M    

maxsize   12000M;

create   user   spsv60    

identified   by   spsv06    

default   tablespace   SPS_DATA    

temporary   tablespace   temp;

grant create session to spsv60    

grant dba to spsv60

create   user   spsv60his

identified   by   spsv60his

default   tablespace   SPS_DATAHIS

temporary   tablespace   temp;

grant create session to spsv60his;

grant dba to spsv60his;

导数据

imp file=F:\BaiduYunDownload\sxdxcheshi20141018\sxdxcheshi20141018.dmp log=F:\sxtcdb20141010\imp.log grants=N indexes=Y rows=Y full=Y userid=spsv60/spsv06@orcl

imp file=F:\sxtcdb20141010\sxtc.dmp log=F:\sxtcdb20141010\imp.log userid=spsv60/spsv06@orcl tables=(inter_service_order)

imp file=.\spsv60his.dmp log=./imp.log grants=N indexes=Y rows=Y full=Y userid=spsv60his/spsv60his@orcl

imp userid=spsv/spsv@10.0.50.6 F:\Workspaces\dmp\all_his_tables.dmp log=F:\imp.log full=y

ALTER TABLESPACE SPS_INDXHIS ADD DATAFILE

'D:\oracle\product\10.2.0\oradata\orcl\SX_SPS_INDXHIS1.DBF'

SIZE 2G;

给表空间增加数据文件

ALTER TABLESPACE SPS_DATA   ADD DATAFILE

'D:\oracle\product\10.2.0\oradata\orcl\SPS_DATA01.DBF'

SIZE 2G;

增加表空间大小

ALTER TABLESPACE SPS_DATA ADD DATAFILE

'D:\oracle\product\10.2.0\oradata\orcl\SX_SPS_DATA2.DBF'

size   1000M    

autoextend   on    

next   200M    

maxsize   12000M;

查看表空间目录

select * from dba_data_files where tablespace_name='SPS_DATA';