查詢數據庫表空間數據文件使用大小限制

DECLARE
K INT:=(SELECT cast(PAGE()/1024 as varchar));
BEGIN
SELECTF."PATH" 數據文件                ,F.CLIENT_PATH,G.NAME 所屬表空間,F.MAX_SIZE||'M' 文件擴展限制,(CASE F.AUTO_EXTEND WHEN '1' THEN '是'  ELSE '否' END) 文件自動擴容,(CASE F.NEXT_SIZE WHEN '0' THEN '默認值' ELSE F.NEXT_SIZE||'M' END) 文件下次擴容值,F.TOTAL_SIZE*K/1024 ||'M' 文件當前大小 ,ROUND((F.FREE_SIZE * K/1024 /(F.TOTAL_SIZE * K/1024.0)),2)*100 ||'%' AS 空閑率,(1-ROUND((F.FREE_SIZE * K/1024 /(F.TOTAL_SIZE * K/1024.0)),2))*100 ||'%' AS 使用率   
FROM    SYS."V$DATAFILE" F LEFT JOIN SYS.V$TABLESPACE G ON F.GROUP_ID=G.ID ORDER BY 1;
END;/

查詢系統所有的賬號
SQL> select username,account_status,created,default_tablespace,default_index_tablespace,temporary_tablespace from dba_users;

查詢所有的數據庫對象

SELECTA.USERNAME "用戶名",(SELECT COUNT(1) FROM DBA_TABLES B WHERE B.OWNER = A.USERNAME) "表數量",( SELECT COUNT(1) FROM DBA_VIEWS G WHERE G.OWNER = A.USERNAME ) "視圖數量",( SELECT COUNT(1) FROM DBA_TRIGGERS H WHERE H.OWNER = A.USERNAME ) "觸發器數量",( SELECT COUNT(DISTINCT I.NAME) FROM DBA_SOURCE I WHERE I.OWNER = A.USERNAME AND I.TYPE = 'FUNCTION' ) "函數數量",( SELECT COUNT(1) FROM DBA_SEQUENCES J WHERE J.SEQUENCE_OWNER = A.USERNAME ) "序列數量",( SELECT COUNT(DISTINCT L.NAME) FROM DBA_SOURCE L WHERE L.OWNER = A.USERNAME AND L.TYPE = 'PROCEDURE' ) "存儲過程數量",( SELECT COUNT(1) FROM DBA_DB_LINKS M WHERE M.OWNER = A.USERNAME ) "DBLINK數量",( SELECT COUNT(1) FROM DBA_INDEXES I WHERE UNIQUENESS = 'UNIQUE' AND OWNER =A.USERNAME OR INDEX_NAME NOT LIKE 'SYS_%' AND OWNER =A.USERNAME) "索引數量",( SELECT COUNT(1) FROM DBA_OBJECTS WHERE OBJECT_TYPE='TYPE' AND OWNER =A.USERNAME ) "自定義類型",( SELECT COUNT(1) FROM DBA_OBJECTS WHERE OBJECT_TYPE='PACKAGE' AND OWNER =A.USERNAME) "PKG數量"
FROMDBA_USERS A WHERE A.USERNAME IN ('MMIS_INNOVATION','MMIS_HD','SENYI','WANGHAI','GK_INNOVATION','SCM_INNOVATION');

查看數據庫集群相關參數值

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;

查看達夢錯誤碼匯總
select * from v$err_info;

查看數據庫的到期時間
select * from v$license;

查詢 SQL 執行記錄
SELECT * FROM V$SQL_HISTORY;

查看建庫時的字符集
select unicode();
–根據返回值進行判斷
–0 表示 GB18030，1 表示 UTF-8，2 表示 EUC-KR

如何獲取達夢當前模式名
SQL> SELECT SF_GET_SCHEMA_NAME_BY_ID(CURRENT_SCHID());

查看實例是否是空格自動填充
select * from v$dm_ini where para_name = ‘BLANK_PAD_MODE’;

查看當前的密碼策略
select PWD_POLICY,SF_GET_USERNAME_BY_ID(ID),* from SYSUSERS;

查看數據庫是否開啟加密通信
SELECT * FROM V$DM_INI WHERE PARA_NAME= ‘COMM_ENCRYPT_NAME’;

#如未開啟，可以用以下命令開啟SP_SET_PARA_STRING_VALUE(2,‘COMM_ENCRYPT_NAME’,‘DES_OFB’);

查看是否是免密登錄
select * from v$dm_ini where para_name=‘ENABLE_LOCAL_OSAUTH’;
如果未開啟，可以用SP_SET_PARA_VALUE(2,‘ENABLE_LOCAL_OSAUTH’,1);

查看備份路徑
select para_name,para_value from v$dm_ini where para_name=‘BAK_PATH’;

查找出活動會話中執行時間大于 1S 的 SQL

select * from (
select timestampdiff(second,s.last_recv_time,sysdate) t ,s.* 
from v$sessions s  where state='ACTIVE')
where t > 1

查看對象或資源發生等待問題
SELECT * FROM v$sessions WHERE state=‘ACTIVE’
AND dbms_lob.substr(sf_get_session_sql(sess_id)) LIKE ‘%語句片段%’；
查看執行效率低的SQL

SELECT*
FROM(SELECTsess_id                                         ,sql_text                                        ,datediff (ss, last_recv_time, SYSDATE) Y_EXETIME,SF_GET_SESSION_SQL (SESS_ID) fullsql            ,clnt_ipFROMV$SESSIONSWHERESTATE = 'ACTIVE')
WHEREY_EXETIME >= 2; 

查看是否有異常SQL

select top 60datediff(ss, last_send_time, sysdate) as time,sess_id,run_status,sql_text,auto_cmt,user_name,appname,clnt_ip,trx_id
from v$sessions
order by time desc;

查看句柄數是否滿了

selectsql_text               ,state                  ,n_stmt "句柄的容量"         ,n_used_stmt as "使用的句柄數",curr_sch               ,user_name              ,trx_id                 ,create_time            ,clnt_type              ,clnt_ip                ,run_status
fromv$sessions;

查看是否用戶設置了最大空閑時間參數
select b.username 賬號,b.password_versions 密碼策略,a.sess_per_user 同時擁有的會話數,a.conn_idle_time 會話訪問服務器的時間上限 from sysusers a,dba_users b where a.id=b.user_id;

查詢當前數據庫實例是否有阻塞

select * from v$trxwait ;
select sess_id,sql_text,trx_id from v$sessions;
sp_close_session(sess_id)；

#查看死鎖歷史select * from V$DEADLOCK_HISTORY;

WITH LOCKSAS (SELECT O.NAME,L.*,S.SESS_ID,S.SQL_TEXT,S.CLNT_IP,S.LAST_SEND_TIMEFROM V$LOCK L, SYSOBJECTS O, V$SESSIONS SWHERE L.TABLE_ID = O.ID AND L.TRX_ID = S.TRX_ID),LOCK_TRAS (SELECT TRX_ID WT_TRXID, TID BLK_TRXIDFROM LOCKSWHERE BLOCKED = 1),RESAS (SELECT SYSDATE STATTIME,T1.NAME,T1.SESS_ID WT_SESSID,S.WT_TRXID,T2.SESS_ID BLK_SESSID,S.BLK_TRXID,T2.CLNT_IP,SF_GET_SESSION_SQL (T1.SESS_ID) FULSQL,DATEDIFF (SS, T1.LAST_SEND_TIME, SYSDATE) SS,T1.SQL_TEXT WT_SQLFROM LOCK_TR S, LOCKS T1, LOCKS T2WHERE     T1.LTYPE = 'OBJECT'AND T1.TABLE_ID <> 0AND T2.LTYPE = 'OBJECT'AND T2.TABLE_ID <> 0AND S.WT_TRXID = T1.TRX_IDAND S.BLK_TRXID = T2.TRX_ID)
SELECT DISTINCT WT_SQL,CLNT_IP,SS,WT_TRXID,BLK_TRXIDFROM RES;

查看死鎖

SELECT O.NAME,L.* FROM V$LOCK L,SYSOBJECTS O WHERE L.TABLE_ID=O.ID AND BLOCKED=1;