Linux与oracle常用命令

1、数据备份DMP文件语句
exp ucr_o2o_audit/o2o#puad@o2odb  file=E:\upload\order_detail_20171204.dmp tables=(t_dc_order_center_detail,t_dc_cb_bs_trade_detail,ts_r_ecs_order,ts_r_trade)
2、查看表空间语句
SELECT a.tablespace_name "表空间名", total "表空间大小", free "表空间剩余大小", (total - free) "表空间使用大小",
total / (1024 * 1024 * 1024) "表空间大小(G)", free / (1024 * 1024 * 1024) "表空间剩余大小(G)",
(total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)", round((total - free) / total, 4) * 100 "使用率 %"
FROM (SELECT tablespace_name, SUM(bytes) free FROM dba_free_space GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) total FROM dba_data_files GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name ;
3、查看进程数量
ps -ef | grep 'TI_DC_CBSS_TRADE_SUB_ITEM.ctl'
//杀掉进程
kill -9
4、oracle数据库死锁问题查看
//查看死锁语句
SELECT s.username,l.OBJECT_ID,l.SESSION_ID,s.SERIAL#,
l.ORACLE_USERNAME,l.OS_USER_NAME,l.PROCESS
FROM V$LOCKED_OBJECT l,V$SESSION S WHERE l.SESSION_ID=S.SID;

//查看造成死锁的sql语句
select sql_text from v$sql where hash_value in
(select sql_hash_value from v$session where sid in
(select session_id from v$locked_object))

//赋予删除死锁权限
grant alter system to UCR_O2O_AUDIT;

//删除死锁语句
alter system kill session '738,1429';

5、查看存储过程运行状态
//查看运行中的存储过程
select name,locks,pins
from v$db_object_cache
where locks > 0 and pins > 0 and type='PROCEDURE';

//查看正在执行的sql语句
select a.program, b.spid, c.sql_text,c.SQL_ID

from v$session a, v$process b, v$sqlarea c

where a.paddr = b.addr

and a.sql_hash_value = c.hash_value

and a.username is not null;

6、查看压缩文件和txt文件数据量
//查询压缩文件内数据
gzip -dc CANPRDE02018A1707281001001.017.gz | grep '1717072867765807' | more
//查询压缩文件内记录数量
gzip -dc CANPRDE02018A1707281001001.017.gz|wc -l
//查看txt文件数量
wc -l order_info_20171115.txt
###查询最后1000行
cat mylog.log | tail -n 1000

7、jar包自动运行语句
nohup java -jar /home/o2o_kafka/kafka_access/jarfile/consumerOrderDetail.jar >kafka_java.log 2>& 1 &








阅读更多

没有更多推荐了,返回首页