首頁 資料庫 mysql教程 Oracle维护常用SQL语句汇总

Oracle维护常用SQL语句汇总

Jun 07, 2016 pm 03:51 PM
oracle sql 如何 常用 匯總 維護 語句 遠端

如何远程判断Oracle数据库的安装平台 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,

如何远程判断Oracle数据库的安装平台
  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、查看数据库库对象
  select owner, object_type, status, count(*) count# from all_objects group by owner, object_type, status;
  8、查看数据库的版本 
  Select version FROM Product_component_version
  Where SUBSTR(PRODUCT,1,6)='Oracle';
  9、查看数据库的创建日期和归档方式
  Select Created, Log_Mode, Log_Mode From V$Database;
  10、如何远程判断Oracle数据库的安装平台
  select * from v$version;
  11、查看数据表的参数信息
  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
  12、查看还没提交的事务
  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。查看数据表的参数信息
  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为哪些进程所用
  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。耗资源的进程(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
  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
  根据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';
  根据sid查对应的sql
  select SID,SQL_TEXT from v$open_cursor where SID='128';

 

20。查看等待(wait)情况
  SELECT v$waitstat.class, v$waitstat.count count, SUM(v$sysstat.value) sum_value
  FROM v$waitstat, v$sysstat WHERE v$sysstat.name IN ('db block gets',
  'consistent gets') group by v$waitstat.class, v$waitstat.count
  21。查看sga情况
  SELECT NAME, BYTES FROM SYS.V_$SGASTAT ORDER BY NAME ASC
  22。查看catched object
  SELECT owner, name, db_link, namespace,
  type, sharable_mem, loads, executions,
  locks, pins, kept FROM v$db_object_cache
  23。查看V$SQLAREA
  SELECT SQL_TEXT, SHARABLE_MEM, PERSISTENT_MEM, RUNTIME_MEM, SORTS,
  VERSION_COUNT, LOADED_VERSIONS, OPEN_VERSIONS, USERS_OPENING, EXECUTIONS,
  USERS_EXECUTING, LOADS, FIRST_LOAD_TIME, INVALIDATIONS, PARSE_CALLS, DISK_READS,
  BUFFER_GETS, ROWS_PROCESSED FROM V$SQLAREA
  24。查看object分类数量
  select decode (o.type#,1,'INDEX' , 2,'TABLE' , 3 , 'CLUSTER' , 4, 'VIEW' , 5 ,
  'SYNONYM' , 6 , 'SEQUENCE' , 'OTHER' ) object_type , count(*) quantity from
  sys.obj$ o where o.type# > 1 group by decode (o.type#,1,'INDEX' , 2,'TABLE' , 3
  , 'CLUSTER' , 4, 'VIEW' , 5 , 'SYNONYM' , 6 , 'SEQUENCE' , 'OTHER' ) union select
  'COLUMN' , count(*) from sys.col$ union select 'DB LINK' , count(*) from
  25。按用户查看object种类
  select u.name schema, sum(decode(o.type#, 1, 1, NULL)) indexes,
  sum(decode(o.type#, 2, 1, NULL)) tables, sum(decode(o.type#, 3, 1, NULL))
  clusters, sum(decode(o.type#, 4, 1, NULL)) views, sum(decode(o.type#, 5, 1,
  NULL)) synonyms, sum(decode(o.type#, 6, 1, NULL)) sequences,
  sum(decode(o.type#, 1, NULL, 2, NULL, 3, NULL, 4, NULL, 5, NULL, 6, NULL, 1))
  others from sys.obj$ o, sys.user$ u where o.type# >= 1 and u.user# =
  o.owner# and u.name 'PUBLIC' group by u.name order by
  sys.link$ union select 'CONSTRAINT' , count(*) from sys.con$
  26。有关connection的相关信息
  1)查看有哪些用户连接
  select s.osuser os_user_name, decode(sign(48 - command), 1, to_char(command),
  'Action Code #' || to_char(command) ) action, p.program oracle_process,
  status session_status, s.terminal terminal, s.program program,
  s.username user_name, s.fixed_table_sequence activity_meter, '' query,
  0 memory, 0 max_memory, 0 cpu_usage, s.sid, s.serial# serial_num
  from v$session s, v$process p where s.paddr=p.addr and s.type = 'USER'
  order by s.username, s.osuser
  2)根据v.sid查看对应连接的资源占用等情况
  select n.name,
  v.value,
  n.class,
  n.statistic#
  from v$statname n,
  v$sesstat v
  where v.sid = 71 and
  v.statistic# = n.statistic#
  order by n.class, n.statistic#
  3)根据sid查看对应连接正在运行的sql
  select /*+ PUSH_SUBQ */
  command_type,
  sql_text,
  sharable_mem,
  persistent_mem,
  runtime_mem,
  sorts,
  version_count,
  loaded_versions,
  open_versions,
  users_opening,
  executions,
  users_executing,
  loads,
  first_load_time,
  invalidations,
  parse_calls,
  disk_reads,
  buffer_gets,
  rows_processed,
  sysdate start_time,
  sysdate finish_time,
  '>' || address sql_address,
  'N' status
  from v$sqlarea
  where address = (select sql_address from v$session where sid = 71)
  27.查询表空间使用情况
  select a.tablespace_name "表空间名称",
  100-round((nvl(b.bytes_free,0)/a.bytes_alloc)*100,2) "占用率(%)",
  round(a.bytes_alloc/1024/1024,2) "容量(M)",
  round(nvl(b.bytes_free,0)/1024/1024,2) "空闲(M)",
  round((a.bytes_alloc-nvl(b.bytes_free,0))/1024/1024,2) "使用(M)",
  Largest "最大扩展段(M)",
  to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') "采样时间"
  from (select f.tablespace_name,
  sum(f.bytes) bytes_alloc,
  sum(decode(f.autoextensible,'YES',f.maxbytes,'NO',f.bytes)) maxbytes
  from dba_data_files f
  group by tablespace_name) a,
  (select f.tablespace_name,
  sum(f.bytes) bytes_free
  from dba_free_space f
  group by tablespace_name) b,
  (select round(max(ff.length)*16/1024,2) Largest,
  ts.name tablespace_name
  from sys.fet$ ff, sys.file$ tf,sys.ts$ ts
  where ts.ts#=ff.ts# and ff.file#=tf.relfile# and ts.ts#=tf.ts#
  group by ts.name, tf.blocks) c
  where a.tablespace_name = b.tablespace_name and a.tablespace_name = c.tablespace_name
  28. 查询表空间的碎片程度
  select tablespace_name,count(tablespace_name) from dba_free_space group by tablespace_name
  having count(tablespace_name)>10;
  alter tablespace name coalesce;
  alter table name deallocate unused;
  create or replace view ts_blocks_v as
  select tablespace_name,block_id,bytes,blocks,'free space' segment_name from dba_free_space
  union all
  select tablespace_name,block_id,bytes,blocks,segment_name from dba_extents;
  select * from ts_blocks_v;
  select tablespace_name,sum(bytes),max(bytes),count(block_id) from dba_free_space
  group by tablespace_name;
  29。查询有哪些数据库实例在运行
  select inst_name from v$active_instances;
  30. 查找oracle性能瓶颈sql
  select sql_text,spid,v$session.program,process from
  v$sqlarea,v$session,v$process
  where v$sqlarea.address=v$session.sql_address
  and v$sqlarea.hash_value=v$session.sql_hash_value
  and v$session.paddr=v$process.addr
  and v$process.spid in (操作系统PID);
  select sid,event,p1,p1text from v$session_wait;
  31. 找出最耗资源的sql
  select * from v$process where spid='2796';
  select sql_hash_value,machine,username,program from v$session where PAddr='63B7A584';
  select * from v$sqltext where hash_value='833203018';
  select * from v$sql where hash_value='833203018';
  select * from v$sqlarea where hash_value='833203018';
  SELECT a.username,a.machine,a.program,a.sid,a.serial#,a.status,c.piece,c.sql_text FROM v$session a,v$process b,v$sqltext c WHERE b.spid='2796' AND b.addr=a.paddr AND a.sql_address=c.address(+) ORDER BY c.piece要找出最耗资源的sql,我们可以首先使用top等工具,找到最好资源的进程(记住进程号),例如,操作系统进程号为2796,然后根据这个进程号(v$process.spid)在v$process中找到进程地址(v$process.addr),然后根据这个地址在 v$session中找到相应的sid(v$session.sid),然后根据这个sid找到相应的hash alue(v$session. sql_hash_value),然后根据这个hash alue在v$sqltext,$sql,v$sqlarea等视图中找到对应的sql语句(sql_text)。
  select * from v$process where spid='2796';
  select sql_hash_value,machine,username,program from v$session where PAddr='63B7A584';
  select * from v$sqltext where hash_value='833203018';
  select * from v$sql where hash_value='833203018';
  select * from v$sqlarea where hash_value='833203018';
  SELECT a.username,a.machine,a.program,a.sid,a.serial#,a.status,c.piece,c.sql_text FROM v$session a,v$process b,v$sqltext c WHERE b.spid='2796' AND b.addr=a.paddr AND a.sql_address=c.address(+) ORDER BY c.piece

本網站聲明
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn

熱AI工具

Undresser.AI Undress

Undresser.AI Undress

人工智慧驅動的應用程序,用於創建逼真的裸體照片

AI Clothes Remover

AI Clothes Remover

用於從照片中去除衣服的線上人工智慧工具。

Undress AI Tool

Undress AI Tool

免費脫衣圖片

Clothoff.io

Clothoff.io

AI脫衣器

Video Face Swap

Video Face Swap

使用我們完全免費的人工智慧換臉工具,輕鬆在任何影片中換臉!

熱門文章

<🎜>:泡泡膠模擬器無窮大 - 如何獲取和使用皇家鑰匙
3 週前 By 尊渡假赌尊渡假赌尊渡假赌
北端:融合系統,解釋
3 週前 By 尊渡假赌尊渡假赌尊渡假赌
Mandragora:巫婆樹的耳語 - 如何解鎖抓鉤
3 週前 By 尊渡假赌尊渡假赌尊渡假赌

熱工具

記事本++7.3.1

記事本++7.3.1

好用且免費的程式碼編輯器

SublimeText3漢化版

SublimeText3漢化版

中文版,非常好用

禪工作室 13.0.1

禪工作室 13.0.1

強大的PHP整合開發環境

Dreamweaver CS6

Dreamweaver CS6

視覺化網頁開發工具

SublimeText3 Mac版

SublimeText3 Mac版

神級程式碼編輯軟體(SublimeText3)

熱門話題

Java教學
1665
14
CakePHP 教程
1423
52
Laravel 教程
1321
25
PHP教程
1269
29
C# 教程
1249
24
oracle游標關閉怎麼解決 oracle游標關閉怎麼解決 Apr 11, 2025 pm 10:18 PM

解決 Oracle 游標關閉問題的方法包括:使用 CLOSE 語句顯式關閉游標。在 FOR UPDATE 子句中聲明游標,使其在作用域結束後自動關閉。在 USING 子句中聲明游標,使其在關聯的 PL/SQL 變量關閉時自動關閉。使用異常處理確保在任何異常情況下關閉游標。使用連接池自動關閉游標。禁用自動提交,延遲游標關閉。

oracle怎麼循環創建游標 oracle怎麼循環創建游標 Apr 12, 2025 am 06:18 AM

Oracle 中,FOR LOOP 循環可動態創建游標, 步驟為:1. 定義游標類型;2. 創建循環;3. 動態創建游標;4. 執行游標;5. 關閉游標。示例:可循環創建游標,顯示前 10 名員工姓名和工資。

oracle日誌寫滿怎麼辦 oracle日誌寫滿怎麼辦 Apr 12, 2025 am 06:09 AM

Oracle 日誌文件寫滿時,可採用以下解決方案:1)清理舊日誌文件;2)增加日誌文件大小;3)增加日誌文件組;4)設置自動日誌管理;5)重新初始化數據庫。在實施任何解決方案前,建議備份數據庫以防數據丟失。

oracle視圖怎麼導出 oracle視圖怎麼導出 Apr 12, 2025 am 06:15 AM

可以通過 EXP 實用程序導出 Oracle 視圖:登錄 Oracle 數據庫。啟動 EXP 實用程序,指定視圖名稱和導出目錄。輸入導出參數,包括目標模式、文件格式和表空間。開始導出。使用 impdp 實用程序驗證導出。

HDFS配置CentOS需要哪些步驟 HDFS配置CentOS需要哪些步驟 Apr 14, 2025 pm 06:42 PM

在CentOS系統上搭建Hadoop分佈式文件系統(HDFS)需要多個步驟,本文提供一個簡要的配置指南。一、前期準備安裝JDK:在所有節點上安裝JavaDevelopmentKit(JDK),版本需與Hadoop兼容。可從Oracle官網下載安裝包。環境變量配置:編輯/etc/profile文件,設置Java和Hadoop的環境變量,使系統能夠找到JDK和Hadoop的安裝路徑。二、安全配置:SSH免密登錄生成SSH密鑰:在每個節點上使用ssh-keygen命令

甲骨文在商業世界中的作用 甲骨文在商業世界中的作用 Apr 23, 2025 am 12:01 AM

Oracle不僅是數據庫公司,還是雲計算和ERP系統的領導者。 1.Oracle提供從數據庫到雲服務和ERP系統的全面解決方案。 2.OracleCloud挑戰AWS和Azure,提供IaaS、PaaS和SaaS服務。 3.Oracle的ERP系統如E-BusinessSuite和FusionApplications幫助企業優化運營。

oracle數據庫怎麼停止 oracle數據庫怎麼停止 Apr 12, 2025 am 06:12 AM

要停止 Oracle 數據庫,請執行以下步驟:1. 連接到數據庫;2. 優雅關機數據庫(shutdown immediate);3. 完全關機數據庫(shutdown abort)。

centos上weblogic的數據庫連接如何配置 centos上weblogic的數據庫連接如何配置 Apr 14, 2025 pm 02:06 PM

在CentOS系統上配置WebLogic數據庫連接,需要完成以下步驟:JDK安裝與環境配置:確保服務器已安裝與WebLogic版本兼容的JDK(例如,WebLogic14.1.1通常需要JDK8)。正確設置JAVA_HOME、CLASSPATH和PATH環境變量。 WebLogic安裝與解壓:從Oracle官方網站下載適用於CentOS系統的WebLogic安裝包,並將其解壓到指定目錄。 WebLogic用戶與目錄創建:創建一個專用的WebLogic用戶賬戶,並設置安全密碼

See all articles