sqlplus /nolog
conn /as sysdba;
--创建表空间
CREATE TABLESPACE epmsdbs_ind
LOGGING
DATAFILE 'D:\oracle\oradata\orcl\epmsdbs_ind01.DBF'
SIZE 32M
AUTOEXTEND ON
NEXT 32M MAXSIZE 2048M
EXTENT MANAGEMENT LOCAL;
--创建用户
drop user ecms824 cascade;
create user ecms824 identified by ecms824
DEFAULT TABLESPACE epmsdbs
TEMPORARY TABLESPACE TEST_TEMP;
grant dba to ecms824;
exit
exit
imp ecms824/ecms824@zhouyi file=E:\DBdata\ecms825\ecms_v1_0824.dmp fromuser=ecms_v1 touser=ecms824 log=d:\epms_sxxa.log tables=(s_privilege_user)
imp epms_sxxa/epms_sxxa@bocoepms file=d:\gzepms20110607.dmp full=y log=d:\epms_sxxa.log ignore=true
--------------------------------------------
fromuser=? 指dmp文件中的用户名
@(bocoepms)指映射到本机配置的服务名, 比如在203服务名是BOCOEPMS,但是映射到本机是GD_EPMS 导到本机可以不用写
例如我本机导库到203 imp ecms_group_1025/ecms_group_1025@bocoepms file=e:\report1029.dmp fromuser=epms_sxxa touser=ecms_group_1025 tables=(b_report_item,b_report_table)
//杀oracle阻塞
SELECT /*+ rule */ s.username,
decode(l.type,'TM','TABLE LOCK',
'TX','ROW LOCK',
NULL) LOCK_LEVEL,
o.owner,o.object_name,o.object_type,
s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser
FROM v$session s,v$lock l,dba_objects o
WHERE l.sid = s.sid
AND l.id1 = o.object_id(+)
AND s.username is NOT Null
--username必须大写
select * from v$session where username='epms_sxxa_0520';
alter system kill session 'sid,serial#';
导出数据库
exp epms_sxxa/epms_sxxa@bocoepms file=d:\epms_sxxa0617.dmp
启动oracle:
telnet 210.41.121.203
oracle
oracle
export ORACLE_SID=BOCOEPMS
sqlplus /nolog
conn /as sysdba;
startup
exit
lsnrctl
start
查找所有用户:
select * from dba_users order by created desc
扩展表空间:
alter database datafile '/opt/oracle/oradata/EPMSDEV/epmsdev.dbf' resize 4500M;
查看用户表空间使用情况
select a.tablespace_name,a.bytes/1024/1024 "Sum MB",(a.bytes-b.bytes)/1024/1024 "used MB",b.bytes/1024/1024 "free MB",round(((a.bytes-b.bytes)/a.bytes)*100,2) "percent_used"
from
(select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) a,
(select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_name) b
where a.tablespace_name=b.tablespace_name
order by ((a.bytes-b.bytes)/a.bytes) desc