首页 数据库

达梦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;

文章评论

    访客ChromeWindows
    2020-12-28 22:07 回复

    干就完了