日常SQL脚本

selectb.SQL_ID,
    b.SQL_TEXT,
    b.SQL_FULLTEXT,
    b.SHARABLE_MEM,
    b.PERSISTENT_MEM,
    b.RUNTIME_MEM,
    b.SORTS,
    b.VERSION_COUNT,
    b.LOADED_VERSIONS,
    b.OPEN_VERSIONS,
    b.USERS_OPENING,
    b.FETCHES,
    b.EXECUTIONS,
    b.END_OF_FETCH_COUNT,
    b.USERS_EXECUTING,
    b.FIRST_LOAD_TIME,
    b.INVALIDATIONS,
    b.PARSE_CALLS,
    b.DISK_READS,
    b.DIRECT_WRITES,
    b.BUFFER_GETS,
    b.APPLICATION_WAIT_TIME,
    b.CONCURRENCY_WAIT_TIME,
    b.CLUSTER_WAIT_TIME,
    b.USER_IO_WAIT_TIME,
    b.PLSQL_EXEC_TIME,
    b.ROWS_PROCESSED,
    b.COMMAND_TYPE,
    b.OPTIMIZER_MODE,
    b.OPTIMIZER_COST,
    b.PARSING_SCHEMA_NAME,
    b.MODULE,
    b.ACTION,
    b.CPU_TIME,
    b.ELAPSED_TIME,
    b.REMOTE,
    b.OBJECT_STATUS,
    b.LAST_LOAD_TIME,
    b.IS_OBSOLETE,
    b.IS_BIND_SENSITIVE,
    b.IS_BIND_AWARE,
    b.CHILD_LATCH,
    b.SQL_PROFILE,
    b.SQL_PATCH,
    b.SQL_PLAN_BASELINE,
    b.LAST_ACTIVE_TIME,
    b.IO_CELL_OFFLOAD_ELIGIBLE_BYTES,
    b.IO_INTERCONNECT_BYTES,
    b.PHYSICAL_READ_REQUESTS,
    b.PHYSICAL_READ_BYTES,
    b.PHYSICAL_WRITE_REQUESTS,
    b.PHYSICAL_WRITE_BYTES,
    b.OPTIMIZED_PHY_READ_REQUESTS,
    b.LOCKED_TOTAL,
    b.PINNED_TOTAL,
    b.IO_CELL_UNCOMPRESSED_BYTES,
    b.IO_CELL_OFFLOAD_RETURNED_BYTES
    fromv$sqlareab
    whereexists(select*
    fromv$open_cursora
    wherea.USER_NAME='MAGICZONE'
    anda.SQL_ID=b.SQL_ID)
    orderby(b.DISK_READS+b.BUFFER_GETS)desc;
    --查询当前会话SQL占用资源
    selecta.sid,name,value,c.PROGRAM
    fromv$sesstata,v$statnameb,v$sessionc
    wherea.STATISTIC#=b.STATISTIC#
    andb.name='redosize'
    anda.SID=c.SID
    orderbyvaluedesc;
    --查找REDO日志产生比较大的会话SID
    SELECTosuser,username,sql_text
    fromv$sessiona,v$sqltextb
    wherea.sql_address=b.address
    anda.SID=391
    orderbyaddress,piece;
    --找出会话ID对应执行的SQL
    selectusername,sid,opname,
    round(sofar*100/totalwork,0)||'%'asprogress,
    time_remaining,sql_text
    fromv$session_longops,v$sql
    wheretime_remaining<>0
    andsql_address=address
    andsql_hash_value=hash_value
    --捕捉运行很久的SQL
    selecta.SID,a.USERNAME,b.SQL_TEXT
    fromv$sessiona,v$sqltextb
    wherea.SQL_ADDRESS=b.ADDRESS
    anda.SQL_HASH_VALUE=b.HASH_VALUE
    orderbya.USERNAME,a.SID,b.PIECE;
    --通过V$SQLAREA查询有问题的查询
    selects.SIDSESSION_ID,
    s.SERIAL#SESSION#,
    s.PROCESS客户端进程,
    s.USERNAMEORACLE用户名,
    s.STATUS状态,
    s.OSUSER操作系统用户名,
    s.MACHINE客户端主机,
    s.PROGRAM程序,
    p.PIDORACLE进程ID,
    p.SPID服务器OS进程,
    p.SERIAL#
    fromv$sessions,v$processp
    wheres.PADDR=p.ADDR
    orderby1,2
    --通过服务器进程查看SESSION信息
    selectsql_text,SQL_FULLTEXT,spid,v$session.program,process
    fromv$sqlarea,v$session,v$process
    wherev$sqlarea.address=v$session.sql_address
    andv$sqlarea.hash_value=v$session.sql_hash_value
    andv$session.paddr=v$process.addr
    andv$process.spid=29763;
    --查看某个操作系统上的Oracle进程执行的SQL
    selectsql_text,
    SQL_FULLTEXT,
    spid,
    v$session.program,
    process,
    v$session.SID,
    v$session.SERIAL#,
    v$session.USERNAME,
    v$session.STATUS,
    v$session.OSUSER,
    v$session.MACHINE,
    v$session.PROGRAM,
    v$process.PID,
    v$process.SPID,
    v$process.SERIAL#
    fromv$sqlarea,v$session,v$process
    wherev$sqlarea.address=v$session.sql_address
    andv$sqlarea.hash_value=v$session.sql_hash_value
    andv$session.paddr=v$process.addr;
    --查看操作系统上所有Oracle进程执行的SQL
    altersystemkillsession'18,324';
    selectdbms_metadata.get_ddl('INDEX','&;INDEX_NAME')fromdual;