select status,T.* from user_indexes T
where table_name='T_ADMIN_DEALER' --查詢表是否有了索引?

select username,default_tablespace from dba_users;--查看用戶和默認表空間的關系
select view_name from user_views --查看視圖名字

select * from user_source; --查詢所有函數和存儲過程

select * from all_users;--查詢所有用戶

select * from v$Session; --查看當前用戶連接

SELECT * FROM USER_ROLE_PRIVS;--查看用戶角色

select * from session_privs; --查看當前用戶權限

SELECT * FROM DBA_ROLE_PRIVS;--查看所有用戶所擁有的角色

select * from dba_roles; --查看所有角色

SELECT NAME FROM V$DATABASE; --查看數據庫名

select * from v$recovery_file_dest; -- 歸檔日志存放的位置

SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024)), 0) ts_size
FROM dba_tablespaces t, dba_data_files d
WHERE t.tablespace_name = d.tablespace_name
GROUP BY t.tablespace_name;? --查看表空間的名稱和大小

SELECT tablespace_name,
file_id,
file_name,
round(bytes / (1024 * 1024), 0) total_space
FROM dba_data_files
ORDER BY tablespace_name; --查看表空間物理文件的名稱及大小

SELECT segment_name,
tablespace_name,
r.status,
(initial_extent / 1024) initialextent,
(next_extent / 1024) nextextent,
max_extents,
v.curext curextent
FROM dba_rollback_segs r, v$rollstat v
WHERE r.segment_id = v.usn(+)
ORDER BY segment_name; --查看回滾段名稱及大小 （select segment_name,status from dba_rollback_segs;）

SELECT NAME FROM v$controlfile; --?查看控制文件?

SELECT MEMBER FROM v$logfile; --查看日志文件

SELECT SUM(bytes) / (1024 * 1024) AS free_space, tablespace_name
FROM dba_free_space
GROUP BY tablespace_name;
SELECT a.tablespace_name,
a.bytes total,
b.bytes used,
c.bytes free,
(b.bytes * 100) / a.bytes "% USED ",
(c.bytes * 100) / a.bytes "% FREE "
FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c
WHERE a.tablespace_name = b.tablespace_name
AND a.tablespace_name = c.tablespace_name; --查看表空間使用情況

SELECT owner, object_type, status, COUNT(*) count#
FROM all_objects
GROUP BY owner, object_type, status;--查看數據庫對象

SELECT version
FROM product_component_version
WHERE substr(product, 1, 6) = 'Oracle';--查看數據庫版本

SELECT created, log_mode, log_mode FROM v$database;
--1G=1024MB
--1M=1024KB
--1K=1024Bytes
--1M=11048576Bytes
--1G=1024*11048576Bytes=11313741824Bytes
SELECT a.tablespace_name "表空間名",
total "表空間大小",
free "表空間剩余大小",
(total - free) "表空間使用大小",
total / (1024 * 1024 * 1024) "表空間大小(G)",
free / (1024 * 1024 * 1024) "表空間剩余大小(G)",
(total - free) / (1024 * 1024 * 1024) "表空間使用大小(G)",
round((total - free) / total, 4) * 100 "使用率 %"
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；--查看數據庫的創建日期和歸檔方式?

?

管理用戶、角色和權限的DBA視圖：
DBA_USERS???????? 提供用戶的信息
DBA_ROLES???????? 顯示數據庫中所有角色
DBA_COL_PRIVS???? 顯示列級對象授權
DBA_ROLE_PRIVS??? 顯示用戶及其角色
DBA_SYS_PRIVS???? 顯示被授予系統權限的用戶
DBA_TAB_PRIVS???? 顯示用戶及他們在表上的權限
ROLE_ROLE_PRIVS?? 顯示授予角色的角色
ROLE_SYS_PRIVS??? 顯示授予角色的系統權限
ROLE_TAB_PRIVS??? 顯示授予角色的表權限
SESSION_PRIVS???? 顯示允許用戶使用的權限
SESSION_ROLES???? 顯示當前允許用戶使用的角色

?

SELECT NAME FROM V$DATABASE; --查看數據庫名

select instance_name,host_name,startup_time,status,database_status from v$instance; --檢查Oracle實例狀態

select group#,status,type,member from v$logfile; --檢查Oracle在線日志狀態

?

select resource_name,
max_utilization,
initial_allocation,
limit_value
from v$resource_limit;--檢查Oracle初始化文件中相關參數值

select sid, event, p1, p2, p3, WAIT_TIME, SECONDS_IN_WAIT
from v$session_wait
where event not like 'SQL%'
and event not like 'rdbms%';--檢查數據庫的等待事件

SELECT SQL_TEXT
FROM (SELECT * FROM V$SQLAREA ORDER BY DISK_READS)
WHERE ROWNUM <= 5;--Disk Read最高的SQL語句的獲取

SELECT *
FROM (SELECT PARSING_USER_ID
EXECUTIONS,
SORTS,
COMMAND_TYPE,
DISK_READS,
SQL_TEXT
FROM V$SQLAREA
ORDER BY DISK_READS DESC)
WHERE ROWNUM < 10;--最差的10條sql語句

SELECT *
FROM (SELECT *
FROM V$SYSTEM_EVENT
WHERE EVENT NOT LIKE 'SQL%'
ORDER BY TOTAL_WAITS DESC)
WHERE ROWNUM <= 5;--等待時間最多的5個系統等待事件的獲取


SELECT USERNAME,
SID,
OPNAME,
ROUND(SOFAR * 100 / TOTALWORK, 0) || '%' AS PROGRESS,
TIME_REMAINING,
SQL_TEXT
FROM V$SESSION_LONGOPS, V$SQL
WHERE TIME_REMAINING <> 0
AND SQL_ADDRESS = ADDRESS
AND SQL_HASH_VALUE = HASH_VALUE;--檢查運行很久的SQL

SELECT P.PID PID,
S.SID SID,
P.SPID SPID,
S.USERNAME USERNAME,
S.OSUSER OSNAME,
P.SERIAL# S_#,
P.TERMINAL,
P.PROGRAM PROGRAM,
P.BACKGROUND,
S.STATUS,
RTRIM(SUBSTR(A.SQL_TEXT, 1, 80)) SQLFROM V$PROCESS P,
V$SESSION S,
V$SQLAREA A WHERE P.ADDR = S.PADDR AND S.SQL_ADDRESS = A.ADDRESS(+) AND P.SPID LIKE '%&1%';--檢查消耗CPU最高的進程

SELECT segment_name table_name, COUNT(*) extents
FROM dba_segments
WHERE owner NOT IN ('SYS', 'SYSTEM')
GROUP BY segment_name
HAVING COUNT(*) = (SELECT MAX(COUNT(*))
FROM dba_segments
GROUP BY segment_name);--檢查碎片程度高的表

SELECT DF.TABLESPACE_NAME NAME,
DF.FILE_NAME "FILE",
F.PHYRDS PYR,
F.PHYBLKRD PBR,
F.PHYWRTS PYW,
F.PHYBLKWRT PBW
FROM V$FILESTAT F, DBA_DATA_FILES DF
WHERE F.FILE# = DF.FILE_ID
ORDER BY DF.TABLESPACE_NAME;--檢查表空間的I/O比例

SELECT SUBSTR(A.FILE#, 1, 2) "#",
SUBSTR(A.NAME, 1, 30) "NAME",
A.STATUS,
A.BYTES,
B.PHYRDS,
B.PHYWRTS
FROM V$DATAFILE A, V$FILESTAT B
WHERE A.FILE# = B.FILE#;-- 檢查文件系統的I/O比例

select sid,
serial#,
username,
SCHEMANAME,
osuser,
MACHINE,
terminal,
PROGRAM,
owner,
object_name,
object_type,
o.object_id
from dba_objects o, v$locked_object l, v$session s
where o.object_id = l.object_id
and s.sid = l.session_id;--檢查死鎖及處理

SELECT a.VALUE + b.VALUE logical_reads,
c.VALUE phys_reads,
round(100 * (1 - c.value / (a.value + b.value)), 4) hit_ratio
FROM v$sysstat a, v$sysstat b, v$sysstat c
WHERE a.NAME = 'db block gets'
AND b.NAME = 'consistent gets'
AND c.NAME = 'physical reads';--檢查緩沖區命中率（如果命中率低于90%則需加大數據庫參數db_cache_size。）

select sum(pinhits) / sum(pins) * 100 from v$librarycache; --檢查共享池命中率（如低于95%，則需要調整應用程序使用綁定變量，或者調整數據庫參數shared pool的大小。）

select name,value from v$sysstat where name like '%sort%'; --檢查排序區（如果disk/(memoty+row)的比例過高，則需要調整sort_area_size(workarea_size_policy=false)或pga_aggregate_target(workarea_size_policy=true)。)

select name,value from v$sysstat where name in ('redo entries','redo buffer allocation retries'); --檢查日志緩沖區(如果redo buffer allocation retries/redo entries超過1%，則需要增大log_buffer。)



select A.tablespace_name, (1 - (A.total) / B.total) * 100 used_percent
from (select tablespace_name, sum(bytes) total
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;--監控數據量的增長情況(根據本周每天的檢查情況找到空間擴展很快的數據庫對象,并采取相應的措施：

--刪除歷史數據
移動規定數據庫中至少保留6個月的歷史數據，所以以前的歷史數據可以考慮備份然后進行清除以便釋放其所占的資源空間。

---擴表空間

alter tablespace <tablespace_name> add datafile ‘<file>’ size <size> autoextend off;

注意：在數據庫結構發生變化時，如增加了表空間，增加了數據文件或重做日志文件這些操作，都會造成Oracle數據庫控制文件的變化，DBA應及進行控制文件的備份，備份方法是：

執行SQL語句：

alter database backup controlfile to '/home/backup/control.bak';
或：

alter database backup controlfile to trace;

這樣，會在USER_DUMP_DEST(初始化參數文件中指定)目錄下生成創建控制文件的SQL命令。)

select index_name, table_name, tablespace_name, status
From dba_indexes
Where owner = 'CTAIS2'
And status <> 'VALID';--檢查失效的索引(注：分區表上的索引status為N/A是正常的，如有失效索引則對該索引做rebuild，如：

alter index INDEX_NAME rebuild tablespace TABLESPACE_NAME; )

SELECT owner, constraint_name, table_name, constraint_type, status
FROM dba_constraints
WHERE status = 'DISABLE'
and constraint_type = 'P';--檢查不起作用的約束(如有失效約束則啟用，如：

alter Table TABLE_NAME Enable Constraints CONSTRAINT_NAME;)

SELECT owner, trigger_name, table_name, status FROM dba_triggers WHERE status = 'DISABLED';--檢查無效的trigger(如有失效觸發器則啟用，如：

alter Trigger TRIGGER_NAME Enable; )

轉載于:https://www.cnblogs.com/zy-303/p/9015305.html

總結

以上是生活随笔為你收集整理的oracle 的一些基础查询的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。