oracle常用狀態查詢

檢視被鎖的表

    select p.spid,a.serial#, c.object_name,b.session_id,b.oracle_username,b.os_user_name from v$process p,v$session a, v$locked_object b,all_objects c where p.addr=a.paddr and a.process=b.process and c.object_id=b.object_id

    檢視連線的程式

    SELECT sid, serial#, username, osuser FROM v$session;

    殺掉程式

    alter system kill session 'sid,serial#';

    檢視當前使用者下表的佔用空間

    Select Segment_Name, Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name

    查詢所有物件佔用空間

    select owner, segment_name,sum(bytes/1024/1024)  from  dba_segments

    group by owner,segment_name

    查詢資料庫中各物件佔用空間大小

    Select Segment_Name,b.object_type, Sum(bytes)/1024/1024 From User_Extents a,user_objects b

    where a.segment_name=b.object_name Group By Segment_Name,b.object_type order by object_type, Segment_Name

    oracle 中去除欄位中的回車符

    update ywj_yxglobj set table_name = replace(table_name,chr(10),'')

    where table_name like 'ACCT_INFO%'

    ASCII()

    c1是一字串，返回c1第一個字母的ASCII碼，他的逆函式是CHR()

    SELECT ASCII('A') BIG_A,ASCII('z') BIG_z FROM empBIG_A BIG_z65 122

    CHR(＜i＞)[NCHAR_CS]

    i是一個數字，函式返回十進位制表示的字元

    select CHR(65),CHR(122),CHR(223) FROM empCHR65 CHR122 CHR223A z B

    如何查出UNDO中的空間是被哪個session佔用的?

    SELECT r.name 回滾段名,

    s.sid,

    s.serial#,

    s.username 使用者名稱,

    s.status,

    s.SQL_ADDRESS,

    t.cr_get,

    t.phy_io,

    t.used_ublk,

    t.noundo,

    substr(s.program, 1, 78) 操作程式

    FROM  sys.v_$session s,sys.v_$transaction t,sys.v_$rollname r

    WHERE t.addr = s.taddr and t.xidusn = r.usn

    檢視錶空間的名稱及大小

    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;

    檢視資料庫的版本

    Select version FROM Product_component_version

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

    檢視資料庫的建立日期和歸檔方式

    Select Created, Log_Mode, Log_Mode From V$Database;

    查詢資料庫中索引佔用表空間的大小

    select a.segment_name,a.tablespace_name,b.table_name,a.bytes/1024/1024 mbytes,a.blocks

    from user_segments a, user_indexes b

    where a.segment_name = b.index_name