oracle脚本重定向到文件
spool d:\user.txt
select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id;
spool off;
查找Oracle数据库全部表结构
SELECT t.table_name,
t.colUMN_NAME,
t.DATA_TYPE || '(' || t.DATA_LENGTH || ')',
t1.COMMENTS
FROM User_Tab_Cols t, User_Col_Comments t1
WHERE t.table_name = t1.table_name
AND t.column_name = t1.column_name;
exp his6/his6@127.0.0.1/orcl file=d:\doc\sqzj.dmp log=D:\Doc\sqjz.log
导出Oracle数据库导出导入所有的表结构和表数据命令
SELECT * FROM v$session;
--查看被锁的表
select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id;
--以下几个为相关表
SELECT * FROM v$lock;
SELECT * FROM v$sqlarea;
SELECT * FROM v$session; --查到机器名和时间,连接工具等很多参数
SELECT * FROM v$process ;
SELECT * FROM v$locked_object;
SELECT * FROM all_objects;
SELECT * FROM v$session_wait;
--查看被锁的表
select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id;
--查看那个用户那个进程照成死锁
select b.username,b.sid,b.serial#,logon_time from v$locked_object a,v$session b where a.session_id = b.sid order by b.logon_time;
--查看连接的进程
SELECT sid, serial#, username, osuser FROM v$session;
--3.查出锁定表的sid, serial#,os_user_name, machine_name, terminal,锁的type,mode --
SELECT s.sid, s.serial#, s.username, s.schemaname, s.osuser, s.process, s.machine,
s.terminal, s.logon_time, l.type
FROM v$session s, v$lock l
WHERE s.sid = l.sid
AND s.username IS NOT NULL
ORDER BY sid;
这个语句将查找到数据库中所有的DML语句产生的锁,还可以发现,
任何DML语句其实产生了两个锁,一个是表锁,一个是行锁。
--杀掉进程 sid,serial#
alter system kill session'210,11562';
1. 如下语句 查询锁定的表 --查到机器名和时间,连接工具等很多参数
SELECT L.SESSION_ID SID,
S.SERIAL#,
L.LOCKED_MODE,
L.OS_USER_NAME,
S.MACHINE,
S.TERMINAL,
O.OBJECT_NAME,
S.LOGON_TIME,
L.ORACLE_USERNAME
FROM V$LOCKED_OBJECT L, ALL_OBJECTS O, V$SESSION S
WHERE L.OBJECT_ID = O.OBJECT_ID
AND L.SESSION_ID = S.SID
ORDER BY SID, S.SERIAL#;
2.然后删除掉系统锁定的此记录,其中SID和serial#为上一条sql查询出的第一个和第二个字段
ALTER system KILL session 'SID,serial#';
如:
ALTER system KILL session '17,28317';
1:生成查询计划
explain plan for
select * from t_call_records where t_bjhm='123456';
2.查看执行计划结果
select * from table(dbms_xplan.display)
查表的索引
SELECT * FROM sys.all_indexes WHERE table_name='SY_SHUYEZXJL'