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

1、查询数据库版本

select * from v$version;

2、查看授权信息

select * from  v$license;

3、查看达梦数据库库名

select name,create_time from v$database;

4、查看大小写配置是否敏感

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

SELECT SF_GET_CASE_SENSITIVE_FLAG();
或
SELECT CASE_SENSITIVE()

5、查询数据库最大连接

select SF_GET_PARA_VALUE(2,'MAX_SESSIONS');

6、查看达梦数据库当前状态

select status$ from v$instance;

7、查询授权截止有效期

select EXPIRED_DATE  from v$license;

8、查询数据文件位置

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

9、查询表空间大小

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

10、查询表空间使用情况

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;

11、查询所有SCHEMA

 select * from dba_objects where object_type='SCH';

12、查询所有用户

select username from dba_users;

13、切换SCHEMA

set schema "testSchema";

14、创建SCHEMA

CREATE SCHEMA "testSchema";

15、查询模式下全量表名称

select TABLE_NAME from all_tables WHERE OWNER='testSchema';

16、删除SCHEMA

drop schema "testSchema";

17、列注释

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

18、查询列注释

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

19、表注释

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

20、查询表注释

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

21、创建表空间

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

22、查看表空间

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

23、创建用户

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

24、查看当前实例的连接数

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