达梦数据库-DMSQL和常用系统视图

最新推荐文章于 2024-05-04 09:38:05 发布

fangyu_7362

最新推荐文章于 2024-05-04 09:38:05 发布

阅读量1.1k

点赞数

文章标签：数据库 java 开发语言

本文链接：https://blog.csdn.net/fangyu_7362/article/details/125465460

版权

存储过程

存储过程的创建

    CREATE OR REPLACE PROCEDURE 存储过程名称(参数 参数类型) AS
        变量声明部分

    BEGIN
        语句部分

    EXCEPTION
        处理异常部分

    END;

重新编译存储过程

    ALTER PROCEDURE 存储过程名称 COMPILE;

删除存储过程

    DROP PROCEDURE 存储过程名称

示例

    1、创建一个表用于存储数据
        CREATE TABLE T1(id INT);

    2、创建一个存储过程, 计算获得的结果插入到T1表中
        CREATE OR REPLACE PROCEDURE p1(x IN INT) AS
            y INT:= 10;

        BEGIN
            x := x + y;
            INSERT INTO T1 VALUES(x);

        END;
        /

    3、调用存储过程
        SQL> CALL p1(10);
        SQL> SELECT * FROM T1;

        行号     ID         
        ---------- -----------
        1          20

存储函数

存储函数创建

    CREATE OR REPLACE FUNCTION 存储过程名称(参数 参数类型) RETURN 返回的数据类型 AS
        变量声明部分

    BEGIN
        语句部分
        RETURN 返回的数据

    EXCEPTION
        处理异常部分

    END;

示例

    CREATE OR REPLACE FUNCTION f1(x IN INT ) RETURN INT AS
        y INT:= 10;
    BEGIN
        x := x - y;
        RETURN x;
    END;
    /

    SQL> SELECT f1(100);

    行号     F1(100)    
    ---------- -----------
    1          90

存储过程和存储函数的参数类型

IN     输入参数
OUT    输出参数
IN OUT 参数既可以输入也可以输出

变量

声明变量

    var1 数据类型:= 值
    var1 数据类型 DEFAULT 值
    SET var1 := 值

控制结构

分支结构

IF 语句

        单分支语法
            IF 条件 THEN
                ...

            ELSE
                ...

            END IF;

        多分支语法
            IF 条件 THEN
                ...

            ELSEIF 条件 THEN
                ...

            ELSE
                ...
                
            END IF;

        示例
            CREATE TABLE a1(id INT , cont VARCHAR(12));

            CREATE OR REPLACE PROCEDURE p11(x IN INT) AS 
            BEGIN
                IF x > 10 THEN
                    INSERT INTO a1 VALUES(x,'x大于10');

                ELSEIF x < 10 THEN
                    INSERT INTO a1 VALUES(x,'小于10');

                ELSE
                    INSERT INTO a1 VALUES(x,'x等于10');
                END IF;
            END;
            /

CASE 语句

        CASE 条件 
            WHEN 值 THEN ...
            WHEN 值 THEN ...
            WHEN 值 THEN ...
        END;


        CASE 
            WHEN 条件判断 THEN ...
            WHEN 条件判断 THEN ...
            WHEN 条件判断 THEN ...
        END;


        示例
            CREATE OR REPLACE PROCEDURE c11(x IN INT) AS
            BEGIN
                x:= CASE 
                    WHEN x = 1 THEN 1   -- THEN 后面不能写DML语句
                    WHEN x = 2 THEN 2
                    WHEN x = 3 THEN 3
                    WHEN x = 4 THEN 4
                END CASE;
            END;
            /

SWITCH 语句

        SWITCH (条件)
        {
            CASE 值: 语句; BREAK;
            CASE 值: 语句; BREAK;
            CASE 值: 语句; BREAK;
        }

循环语句

LOOP 循环

    CREATE OR REPLACE PROCEDURE f1() AS 
        i INT:= 0;
        s INT:=0;
    BEGIN
        LOOP 
            IF i = 100 THEN

                EXIT;
            END IF;
            s := i + s;
            i := i + 1;
        END LOOP;
    END;
    /

WHILE 循环

    CREATE OR REPLACE PROCEDURE f1() AS 
        i INT:= 0;
        s INT:=0;
    BEGIN
        WHILE i < 100 LOOP
            s := i + s;
            i := i + 1;
        END LOOP;
    END;
    /

FOR 循环

    CREATE TABLE t11(id int);
    
    CREATE OR REPLACE PROCEDURE f1() AS 
        i INT:= 0;
        s INT:=0;
    BEGIN
        FOR a IN 1 .. 3 LOOP
            INSERT INTO T11 VALUES(a);
        END LOOP;
    END;
    /

    SQL> SELECT * FROM t11;

    行号     ID         
    ---------- -----------
    1          1
    2          2
    3          3

REPEAT 循环

        CREATE OR REPLACE PROCEDURE f1() AS 
            i INT:= 0;
            s INT:=0;
        BEGIN
        REPEAT
                i := i + 1;
                INSERT INTO T11 VALUES(i);
            UNTIL i > 10;
        END;
        /

退出语句

EXIT
    EXIT WHEN 1 > 10;

CONTINUE

游标

静态游标: 只读游标静态游标又分为: 隐式游标和显示游标

定义游标
%FOUND 语句是否修改或查询到了记录, 是返回 TRUE, 否则返回 FALSE
%NOTFOUND 语句是否未能成功修改或查询到记录, 是返回 TRUE, 否则返回FALSE
%ISOPEN 游标是否打开。是返回 TRUE, 否返回 FALSE。由于系统在语句执行完成后会自动关闭隐式游标, 因此隐式游标的%ISOPEN 属性永远为 FALSE
%ROWCOUNT DML 语句执行影响的行数, 或 SELECT…INTO 语句返回的行数。

示例

    CREATE OR REPLACE TABLE c1(id INT , name VARCHAR(12));
    INSERT INTO c1 VALUES(1,'AAA');
    INSERT INTO c1 VALUES(2,'BBB');
    INSERT INTO c1 VALUES(3,'CCC');
    INSERT INTO c1 VALUES(4,'DDD');
    COMMIT;

    DECLARE
        CURSOR cur1 is select * from c1 where id = 1;
    BEGIN
        OPEN cur1 ;
        IF cur1%NOTFOUND THEN
            SELECT 1 FROM dual;
        END IF;
        CLOSE cur1;
    end ;
    /

动态SQL

CREATE OR REPLACE PROCEDURE p1(x IN INT) AS
    sql1 VARCHAR := 'SELECT * FROM c1 WHERE id=?';
BEGIN
    EXECUTE IMMEDIATE sql1 using x;
END;
SQL> call p1(1);

行号     ID          CON
---------- ----------- ---
1          1           AAA

包

作用: 管理存储过程和函数

包重新编译

    ALTER PACKAGE pkg_name COMPILE;

删除包

    DROP PACKAGE pkg_name;

删除包体

    DROP PACKAGE BODY pkg_name;

创建包示例

    1、创建包
            CREATE OR REPLACE PACKAGE PersonPackage AS 
                E_NoPerson EXCEPTION;
                PersonCount INT;
                Pcur CURSOR;
                PROCEDURE AddPerson(Pname VARCHAR(100), Pcity varchar(100));
                PROCEDURE RemovePerson(Pname VARCHAR(100), Pcity varchar(100));
                PROCEDURE RemovePerson(Pid INT);
                FUNCTION GetPersonCount RETURN INT;
                PROCEDURE PersonList;
            END PersonPackage;

    2、创建包体
        在包体中需要实现具体的包中定义的对象
            CREATE OR REPLACE PACKAGE BODY PersonPackage AS PROCEDURE AddPerson(Pname VARCHAR(100), Pcity varchar(100)) AS 
                BEGIN
                    INSERT INTO Person(Name, City) VALUES (Pname, Pcity);
                    PersonCount = PersonCount + SQL % ROWCOUNT;
                END AddPerson;

                PROCEDURE RemovePerson(Pname VARCHAR(100), Pcity varchar(100)) AS 
                BEGIN
                    DELETE FROM Person WHERE NAME LIKE Pname AND City LIKE Pcity;
                END RemovePerson;

                PROCEDURE RemovePerson(Pid INT) AS 
                BEGIN
                    DELETE FROM Person WHERE Id = Pid; PersonCount = PersonCount - SQL % ROWCOUNT;
                END RemovePerson;

                FUNCTION GetPersonCount RETURN INT AS BEGIN RETURN PersonCount;
                END GetPersonCount;

                PROCEDURE PersonList AS DECLARE V_id INT;
                    V_name VARCHAR(100);
                    V_city VARCHAR(100);
                    BEGIN IF PersonCount = 0 THEN RAISE E_NoPerson;
                    END IF;

                    OPEN Pcur FOR
                    SELECT Id, Name, City FROM Person;

                    LOOP FETCH Pcur INTO V_id,
                        V_name,
                        V_city;
                        EXIT
                        WHEN Pcur % NOTFOUND;
                        PRINT ('No.' || (cast (V_id AS varchar(100))) || ' ' || V_name || '来自' || V_city);
                    END LOOP;

                    CLOSE Pcur;
                END PersonList;
            BEGIN
            SELECT COUNT(*) INTO PersonCount FROM Person;
            END PersonPackage;

触发器

创建触发器语法

    CREATE [OR REPLACE] TRIGGER 触发器名[WITH ENCRYPTION]
    BEFORE|AFTER|INSTEAD OF DELETE|INSERT|UPDATE [OF 列名] ON 表名
    [FOR EACH ROW [WHEN 条件]]
    BEGIN
        DMSQL 程序语句
    END;

删除触发器

    DROP TRIGGER t_name;
    ALTER TRIGGER t_name DISABLE;   将触发器置为失效
    ALTER TRIGGER t_name ENABLE;    将触发器置为有效

重新编译

    ALTER TRIGGER t_name COMPILE;

表级触发器

    对于表级别触发器,只针针对表的DML操作进行触发

事件触发器

触发事件

             DDL: CREATE、ALTER、DROP、GRANT、REVOKE 以及 TRUNCATE
        系统级别: LOGIN/LOGON、LOGOUT/LOGOFF、AUDIT、NOAUDIT、BACKUP DATABASE、RESTORE DATABASE、TIMER、STARTUP、SHUTDOWN 以及 SERERR

时间触发器
    针对某个时间点触发, 支持分钟级别触发

    示例: 每个月的第 28 天, 从早上 9 点开始到晚上 18 点之间, 每隔一分钟就打印一个字符串 Hello World
        CREATE
        OR REPLACE TRIGGER timer2
        AFTER
            TIMER ON DATABASE FOR EACH 1 MONTH DAY 28
        FROM
            TIME '09:00' TO TIME '18:00' FOR EACH 1 MINUTE DECLARE str VARCHAR;

        BEGIN PRINT 'HELLO WORLD';

        END;

示例
    CREATE TRIGGER trg_upd_score
    BEFORE UPDATE ON test_trigger_1
    FOR EACH ROW
    BEGIN
        IF NEW.score < 0 THEN
            SET NEW.score=0;
        ELSEIF NEW.score > 100 THEN
            SET NEW.score = 100;
        END IF;
    END;//
    delimiter ;

    NEW.score 更新以后该字段的值
    OLD.score 更新之前该字段的值

DM 常用系统表

DBA_TAB_PARTITIONS      表分区信息
DBA_TAB_PRIVS           表权限
DBA_TAB_STATISTICS      表的统计信息
DBA_TAB_SUBPARTITIONS   表的子分区信息
DBA_TRIGGERS            触发器信息
DBA_TRIGGER_COLS        触发器字段信息
DBA_USERS               用户信息 
DBA_VIEWS               视图信息
DBA_COL_COMMENTS        系统中所有表的字段注释
DBA_CONSTRAINTS         约束信息
DBA_CONS_COLUMNS        约束字段信息
DBA_DATA_FILES          数据文件信息
DBA_DB_LINKS            存放dblink信息
DBA_FREE_SPACE          空闲表空间
DBA_INDEXES             索引信息
DBA_DIRECTORIES         使用dexpdp 导入导出时需要设置的路径配置信息
DBA_JSON_COLUMNS        系统的json字段信息
DBA_OBJECTS             数据库对象信息
DBA_ROLES               系统中角色信息
DBA_SEGMENTS            数据段信息
DBA_SEQUENCES           创建的序列信息
DBA_SOURCE              创建的存储过程函数等信息
DBA_TABLES              系统中所有表信息
DBA_TABLESPACES         表空间信息
DBA_TAB_COLS            表的字段信息
DBA_TAB_COMMENTS        表的注释信息
DBA_PROXIES          记录所有代理用户的全部信息只有角色DBA和DB_OBJECT_ADMIN才能访问该系统视图
DBA_ROLE_PRIVS          角色权限
DBA_SYNONYMS            同义词
DBA_SYS_PRIVS           系统权限表
DBA_TAB_COLUMNS         系统中所有表的字段信息
DBA_ENCRYPTED_COLUMNS   加密字段
DBA_IND_COLUMNS         系统中那些字段添加了索引
DBA_IND_PARTITIONS      分区索引
DBA_IND_SUBPARTITIONS   子分区索引
DBA_PART_KEY_COLUMNS    分区表的分区字段
DBA_PART_TABLES         分区表的状态信息
DBA_PROCEDURES          系统中又那些存储过程
DBA_SOURCE_AE           系统中所有包和存储过程的定义
DBA_COL_PRIVS           字段权限
SYSOBJECTS              系统中所有对象的信息, 包括对象的类型、创建事件、是否有效等信息
SYSINDEXES              系统中所有索引的信息
SYSCOLUMNS              系统中所有表的列信息
SYSCONS                 系统中所有约束信息
SYSSTATS                系统中的统计信息
SYSTEXTS                存放字典对象的文本信息使用时, 系统内部临时解析的字典对象才会放入此处
SYSGRANTS               记录系统中权限信息
SYSAUDIT                记录系统中的审计设置
SYSAUDITRULES           记录系统中审计规则的信息
SYSHPARTTABLEINFO       记录系统中分区表的信息
SYSMACCOMPS             记录策略的范围
SYSMACGRPS              记录策略所在组的信息
SYSMACUSRPLY            记录用户的策略信息
USER_PROXIES            记录当前用户的代理的全部信息只有角色PUBLIC、DB_AUDIT_PUBLIC、 DB_POLICY_PUBLIC以及DB_OBJECT_PUBLIC才能访问该系统视图
PROXY_USERS             记录所有代理用户名的信息只有角色DBA、DB_AUDIT_ADMIN 、DB_POLICY_ADMIN以及DB_OBJECT_ADMIN才能访问该系统视图
SYS.PROXY_INFO$         记录所有代理用户ID的信息只有角色DBA、DB_AUDIT_ADMIN 、DB_POLICY_ADMIN以及DB_OBJECT_ADMIN才能访问该系统表
SYSFCOLDEFAULT          记录表添加列时追加列的默认值
SYSINJECTHINT           记录已指定的SQL语句和对应的HINT
SYSMSTATS               记录多维统计信息的内容
SYSSTATTABLEIDU         记录所有表在上一次收集统计信息时的总行数和之后对表增删改的影响行数以及是否有过TRUNCATE操作
SYSDEPENDENCIES         记录对象间的依赖关系
SYSUSERINI              记录定制的INI参数
SYSCOLINFOS             记录列的附加信息, 例如是否虚拟列
SYSRESOURCES            记录用户使用系统资源的限制信息
SYSOBJINFOS             记录对象的依赖信息
SYSUSERS                记录系统中用户信息
SYSCOLUMNCOMMENTS       记录列的注释信息
SYSTABLECOMMENTS        记录表或视图的注释信息
SYSCONTEXTINDEXES       记录全文索引的信息
SYSPWDCHGS              记录密码的修改信息
SYSCOLCYT               记录列的加密信息
SYSACCHISTORIES         记录登录失败的历史信息
SYSMACOBJ               录扩展客体标记信息
SYSMACTABPLY            录表策略信息
SYSMACLABELS            录策略的标记信息
SYSMACGRPS              录策略所在组的信息
SYSMACLVLS              录策略的等级
SYSMACPLYS              录策略定义
DBA_ARGUMENTS
DBA_DEPENDENCIES
DBA_POLICIES
DBA_POLICY_CONTEXTS 
DBA_POLICY_GROUPS
DBA_PROFILES
DBA_SEC_RELEVANT_COLS
DBA_TAB_MODIFICATIONS

动态性能视图

资源管理

    V$DYNAMIC_TABLES                获得所有的动态性能视图名称
    V$DICT_CACHE_ITEM               显示字典缓存中的字典对象信息
    V$DICT_CACHE                    显示字典缓存信息
    V$BUFFERPOOL                    页面缓冲区动态性能表, 用来记录页面缓冲区结构的信息
    V$BUFFER_LRU_FIRST              显示所有缓冲区LRU链首页信息
    V$BUFFER_UPD_FIRST              显示所有缓冲区UPDATE链首页信息
    V$BUFFER_LRU_LAST               显示所有缓冲区LRU链末页信息
    V$BUFFER_UPD_LAST               显示所有缓冲区UPDATE链末页信息
    V$CACHEITEM                     显示缓冲区中缓冲项的相关信息在ini参数USE_PLN_POOL !=0时才统计
    V$CACHERS                       显示结果集缓冲区的相关信息在ini参数USE_PLN_POOL !=0时才统计
    V$CACHESQL                      显示SQL缓冲区中SQL语句的信息在ini参数USE_PLN_POOL !=0时才统计
    V$SQLTEXT                       显示缓冲区中的SQL语句信息
    V$SQL_PLAN                      显示缓冲区中的执行计划信息在ini参数USE_PLN_POOL !=0时才统计
    V$MEM_POOL                      显示所有的内存池信息
    V$MEM_REGINFO                   显示系统当前已分配并未释放的内存信息, 当MEMORY_LEAK_CHECK为1时才会在此动态视图注册信息
    V$GSA                           显示全局SORT内存缓冲区的使用情况
    V$MEM_HEAP                      显示系统当前内存堆的信息, 仅当系统启动时MEMORY_LEAK_CHECK为1时有效
    V$LARGE_MEM_SQLS                最近1000条使用大内存的sql语句一条sql语句使用的内存值超过ini参数LARGE_MEM_THRESHOLD, 就认为使用了大内存
    V$SYSTEM_LARGE_MEM_SQLS         系统中使用大内存最多的20条sql语句字段定义与v$large_mem_sqls相同
    V$SCP_CACHE                     显示缓存池信息
    V$DB_SYSPRIV_CACHE              系统权限缓存信息
    V$DB_OBJPRIV_CACHE              对象权限缓存信息
    V$SQL_STAT                      语句级资源监控内容记录当前正在执行的SQL语句的资源开销需要ENABLE_MONITOR=1才开始监控其中5~58列中的监控项, 可以通过SP_SET_SQL_STAT_THRESHOLD()设置监控阀值, 超过阀值才开始监控具体使用参考《DM8_SQL语言使用手册》
    $SQL_STAT_HISTORY               语句级资源监控内容记录历史SQL语句执行的资源开销需要ENABLE_MONITOR=1才开始监控视图的格式和V$SQL_STAT一样单机最大行数为10000
    V$HLDR_TABLE                    记录当前系统中所有HLDR使用HLDR_BUF的情况

段簇页

    V$SEGMENT_INFOS                 显示所有的段信息
    V$SEGMENTINFO                   索引叶子段信息视图查询该视图时, 一定要带WHERE条件, 并且必须是等值条件
    V$BTREE_INNER_PAGES             索引的叶子段/内节点段的页信息视图查询该视图时, 一定要带WHERE条件, 并且必须是等值条件如: select * from v$btree_leaf_pages where index_id =id;
    V$BTREE_LIST_PAGES              LIST索引的叶子段的页信息视图查询该视图时, 一定要带WHERE条件, 并且必须是等值条件如: select * from v$btree_list_pages where index_id = id;
    V$TABLE_LOB_PAGES               表中的大字段的页信息视图查询该视图时, 一定要带WHERE条件, 并且必须是等值条件如: select * from v$table_lob_pages where table_id = id;
    V$LOB_SEG                       用于查看各表大字段的段首页中记录的信息
    V$RESOURCE_LIMIT                显示表、用户的空间限制信息
    V$SEGMENT_PAGES                 段中数据页的信息视图查询该视图时, 一定要带WHERE条件指定GROUP_ID和SEG_ID, 并且必须是等值条件例如: select * from v$segment_pages where group_id=1 and seg_id = 200;
    V$PSEG_SYS                      显示当前回滚段信息
    V$PSEG_ITEMS                    显示回滚系统中当前回滚项信息
    V$PSEG_COMMIT_TRX               显示回滚项中已提交但未PURGE的事务信息
    V$PSEG_PAGE_INFO                显示当前回滚页信息
    V$PURGE                         显示当前PURGE回滚段信息
    V$PURGE_PSEG_OBJ                显示PURGE系统中, 待PURGE的所有PSEG对象信息
    V$PURGE_PSEG_TAB                显示待PURGE表信息

数据库信息

    V$LICENSE                       显示LICENSE信息, 用来查询当前系统的LICENSE信息
    V$VERSION                       显示版本信息, 包括服务器版本号与DB版本号如果为DMDSC环境, 则还会增加显示DMDSC版本号
    V$DATAFILE                      显示数据文件信息
    V$DATABASE                      显示数据库信息
    V$IID                           显示下一个创建的数据库对象的ID该视图提供用户可以查询下一个创建对象的ID的值, 可以方便用户查询预知自己所要建立对象的信息
    V$HINT_INI_INFO                 显示支持的HINT参数信息
    V$ERR_INFO                      显示系统中的错误码信息
    V$INSTANCE                      显示实例信息
    V$DYNAMIC_TABLES                显示当前数据库中的动态视图
    V$RESERVED_WORDS                保留字统计表, 记录保留字的分类信息

数据库对象相关

    V$TABLESPACE                    显示表空间信息, 不包括RLOG表空间信息
    V$HUGE_TABLESPACE               显示HUGE表空间信息
    V$HUGE_TABLESPACE_PATH          显示HUGE表空间路径信息
    V$SEQCACHE                      显示当前系统中缓存的序列的信息
    V$PKGPROCS                      显示包中的方法信息
    V$PKGPROCPARAMS                 显示包中方法的参数信息
    V$DB_CACHE                      数据字典缓存表, 用于记录数据字典的实时信息
    V$DB_OBJECT_CACHE               数据字典对象缓存表, 用于记录数据字典中每个对象的信息
    V$OBJECT_USAGE                  记录索引监控信息
    V$IFUN                          显示数据库提供的所有函数
    V$IFUN_ARG                      显示数据库提供的所有函数的参数
    V$SYSSTAT                       显示系统统计信息
    V$JOBS_RUNNING                  显示系统中正在执行的作业信息
    V$AUTO_STAT_TABLE_IDU           记录表中数据的变化信息在ini参数AUTO_STAT_OBJ =1时才统计

数据库配置参数

    V$PARAMETER                     显示ini参数和dminit建库参数的类型及参数值信息(当前会话值、系统值及dm.ini文件中的值)
    V$DM_INI                        所有ini参数和dminit建库参数信息
    V$DM_ARCH_INI                   归档参数信息
    V$DM_MAL_INI                    MAL参数信息
    V$DM_REP_RPS_INST_NAME_INI      数据复制服务器参数信息
    V$DM_REP_MASTER_INFO_INI        数据复制主库参数信息
    V$DM_REP_SLAVE_INFO_INI         数据复制从机参数信息
    V$DM_REP_SLAVE_TAB_MAP_INI      数据复制从机表对应关系参数信息
    V$DM_REP_SLAVE_SRC_COL_INFO_INI 数据复制从机列对应关系参数信息
    V$DM_LLOG_INFO_INI              逻辑日志信息参数信息
    V$DM_LLOG_TAB_MAP_INI           逻辑日志与表对应的参数信息
    V$DM_TIMER_INI                  定时器参数信息
    V$OPTION                        安装数据库时的参数设置
    V$OBSOLETE_PARAMETER            已作废的INI信息

日志管理

    V$RLOG      显示日志的总体信息通过该视图可以了解系统当前日志事务号LSN的情况、归档日志情况、检查点的执行情况等
    V$RLOG_PKG   显示日志包信息通过该视图可以查询日志系统中当前日志包的使用情况, 如包的长度、最大LSN、最小LSN等通过该视图还可以查询当前实例日志系统中等待刷盘的链表上的日志包信息
    V$RLOG_PKG_STAT   显示当前实例日志系统中日志包使用的统计信息
    V$RLOGFILE         显示日志文件的具体信息包括文件号、完整路径、文件的状态、文件大小等等
    V$ARCHIVED_LOG     显示当前实例的所有归档日志文件信息此动态视图还有一些未列出的保留列, 查询时均显示NULL
    V$LOGMNR_LOGS      显示当前会话添加的需要分析的归档日志文件此动态视图还有一些未列出的保留列, 查询时均显示NULL
    V$LOGMNR_PARAMETERS   显示当前会话START_LOGMNR启动日志文件分析的参数此动态视图还有一些未列出的保留列, 查询时均显示NULL
    V$LOGMNR_CONTENTS   显示当前会话日志分析的内容此动态视图还有一些未列出的保留列, 查询时均显示NULL
    V$ARCH_QUEUE       显示当前归档任务队列信息
    V$RLOG_HISTORY    显示日志的历史总体信息通过该视图可以了解最近一周内系统每过一分钟日志事务号LSN的情况、归档日志情况以及检查点的执行情况等

会话

    V$CONNECT          显示活动连接的所有信息
    V$SESSIONS         显示会话的具体信息, 如执行的sql语句、主库名、当前会话状态、用户名等等
    V$SESSION_SYS      显示系统中会话的一些状态统计信息
    V$OPEN_STMT        连接语句句柄表, 用于记录SESSION上语句句柄的信息
    V$SESSION_HISTORY               显示会话历史的记录信息, 如主库名、用户名等, 与V$SESSIONS的区别在于会话历史记录只记录了会话一部分信息, 对于一些动态改变的信息没有记录, 如执行的SQL语句等
    V$CONTEXT                       显示当前会话所有上下文的名字空间、属性和值
    V$SESSION_STAT                  记录每个session上的相关统计信息
    V$NLS_PARAMETERS                显示当前会话的日期时间格式和日期时间语言

SQL执行相关

    V$SQL_HISTORY                   当INI参数ENABLE_MONITOR=1时, 显示执行sql的历史记录信息；可以方便用户经常使用的记录进行保存
    V$SQL_NODE_HISTORY              通过该视图既可以查询sql执行节点信息, 包括sql节点的类型、进入次数和使用时间等等；又可以查询所有执行的sql节点执行情况, 如哪些使用最频繁、耗时多少等当INI参数ENABLE_MONITOR 和 MONITOR_SQL_EXEC都开启时, 才会记录SQL执行节点信息如果需要时间统计信息, 还需要打开MONITOR_TIME
    V$SQL_NODE_NAME                 显示所有的sql节点描述信息, 包括sql节点类型、名字和详细描述
    V$COSTPARA                      显示SQL计划的代价信息
    V$LONG_EXEC_SQLS                当INI参数ENABLE_MONITOR=1、MONITOR_TIME=1时, 显示系统最近1000条执行时间超过预定值的SQL语句默认预定值为1000毫秒可通过SP_SET_LONG_TIME系统函数修改, 通过SF_GET_LONG_TIME系统函数查看当前值
    V$SYSTEM_LONG_EXEC_SQLS         当INI参数ENABLE_MONITOR=1、MONITOR_TIME=1时, 显示系统自启动以来执行时间最长的20条SQL语句, 不包括执行时间低于预定值的语句
    V$VMS                           显示虚拟机信息
    V$STKFRM                        显示虚拟机栈桢信息该参数必须在INI参数ENABLE_MONITOR和MONITOR_SQL_EXEC都开启时才有信息
    V$STMTS                         显示当前活动会话的最近的语句的相关信息
    V$SQL_PLAN_NODE                 当INI参数ENABLE_MONITOR和MONITOR_SQL_EXEC都开启时, 显示执行计划的节点信息
    V$SQL_SUBPLAN                   显示子计划信息
    V$SQL_PLAN_DCTREF               显示所有执行计划相关的详细字典对象信息
    V$MTAB_USED_HISTORY             显示系统自启动以来使用MTAB空间最多的50个操作符信息
    V$SORT_HISTORY                  当INI参数ENABLE_MONITOR=1都打开时, 显示系统自启动以来使用排序页数最多的50个操作符信息
    V$HASH_MERGE_USED_HISTORY       HASH MERGE连接操作符使用的缓存信息
    V$PLSQL_DDL_HISTORY             记录DMSQL程序中执行的DDL语句, 主要监控truncate table和Execute immediate DDL语句的情况
    V$PRE_RETURN_HISTORY            记录大量数据返回结果集的历史信息(查询大量数据产生)
    V$DMSQL_EXEC_TIME               记录动态监控的sql语句执行时间当ENABLE_MONITOR_DMSQL=1时才会记录监控的sql语句
    V$VIRTUAL_MACHINE               显示活动的虚拟机信息

事务和检查点

    V$TRX                           显示所有活动事务的信息通过该视图可以查看所有系统中所有的事务以及相关信息, 如锁信息等
    V$TRXWAIT                       显示事务等待信息
    V$TRX_VIEW                      显示当前事务可见的所有活动事务视图信息根据达梦多版本规则, 通过该视图可以查询系统中自己所见的事务信息；可以通过与v$trx表的连接查询它所见事务的具体信息
    V$RECV_ROLLBACK_TRX             显示数据库启动时回滚的所有事务信息
    V$LOCK                          显示活动的事务锁信息
    V$DEADLOCK_HISTORY              记录死锁的历史信息目前DMDSC环境下暂不支持记录死锁历史信息
    V$FLASHBACK_TRX_INFO            显示闪回信息
    V$CKPT_HISTORY                  显示检查点历史信息
    V$CKPT                          显示系统检查点信息

事件

    V$WAIT_HISTORY                  通过该视图可以查询等待事件的具体信息, 如等待的线程id, 会话id等可以查看具体等待事件的信息, 如果某个事务等待时间过长, 则可以查询到具体事务信息以及所在的线程和所牵涉的对象, 分析原因进行优化等操作
    V$EVENT_NAME                    显示当前系统所支持的等待事件的类型汇总信息
    V$SYSTEM_EVENT                  显示自系统启动以来所有等待事件的详细信息
    V$SESSION_EVENT                 显示当前会话等待事件的所有信息
    V$SESSION_WAIT_HISTORY          显示会话等待事件的历史信息
    V$DANGER_EVENT                  数据库重要事件和行为信息视图
    V$TASK_QUEUE                    任务队列信息
    V$TRACE_QUEUE                   事件跟踪任务队列信息

进程和线程

    V$PROCESS                       显示当前进程信息
    V$THREADS                       显示系统中所有活动线程的信息
    V$LATCHES                       显示正在等待的线程信息
    V$WTHRD_HISTORY                 通过本视图可以观察系统从启动以来, 所有活动过线程的相关历史信息其中CHG_TYPE有REUSE_OK(本SESSION 重用成功)、REUSE_FAIL (重用失败)、To_IDLE(不重用, 直接变IDLE)等几种类型

系统信息

    V$SYSTEMINFO                    系统信息视图
    V$CMD_HISTORY                   通过本视图可以观察系统的一些命令的历史信息其中cmd指的是SESS_ALLOC, SESS_FREE, CKPT, TIMER_TRIG, SERERR_TRIG, LOG_REP, MAL_LETTER,  CMD_LOGIN等
    V$RUNTIME_ERR_HISTORY           监控运行时错误历史异常分为三种: 一种是系统异常, 用户没有捕获, 由vm_raise_runtime_error产生；第二种是用户异常, 用户捕获错误, 并抛出自定义异常, 由nthrow_exec产生；第三种是语法异常, 语法未通过, 由nsvr_build_npar_cop_out产生
    V$INSTANCE_LOG_HISTORY          用于查询服务器实例运行期间生成的最近1万条事件日志
    V$MAL_SYS                       MAL系统信息视图如果是数据守护环境, 则只显示主库的MAL系统信息
    V$MAL_INFO                      MAL邮箱信息视图
    V$MAL_LETTER_INFO               MAL上的信件信息视图
    V$MAL_USING_LETTERS             服务器中正在使用或者使用过但是没有释放的邮件信息, 用于检查MAL系统潜在的内存泄露, INI参数MAL_LEAK_CHECK为1时有效

通讯

    V$DBLINK                        动态使用到的数据库链接信息视图

MPP

    V$MPP_CFG_SYS                   MPP系统配置信息视图
    V$MPP_CFG_ITEM                  MPP站点配置信息视图
    V$MAL_SITE_INFO                 MAL站点信息视图, MPP模式下, 自动收集MPP各个站点的信息

DSC

    V$DSC_EP_INFO                   显示实例信息, 登录任意节点查询得到的结果一致
    V$DSC_GBS_POOL                  显示 GBS 控制结构的信息, 仅显示登录节点的信息
    V$DSC_GBS_POOLS_DETAIL          显示分片的 GBS_POOL 详细信息, 仅显示登录节点的信息
    V$DSC_GBS_CTL                   显示 GBS 控制块信息多个 pool, 依次扫描, 仅显示登录节点的信息
    V$DSC_GBS_CTL_DETAIL            显示 GBS 控制块详细信息多个 pool, 依次扫描, 仅显示登录节点的信息
    V$DSC_GBS_CTL_LRU_FIRST         显示 GBS 控制块 LRU 链表首页信息多个 pool, 依次扫描, 仅显示登录节点的信息
    V$DSC_GBS_CTL_LRU_FIRST_DETAIL  显示 GBS 控制块 LRU 链表首页详细信息多个 pool, 依次扫描, 仅显示登录节点的信息
    V$DSC_GBS_CTL_LRU_LAST          显示 GBS 控制块 LRU 链表尾页信息多个 pool, 依次扫描, 仅显示登录节点的信息
    V$DSC_GBS_CTL_LRU_LAST_DETAIL   显示 GBS 控制块 LRU 链表尾页详细信息多个 pool, 依次扫描, 仅显示登录节点的信息
    V$DSC_GBS_REQUEST_CTL           显示等待 GBS 控制块的请求信息多个 pool, 依次扫描, 仅显示登录节点的信息
    V$DSC_LBS_POOL                  显示 LBS 控制结构的信息, 仅显示登录节点的信息
    V$DSC_LBS_POOLS_DETAIL          显示分片的 LBS_POOL 详细信息多个 pool, 依次扫描, 仅显示登录节点的信息
    V$DSC_LBS_CTL                   显示 LBS 控制块信息多个 pool, 依次扫描, 仅显示登录节点的信息
    V$DSC_LBS_CTL_LRU_FIRST         显示 LBS 的 LRU_FIRST 控制块信息多个 pool, 依次扫描, 仅显示登录节点的信息
    V$DSC_LBS_CTL_LRU_LAST          显示 LBS 的 LRU_LAST 控制块信息多个 pool, 依次扫描, 仅显示登录节点的信息
    V$DSC_LBS_CTL_DETAIL            显示 LBS 控制块详细信息多个 pool, 依次扫描, 仅显示登录节点的信息
    V$DSC_LBS_CTL_LRU_FIRST_DETAIL  显示 LBS 的 LRU_FIRST 控制块详细信息多个 pool, 依次扫描, 仅显示登录节点的信息
    V$DSC_LBS_CTL_LRU_LAST_DETAIL   显示 LBS 的 LRU_LAST 控制块详细信息多个 pool, 依次扫描, 仅显示登录节点的信息
    V$DSC_GTV_SYS                   显示 GTV 控制结构的信息, 仅登录集群环境控制节点才能获取数据, 登录其他节点返回数据无效
    V$DSC_GTV_TINFO                 显示 TINFO 控制结构的信息, 仅登录集群环境控制节点才能获取数据, 登录其他节点返回数据为空暂时没有什么用, 查询结果为空
    V$DSC_GTV_ACTIVE_TRX            显示全局活动事务信息, 仅登录集群环境控制节点才能获取数据, 登录其他节点返回数据为空暂时没有什么用, 查询结果为空
    V$DSC_LOCK                      显示全局活动的事务锁信息, 登录任意节点查询得到的结果一致
    V$DSC_TRX                       显示所有活动事务的信息通过该视图可以查看所有系统中所有的事务以及相关信息, 如锁信息等, 登录任意节点查询得到的结果一致
    V$DSC_TRXWAIT                   显示事务等待信息, 登录任意节点查询得到的结果一致
    V$DSC_TRX_VIEW                  显示当前事务可见的所有活动事务视图信息根据达梦多版本规则, 通过该视图可以查询系统中自己所见的事务信息；可以通过与 v$dsc_trx 表的连接查询它所见事务的具体信息, 登录任意节点查询得到的结果一致
    V$ASMATTR                       如果使用有 ASM 文件系统, 可通过此视图查看 ASM 文件系统相关属性, 登录任意节点查询得到的结果一致
    V$ASMGROUP                      如果使用有 ASM 文件系统, 可通过此视图查看 ASM 磁盘组信息, 登录任意节点查询得到的结果一致
    V$ASMDISK                       如果使用有 ASM 文件系统, 可通过此视图查看所有的 ASM 磁盘信息, 登录任意节点查询得到的结果一致
    V$ASMFILE                       如果使用有 ASM 文件系统, 可通过此视图查看所有的 ASM 文件信息, 登录任意节点查询得到的结果一致
    V$DCR_INFO                      查看 DCR 配置的全局信息, 登录任意节点查询得到的结果一致
    V$DCR_GROUP                     查看 DCR 配置的组信息, 登录任意节点查询得到的结果一致
    V$DCR_EP                        查看 DCR 配置的节点信息, 登录任意节点查询得到的结果一致
    V$DSC_REQUEST_STATISTIC         统计 DSC 环境内 TYPE 类型请求时间, 仅显示登录节点的信息
    V$DSC_REQUEST_PAGE_STATISTIC    统计 lbs_XX 类型最耗时的前 100 页地址信息, 仅显示登录节点的信息
    V$DSC_CRASH_OVER_INFO           显示 DSC 环境各节点数据页最小first_modified_lsn, 以及故障节点 file_lsn如果活动节点 buffer 中不存在更新页则 min_first_modified_lsn 为 NULL；节点故障后, 只有在所有 OK 节点 min_first_modified_lsn 都大于或等于故障节点file_lsn 之后, 才允许故障节点重加入；满足所有 OK 节点 min_first_modified_lsn都大于 crash_lsn 之后, crash_lsn 会清零
    V$DSC_GLS_SYS                   显示 DSC 环境下的系统全局封锁相关信息

数据守护

    V$RECOVER_STATUS                该视图需要在主库上查询(DMDSC主库需要在控制节点上查询), 用于查询备库的恢复进度, 如果已恢复完成, 查询结果为空
    V$KEEP_RLOG_PKG                 该视图需要在备库上查询(DMDSC备库需要在控制节点(重演节点)上查询), 用于查询备库上的KEEP_RLOG_PKG信息, 在备库归档状态有效的情况下可以查到内容
    V$RAPPLY_SYS                    该视图需要在备库上查询(DMDSC备库需要在控制节点(重演节点)上查询), 用于查询备库重做日志时的一些系统信息
    V$RAPPLY_LOG_TASK               该视图需要在备库上查询(DMDSC备库需要在控制节点(重演节点)上查询), 用于查询备库当前重做任务的日志信息
    V$ARCH_FILE                     查询本地归档日志信息对DMDSC集群, 除了显示本地归档外, 也显示远程归档信息
    V$ARCH_STATUS                   查询归档状态信息, 归档状态是由主库记录和维护的, 此视图只在主库上查询有效, 备库上的查询结果没有实际意义
    V$MAL_LINK_STATUS               查询本地实例到远程实例的MAL链路连接状态
    V$DMWATCHER                     查询当前登录实例所对应的守护进程信息, 注意一个守护进程可以同时守护多个组的实例, 因此查询结果中部分字段(N_GROUP、SWITCH_COUNT)为守护进程的全局信息, 并不是当前登录实例自身的守护信息在DMDSC集群环境中, 只显示控制守护进程的信息
    V$UTSK_INFO                     查询守护进程向服务器发送请求的执行情况
    V$UTSK_SYS2                     显示服务器当前的全局信息
    V$ARCH_SEND_INFO                此视图用于在主库上查询各备库的日志发送统计信息
    V$RAPPLY_STAT                   此视图用于在备库上查询备库重演日志的统计信息
    V$RAPPLY_LSN_INFO               查询备库的重演信息, 如果备库是DMDSC集群, 则需要在控制节点(重演节点)上查询如果在主库上查询此视图, 则查到的是主库曾经作为备库时的历史重演信息其中后面四个CKPT_XX字段, 是在备库刷检查点时才调整, 前面四个是随备库重演而动态调整的
    V$RAPPLY_PARALLEL_INFO          查询备库的并行重演信息, 如果备库是DMDSC集群, 则需要在控制节点(重演节点)上查询

系统包

    V$CACHEPKG                      显示当前系统中的包的使用信息
    V$DBMS_LOCKS                    显示当前系统中的申请的DBMS_LOCK包封锁情况
    V$DB_PIPES                      记录使用DBMS_PIPE包创建的管道的相关信息
    V$SCHEDULER_JOBS_RUNNING        记录数据库中所有由DBMS_SCHEDULER包创建且正在执行的作业

捕获

    V$CAPTURE                       显示捕获信息

审计与加密

    V$AUDITRECORDS                  显示审计记录, 用来查询当前系统默认路径下的审计文件信息此动态性能视图只有在审计开关打开时才有内容, 且只有审计用户可以查询
    V$AUDIT_SPACE                   显示审计文件存储空间信息此动态性能视图只有审计用户可以查询
    V$CIPHERS                       显示系统加密算法信息
    V$EXTERNAL_CIPHERS              显示系统中所有的第三方加密算法信息
    V$EXTERNAL_CIPHER_LIBS          显示加密引擎、第三方链接库相关信息

常用SQL语句

查看表空间使用率

SELECT
    a.tablespace_name "表空间名称",
    total / (1024 * 1024) "表空间大小(M)",
    free / (1024 * 1024) "表空间剩余大小(M)",
    (total - free) / (1024 * 1024) "表空间使用大小(M)",
    total / (1024 * 1024 * 1024) "表空间大小(G)",
    free / (1024 * 1024 * 1024) "表空间剩余大小(G)",
    (total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)",
    round((total - free) / total, 4) * 100 "使用率 %"
FROM
    (SELECT tablespace_name, SUM(bytes) free FROM dba_free_space GROUP BY tablespace_name ) a,
    (SELECT tablespace_name, SUM(bytes) total FROM dba_data_files GROUP BY tablespace_name) b
WHERE
    a.tablespace_name = b.tablespace_name;

查看临时表空间使用情况

SELECT
        a.tablespace_name "表空间名称"                         ,
        total        / (1024 * 1024) "表空间大小(M)"                 ,
        free         / (1024 * 1024) "表空间剩余大小(M)"               ,
        (total       - free) / (1024 * 1024 ) "表空间使用大小(M)"      ,
        total        / (1024 * 1024 * 1024) "表空间大小(G)"          ,
        free         / (1024 * 1024 * 1024) "表空间剩余大小(G)"        ,
        (total       - free) / (1024 * 1024 * 1024) "表空间使用大小(G)",
        round((total - free) / total, 4) * 100 "使用率 %"
FROM
        (SELECT tablespace_name,SUM(bytes) free FROM dba_free_space GROUP BY tablespace_name) a,
        (SELECTtablespace_name, SUM(bytes) total FROM dba_data_files GROUP BY tablespace_name) b
WHERE
        a.tablespace_name = b.tablespace_name and a.tablespace_name='TEMP';

查看会话信息

SELECT count(*) FROM v$sessions WHERE state='ACTIVE';


SELECT
    SYSDATE,
    SF_GET_SESSION_SQL (SESS_ID),
    sess_id,
    sess_seq,
    sql_text,
    state,
    seq_no,
    user_name,
    trx_id,
    create_time,
    clnt_ip
FROM
    v $ sessions
WHERE
    state = 'ACTIVE';

锁查询

SELECT
    o.name,
    l.*
FROM
    v $ LOCK l,
    sysobjects o
WHERE
    l.table_id = o.id
    AND blocked = 1;

WITH LOCKS AS (
    SELECT
        o.name,
        l.*,
        s.sess_id,
        s.sql_text,
        s.clnt_ip,
        s.last_send_time
    FROM
        v $ LOCK l,
        sysobjects o,
        v $ sessions s
    WHERE
        l.table_id = o.id
        AND l.trx_id = s.trx_id
),
lock_tr AS (
    SELECT
        trx_id wt_trxid,
        tid blk_trxid
    FROM
        LOCKS
    WHERE
        blocked = 1
),
res AS (
    SELECT
        SYSDATE stattime,
        t1.name,
        t1.sess_id wt_sessid,
        s.wt_trxid,
        t2.sess_id blk_sessid,
        s.blk_trxid,
        t2.clnt_ip,
        SF_GET_SESSION_SQL (t1.sess_id) fulsql,
        datediff (ss, t1.last_send_time, SYSDATE) ss,
        t1.sql_text wt_sql
    FROM
        lock_tr s,
        LOCKS t1,
        LOCKS t2
    WHERE
        t1.ltype = 'OBJECT'
        AND t1.table_id <> 0
        AND t2.ltype = 'OBJECT'
        AND t2.table_id <> 0
        AND s.wt_trxid = t1.trx_id
        AND s.blk_trxid = t2.trx_id
) --select distinct clnt_ip from res;
SELECT
    DISTINCT wt_sql,
    clnt_ip,
    ss
FROM
    res;

已执行但未提交的SQL语句查询

SELECT
    t1.sql_text,
    t1.state,
    t1.trx_id
FROM
    v $ sessions t1,
    v $ trx t2
WHERE
    t1.trx_id = t2.id
    AND t1.state = 'IDLE'
    AND t2.status = 'ACTIVE';

有事务未提交的表查询

SELECT
    b.object_name,
    c.sess_id,
    a.*
FROM
    v $ LOCK a,
    dba_objects b,
    v $ sessions c
WHERE
    a.table_id = b.object_id
    AND ltype = 'OBJECT'
    AND a.trx_id = c.trx_id;

长时间的 SQL 查询

SELECT
    t1.sql_text,
    t1.state,
    t1.trx_id
FROM
    v $ sessions t1,
    v $ trx t2
WHERE
    t1.trx_id = t2.id
    AND t1.state = 'IDLE'
    AND t2.status = 'ACTIVE';

找出已执行超过 2 秒的活动 SQL

SELECT
    *
FROM
    (
        SELECT
            sess_id,
            sql_text,
            datediff (ss, last_recv_time, SYSDATE) Y_EXETIME,
            SF_GET_SESSION_SQL (SESS_ID) fullsql,
            clnt_ip
        FROM
            V $ SESSIONS
        WHERE
            STATE = 'ACTIVE'
    )
WHERE
    Y_EXETIME >= 2;

查看数据字典缓存信息

SQL> SELECT * FROM V$DB_CACHE;  -- 缓存元数据信息的视图
行号     DB_ADDR            POOL_ID     TOTAL_SIZE           USED_SIZE            DICT_NUM    SIZE_LRU_DISCARD     LRU_DISCARD DDL_DISCARD DISABLED_SIZE DISABLED_DICT_NUM
---------- ------------------ ----------- -------------------- -------------------- ----------- -------------------- ----------- ----------- ------------- -----------------
1          0x00007F05353163E8 0           52428800             135398               58          0                    0           2           0             0

-- LRU_DISCARD 如果很大, 则需要调整参数 DICT_BUF_SIZE

查看任务队列

SQL> select * from v$task_queue;
行号     WAITING     READY                TOTAL_WAIT_TIME      AVERAGE_WAIT_TIME
---------- ----------- -------------------- -------------------- -----------------
1          0           43800                1945                 0

-- WAITING 如果很大则考虑, 调大TASK_THREADS 或者重启数据库

详细内容请参考官方文档：https://eco.dameng.com

更多详情请访问达梦在线服务平台 https://eco.dameng.com/

fangyu_7362

关注

0
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
达梦数据库-DMSQL和常用系统视图

重新编译存储过程删除存储过程示例存储函数存储函数创建示例存储过程和存储函数的参数类型变量声明变量控制结构分支结构IF 语句CASE 语句SWITCH 语句循环语句LOOP 循环WHILE 循环FOR 循环REPEAT 循环退出语句游标静态游标: 只读游标静态游标又分为: 隐式游标和显示游标定义游标%FOUND 语句是否修改或查询到了记录, 是返回 TRUE, 否则返回 FALSE%NOTFOUND 语句是否未能成功修
复制链接

扫一扫