达梦数据库常用命令

达梦数据库常用命令

以下举例操作的步骤按照下列目录结构：
达梦数据库安装目录在/home/dmdba/dmdbms下
数据文件以及各个配置文件存放目录/data/DAMENG下

1、查看会话信息

select count(*) from v$sessions where state='ACTIVE';

select user_name,appname,clnt_host,clnt_ip from v$sessions;

select user_name, appname, clnt_ip, count(*) from  v$sessions  group by user_name,  appname, clnt_ip;

2、处理锁等待

①查看被挂起的事务（TRX_ID）
SELECT VTW.ID AS TRX_ID, VS.SESS_ID ,VS.SQL_TEXT,VS.APPNAME ,VS.CLNT_IP FROM V$TRXWAIT VTW LEFT JOIN V$TRX VT ON
(VTW.ID=VT.ID) LEFT JOIN V$SESSIONS VS ON (VT.SESS_ID=VS.SESS_ID);
②通过挂起事务ID（TRX_ID）找到它等待的事务（WAIT_FOR_ID）。
SELECT WAIT_FOR_ID,WAIT_TIME FROM V$TRXWAIT WHERE ID=321646; 
③通过等待事务ID（WAIT_FOR_ID）定位到连接以及执行的语句
SELECT VT.ID AS TRX_ID,VS.SESS_ID,VS.SQL_TEXT ,VS.APPNAME,VS.CLNT_IP FROM
V$TRX VT LEFT JOIN V$SESSIONS VS ON (VT.SESS_ID=VS.SESS_ID) WHERE
VT.ID = 321643;
--SP_CLOSE_SESSION关闭等待事务（SESS_ID）
SP_CLOSE_SESSION(142344256);

3、统计信息收集

①模式统计信息收集
一般在首次迁移完成后使用，如下：
DBMS_STATS.GATHER_SCHEMA_STATS('模式名',100,TRUE,'FOR ALL COLUMNS SIZE AUTO');
注：如果数据量较大，该过程可能较慢，请耐心等待
②表统计信息收集
DBMS_STATS.gather_table_stats('模式名','表名');
③索引统计信息收集
DBMS_STATS.gather_index_stats('模式名','索引名');

4、查看数据文件和表空间对应关系

①select ts.NAME,df.PATH from v$tablespace AS ts,v$datafile AS df where ts.id=df.group_id;
②查看表空间及表空间的物理文件的名称及大小
select t.name tablespace_name,t.id file_id,d.path file_name,d.total_size*SF_GET_PAGE_SIZE()/1024/1024||'M' total_space
from v$tablespace t, v$datafile d where t.id=d.group_id;  
③查看表空间大小以及使用率
select a.tablespace_name, a.total_mb, round(b.free_mb, 2) free_mb, TO_CHAR(ROUND((a.total_mb - b.free_mb) / a.total_mb * 100, 2),'990.99') || '%' "usage %" from (select tablespace_name, sum(bytes) / 1024 / 1024 total_mb from dba_data_files group by tablespace_name) a,(select tablespace_name, sum(bytes) / 1024 / 1024 free_mb from dba_free_space group by tablespace_name) b,dba_tablespaces d where a.tablespace_name = b.tablespace_name(+) and a.tablespace_name = d.tablespace_name(+) order by 1,4 desc;

5、授权相关

①授权给其他用户本用户下的所有表的操作权限
declare
begin
for rec in (select OWNER,TABLE_NAME FROM dba_tables where OWNER='模式名')
loop
execute immediate 'grant all on '||rec.OWNER||'.'||'"'||rec.TABLE_NAME||'"'||' to username';
end loop;
end;
/

②授权给其他用户本用户下的所有序列的select权限
declare
begin
for rec in (select SEQUENCE_OWNER,SEQUENCE_NAME FROM dba_sequences where SEQUENCE_OWNER='HTTC')
loop
execute immediate 'grant select on '||rec.SEQUENCE_OWNER||'.'||'"'||rec.SEQUENCE_NAME||'"'||' to username';
end loop;
end;
/

③授权给其他用户本用户下的所有视图的操作权限
declare
begin
for rec in (select OWNER,VIEW_NAME FROM dba_views where OWNER='HTTC')
loop
execute immediate 'grant all on '||rec.OWNER||'.'||'"'||rec.VIEW_NAME||'"'||' to username';
end loop;
end;
/

④授权给其他用户本用户下的所有存储过程的执行权限
declare
begin
for rec in (select OWNER,OBJECT_NAME FROM dba_procedures where OWNER='HTTC')
loop
execute immediate 'grant execute on '||rec.OWNER||'.'||'"'||rec.OBJECT_NAME||'"'||' to username';
end loop;
end;
/

6、更换新license

cd /home/dmdba/dmdbms/bin下，将已经存在的dm.key文件改名为dmbak.key，然后将新的授权文件放在该bin目录下，将文件名改为dm.key。
如果数据库已经在运行，放完dm.key文件之后，登录进去数据库执行key文件生效语句   sp_load_lic_info();

7、sql语句查看数据库初始化相关参数

--簇大小
SELECT SF_GET_EXTENT_SIZE();
--页大小
SELECT SF_GET_PAGE_SIZE ();
--大小写是否敏感
SELECT SF_GET_CASE_SENSITIVE_FLAG();
--字符集
SELECT SF_GET_UNICODE_FLAG (); 

8、查看当前数据库中是否配置了IP登录限制

SELECT B.USERNAME,A.ALLOW_ADDR,A.NOT_ALLOW_ADDR FROM SYSUSERS A,ALL_USERS B WHERE A.ID=B.USER_ID AND B.USERNAME='USERNAME';

9、查看用户下表的数量以及表的数据量

①表的数量
create table dm_tables(tab_owner varchar(100),tab_name varchar(100),tab_count int);

declare
begin
 for rec in(select owner,object_name from all_objects where
owner='SYSDBA'and object_type='TABLE')loop
 begin
 execute immediate 'insert into dm_tables select '''|| rec.owner 
||''','''|| rec.object_name ||''',count(*) from '|| rec.owner || '.'
|| rec.object_name;
 exception when others then
   print rec.owner || '.' || rec.object_name || 'get count error';
 end;
 end loop;
end;
/
select * from dm_tables;

②各个表的数据量
SELECT
        OWNER                           as "用户名",
        sum(BYTES) / 1024 / 1024 as "所有表的大小(MB)"
FROM
        DBA_SEGMENTS
WHERE
        SEGMENT_NAME in
        (
                select t2.OBJECT_NAME from dba_objects t2 where t2.OBJECT_TYPE = 'TABLE'
        )
group by
        OWNER
order by
        2 desc;

10、获取数据库对象定义

①使用DBMS_METADATA.GET_DDL获取数据库对象定义
--开启系统包
sp_create_system_packages(1);
select dbms_metadata.get_ddl(‘类型’,’对象名’,’模式名’);
--类型：包括表、视图、索引、约束、表空间、存储模块等；
--模式名：针对常见数据库对象，都需要模式名，对于没有模式归属的对象，不需要指明模式名。
--此方法不能使用在MPP环境下

②使用SP_TABLEDEF获取表的定义
CALL SP_TABLEDEF('模式名', '表名');

③使用INDEXDEF获取索引的定义
--创建索引后，可以通过 INDEXDEF 系统函数查看索引的定义；INDEX_ID 为索引 ID，PREFLAG 表示返回信息中是否增加模式名前缀，取值1或0。
SELECT  INDEXDEF(INDEX_ID int, PREFLAG int);

④使用SP_VIEWDEF和SYSTEXTS获取视图的定义
--查看视图定义信息
select
        b.id 对象ID,
        b.seqno 视图定义0_视图查询子句1,
        b.txt 文本信息
from
        sysobjects a,
        sys.systexts b
where
        a.id=b.id
    and a.name ='EMP';
--视图的定义信息也可以通过 SP_VIEWDEF 系统过程来查看。对于物化视图而言，其定义只有通过该系统函数才能完整地获取
CALL  SP_VIEWDEF('模式名', '视图名'); 

⑤使用DBMS_METADATA和SYSTEXTS SYSOBJECTS获取触发器、过程、函数的定义
--查看触发器、过程、函数的创建脚本
select DBMS_METADATA.GET_DDL('TABLE','TEST','GUOQI');

--查看触发器、过程、函数的创建脚本
select  t1.TXT, t2.SUBTYPE$,t2.NAME from SYSTEXTS t1,SYSOBJECTS t2 where t2.id=t1.id;

11、查看数据库中所有类型的对象的数量

--当前用户可以查看的所有类型的对象统计信息
select object_type, count(*) from "ALL_OBJECTS" group by object_type;
--查看当前模式下的拥有的所有类型的对象统计信息
select object_type,count(1) from "USER_OBJECTS" group by object_type;

12、查看数据库中所有用户

select
        username 用户名,
        ACCOUNT_STATUS 账号状态,
        EXPIRY_DATE 密码有效期,
        DEFAULT_TABLESPACE 默认表空间
from
        dba_users
order by 1;

13、查找含有大字段的表

SELECT
        OWNER ,
        TABLE_NAME ,
        COLUMN_NAME,
        DATA_TYPE
FROM
        DBA_TAB_COLUMNS
WHERE
        OWNER      ='SYS'
    AND DATA_TYPE IN ('TEXT', 'BLOB', 'CLOB', 'LONGVARCHAR', 'IMAGE', 'LONGVARBINARY', 'BFILE');
--需要指定模式名

14、更改用户名密码

--本用户登录或者SYSDBA用户登录执行以下语句
ALTER USER USERNAME IDENTIFIED BY "新密码";

15、用户解锁

--SYSDBA登录执行解锁命令
ALTER USER USERNAME ACCOUNT UNLOCK;