IT貓撲網(wǎng):您身邊最放心的安全下載站! 最新更新|軟件分類|軟件專題|手機(jī)版|論壇轉(zhuǎn)貼|軟件發(fā)布

您當(dāng)前所在位置:首頁數(shù)據(jù)庫Oracle → Oracle維護(hù)常用SQL語句匯總

Oracle維護(hù)常用SQL語句匯總

時間:2015/6/28來源:IT貓撲網(wǎng)作者:網(wǎng)管聯(lián)盟我要評論(0)

  如何遠(yuǎn)程判斷oracle數(shù)據(jù)庫的安裝平臺

  select * from v$version;

  查看表空間的使用情況

  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;

  1、查看表空間的名稱及大小

  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;

  2、查看表空間物理文件的名稱及大小

  select tablespace_name, file_id, file_name,

  round(bytes/(1024*1024),0) total_space

  from dba_data_files

  order by tablespace_name;

  3、查看回滾段名稱及大小

  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 ;

  4、查看控制文件

  select name from v$controlfile;

  5、查看日志文件

  select member from v$logfile;

  6、查看表空間的使用情況

  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;

  7、查看數(shù)據(jù)庫庫對象

  select owner, object_type, status, count(*) count# from all_objects group by owner, object_type, status;

  8、查看數(shù)據(jù)庫的版本

  Select version FROM Product_component_version

  Where SUBSTR(PRODUCT,1,6)='Oracle';

  9、查看數(shù)據(jù)庫的創(chuàng)建日期和歸檔方式

  Select Created, Log_Mode, Log_Mode From V$Database;

  10、如何遠(yuǎn)程判斷Oracle數(shù)據(jù)庫的安裝平臺

  select * from v$version;

  11、查看數(shù)據(jù)表的參數(shù)信息

  SELECT partition_name, high_value, high_value_length, tablespace_name,

  pct_free, pct_used, ini_trans, max_trans, initial_extent,

  next_extent, min_extent, max_extent, pct_increase, FREELISTS,

  freelist_groups, LOGGING, BUFFER_POOL, num_rows, blocks,

  empty_blocks, avg_space, chain_cnt, avg_row_len, sample_size,

  last_analyzed

  FROM dba_tab_partitions

  --WHERE table_name = :tname AND table_owner = :towner

  ORDER BY partition_position

#p#副標(biāo)題#e#

  12、查看還沒提交的事務(wù)

  select * from v$locked_object;

  select * from v$transaction;

  14、回滾段查看

  select rownum, sys.dba_rollback_segs.segment_name Name, v$rollstat.extents

  Extents, v$rollstat.rssize Size_in_Bytes, v$rollstat.xacts XActs,

  v$rollstat.gets Gets, v$rollstat.waits Waits, v$rollstat.writes Writes,

  sys.dba_rollback_segs.status status from v$rollstat, sys.dba_rollback_segs,

  v$rollname where v$rollname.name(+) = sys.dba_rollback_segs.segment_name and

  v$rollstat.usn (+) = v$rollname.usn order by rownum

  15、捕捉運行很久的SQL

  column username format a12

  column opname format a16

  column progress format a8

  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

  /

  16。查看數(shù)據(jù)表的參數(shù)信息

  SELECT   partition_name, high_value, high_value_length, tablespace_name,

  pct_free, pct_used, ini_trans, max_trans, initial_extent,

  next_extent, min_extent, max_extent, pct_increase, FREELISTS,

  freelist_groups, LOGGING, BUFFER_POOL, num_rows, blocks,

  empty_blocks, avg_space, chain_cnt, avg_row_len, sample_size,

  last_analyzed

  FROM dba_tab_partitions

  --WHERE table_name = :tname AND table_owner = :towner

  ORDER BY partition_position

  17。查找object為哪些進(jìn)程所用

  select

  p.spid,

  s.sid,

  s.serial# serial_num,

  s.username user_name,

  a.type object_type,

  s.osuser os_user_name,

  a.owner,

  a.object object_name,

  decode(sign(48 - command),

  1,

  to_char(command), 'Action Code #' || to_char(command) ) action,

  p.program oracle_process,

  s.terminal terminal,

  s.program program,

  s.status session_status

  from v$session s, v$access a, v$process p

  where s.paddr = p.addr and

  s.type = 'USER' and

  a.sid = s.sid   and

  a.object='SUBSCRIBER_ATTR'

  order by s.username, s.osuser

  18。耗資源的進(jìn)程(top session)

  select s.schemaname schema_name,    decode(sign(48 - command), 1,

  to_char(command), 'Action Code #' || to_char(command) ) action,    status

  session_status,   s.osuser os_user_name,   s.sid,   p.spid ,   s.serial# serial_num,

  nvl(s.username, '[Oracle process]') user_name,   s.terminal terminal,

  s.program program,   st.value criteria_value from v$sesstat st,   v$session s , v$process p

  where st.sid = s.sid and   st.statistic# = to_number('38') and   ('ALL' = 'ALL'

  or s.status = 'ALL') and p.addr = s.paddr order by st.value desc, p.spid asc, s.username asc, s.osuser asc

#p#副標(biāo)題#e#

  19。查看鎖(lock)情況

  select /*+ RULE */ ls.osuser os_user_name,   ls.username user_name,

  decode(ls.type, 'RW', 'Row wait enqueue lock', 'TM', 'DML enqueue lock', 'TX',

  'Transaction enqueue lock', 'UL', 'User supplied lock') lock_type,

  o.object_name object,   decode(ls.lmode, 1, null, 2, 'Row Share', 3,

  'Row Exclusive', 4, 'Share', 5, 'Share Row Exclusive', 6, 'Exclusive', null)

  lock_mode,    o.owner,   ls.sid,   ls.serial# serial_num,   ls.id1,   ls.id2

  from sys.dba_objects o, (   select s.osuser,    s.username,    l.type,

  l.lmode,    s.sid,    s.serial#,    l.id1,    l.id2   from v$session s,

  v$lock l   where s.sid = l.sid ) ls where o.object_id = ls.id1 and    o.owner

  <> 'SYS'   order by o.owner, o.object_name

  根據(jù)sid查是哪臺電腦的鏈接

  column osuser format a15

  column username format a10

  column machine format a30

  select osuser,machine,username,sid,serial# from v$session where sid='128';

  根據(jù)sid查對應(yīng)的sql

  select SID,SQL_TEXT from v$open_cursor where SID='128';

關(guān)鍵詞標(biāo)簽:Oracle維護(hù),SQL語句

相關(guān)閱讀

文章評論
發(fā)表評論

熱門文章 Oracle中使用alter table來增加,刪除,修改列Oracle中使用alter table來增加,刪除,修改列oracle中使用SQL語句修改字段類型-oracle修oracle中使用SQL語句修改字段類型-oracle修使用低權(quán)限Oracle數(shù)據(jù)庫賬戶得到管理員權(quán)限使用低權(quán)限Oracle數(shù)據(jù)庫賬戶得到管理員權(quán)限Oracle對user的訪問控制Oracle對user的訪問控制

相關(guān)下載

人氣排行 ORACLE SQL 判斷字符串是否為數(shù)字的語句Oracle中使用alter table來增加,刪除,修改列的語法ORACLE和SQL語法區(qū)別歸納(1)oracle grant 授權(quán)語句如何加速Oracle大批量數(shù)據(jù)處理Oracle刪除表的幾種方法ORACLE修改IP地址后如何能夠使用Oracle 10g創(chuàng)建表空間和用戶并指定權(quán)限