ORA-00054: 资源正忙 解决办法

---不关心索引部分内容可以直接跳到第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 = '');