Oracle常用命令

標籤：div   復原   變數   size   hrp   member   where   control   資料表空間   

Oracle常用命令

• 查詢oracle版本

select * from v$version;

• 比較命令decode

select decode(sign(變數1 - 變數2), -1, 變數1, 變數2) from dual; --取較小值

• 查看錶大小

select segment_name,       tablespace_name,       bytes B,       bytes / 1024 KB,       bytes / 1024 / 1024 MB  from user_segments where segment_type = ‘TABLE‘;

• 查看死結與解鎖

　　查詢死結：

SELECT l.session_id sid,       s.serial#,       l.locked_mode,       l.oracle_username,       l.os_user_name,       s.machine,       s.terminal,       o.object_name,       s.logon_time  FROM v$locked_object l, all_objects o, v$session s WHERE l.object_id = o.object_id   AND l.session_id = s.sid ORDER BY sid, s.serial#;

　　解鎖：

alter system kill session ‘sid,serial#‘; --結合上面查詢死結結果使用

• 查詢資料庫正在使用使用者資訊

select a."MACHINE", a."PROGRAM", a."PREV_EXEC_START", a.terminal  from v$session a where a.username = ‘HRPDEV‘;

• 查看錶空間名稱及大小

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 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;

 

Oracle常用命令