ETL系列：开发人员常用oracle的查询语句

*************************************************

** 转发请注明原文，尊重原创

** 原文来自： blog.csdn.net/clark_xu 徐长亮的专栏

*************************************************

1 数据库信息查看

数据库信息查看

--查看实例名     select instance_number,instance_name,host_namefrom v$instance;     --查看RAC监听配置     selectname,value,display_valuefrom v$parameter wherenamein('remote_listener','local_listener');     --查看内存SGA,PGA分配情况     select *from v$sgastat;     select *from v$pgastat;     --查看查看是否归档，创建时间及平台类型     select dbid,name,db_unique_name,created,log_mode,platform_namefrom v$database;

--dblink查看 select *from dba_db_links;

数据文件查看

--查看表空间的大小，使用大小，可用大小 SELECT a.tablespace_name,        round(a.bytes / (1024 *1024 * 1024)) total_GB,        round(b.bytes / (1024 *1024 * 1024)) used_GB,        round(c.bytes / (1024 *1024 * 1024)) free_GB,        trunc((b.bytes * 100) / a.bytes) "% USED ",        trunc((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  orderby a.tablespace_namedesc;

---查看数据文件的大小，使用大小，空闲大小 select a.file_id,        a.file_name,        a.filesize,        b.freesize,        (a.filesize - b.freesize) usedsize   from (select file_id,                file_name,                round(bytes /1024 / 1024 /1024) filesize           from dba_data_files) a,        (select file_id,round(sum(dfs.bytes) /1024 / 1024 /1024) freesize           from dba_free_space dfs          groupby file_id) b  where a.file_id = b.file_id;

--查看临时数据文件        SELECT *FROM DBA_TEMP_FILES;        SELECT *FROM DBA_TEMP_FREE_SPACE;        --UNDO表空间监控      SELECT *FROM V$UNDOSTAT ORDERBY BEGIN_TIMEDESC;      SELECT *FROM DBA_ROLLBACK_SEGS;      SELECT *FROM V$ROLLSTAT;      SELECT *FROM DBA_UNDO_EXTENTSWHERE STATUS='ACTIVE'      SELECTNAME,VALUEFROM V$PARAMETERWHERENAME='UNDO_TABLESPACE'

--查看大表占用的空间 select segment_name,round(sum(bytes) /1024 / 1024 /1024, 2)   from user_segments  where segment_typein ('TABLE','TABLE PARTITION') groupby segment_name  orderbyround(sum(bytes) / 1024 / 1024 / 1024,2) desc;

--查看ASM diskgroup的大小（asm磁盘组） select *from v$asm_diskgroup; select *from v$asm_disk_stat;

 

2 锁的查看和执行计划查看

--查看被锁对象 SELECT object_name,        machine,        s.sid,        s.serial#,        s.blocking_session,        s.wait_class,        s.seconds_in_wait,        s.inst_id   FROM gv$locked_object l, dba_objects o, gv$session s  WHERE l.object_id = o.object_id    AND l.session_id = s.sid;    --查看会话操作系统进程     select s.sid, s.serial#, s.username, p.spid       from v$session s, v$process p      where s.paddr = p.addr        and s.sid ='1335'        and s.serial# ='2147';     --查看会话的sql_id select a.SQL_ID   from gv$session a  where a.sid ='2671'    and a.SERIAL# ='4499'--2dnhpsg563z4c  --查看执行计划  select *    from TABLE(DBMS_XPLAN.DISPLAY_CURSOR('5sc6fsqgt6x9w',0,'ADVANCED'));

--查看阻塞锁 SELECT (  '节点 '           || a.inst_id           || ' session '           || a.sid           || ','           || a_s.serial#           || ' 阻塞了节点 '           || b.inst_id           || ' session '           || b.sid           || ','           || b_s.serial#)             blockinfo,          a.inst_id,          a_s.sid,          a_s.schemaname,          a_s.module,          a_s.status,          a.TYPE lock_type,          a.id1,          a.id2,          DECODE (a.lmode,                  0,'none',                  1,NULL,                  2,'row-S (SS)',                  3,'row-X (SX)',                  4,'share (S)',                  5,'S/Row-X (SSX)',                  6,'exclusive (X)')             lock_mode,          '后为被阻塞信息' remark_flag,          b.inst_id blocked_inst_id,          b_s.sid blocked_sid,          b.TYPE blocked_lock_type,          DECODE (b.request,                  0,'none',                  1,NULL,                  2,'row-S (SS)',                  3,'row-X (SX)',                  4,'share (S)',                  5,'S/Row-X (SSX)',                  6,'exclusive (X)')             blocked_lock_request,          b_s.schemaname blocked_schemaname,          b_s.module blocked_module,          b_s.status blocked_status,          b_s.sql_id blocked_sql_id,          obj.owner blocked_owner,          obj.object_name blocked_object_name,          obj.object_type blocked_object_type     FROM gv$lock a,          gv$lock b,          gv$session a_s,          gv$session b_s,          dba_objects obj    WHERE    a.id1 = b.id1          AND a.id2 = b.id2          AND a.block >0    --阻塞了其他人          AND b.request >0          AND (  (a.inst_id = b.inst_id AND a.sid <> b.sid)               OR (a.inst_id <> b.inst_id))          AND a.sid = a_s.sid          AND a.inst_id = a_s.inst_id          AND b.sid = b_s.sid          AND b.inst_id = b_s.inst_id          AND b_s.row_wait_obj# = obj.object_id(+) ORDERBY a.inst_id, a.sid;