oracle查看對象信息，oracle查看對象
1.查看某用戶下所有對象的信息：
![]()
SELECT owner, object_type, status, COUNT(*) count#
FROM all_objects
where owner='xxx'
GROUP BY owner, object_type, status
order by 2;
View Code
2.查看表行數（多個表）（需先分析表）：
![]()
1 select 'analyze table '|| table_name||' compute statistics;' from user_tables;
2
3 select table_name,num_rows from dba_tables where owner='WEBAGENT' order by 1;
4
5 select count(*) from WITHDRAWAL_ORG;
View Code
3.查看表空間大小：
![]()
SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024*1024)), 0) ts_size_GB
FROM dba_tablespaces t, dba_data_files d
WHERE t.tablespace_name = d.tablespace_name
GROUP BY t.tablespace_name;
View Code
4.查看表空間的使用情況：
![]()
SELECT tablespace_name ,SUM(bytes) / (1024 * 1024) AS free_space_MG
FROM dba_free_space
GROUP BY tablespace_name;
View Code
![]()
SELECT a.tablespace_name,
a.bytes total,
b.bytes used,
c.bytes free,
(b.bytes * 100) / a.bytes "% USED ",
(c.bytes * 100) / a.bytes "% FREE "
FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c
WHERE a.tablespace_name = b.tablespace_name
AND a.tablespace_name = c.tablespace_name;
View Code
5.查看表空間物理文件的名稱及大小：
![]()
SELECT tablespace_name,
file_id,
file_name,
round(bytes / (1024 * 1024*1024), 0) total_space
FROM dba_data_files
ORDER BY tablespace_name;
View Code
6.查正在執行的sql和執行過的sql:
a.查詢正在執行的sql
![]()
select a.username, a.sid,b.SQL_TEXT, b.SQL_FULLTEXT
from v$session a, v$sqlarea b
where a.sql_address = b.address;
View Code
b.查詢指定時間內執行過的sql
![]()
select b.SQL_TEXT,b.FIRST_LOAD_TIME,b.SQL_FULLTEXT
from v$sqlarea b
where b.FIRST_LOAD_TIME between '2009-10-15/09:24:47' and
'2009-10-15/09:24:47' order by b.FIRST_LOAD_TIME
View Code
7.回收站：
![]()
1 drop table t1 purge;
2 purge recyclebin;
View Code
8.監控oracle的等待事件：
![]()
select event,
sum(decode(wait_Time, 0, 0, 1)) "Prev",
sum(decode(wait_Time, 0, 1, 0)) "Curr",
count(*) "Tot"
from v$session_Wait
group by event
order by 4;
View Code
9.統計模式內的對象信息：
a.查看當前schema的對象信息
![]()
select object_type,count(*) from user_objects group by object_type;
View Code
b.sys用戶下查看指定schema的對象信息
![]()
select SEGMENT_TYPE，COUNT(*) from dba_segments where tablespace_name='TSP_WEBAGENT' GROUP BY SEGMENT_TYPE;
View Code
