ORACLE用户创建导库基本命令

 

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