达梦数据库常用命令
以下举例操作的步骤按照下列目录结构:
达梦数据库安装目录在/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;