在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';