18、根据sid查是哪台电脑的链接
select osuser,machine,username,sid,serial# from v$session where sid='128';
19、根据sid查对应的sql
select SID,SQL_TEXT from v$open_cursor where SID='128';
20、查看等待（wait）情况
SELECT v$waitstat.class, v$waitstat.count count, SUM(v$sysstat.value) sum_value
FROM v$waitstat, v$sysstat WHERE v$sysstat.name IN ('db block gets',
'consistent gets') group by v$waitstat.class, v$waitstat.count
21、查看sga情况
SELECT NAME, BYTES FROM SYS.V_$SGASTAT ORDER BY NAME ASC
22、查看catched object
SELECT owner, name, db_link, namespace, type, sharable_mem, loads, executions, locks, pins, kept FROM v$db_object_cache
23、查看V$SQLAREA
SELECT SQL_TEXT, SHARABLE_MEM, PERSISTENT_MEM, RUNTIME_MEM, SORTS,
VERSION_COUNT, LOADED_VERSIONS, OPEN_VERSIONS, USERS_OPENING, EXECUTIONS,
USERS_EXECUTING, LOADS, FIRST_LOAD_TIME, INVALIDATIONS, PARSE_CALLS, DISK_READS,
BUFFER_GETS, ROWS_PROCESSED FROM V$SQLAREA
24、查看object分类数量
select OBJECT_TYPE,COUNT(*) from all_objects GROUP BY OBJECT_TYPE;
25、有关connection的相关信息
1）查看有哪些用户连接
select s.osuser os_user_name, decode(sign(48 - command), 1, to_char(command),
'Action Code #' || to_char(command) ) action, p.program oracle_process,
status session_status, s.terminal terminal, s.program program,
s.username user_name, s.fixed_table_sequence activity_meter, ''query,
0 memory, 0 max_memory, 0 cpu_usage, s.sid, s.serial# serial_num
from v$session s, v$process p where s.paddr=p.addr and s.type ='USER'
order by s.username, s.osuser
2）根据v.sid查看对应连接的资源占用等情况
select n.name,v.value,n.class,n.statistic#
from v$statname n,v$sesstat v
where v.sid = 71 and v.statistic# = n.statistic#
order by n.class, n.statistic#
3）根据sid查看对应连接正在运行的sql
select command_type,sql_text,sharable_mem,persistent_mem,runtime_mem,sorts,version_count,loaded_versions,open_versions,users_opening,
executions,users_executing,loads,first_load_time,invalidations,parse_calls,disk_reads,buffer_gets,rows_processed,sysdate start_time,sysdate finish_time,
address sql_address,'N' status
from v$sqlarea
where address = (select sql_address from v$session where sid = 71)
26、查询表空间的碎片程度
select a.tablespace_name,
trunc(sqrt(max(blocks)/sum(blocks))* (100/sqrt(sqrt(count(blocks)))),2) fsfi
from dba_free_space a,dba_tablespaces b
where a.tablespace_name=b.tablespace_name
and b.contents not in('TEMPORARY','UNDO','SYSAUX')
group by A.tablespace_name
order by fsfi;
27、查询有哪些数据库实例在运行
select inst_name from v$active_instances;
28、获取oracle前10条最耗资源的sql语句
SELECT * FROM
(SELECT PARSING_USER_ID
EXECUTIONS,
SORTS,
COMMAND_TYPE,
DISK_READS,
sql_text
FROM v$sqlarea
ORDER BY disk_reads DESC )
WHERE ROWNUM<11;
SDE常用SQL （通过CMD直接运行）
1、查看sde连接状态，快速查看有几个连接(不用进入sql*plus）
sdemon -o status
2、删除当前连接的某个用户，需要提供S-ID，及sde的密码
sdemon -o kill -t 1731 -p sde
3、断开所有用户的连接
sdemon -o kill -t all -s 127.0.0.1 -i 5151 -p sde
4、差看sde的实例个数和名称
sdemon -o info -I instances
5、查看sde实例占用的表，表后面的id为，sde库中table_registry
sdemon -o info -I locks
6、查询操作系统类型、操作系统环境变量、系统盘符、sde安装路径等信息
sdemon -o info -I vars
7、对SDE服务开启、停止、暂停
sdemon -o start -p sde
sdemon -o shutdown -p sde
sdemon -o pause -p sde