1、查看锁定对像

?? SELECT A.OWNER 方案名,?
?????? A.OBJECT_NAME 表名,?
?????? B.XIDUSN 回滚段号,?
?????? B.XIDSLOT 槽号,?
?????? B.XIDSQN 序列号,?
?????? B.SESSION_ID 锁表SESSION_ID,?
?????? B.ORACLE_USERNAME 锁表用户名,?
?????? decode(D.type,?
????????????? 'XR',?
????????????? 'NULL',?
????????????? 'RS',?
????????????? 'SS(Row-S)',?
????????????? 'CF',?
????????????? 'SS(Row-S)',?
????????????? 'TM',?
????????????? 'TABLE LOCK',?
????????????? 'PW',?
????????????? 'TABLE LOCK',?
????????????? 'TO',?
????????????? 'TABLE LOCK',?
????????????? 'TS',?
????????????? 'TABLE LOCK',?
????????????? 'RT',?
????????????? 'ROW LOCK',?
????????????? 'TX',?
????????????? 'ROW LOCK',?
????????????? 'MR',?
????????????? 'S(Share)',?
????????????? NULL) 锁定方式,?
???????
?????? C.MACHINE 用户组,?
?????? C.TERMINAL 机器名,?
?????? B.OS_USER_NAME 系统用户名,?
?????? B.PROCESS 系统进程id,?
?????? DECODE(C.STATUS, 'INACTIVE', '不活动', 'ACTIVE', '活动') 活动情况,?
?????? C.SERVER,?
?????? C.SID,?
?????? C.SERIAL#,?
?????? C.PROGRAM 连接方式,?
?????? C.LOGON_TIME?
? FROM ALL_OBJECTS A, V$LOCKED_OBJECT B, SYS.GV_$SESSION C, v$lock d?
?WHERE (A.OBJECT_ID = B.OBJECT_ID)?
?? AND (B.PROCESS = C.PROCESS)?
?? and C.sid = d.sid?
?? and B.LOCKED_MODE = D.LMODE?
?ORDER BY 1, 2;

2、杀死会话

ALTER SYSTEM KILL SESSION 'sid,serial#';

3、查看表结构

desc tablename

4、查看对象的定义

SELECT dbms_metadata.get_ddl(‘TABLE’,’EMPLOYEE’);

select dbms_metadata.get_ddl('VIEW','C_VIEW') from dual;

select dbms_metadata.get_ddl('DB_LINK','L_TEST',user) from dual;

5、查看表的大小

select segment_name,bytes/1024/1024 as "MB" from dba_segments where segment_name=:tablename;

6、查看数据文件

select tablespace_name,file_name,bytes/1024/1024 as "size(MB)" from dba_data_files;

7、查看索引使用

alter index INDEX_NAME monitoring usage;
开始监视索引的使用情况
?
select * from v$object_usage;
检查索引有没有使用过
?
alter index INDEX_NAME nomonitoring usage;
停止监视索引的使用情况

8、查看表的执行计划

?? explain plan工作实质、前提及操作方法
???? 1. 工作实质
????? 将SQL语句预估的执行计划加载到表plan_table,是对表plan_table 执行了DML操作，故不会执行隐式提交
????? 可以对select,insert,update,merge,delete,create table, create index,alter index等加载执行计划到plan_table
???? 2. 前提条件
????? 需要先创建plan_table，创建方法：@?/rdbms/admin/utlxplan
????? 对当前的SQL语句有执行权限以及对依赖的对象有相应操作的权限
???? 3. 使用方法：
?????? explain plan for select * from scott.emp where ename='SCOTT';??? --未设置标记位
?????? explain plan set statement_id='TEST' for select * from scott.emp where ename='SCOTT'
???? 4.也可直接根据 statement_id查找plan_table?? select * from plan_table where statement_id=:id? order by plan_id;

9、查看会话状态

SELECT A.SID "会话SID",
?????? A.SERIAL# "会话序列号",
?????? OPNAME "操作类型",
?????? TARGET_DESC "操作对象"
? FROM v$session_longops a, v$session b
?WHERE a.sid = b.sid AND a.serial# = b.serial#

?