达梦7
1.查询会话中程序占用会话数
select appname,count(*) from v$sessions group by "V$SESSIONS".APPNAME;
2.查看用户下指定表占用空间
SELECT TABLE_USED_SPACE('username','tablename')*SF_GET_PAGE_SIZE()/1024/1024||'M'
3.查看用户下指定表实际使用的空间
SELECT TABLE_USED_PAGES('unsername','tablename')*SF_GET_PAGE_SIZE()/1024/1024||'M'
4.批量删除包含test字段的表
select 'drop table '||owner||'.'||table_name||'; 'from DBA_TABLES where TABLE_NAME like '%test%' and OWNER = 'test';
5.批量查询表空间大小以及占用百分比
select
a.tablespace_name ,
a.bytes /1024/1024 "Sum MB" ,
(a.bytes -b.bytes)/1024/1024 "used MB",
b.bytes /1024/1024 "free MB" ,
round(((a.bytes-b.bytes)/a.bytes)*100, 2) "percent_used"
from
(
select
tablespace_name,
sum(bytes) bytes
from
dba_data_files
group by
tablespace_name
)
a,
(
select
tablespace_name ,
sum(bytes) bytes,
max(bytes) largest
from
dba_free_space
group by
tablespace_name
)
b
where
a.tablespace_name=b.tablespace_name
order by
((a.bytes-b.bytes)/a.bytes) desc
达梦6
1.查询cpu占用较长时间未释放的sql
select sql_text,app_name,login_name,cpu_time_call frome v$session where cpu_time_call > 1000;
干就完了