oracle死锁、导数据、表空间等操作

--查询死锁进程
select object_name,machine,s.sid,s.serial#
from v$locked_object l,dba_objects o ,v$session s
where l.object_id　=　o.object_id and l.session_id=s.sid;

--杀进程
alter system kill session 'sid,serial#'

--列出所有数据文件
select name from V$datafile

--查数据库表空间大小
SELECT   tablespace_name, ROUND (SUM (BYTES) / 1024 / 1024, 2) "USED(M)",owner
FROM dba_segments
where owner in (SELECT USER FROM dual )
GROUP BY owner, tablespace_name ;

--查询数据表空间大小
select Segment_Name, Sum(bytes) / 1024 / 1024 as MB
from User_Extents
inner join user_tables on User_Extents.Segment_Name = user_tables.TABLE_NAME
group by Segment_Name order by MB desc
 
--改变表空间大小
alter tablespace JINGFEN add datafile
'/opt/ora10/product/oradata/路径/文件名.DBF' size 5000M  
autoextend on  
next 50M  
maxsize unlimited

--命令行导出数据表
exp 用户名/密码@orcl full=n TABLES=(表名1,表名2) file=D:/文件名.dmp 
expdp userid=用户名/密码@orcl EXCLUDE=TABLE:\"IN ('表名1', '表名2')\" dumpfile=%date:~0,4%%date:~5,2%%date:~8,2%文件名.dmp --以排除法导出数据表，expdp导出文件存放在服务器端,%date:~0,4%%date:~5,2%%date:~8,2%以日期作为文件名开头，用于定时备份。

--命令行导出数据库
exp 用户名/密码@orcl full=n direct=y recordlength=65535 buffer=40960000 file=D:/文件名.dmp log=imp.log
--加速选项 direct=y recordlength=65535 buffer=40960000
--解决1455错误 INDEXES=n STATISTICS=none 
imp 用户名/密码@orcl full=y buffer=40960000 log=imp.log file=D:/文件名.dmp