oracle常用查询sql

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了oracle常用查询sql，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含27828字，纯文字阅读大概需要40分钟。

内容图文

展开 #!/bin/sh
## create by Gordon Chen

echo "\n=============`date`===================\n"

if [ "$LOGNAME" = "oracle" ]; then
   SQLPLUS_CMD="/ as sysdba";
else
   SQLPLUS_CMD="/ as sysdba";
fi

case $1 in
   si)
     if [ "$LOGNAME" = "oracle" ]; then
        sqlplus "/ as sysdba"
     else
        sqlplus "/ as sysdba"
     fi
     ;;
   res)
         sqlplus -s "$SQLPLUS_CMD" << EOF
         set linesize 140
         set pagesize 200
         set serveroutput on
         alter session set cursor_sharing=force;
         exec xj_exp_data.PRC_RSCTL_M(‘$2‘,$3);
         exit;
EOF
  ;;
difplan_1000_times)
     sqlplus -s "$SQLPLUS_CMD" << EOF
         set line 300;
         col new_cpu format 999999.999;
         col old_cpu format 999999.999;
         col new_etime format 999999.999;
         col old_etime format 999999.999;
         col new_buff format 99999999.9;
         col old_buff format 99999999.9;  
         set pagesize 300;
         col username for a18;
select distinct  a.username,a.SQL_ID,a.PLAN_HASH_VALUE new_plan,b.PLAN_HASH_VALUE old_plan,a.AVG_CTIME new_cpu,b.AVG_CTIME old_cpu,a.AVG_LIO new_buff,b.AVG_LIO old_buff,a.avg_etime new_etime,b.AVG_ETIME old_etime
from
(select a.username,a.sql_id,b.plan_hash_value,(cpu_time/executions)/1000000 avg_ctime,buffer_gets/executions avg_lio,(elapsed_time/executions)/1000000 avg_etime
from v\$session a,v\$sql b where a.sql_id=b.sql_id and b.executions>1000 ) a left join xj_exp_data.xj_sql_baseline b
on a.sql_id = b.sql_id
where a.PLAN_HASH_VALUE <> b.PLAN_HASH_VALUE AND ((a.AVG_LIO-b.AVG_LIO)/b.AVG_LIO>1000 OR (A.AVG_CTIME-B.AVG_CTIME)/B.AVG_CTIME>1000)
order by 5,1,2;
     exit
EOF
     ;;
 log)
     sqlplus -s "$SQLPLUS_CMD" << EOF
spool /tmp/oralog.txt
select value from v\$parameter where name=‘background_dump_dest‘ or name=‘instance_name‘;
spool off
!cat /tmp/oralog.txt |grep -v -i value |grep -v ‘\-\-‘ |grep "\/"
exit
EOF
  ;;
 undo)
     sqlplus -s "$SQLPLUS_CMD" << EOF
    set line 200 pages 2000
    ALTER SESSION SET NLS_DATE_FORMAT=‘YYYY/MM/DD HH24:MI:SS‘ ;
    show parameter undo
    col tablespace_name for a20
    col file_name for a80
  select tablespace_name,file_name,bytes/1024/1024/1024 sizeG,status,autoextensible
    from dba_data_files where tablespace_name like ‘%UNDO%‘ order by 1;    
    col tablespace_name for a25
    select t.tablespace_name,
       total_GB,
       free_GB,
       round(100 * (1 - (free_GB / total_GB)), 3) || ‘%‘ "used_ts%"
  from (select tablespace_name, sum(bytes) / 1024 / 1024 / 1024 total_GB
          from dba_data_files
         group by tablespace_name) t,
       (select tablespace_name, sum(bytes) / 1024 / 1024 / 1024 free_GB
          from dba_free_space
         group by tablespace_name) f
 where t.tablespace_name = f.tablespace_name(+)
   and t.tablespace_name like ‘%UNDO%‘
 order by tablespace_name;
select tablespace_name, status, sum(bytes / 1024 / 1024 / 1024) GB
  from dba_undo_extents
 group by tablespace_name, status;
 
select u.begin_time,
       u.end_time,
       t.name         "undo_tbs_name",
       u.undoblks     "blocks_used",
       u.txncount     "transactions",
       u.maxquerylen  "longest query",
       u.expblkreucnt "expired blocks"
  from v\$undostat u, v\$tablespace t
 where u.undotsn = t.ts#
 and rownum < 21
 order by undoblks desc, maxquerylen;
!echo ****************undo advice******************
col UNDO_RETENTION for a15
col DB_BLOCK_SIZE for a15
select ur undo_retention, dbs db_block_size, ((ur * (ups * dbs))+(dbs * 24))/1024/1024 as "Average_Mb"
from (select value as ur from v\$parameter where name = ‘undo_retention‘),
(select (sum(undoblks) / sum(((end_time - begin_time) * 86400))) ups from v\$undostat),
(select value as dbs from v\$parameter where name = ‘db_block_size‘);

col UNDO_RETENTION for a15
col DB_BLOCK_SIZE for a15
select ur undo_retention, dbs db_block_size, ((ur * (ups * dbs)) + (dbs * 24)) / 1024 / 1024 as "Peak_Mb"
from (select value as ur from v\$parameter where name = ‘undo_retention‘),
(select (undoblks / ((end_time - begin_time) * 86400)) ups from v\$undostat where undoblks in (select max(undoblks) from v\$undostat)),
(select value as dbs from v\$parameter where name = ‘db_block_size‘);

!echo ****************undo detail******************
select s.sid,s.serial#,s.sql_id,v.usn,segment_name,r.status, v.rssize/1024/1024 mb
From dba_rollback_segs r, v\$rollstat v,v\$transaction t,v\$session s
Where r.segment_id = v.usn and v.usn=t.xidusn and t.addr=s.taddr
order by segment_name ;

select * from (select begin_time,txncount,maxquerylen,unexpiredblks,expiredblks,tuned_undoretention from v\$undostat order by begin_time) where rownum < 31;   
!echo ***********transactions rollback*************
select usn, state, undoblockstotal "Total", undoblocksdone "Done", undoblockstotal-undoblocksdone "ToDo",decode(cputime,0,‘unknown‘,sysdate+(((undoblockstotal-undoblocksdone) / (undoblocksdone / cputime)) / 86400)) "Estimated time to complete"
  from v\$fast_start_transactions;
exit
EOF
   ;;
   ke)
    sqlplus -s "$SQLPLUS_CMD" << EOF
        set lines 150 pages 2000
        select sid,serial#,username,program,sql_id from v\$session where event#=‘$2‘ order by sql_id;
                select ‘alter system kill session ‘‘‘|| sid ||‘,‘||serial# ||‘‘‘ immediate;‘ from v\$session where event#=‘$2‘;
                select ‘ps -ef|grep ‘||to_char(spid)||‘|grep LOCAL=NO|awk ‘‘{print " -9 "\$2}‘‘|xargs kill‘ kill_sh from v\$process p,v\$session s where s.paddr=p.addr and s.type=‘USER‘ and s.event#=‘$2‘;
                exit    
                         
EOF
     ;;
   active)
         sqlplus -s "$SQLPLUS_CMD" << EOF
         set lines 150 pages 2000
         col sid format 9999
         col s# format 99999
         col spid format a10
         col username format a10
         col event format a30
         col machine format a20
         col p123 format a18
         col wt format 999
         col SQL_ID for a18
         alter session set cursor_sharing=force;
            SELECT /* LEADING(S) FIRST_ROWS */
             S.SID,
             S.SERIAL# S#,
             S.SQL_ID,
             P.SPID,
             NVL(S.USERNAME, SUBSTR(P.PROGRAM, LENGTH(P.PROGRAM) - 6)) USERNAME,
             S.MACHINE,
             S.EVENT,
             S.P1 || ‘/‘ || S.P2 || ‘/‘ || S.P3 P123,
             S.WAIT_TIME WT,
             NVL(SQL_ID, S.PREV_SQL_ID) SQL_ID
              FROM V\$PROCESS P, V\$SESSION S
             WHERE P.ADDR = S.PADDR
               AND S.STATUS = ‘ACTIVE‘
               AND P.BACKGROUND IS NULL;
         exit
EOF
     ;;
 highpara)
     sqlplus -s "$SQLPLUS_CMD" << EOF
     set linesize 150
     col sql_t format a50;                                       
      select substr(sql_text, 1, 50) as sql_t,
             trim(program),
             min(sql_id),
             count(*)
        from (select sql_text, a.sql_id, program
                from v\$session a, v\$sqlarea b,v\$px_session px
               where a.sql_id = b.sql_id
                 and a.sid = px.qcsid
                 and a.status = ‘ACTIVE‘
                 and a.sql_id is not null
              union all
              select sql_text, a.PREV_SQL_ID as sql_id, program
                from v\$session a, v\$sqlarea b,v\$px_session px
               where a.sql_id is null
                 and a.PREV_SQL_ID = b.sql_id
                 and a.sid = px.qcsid
                 and a.status = ‘ACTIVE‘)
       group by substr(sql_text, 1, 50), trim(program)
       order by 1;
               exit
EOF
     ;;
 event)
     sqlplus -s "$SQLPLUS_CMD" << EOF
     set linesize 150 pages 100
     col event for a60
     select event#,event,count(*) from v\$session
   where status=‘ACTIVE‘ and event not like ‘%message%‘ group by event#, event order by 3;              
exit
EOF
     ;;     
   size)
     sqlplus -s "$SQLPLUS_CMD" << EOF
         set lines 150 pages 2000
         col owner format a10
         col segment_name for a30
         alter session set cursor_sharing=force;
         SELECT  OWNER,SEGMENT_NAME, SEGMENT_TYPE, SUM(BYTES)/1048576 SIZE_MB,
                    MAX(INITIAL_EXTENT) INIEXT, MAX(NEXT_EXTENT) MAXEXT FROM DBA_SEGMENTS
             WHERE SEGMENT_NAME = upper(‘$2‘)
               AND (‘$3‘ IS NULL OR UPPER(OWNER) = UPPER(‘$3‘))
               AND SEGMENT_TYPE LIKE ‘TABLE%‘
             GROUP BY OWNER, SEGMENT_NAME, SEGMENT_TYPE
           UNION ALL
             SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, SUM(BYTES)/1048576 SIZE_MB,
                    MAX(INITIAL_EXTENT) INIEXT, MAX(NEXT_EXTENT) MAXEXT FROM DBA_SEGMENTS
             WHERE (OWNER,SEGMENT_NAME) IN (
                SELECT OWNER,INDEX_NAME FROM DBA_INDEXES WHERE TABLE_NAME=upper(‘$2‘) AND
                (‘$3‘ IS NULL OR UPPER(OWNER) = UPPER(‘$3‘))
                UNION
                SELECT OWNER,SEGMENT_NAME FROM DBA_LOBS WHERE TABLE_NAME=upper(‘$2‘) AND
                (‘$3‘ IS NULL OR UPPER(OWNER) = UPPER(‘$3‘)))
             GROUP BY OWNER, SEGMENT_NAME, SEGMENT_TYPE;
          exit
EOF
     ;;
   idxdesc)
     sqlplus -s "$SQLPLUS_CMD" << EOF
     alter session set cursor_sharing=force;
      SET linesize 500
      col INDEX_COL  FOR a30
      col INDEX_TYPE FOR a22
      col INDEX_NAME FOR a32
      col table_name FOR a32
      SELECT B.OWNER||‘.‘||B.INDEX_NAME INDEX_NAME,
             A.INDEX_COL,B.INDEX_TYPE||‘-‘||B.UNIQUENESS INDEX_TYPE,B.PARTITIONED
        FROM (SELECT TABLE_OWNER,TABLE_NAME,INDEX_NAME, SUBSTR(MAX(SYS_CONNECT_BY_PATH(COLUMN_NAME, ‘,‘)), 2) INDEX_COL
                FROM (SELECT TABLE_OWNER, TABLE_NAME,INDEX_NAME,  COLUMN_NAME,
                             ROW_NUMBER() OVER(PARTITION BY TABLE_OWNER, TABLE_NAME, INDEX_NAME
                             ORDER BY TABLE_OWNER, INDEX_NAME, COLUMN_POSITION, COLUMN_NAME) RN
                        FROM DBA_IND_COLUMNS
                       WHERE TABLE_NAME = UPPER(‘$2‘)
                         AND TABLE_OWNER = UPPER(‘$3‘))
               START WITH RN = 1
              CONNECT BY PRIOR RN = RN - 1
                     AND PRIOR TABLE_NAME = TABLE_NAME
                     AND PRIOR INDEX_NAME = INDEX_NAME
                     AND PRIOR TABLE_OWNER = TABLE_OWNER
               GROUP BY TABLE_NAME, INDEX_NAME, TABLE_OWNER
               ORDER BY TABLE_OWNER, TABLE_NAME, INDEX_NAME
             ) A,
            (SELECT * FROM DBA_INDEXES WHERE TABLE_NAME = UPPER(‘$2‘) AND TABLE_OWNER = UPPER(‘$3‘)) B
       WHERE A.TABLE_OWNER = B.TABLE_OWNER
         AND A.TABLE_NAME = B.TABLE_NAME
         AND A.INDEX_NAME =B.INDEX_NAME;  
         exit
EOF
     ;;
   tsfree)
     sqlplus -s "$SQLPLUS_CMD" << EOF
     set lines 200 pages 2000
     select count(*) recycnum  from dba_recyclebin;
    
     col tablespace_name for a40  
     SELECT /*+ ordered */D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,SPACE - NVL(FREE_SPACE, 0) "USED_SPACE(M)",
            ROUND((1 - NVL(FREE_SPACE, 0) / SPACE) * 100, 2) "USED_RATE(%)", FREE_SPACE "FREE_SPACE(M)"
             FROM (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE, SUM(BLOCKS) BLOCKS
               FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) D,
                              (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_SPACE
               FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F
      WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
      ORDER BY  "USED_RATE(%)" DESC;
         exit
EOF
      ;;
   tablespace)
     sqlplus -s "$SQLPLUS_CMD" << EOF
         set lines 200 pages 2000
         alter session set cursor_sharing=force;
         select TABLESPACE_NAME TS_NAME,INITIAL_EXTENT INI_EXT,NEXT_EXTENT NXT_EXT,
            STATUS,CONTENTS, EXTENT_MANAGEMENT EXT_MGR,ALLOCATION_TYPE ALLOC_TYPE
         FROM DBA_TABLESPACES ORDER BY 5,1;
         exit
EOF
      ;;
   datafile)
     sqlplus -s "$SQLPLUS_CMD" << EOF
         set lines 150 pages 2000
         col name format a60
         col file# format 9999
         col size_mb format 99999
         alter session set cursor_sharing=force;
         select /*+ RULE */
            f.file#, F.NAME, TRUNC(f.BYTES/1048576,2) SIZE_MB , f.CREATION_TIME,f.status,d.AUTOEXTENSIBLE exten
         FROM V\$DATAFILE F,V\$TABLESPACE T,DBA_DATA_FILES D
         WHERE F.ts#=T.ts# and f.name=d.file_name AND T.NAME = NVL(UPPER(‘$2‘),‘SYSTEM‘)
         order by f.CREATION_TIME;
         exit
EOF
     ;;
   sqltext)
     sqlplus -s "$SQLPLUS_CMD" << EOF
         alter session set cursor_sharing=force;
         SELECT /* SHSNC */ SQL_TEXT FROM V\$SQLTEXT
          WHERE SQL_ID = to_char(‘$2‘)
          ORDER BY PIECE;
         exit
EOF
   ;;
   plan)
     sqlplus -s "$SQLPLUS_CMD" << EOF
         alter session set cursor_sharing=force;
         set linesize 500 pages 4000
         SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(to_char(‘$2‘),NULL));
         exit
EOF
     ;;
   lock)
     sqlplus -s "$SQLPLUS_CMD" << EOF
         set lines 150 pages 2000
         col type format a12
         col hold format a12
         col request format a12
         col BLOCK_OTHERS format a16
         alter session set cursor_sharing=force;
         select  /*+ RULE */
            sid,
            decode(type,
                    ‘MR‘, ‘Media Recovery‘,
                    ‘RT‘, ‘Redo Thread‘,
                    ‘UN‘, ‘User Name‘,
                    ‘TX‘, ‘Transaction‘,
                    ‘TM‘, ‘DML‘,
                    ‘UL‘, ‘PL/SQL User Lock‘,
                    ‘DX‘, ‘Distributed Xaction‘,
                    ‘CF‘, ‘Control File‘,
                    ‘IS‘, ‘Instance State‘,
                    ‘FS‘, ‘File Set‘,
                    ‘IR‘, ‘Instance Recovery‘,
                    ‘ST‘, ‘Disk Space Transaction‘,
                    ‘TS‘, ‘Temp Segment‘,
                    ‘IV‘, ‘Library Cache Invalidation‘,
                    ‘LS‘, ‘Log Start or Switch‘,
                    ‘RW‘, ‘Row Wait‘,
                    ‘SQ‘, ‘Sequence Number‘,
                    ‘TE‘, ‘Extend Table‘,
                    ‘TT‘, ‘Temp Table‘,
                    ‘TC‘, ‘Thread Checkpoint‘,
                                ‘SS‘, ‘Sort Segment‘,
                                ‘JQ‘, ‘Job Queue‘,
                                ‘PI‘, ‘Parallel operation‘,
                                ‘PS‘, ‘Parallel operation‘,
                                ‘DL‘, ‘Direct Index Creation‘,
                    type) type,
            decode(lmode,
                    0, ‘None‘,            
                    1, ‘Null‘,            
                    2, ‘Row-S (SS)‘,      
                    3, ‘Row-X (SX)‘,      
                    4, ‘Share‘,           
                    5, ‘S/Row-X (SSX)‘,   
                    6, ‘Exclusive‘,       
                    to_char(lmode)) hold,
             decode(request,
                    0, ‘None‘,            
                    1, ‘Null‘,            
                    2, ‘Row-S (SS)‘,      
                    3, ‘Row-X (SX)‘,      
                    4, ‘Share‘,           
                    5, ‘S/Row-X (SSX)‘,   
                    6, ‘Exclusive‘,       
                    to_char(request)) request,
             ID1,ID2,CTIME,
             decode(block,
                    0, ‘Not Blocking‘,  
                    1, ‘Blocking‘,      
                    2, ‘Global‘,        
                    to_char(block)) block_others
                from v\$lock
            where type <> ‘MR‘ and to_char(sid) = nvl(‘$2‘,to_char(sid)) ;
         exit
EOF
     ;;
   lockwait)
     sqlplus -s "$SQLPLUS_CMD" << EOF
         set lines 150 pages 2000
         col HOLD_SID format 99999
         col WAIT_SID format 99999
         col type format a20
         col hold format a12
         col request format a12
         alter session set cursor_sharing=force;
         SELECT /*+ ORDERED USE_HASH(H,R) */
            H.SID HOLD_SID,
            R.SID WAIT_SID,
            decode(H.type,
                    ‘MR‘, ‘Media Recovery‘,
                    ‘RT‘, ‘Redo Thread‘,
                    ‘UN‘, ‘User Name‘,
                    ‘TX‘, ‘Transaction‘,
                    ‘TM‘, ‘DML‘,
                    ‘UL‘, ‘PL/SQL User Lock‘,
                    ‘DX‘, ‘Distributed Xaction‘,
                    ‘CF‘, ‘Control File‘,
                    ‘IS‘, ‘Instance State‘,
                    ‘FS‘, ‘File Set‘,
                    ‘IR‘, ‘Instance Recovery‘,
                    ‘ST‘, ‘Disk Space Transaction‘,
                    ‘TS‘, ‘Temp Segment‘,
                    ‘IV‘, ‘Library Cache Invalidation‘,
                    ‘LS‘, ‘Log Start or Switch‘,
                    ‘RW‘, ‘Row Wait‘,
                    ‘SQ‘, ‘Sequence Number‘,
                    ‘TE‘, ‘Extend Table‘,
                    ‘TT‘, ‘Temp Table‘,
                    ‘TC‘, ‘Thread Checkpoint‘,
                                ‘SS‘, ‘Sort Segment‘,
                                ‘JQ‘, ‘Job Queue‘,
                                ‘PI‘, ‘Parallel operation‘,
                                ‘PS‘, ‘Parallel operation‘,
                                ‘DL‘, ‘Direct Index Creation‘,
                    H.type) type,
            decode(H.lmode,
                    0, ‘None‘,         1, ‘Null‘,
                    2, ‘Row-S (SS)‘,   3, ‘Row-X (SX)‘,
                    4, ‘Share‘,        5, ‘S/Row-X (SSX)‘,
                    6, ‘Exclusive‘,    to_char(H.lmode)) hold,
             decode(r.request,         0, ‘None‘,
                    1, ‘Null‘,         2, ‘Row-S (SS)‘,
                    3, ‘Row-X (SX)‘,   4, ‘Share‘,
                    5, ‘S/Row-X (SSX)‘,6, ‘Exclusive‘,
                    to_char(R.request)) request,
            R.ID1,R.ID2,R.CTIME
          FROM V\$LOCK H,V\$LOCK R
          WHERE H.BLOCK = 1 AND R.REQUEST > 0 AND H.SID <> R.SID
            and H.TYPE <> ‘MR‘ AND R.TYPE <> ‘MR‘
            AND H.ID1 = R.ID1 AND H.ID2 = R.ID2 AND H.TYPE=R.TYPE
            AND H.LMODE > 0 AND R.REQUEST > 0 ORDER BY 1,2;
         exit
EOF
      ;;
   objlike)
     sqlplus -s "$SQLPLUS_CMD" << EOF
         set linesize 120
         col type format a16
         col OWNER format a12
         col status format a8
         col CREATED format a10
         col MODIFIED format a19
         col OBJECT_NAME format a30
         alter session set cursor_sharing=force;
         SELECT /* SHSNC */ OBJECT_TYPE TYPE,OBJECT_ID ID,OWNER,OBJECT_NAME,
               TO_CHAR(CREATED,‘YYYY/MM/DD‘) CREATED,
               TO_CHAR(LAST_DDL_TIME,‘YYYY/MM/DD HH24:MI:SS‘) MODIFIED,STATUS
           FROM ALL_OBJECTS
           WHERE OBJECT_TYPE IN (‘CLUSTER‘,‘FUNCTION‘,‘INDEX‘,
                ‘PACKAGE‘,‘PROCEDURE‘,‘SEQUENCE‘,‘SYNONYM‘,
                ‘TABLE‘,‘TRIGGER‘,‘TYPE‘,‘VIEW‘)
             AND (‘$3‘ IS NULL OR UPPER(OWNER) = UPPER(‘$3‘))
             AND OBJECT_NAME LIKE UPPER(‘%$2%‘);
         exit
EOF
      ;;
   tablike)
     sqlplus -s "$SQLPLUS_CMD" << EOF
         set linesize 120
         col OWNER format a12
         col status format a8
         col CREATED format a10
         col MODIFIED format a19
         col OBJECT_NAME format a30
         alter session set cursor_sharing=force;
         SELECT /* SHSNC */ OBJECT_ID ID,OWNER,OBJECT_NAME,
               TO_CHAR(CREATED,‘YYYY/MM/DD‘) CREATED,
               TO_CHAR(LAST_DDL_TIME,‘YYYY/MM/DD HH24:MI:SS‘) MODIFIED,STATUS
           FROM ALL_OBJECTS
           WHERE OBJECT_TYPE = ‘TABLE‘
             AND (‘$3‘ IS NULL OR UPPER(OWNER) = UPPER(‘$3‘))
             AND OBJECT_NAME LIKE UPPER(‘%$2%‘);
         exit
EOF
      ;;
   tstat)
     sqlplus -s "$SQLPLUS_CMD" << EOF
         set lines 150 pages 2000
         
         col owner format a10
         col partname format a30
         col INIEXT format 99999
         col nxtext format 99999
         col avgspc format 99999
         col ccnt format 999
         col rowlen format 9999
         col ssize format 9999999
         alter session set nls_date_format=‘yyyy-mm-dd hh24:mi:ss‘;
         alter session set cursor_sharing=force;
         SELECT   OWNER,NULL PARTNAME,
            NUM_ROWS NROWS, BLOCKS, AVG_SPACE AVGSPC,CHAIN_CNT CCNT, AVG_ROW_LEN ROWLEN,
            SAMPLE_SIZE SSIZE,LAST_ANALYZED ANADATE
         FROM ALL_TABLES
         WHERE UPPER(OWNER)=NVL(UPPER(‘$3‘),OWNER)  AND TABLE_NAME=UPPER(‘$2‘)
         UNION ALL
         SELECT     TABLE_OWNER OWNER,PARTITION_NAME PARTNAME,
            NUM_ROWS NROWS, BLOCKS, AVG_SPACE AVGSPC,CHAIN_CNT CCNT, AVG_ROW_LEN ROWLEN,
            SAMPLE_SIZE SSIZE,LAST_ANALYZED ANADATE
         FROM ALL_TAB_PARTITIONS
         WHERE UPPER(TABLE_OWNER)=NVL(UPPER(‘$3‘),TABLE_OWNER)  AND TABLE_NAME=UPPER(‘$2‘);
         
         
         exit
EOF
     ;;
   istat)
     sqlplus -s "$SQLPLUS_CMD" << EOF
         set lines 150 pages 2000
         col OWNER format a10
         col lkey format 999
         col dkey format 999
         col lev format 99
         col anaday format a10
         alter session set cursor_sharing=force;
         SELECT /* SHSNC */  
            TABLE_OWNER OWNER, INDEX_NAME,
            BLEVEL LEV, LEAF_BLOCKS LBLKS,TRUNC(NUM_ROWS) NROWS,
            DISTINCT_KEYS DROWS,
            CLUSTERING_FACTOR CLSFCT,SAMPLE_SIZE SSIZE,
            TO_CHAR(LAST_ANALYZED,‘YYYY/MM/DD‘) ANADAY,
            PARTITIONED PAR
         FROM DBA_INDEXES
         WHERE (upper(table_owner) in null or UPPER(TABLE_OWNER)=NVL(UPPER(‘$3‘),TABLE_OWNER))
           AND TABLE_NAME=UPPER(‘$2‘);
         exit
EOF
     ;;
   ipstat)
     sqlplus -s "$SQLPLUS_CMD" << EOF
         set lines 150 pages 2000
         col OWNER format a10
         col lkey format 999
         col dkey format 999
         col lev format 99
         col anaday format a10
         alter session set cursor_sharing=force;
         SELECT
            PARTITION_NAME,
            BLEVEL LEV, LEAF_BLOCKS LBLKS,TRUNC(NUM_ROWS) NROWS,
            DISTINCT_KEYS DROWS,
            CLUSTERING_FACTOR CLSFCT,SAMPLE_SIZE SSIZE,
            TO_CHAR(LAST_ANALYZED,‘YYYY/MM/DD‘) ANADAY
         FROM DBA_IND_PARTITIONS
         WHERE UPPER(INDEX_OWNER)=NVL(UPPER(‘$3‘),INDEX_OWNER)
           AND INDEX_NAME=UPPER(‘$2‘);
         exit
EOF
     ;;
   objsql)
     sqlplus -s "$SQLPLUS_CMD" << EOF
         set linesize 120
         col vers format 999
         alter session set cursor_sharing=force;
         SELECT /* SHSNC */
            HASH_VALUE, OPEN_VERSIONS VERS,
            SORTS, EXECUTIONS EXECS,  
            DISK_READS READS, BUFFER_GETS GETS,
            ROWS_PROCESSED ROWCNT
          FROM V\$SQL WHERE EXECUTIONS > 10 AND HASH_VALUE IN
            (SELECT /*+ NL_SJ */ DISTINCT HASH_VALUE
               FROM V\$SQL_PLAN WHERE OBJECT_NAME=UPPER(‘$2‘)
               AND NVL(OBJECT_OWNER,‘A‘)=UPPER(NVL(‘$3‘,‘A‘)));
         exit
EOF
     ;;
   longops)
     sqlplus -s "$SQLPLUS_CMD" << EOF
         set linesize 120
         col MESSAGE format a30
         col opname for a20
         col username for a20
         set pagesize 1000
         alter session set cursor_sharing=force;
         select opname,TIME_REMAINING REMAIN,
                ELAPSED_SECONDS ELAPSE,MESSAGE,
                SQL_ID,sid,username
         from v\$session_longops where TIME_REMAINING >0;
         exit
EOF
     ;;
   tran)
     sqlplus -s "$SQLPLUS_CMD" << EOF
         set lines 150 pages 2000
         col USERNAME format a12
         col rbs format a22
         col BLKS_RECS format a16
         col START_TIME format a17
         col LOGIO format 99999
         col PHY_IO FORMAT 99999
         COL CRGET FORMAT 99999
         COL CRMOD FORMAT 99999
         alter session set cursor_sharing=force;
         SELECT /*+ rule */   
             S.SID,S.SERIAL#,S.USERNAME, R.NAME RBS,
             T.START_TIME,  
             to_char(T.USED_UBLK)||‘,‘||to_char(T.USED_UREC) BLKS_RECS ,
             T.LOG_IO LOGIO,T.PHY_IO PHYIO,T.CR_GET CRGET,T.CR_CHANGE CRMOD
           FROM V\$TRANSACTION T, V\$SESSION S,V\$ROLLNAME R,
                V\$ROLLSTAT RS
           WHERE T.SES_ADDR(+) = S.SADDR   
             AND T.XIDUSN = R.USN AND S.USERNAME IS NOT NULL  
             AND R.USN = RS.USN ;
         exit
EOF
     ;;
   depend)
     sqlplus -s "$SQLPLUS_CMD" << EOF
         set linesize 170 pages 2000
         col name for a15
         col D_NAME for a30
         col D_OWNER for a20
         col dblink for a20
         alter session set cursor_sharing=force;
         SELECT TYPE,REFERENCED_OWNER D_OWNER,
               REFERENCED_NAME D_NAME,REFERENCED_TYPE D_TYPE,
               REFERENCED_LINK_NAME DBLINK, DEPENDENCY_TYPE DEPEND
           FROM DBA_DEPENDENCIES
           WHERE
             UPPER(OWNER) = NVL(UPPER(‘$3‘),OWNER)
             AND NAME  = UPPER(‘$2‘);
         SELECT   REFERENCED_TYPE TYPE,OWNER R_OWNER,
                NAME R_NAME, TYPE R_TYPE,DEPENDENCY_TYPE DEPEND
           FROM DBA_DEPENDENCIES
           WHERE
             UPPER(REFERENCED_OWNER) = NVL(UPPER(‘$3‘),REFERENCED_OWNER)
             AND REFERENCED_NAME  = UPPER(‘$2‘)
             AND REFERENCED_LINK_NAME IS NULL;
         exit
EOF
     ;;
   latch)
     sqlplus -s "$SQLPLUS_CMD" << EOF
         set lines 150 pages 2000
         alter session set cursor_sharing=force;
         SELECT  NAME FROM V\$LATCHNAME WHERE LATCH#=TO_NUMBER(‘$2‘);
         exit
EOF
     ;;
   hold)
     sqlplus -s "$SQLPLUS_CMD" << EOF
         set linesize 120
         col USERNAME format a16
         col MACHINE format a20
         alter session set cursor_sharing=force;
         SELECT /*+ RULE */
             S.SID,S.SERIAL#,P.SPID,S.USERNAME,
             S.MACHINE,S.STATUS
           FROM V\$PROCESS P, V\$SESSION S, V\$LOCKED_OBJECT O   
           WHERE P.ADDR = S.PADDR
             AND O.SESSION_ID=S.SID    
             AND S.USERNAME IS NOT NULL
             AND O.OBJECT_ID=TO_NUMBER(‘$2‘);
         exit
EOF
     ;;
   sort)
     sqlplus -s "$SQLPLUS_CMD" << EOF
         set lines 150 pages 2000
         col USERNAME format a12
         col MACHINE format a16
         col TABLESPACE format a20
         alter session set cursor_sharing=force;
         SELECT /*+ ordered */  
           B.SID,B.SERIAL#,B.USERNAME,B.MACHINE,A.BLOCKS,A.TABLESPACE,
           A.SEGTYPE,A.SEGFILE# FILE#,A.SEGBLK# BLOCK#
           FROM V\$SORT_USAGE A,V\$SESSION B
           WHERE A.SESSION_ADDR = B.SADDR;
         exit
EOF
     ;;
   desc)
     sqlplus -s "$SQLPLUS_CMD" << EOF
         set linesize 150 pages 2000
         col name format a30
         col nullable format a8
         col type format a30
         alter session set cursor_sharing=force;
         select COLUMN_ID NO#,COLUMN_NAME NAME,
           DECODE(NULLABLE,‘N‘,‘NOT NULL‘,‘‘) NULLABLE,
           (case  
              when data_type=‘CHAR‘ then data_type||‘(‘||data_length||‘)‘
              when data_type=‘VARCHAR‘ then data_type||‘(‘||data_length||‘)‘
              when data_type=‘VARCHAR2‘ then data_type||‘(‘||data_length||‘)‘
              when data_type=‘NCHAR‘ then data_type||‘(‘||data_length||‘)‘
              when data_type=‘NVARCHAR‘ then data_type||‘(‘||data_length||‘)‘
              when data_type=‘NVARCHAR2‘ then data_type||‘(‘||data_length||‘)‘
              when data_type=‘RAW‘ then data_type||‘(‘||data_length||‘)‘
              when data_type=‘NUMBER‘ then
                    (
                       case
                          when data_scale is null and data_precision is null then ‘NUMBER‘
                          when data_scale <> 0  then ‘NUMBER(‘||NVL(DATA_PRECISION,38)||‘,‘||DATA_SCALE||‘)‘
                      else ‘NUMBER(‘||NVL(DATA_PRECISION,38)||‘)‘
                       end
                    )
              else
                 ( case
      & 本文系统来源：https://www.cnblogs.com/yaoyangding/p/12048077.html

内容总结

以上是互联网集市为您收集整理的oracle常用查询sql全部内容，希望文章能够帮你解决oracle常用查询sql所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

---