国产DM数据库实用SQL-随时更新

#创建表空间
create tablespace test datafile '/opt/dmdbms/data/DAMENG/gsptest.dbf' size 256;

#创建用户

create user test identified by 123456789 default tablespace test ;

#创建schema

create schema test AUTHORIZATION user;

#赋dba权限

grant dba,resource to test ;

#查询数据库版本

select * from v$version;

#查询服务器信息

select * from v$SYSTEMINFO;

#查询会话连接数

select * from v$sessions;

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

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

#查看数据库服务器配置参数

select * from v$dm_ini;

#备份目录下
dexp.exe test/123456789@ip file=c:\20191030.dmp log=c:\20191030.txt

#还原

dimp.exe test/123456789@ip fromuser=test touser=test file=c:\20191030.dmp log=c:\201910302.txt

#获取scheme下所有表名

SELECT table_name FROM information_schema.tables WHERE table_schema = ‘test’;

#获取当前登录用户schame下指定表 表结构信息

select * from user_tab_columns where table_name=‘abctest’

#获取主键

SELECT UC.TABLE_NAME, UCC.COLUMN_NAME FROM USER_CONSTRAINTS UC, USER_CONS_COLUMNS UCC WHERE UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME AND UC.TABLE_NAME = ‘abctest’ AND CONSTRAINT_TYPE = 'P' 

#获取表数据总量

select count(*) from abctest;

 #SYSDBA用户下查询所有表空间

select * from dba_tablespaces