導讀:1建站知識Oracle DBA常用語句，對于表空間大小等查看都是不錯的sql語句。如何seo優化推廣網站企業網站建設。

查看表空間的名稱及大小

復制代碼 代碼如下:

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

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

復制代碼 代碼如下:

SQL>select tablespace_name, file_id, file_name,round(bytes/(1024*1024),0) total_space from dba_data_files order by tablespace_name;

查看回滾段名稱及大小:

復制代碼 代碼如下:

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

如何查看某個回滾段里面，跑的什么事物或者正在執行什么sql語句:

復制代碼 代碼如下:

SQL>select d.sql_text,a.name from v$rollname a,v$transaction b,v$session c,v$sqltext d where a.usn=b.xidusn and b.addr=c.taddr and c.sql_address= d.address and c.sql_hash_value=d.hash_value and a.usn=1;

(備注：你要看哪個，就把usn=?寫成幾就行了) 查看控制文件: SQL>select * from v$controlfile; 查看日志文件: SQL> col member format a50 SQL>select * from v$logfile; 如何查看當前SQL*PLUS用戶的sid和serial#: SQL>select sid, serial#, status from v$session where audsid=userenv('sessionid'); 如何查看當前數據庫的字符集: SQL>select userenv('language') from dual; SQL>select userenv('lang') from dual; 怎么判斷當前正在使用何種SQL優化方式: 用explain plan產生EXPLAIN PLAN，檢查PLAN_TABLE中ID=0的POSITION列的值。 SQL>select decode(nvl(position,-1),-1,'RBO',1,'CBO') from plan_table where id=0; 如何查看系統當前最新的SCN號： SQL>select max(ktuxescnw * power(2,32) + ktuxescnb) from x$ktuxe; 在ORACLE中查找TRACE文件的腳本: SQL>select u_dump.value || '/' || instance.value || '_ora_' || v$process.spid || nvl2(v$process.traceid, '_' || v$process.traceid, null ) || '.trc'"Trace File" from v$parameter u_dump cross join v$parameter instance cross join v$process join v$session on v$process.addr = v$session.paddr where u_dump.name = 'user_dump_dest' and instance.name = 'instance_name' and v$session.audsid=sys_context('userenv','sessionid'); SQL>select d.value || '/ora_' || p.spid || '.trc' trace_file_name from (select p.spid from sys.v_$mystat m,sys.v_$session s, sys.v_$process p where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,(select value from sys.v_$parameter where name ='user_dump_dest') d; 如何查看客戶端登陸的IP地址: SQL>select sys_context('userenv','ip_address') from dual; 如何在生產數據庫中創建一個追蹤客戶端IP地址的觸發器：

關鍵詞標簽: 常用 語句