一些常用的SQL 语句 之5
来源:互联网 发布:查看手机4g网络dns 编辑:程序博客网 时间:2024/06/16 02:45
19、查看catched object
SELECT owner, name, db_link, namespace,
type, sharable_mem, loads, executions,
locks, pins, kept FROM v$db_object_cache
20、查看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
21、查看object分类数量
select decode (o.type#,1,'INDEX' , 2,'TABLE' , 3 , 'CLUSTER' , 4, 'VIEW' , 5
,
'SYNONYM' , 6 , 'SEQUENCE' , 'OTHER' ) object_type , count(*) quantity from
sys.obj$ o where o.type# > 1 group by decode (o.type#,1,'INDEX' , 2,'TABLE' ,
3
, 'CLUSTER' , 4, 'VIEW' , 5 , 'SYNONYM' , 6 , 'SEQUENCE' , 'OTHER' ) union
select
'COLUMN' , count(*) from sys.col$ union select 'DB LINK' , count(*) from
22、按用户查看object种类
select u.name schema, sum(decode(o.type#, 1, 1, NULL)) indexes,
sum(decode(o.type#, 2, 1, NULL)) tables, sum(decode(o.type#, 3, 1, NULL))
clusters, sum(decode(o.type#, 4, 1, NULL)) views, sum(decode(o.type#, 5, 1,
NULL)) synonyms, sum(decode(o.type#, 6, 1, NULL)) sequences,
sum(decode(o.type#, 1, NULL, 2, NULL, 3, NULL, 4, NULL, 5, NULL, 6, NULL, 1))
others from sys.obj$ o, sys.user$ u where o.type# >= 1 and u.user# =
o.owner# and u.name <> 'PUBLIC' group by u.name order by
sys.link$ union select 'CONSTRAINT' , count(*) from sys.con$
23、有关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 /*+ PUSH_SUBQ */
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)
24、查询表空间使用情况select a.tablespace_name "表空间名称",
100-round((nvl(b.bytes_free,0)/a.bytes_alloc)*100,2) "占用率(%)",
round(a.bytes_alloc/1024/1024,2) "容量(M)",
round(nvl(b.bytes_free,0)/1024/1024,2) "空闲(M)",
round((a.bytes_alloc-nvl(b.bytes_free,0))/1024/1024,2) "使用(M)",
Largest "最大扩展段(M)",
to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') "采样时间"
from (select f.tablespace_name,
sum(f.bytes) bytes_alloc,
sum(decode(f.autoextensible,'YES',f.maxbytes,'NO',f.bytes)) maxbytes
from dba_data_files f
group by tablespace_name) a,
(select f.tablespace_name,
sum(f.bytes) bytes_free
from dba_free_space f
group by tablespace_name) b,
(select round(max(ff.length)*16/1024,2) Largest,
ts.name tablespace_name
from sys.fet$ ff, sys.file$ tf,sys.ts$ ts
where ts.ts#=ff.ts# and ff.file#=tf.relfile# and ts.ts#=tf.ts#
group by ts.name, tf.blocks) c
where a.tablespace_name = b.tablespace_name and a.tablespace_name =
c.tablespace_name
25、 查询表空间的碎片程度
select tablespace_name,count(tablespace_name) from dba_free_space group by
tablespace_name
having count(tablespace_name)>10;
alter tablespace name coalesce;
alter table name deallocate unused;
create or replace view ts_blocks_v as
select tablespace_name,block_id,bytes,blocks,'free space' segment_name from
dba_free_space
union all
select tablespace_name,block_id,bytes,blocks,segment_name from dba_extents;
select * from ts_blocks_v;
select tablespace_name,sum(bytes),max(bytes),count(block_id) from
dba_free_space
group by tablespace_name;
- 一些常用的SQL 语句 之5
- 一些常用的SQL 语句 之4
- 一些常用的SQL语句
- 一些常用的SQL语句
- 一些常用的SQL语句
- 一些常用的SQL语句
- 一些常用的SQL语句
- 常用的一些SQL语句
- 一些常用的sql语句
- 一些常用的SQL语句
- 一些常用的sql语句
- 一些常用的sql语句
- sql server的一些常用 sql 语句
- 搜集的Sql一些常用的语句
- 一些常用SQL语句的总结
- 一些常用SQL语句的总结 竖项变横项
- 常用的一些数据统计SQL语句
- sql 一些不常用的 语句
- Android - ImageView 加边框 实例
- 写占位符的时候不要加单引号和分号
- 快速排序及其优化
- 利用PHP实现对服务器性能与状态的监控
- 如何找回老版本的“体着色”
- 一些常用的SQL 语句 之5
- 澜沧江-湄公河
- 一步一步写算法(之单词统计)
- C#+.NET中调用VB编写的DLL代码事例
- ubuntu 11.10 正式版下载
- 二分法矩阵求斐波那契(fibonacci)数列第n项
- c++ LPCTSTR、TCHAR、BSTR
- hdu++1800++STL
- parsing xml with sax and pull