---不关心索引部分内容可以直接跳到第2步
1、我需要在三张表上分别建立一个索引:
CREATE INDEX 索引名 ON 表名 (列名1,列名2) TABLESPACE 表空间名;
-----------------------------------------------------
CREATE INDEX SQEID_PHONENUM_1 ON COMPANY (ID,PHONE)
TABLESPACE TABLE_SPACE_TEST;
CREATE INDEX SQEID_PHONENUM_2 ON PERSON (ID,PHONE)
TABLESPACE TABLE_SPACE_TEST;
CREATE INDEX SQEID_PHONENUM_3 ON PROJECT (ID,PHONE)
TABLESPACE TABLE_SPACE_TEST;
//查询当前用户下所有的索引信息
SELECT * FROM USER_INDEXES;
//查询索引 "SQEID_PHONENUM_1" 是否成功创建
SELECT * FROM USER_IND_COLUMNS WHERE INDEX_NAME='SQEID_PHONENUM_1';
2、如果执行报错:ORA-00054: 资源正忙
//执行如下语句判断出哪些表被锁住了
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;
//通过如下语句可以杀死锁住了表的会话
ALTER SYSTEM KILL SESSION 'SID,SERIAL';
--------------------------------------------------------------------
附:如下可能有用的语句:
//查询当前数据库下哪些会话锁住了
select session_id from v$locked_object;
//通过sid(session_id)查询该会话具体信息
SELECT sid, serial#, username, osuser FROM v$session where sid = ;
性能查询sql
//查正在运行的耗时sql
1.1 select ss.sid,se.command,ss.value CPU ,se.username,se.program
from v$sesstat ss, v$session se
where ss.statistic# in
(select statistic#
from v$statname
where name = 'CPU used by this session')
and se.sid=ss.sid
and ss.sid>6
and se.command <>0
and username = 'tysp'
order by CPU desc
1.2 select s.sid, w.event, w.wait_time, w.seq#, q.sql_text
from v$session_wait w, v$session s, v$process p, v$sqlarea q
where s.paddr=p.addr and
s.sid=xxxxx and s.sql_address=q.address;
//查正在运行的耗时sql
SELECT sid,serial#, osuser, a.username, cpu_time / executions / 1000000 || 's', b.SQL_FULLTEXT, machine
FROM v$session a,
v$sqlarea b
WHERE a.sql_address = b.address
ORDER BY cpu_time / executions DESC;
ALTER SYSTEM KILL SESSION 'sid,serial#';
// 慢查询sql
select *
from (select sa.SQL_TEXT,
sa.SQL_FULLTEXT,
sa.EXECUTIONS "执行次数",
round(sa.ELAPSED_TIME / 1000000, 2) "总执行时间",
round(sa.ELAPSED_TIME / 1000000 / sa.EXECUTIONS, 2) "平均执行时间",
sa.COMMAND_TYPE,
sa.PARSING_USER_ID "用户ID",
u.username "用户名",
sa.HASH_VALUE
from v$sqlarea sa
left join all_users u
on sa.PARSING_USER_ID = u.user_id
where sa.EXECUTIONS > 0
order by (sa.ELAPSED_TIME / sa.EXECUTIONS) desc)
where rownum <= 50
查阻塞的会话:
Select osuser,machine,program,Module,sid,serial#,event,t2.logon_time
from v$locked_object t1,v$session t2
where t1.session_id=t2.sid order by t2.logon_time
//查询表上的索引
SELECT *
FROM USER_IND_COLUMNS
WHERE INDEX_NAME in (SELECT INDEX_NAME FROM USER_INDEXES where table_name = '');