查看oracle实际信息,[数据库]oracle查看对象信息

[数据库]oracle查看对象信息

0 2015-12-14 16:00:03

1.查看某用户下所有对象的信息：

SELECT owner, object_type, status, COUNT(*) count# FROM all_objects where owner='xxx'GROUP BY owner, object_type, statusorder 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_GBFROM 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_MGFROM 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 bwhere 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

转载请保留本文网址：http://www.shaoqun.com/a/167667.html

*特别声明：以上内容来自于网络收集，著作权属原作者所有，如有侵权，请联系我们：admin@shaoqun.com。

oracle

0