一、达梦数据库统计信息的收集更新:
1、某用户下的所有索引:
DBMS_STATS.GATHER_SCHEMA_STATS('SSCKF',100,TRUE,'FOR ALL INDEXED SIZE AUTO');
2、某用户下所有字段(包括索引):
DBMS_STATS.GATHER_SCHEMA_STATS('SSCKF',100,TRUE,'FOR ALL COLUMNS SIZE AUTO');
3、某表下的所有字段:
DBMS_STATS.GATHER_TABLE_STATS('SSCKF','FO_ACCOUNTBILL',null,100,TRUE,'FOR ALL COLUMNS SIZE AUTO');
4、收集某一列的统计信息:
sp_col_stat_init('JXWOA','OA2_RECEIVEDOC','RD_STATE');
sp_col_stat_init('JXWOA','OA2_SENDDOC','SD_STATE');
----------------------------------------------------------------------------------------------------
二、常用系统视图查询
1、查询数据库版本
select * from v$version;
2、查询授权信息
select * from v$license;
3、查询服务器信息
select * from V$SYSTEMINFO;
4、查询会话连接信息
select * from v$sessions;
select count(*),state from v$sessions group by state;
select count(),clnt_ip from v$sessions group by clnt_ip;
5、查看数据库服务器配置参数
select * from v$dm_ini;
6、查询最近的sql执行记录
select * from v$sql_history;
7、查询某个用户下所有的表
select * from user_tables; --查询当前用户下所有的表
select * from all_tables where owner=’TEST’; --dba用户查询某个模式下的所有表
8、查询某个用户下所有表字段
select * from all_tab_cols where owner=’TEST’;
9、查看表注释
select * from ALL_TAB_COMMENTS where ower=’TEST’;
10、查看字段注释
select * from ALL_COL_COMMENTS where ower=’TEST’;
11、查询数据库基本信息
select '实例名称' 数据库选项,INSTANCE_NAME 数据库集群相关参数值 FROM v$instance union all
select '数据库版本',substr(svr_version,instr(svr_version,'(')) FROM v$instance union all SELECT '字符集',CASE SF_GET_UNICODE_FLAG() WHEN '0' THEN 'GBK18030' WHEN '1' then 'UTF-8' when '2' then 'EUC-KR' end union all
SELECT '页大小',cast(PAGE()/1024 as varchar) union all SELECT '簇大小',cast(SF_GET_EXTENT_SIZE() as varchar) union all
SELECT '大小写敏感',cast(SF_GET_CASE_SENSITIVE_FLAG() as varchar) union all
select '数据库模式',MODE$ from v$instance union all
select '唯一魔数',cast(permanent_magic as varchar) union all
select 'LSN',cast(cur_lsn as varchar) from v$rlog;