系统国产化之-达梦数据库基础操作命令

查询数据库版本

select * from v$version;

查看授权信息

select * from  v$license;

查看达梦数据库库名

select name,create_time from v$database;

查看大小写配置是否敏感

--大小敏感（1为大小写敏感，0为大小写不敏感）

SELECT SF_GET_CASE_SENSITIVE_FLAG();或SELECT CASE_SENSITIVE()

查询数据库最大连接

select SF_GET_PARA_VALUE(2,'MAX_SESSIONS');

查看达梦数据库当前状态

select status$ from v$instance;

查询授权截止有效期

select EXPIRED_DATE  from v$license;

查询数据文件位置

select GROUP_ID , ID ,path,STATUS$ from v$datafile;

查询表空间大小

select FILE_NAME,FILE_ID,TABLESPACE_NAME,BYTES/1024/1024||'M'  from dba_data_files;

查询表空间使用情况

select t1.NAME tablespace_name,t2.FREE_SIZE*SF_GET_PAGE_SIZE()/1024/1024 ||'M' free_space,t2.TOTAL_SIZE*SF_GET_PAGE_SIZE()/1024/1024 ||'M' total_space,t2.FREE_SIZE*100/t2.total_size "% FREE" from V$TABLESPACE t1, V$DATAFILE t2 where t1.ID=t2.GROUP_ID;

查询所有SCHEMA

 select * from dba_objects where object_type='SCH';

查询所有用户

select username from dba_users;

切换SCHEMA

set schema "testSchema";

创建SCHEMA

CREATE SCHEMA "testSchema";

查询模式下全量表名称

select TABLE_NAME from all_tables WHERE OWNER='testSchema';

删除SCHEMA

drop schema "testSchema";

列注释

COMMENT ON COLUMN testSchema.peoples.role_id is '角色Id';

查询列注释

select * from SYSCOLUMNCOMMENTS where SCHNAME='testSchema' and TVNAME='peoples' and COLNAME='role_id';

表注释

comment on table testSchema.peoples is '这是一个表注释';

查询表注释

select * from SYSTABLECOMMENTS where SCHNAME='testSchema' and TVNAME='peoples';

创建表空间

CREATE TABLESPACE MANAGEMENT DATAFILE 'MANAGEMENT.DBF' SIZE 128;

查看表空间

select * from dba_data_files;select * from dba_tablespaces;select * from v$tablespace;

创建用户

CREATE USER testUser IDENTIFIED BY "testUsersPwd" DEFAULT TABLESPACE MANAGEMENT;GRANT DBA TO testSchema;

查看当前实例的连接数

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

来源地址：https://blog.csdn.net/wangguoqing_it/article/details/126400007