1.查詢實例信息
SELECT INST_ID, INSTANCE_NAME, TO_CHAR(STARTUP_TIME, 'YYYY-MM-DD HH24:MI:SS') AS STARTUP_TIME FROM GV$INSTANCE ORDER BY INST_ID;
2.查看是否歸檔
archive log list?
3.查看數據庫參數
SELECT NAME , TYPE , VALUE FROM V$PARAMETER ORDER BY NAME;
4.查看集群參數
SELECT INST_ID , NAME , VALUE FROM GV$PARAMETER ORDER BY INST_ID;
5.查看歸檔空間使用情況
SELECT?TO_CHAR(completion_time, 'YYYY-MM-DD') AS day,COUNT(*) AS logs_per_day,ROUND(SUM(blocks * block_size)/1024/1024) AS size_mb
FROM v$archived_log
GROUP BY TO_CHAR(completion_time, 'YYYY-MM-DD')
ORDER BY day DESC;
6.查看所有用戶大小
select sum(BYTES) /1024/1024/1024 as SIZE_G from dba_segments;
7.查看所有數據文件總大小
SELECT?ROUND(SUM(bytes)/1024/1024/1024, 2) AS DATAFILE_TOTAL_SIZE_GB
FROM?dba_data_files;
8.查看組件信息
col comp_name for a30
SELECT COMP_NAME , VERSION , STATUS FROM DBA_REGISTRY ORDER BY COMP_NAME;
9.查詢redo日志組
set linesize 300
col member format a60
SELECT t1.GROUP#,t1.MEMBER,t2.BYTES/1024/1024 as log_size_MB,t2.STATUS FROM v$logfile t1,v$log t2 WHERE t1.GROUP#=t2.GROUP#;
10.查詢重做日志組基本信息
SELECT?group# AS "GROUP#",thread# AS "THREAD#",sequence# AS "SEQUENCE#",bytes AS "BYTES",blocksize AS "BLOCKSIZE",members AS "MEMBERS",archived AS "ARC",status AS "STATUS",first_change# AS "FIRST_CHANGE#",TO_CHAR(first_time, 'DD-MON-YY') AS "FIRST_TIME",next_change# AS "NEXT_CHANGE#",TO_CHAR(next_time, 'DD-MON-YY') AS "NEXT_TIME",con_id AS "CON_ID"
FROM?v$log
ORDER BY?thread#, group#;
11.表空間使用情況
SELECT a.tablespace_name "tablespace",round(total / (1024 * 1024 * 1024), 2) "total(G)",round(free / (1024 * 1024 * 1024), 2) "free(G)",round((total - free) / (1024 * 1024 * 1024), 2) "used(G)",round((total - free) / total, 4) * 100 "used(%)"
FROM (SELECT tablespace_name, SUM(bytes) free FROM dba_free_space GROUP BY tablespace_name) a,(SELECT tablespace_name, SUM(bytes) total FROM dba_data_files GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name;
12.臨時表空間使用情況
select c.tablespace_name "tablespace_name",round(c.bytes / 1024 / 1024 / 1024, 2) "total(G)",round((c.bytes - d.bytes_used) / 1024 / 1024 / 1024, 2) "free(G)",round(d.bytes_used / 1024 / 1024 / 1024, 2) "used(G)",round(d.bytes_used * 100 / c.bytes, 4) || '%' "used(%)"
from (select tablespace_name, sum(bytes) bytesfrom dba_temp_filesGROUP by tablespace_name) c,(select tablespace_name, sum(bytes_cached) bytes_usedfrom v$temp_extent_poolGROUP by tablespace_name) d
where c.tablespace_name = d.tablespace_name;
13.查看分區和段空間管理方式
select tablespace_name , extent_management , allocation_type , segment_space_management from dba_tablespaces;
tablespace_name ? ? ? ? ?"表空間名",
extent_management ? ? ? ?"表空間管理方式", --默認LOCAL
allocation_type ? ? ? ? ?"分區管理方式", --默認SYSTEM，自動。
segment_space_management "段空間管理方式" --默認AUTO，自動。
14.表空間文件使用率
set linesize 200
col datafile for a50
select b.file_id "number", b.file_name "datafile" , b.tablespace_name "tablespace_name",?b.bytes/1024/1024 "total(Mb)", (b.bytes-sum(nvl(a.bytes,0)))/1024/1024 ?"used(MB)" ,substr((b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100,1,5) ?"used(%)"?
from dba_free_space a,dba_data_files b where a.file_id=b.file_id?
group by b.tablespace_name,b.file_id,b.file_name,b.bytes?
order by b.tablespace_name;
16.Oracle NLS參數查詢
SELECT parameter , value FROM nls_database_parameters ORDER BY parameter;
17.查詢所有賬戶狀態
col PROFILE for a30
col username for a30
SELECT username , account_status , lock_date , expiry_date , profile , created FROM dba_users ORDER BY account_status, username;
18.查詢所有Profile配置的SQL
col limit for a30
SELECT profile , resource_name , resource_type ,limit ,common,inherited,implicit FROM dba_profiles ORDER BY profile, resource_type, resource_name;
19.查詢角色授權的SQL語句
SELECT grantee, granted_role , admin_option , delegate_option ,default_role ,common ,inherited FROM dba_role_privs WHERE granted_role = 'DBA' ORDER BY grantee;
20.查詢集群狀態?
crsctl status res -t21.查看asm磁盤空間
?
su - gridasmcmd lsdg22.查看監聽狀態
su - grid?lsnrctl status
