oracle 操作

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'