数据库常用SQL
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#
页:
[1]