1 、查看所有表空间大小及其使用情况
SELECT a.tablespace_name ,
round(total/ (10241024),2) Total_M,
round(free/ (10241024),2) Free_M,
round((total- free)/(1024*1024),2) Used_M ,
round((total - free) / total, 4) * 100 "Used_%"
FROM
(SELECT tablespace_name,SUM(bytes) free FROM dba_free_space GROUP BY tablespace_name) a,
(SELECT tablespace_name,SUM(bytes) total,MAXBYTES FROM dba_data_files GROUP BY tablespace_name,MAXBYTES) b
WHERE a.tablespace_name = b.tablespace_name
order by 5 desc;
为某个表空间增加数据数据文件
alter tablespace DMHR add datafile '/opt/dmdbms/data/DAMENG/DMHR02.DBF' size 32G autoextend on;

alter tablespace DMHR RESIZE DATAFILE '/opt/dmdbms/data/DAMENG/DMHR03.DBF' to 33G;
开启和关闭表空间自动扩展
alter tablespace DMHR DATAFILE '/opt/dmdbms/data/DAMENG/DMHR03.DBF' autoextend on;
alter tablespace DMHR DATAFILE '/opt/dmdbms/data/DAMENG/DMHR03.DBF' autoextend off;
alter tablespace DMHR RESIZE DATAFILE '/opt/dmdbms/data/DAMENG/DMHR03.DBF' to 33 ;
表空间数据文件情况
select tablespace_name,STATUS,file_name,file_id ,bytes/1024/1024,MAXBYTES/1024/1024,AUTOEXTENSIBLE from dba_data_files;

各个业务用户数据量
select owner，sum(bytes/1024/1024) from dba_segments group by owner;

select * from v$DM_INI where PARA_NAME='GLOBAL_PAGE_SIZE';

select
A.FAILED_NUM,
A.LIFE_TIME,
A.REUSE_TIME,
A.LOCK_TIME,
A.GRACE_TIME,
B.USERNAME
from SYSUSERS A,DBA_USERS B
where A.ID=B.USER_ID;

达梦数据库用户访问限制
IP访问限制
允许 IP 和禁止 IP 用于控制此登录是否可以从某个 IP 访问数据库，其中禁止 IP 优先。在设置 IP 时，设置的允许和禁止 IP 需要用双引号括起来，中间用逗号隔开，如"192.168.0.29", "192.168.0.30"，也可以利用来设置网段，如"192.168.0."。
1.1 设置ip白名单
alter user "AAA" allow_ip "192.168.10.72" ,"192.168.10.73";
1.2 设置ip黑名单
alter user "AAA" not_allow_ip "127.0.0.1" ;
3 取消限制
alter user "AAA" not_allow_ip null;
alter user "AAA" allow_ip null
alter user "AAA" allow_datetime null;
alter user "AAA" not_allow_datetime null;
4 查看所有用户访问规则
Select A.allow_addr,A.not_allow_addr,A.allow_dt,A.not_allow_dt,B.USERNAME from SYSUSERS A ,DBA_USERS B WHERE A.ID=B.USER_ID;

3 、查看指定用户占用的空间大小，如查看SYSDBA用户占用的空间大小
SELECT USER_USED_SPACE('SYSDBA') * PARA_VALUE / 1024 / 1024 "USER_USED_SPACE(MB)"
FROM V$DM_INI WHERE PARA_NAME LIKE 'GLOBAL_PAGE_SIZE';
4 、查看指定表占用的空间大小，如查看DMHR用户下CITY表占用的空间大小
SELECT TABLE_USED_PAGES('DMHR', 'CITY') * PARA_VALUE / 1024 / 1024 "USER_USED_SPACE(MB)"
FROM V$DM_INI
WHERE PARA_NAME LIKE 'GLOBAL_PAGE_SIZE';
5 、查看指定表已使用的空间大小，如查看DMHR用户下CITY表已使用的空间大小
SELECT TABLE_USED_SPACE('DMHR', 'CITY') * PARA_VALUE / 1024 / 1024 "USER_USED_SPACE(MB)"
FROM V$DM_INI
WHERE PARA_NAME LIKE 'GLOBAL_PAGE_SIZE';
6 、查看指定索引占用的空间大小，如查看DMHR用户下EMPLOYEE表已上的索引INDEX33555541占用的空间大小，达梦数据库查询索引空间占用是以索引的ID为传入参数，可以通过以下SQL查询索引的ID
SELECT NAME, ID
FROM SYSOBJECTS
WHERE NAME IN (SELECT INDEX_NAME
FROM DBA_INDEXES
WHERE OWNER = 'DMHR'
AND TABLE_NAME = 'EMPLOYEE')
AND INDEX_NAME = 'INDEX33555541';
然后根据上面查到的ID来查询对应的索引占用的空间大小
SELECT INDEX_USED_PAGES(33555524) * PARA_VALUE / 1024 / 1024 "USER_USED_SPACE(MB)"
FROM V$DM_INI
WHERE PARA_NAME LIKE 'GLOBAL_PAGE_SIZE';
7 、查看指定索引已使用的空间大小，如查看DMHR用户下EMPLOYEE表已上的索引INDEX33555541使用的空间大小，达梦数据库查询索引空间占用是以索引的ID为传入参数，可以通过以下SQL查询索引的ID
SELECT NAME, ID
FROM SYSOBJECTS
WHERE NAME IN (SELECT INDEX_NAME
FROM DBA_INDEXES
WHERE OWNER = 'DMHR'
AND TABLE_NAME = 'EMPLOYEE')
AND INDEX_NAME = 'INDEX33555541';
然后根据上面查到的ID来查询对应的索引使用的空间大小
SELECT INDEX_USED_SPACE(33555524) * PARA_VALUE / 1024 / 1024 "USER_USED_SPACE(MB)"
FROM V$DM_INI
WHERE PARA_NAME LIKE 'GLOBAL_PAGE_SIZE';

为某个表空间增加数据数据文件
alter tablespace DMHR add datafile '/opt/dmdbms/data/DAMENG/DMHR02.DBF' size 32G autoextend on;

alter tablespace DMHR RESIZE DATAFILE '/opt/dmdbms/data/DAMENG/DMHR03.DBF' to 33G;
开启和关闭表空间自动扩展
alter tablespace DMHR DATAFILE '/opt/dmdbms/data/DAMENG/DMHR03.DBF' autoextend on;
alter tablespace DMHR DATAFILE '/opt/dmdbms/data/DAMENG/DMHR03.DBF' autoextend off;
alter tablespace DMHR RESIZE DATAFILE '/opt/dmdbms/data/DAMENG/DMHR03.DBF' to 33 ;

select tablespace_name,STATUS,file_name,file_id ,bytes/1024/1024,MAXBYTES/1024/1024,AUTOEXTENSIBLE from dba_data_files;