--查询所有的oracle表、表空间、数据库连接等信息
--1.查询表大小
select table_name, num_rows, avg_row_len, num_rows * avg_row_len
-- , 'delete from ' || table_name || ';'
from user_tables t
order by num_rows * avg_row_len desc;
--2.查询表空间路径-------
select * from dba_data_files t
where t.tablespace_name = 'USERS';
--3.查询表空间容量-----------
select dbf.tablespace_name,
dbf.totalspace "总量(M)",
dbf.totalblocks as 总块数,
dfs.freespace "剩余总量(M)",
dfs.freeblocks "剩余块数",
(dfs.freespace / dbf.totalspace) * 100 "空闲比例"
from (select t.tablespace_name,
sum(t.bytes) / 1024 / 1024 totalspace,
sum(t.blocks) totalblocks
from dba_data_files t
group by t.tablespace_name) dbf,
(select tt.tablespace_name,
sum(tt.bytes) / 1024 / 1024 freespace,
sum(tt.blocks) freeblocks
from dba_free_space tt
group by tt.tablespace_name) dfs
where trim(dbf.tablespace_name) = trim(dfs.tablespace_name);
--4数据库连接管理
select count(*) from v$process;
select value from v$parameter where name = 'processes';
SELECT osuser, a.username,cpu_time/executions/1000000||'s', sql_fulltext,machine
from v$session a, v$sqlarea b
where a.sql_address =b.address order by cpu_time/executions desc;
