如何在Oracle中查询事务计数

旧城等待， 2023-10-05 14:58 55阅读 0赞

## 如何在Oracle中查询事务计数 ##

数据库扩展期间可能需要的脚本。计算两个特定日期之间的交易编号。

该脚本已在11gR1和11gR2中进行了测试。在生产环境中运行脚本之前，请在测试服务器上对其进行测试。

以下查询可用于在Oracle数据库中查找事务计数。

## Oracle中每天的平均交易数 ##

<table style="margin-left:0px;"> 
 <tbody> 
  <tr> 
   <td style="text-align:left;vertical-align:top;"> 
    <div style="margin-left:0px;"> 
     <div style="margin-left:0px;"> 
      <span style="color:#333333;"><span style="color:#5499de;">1</span></span> 
     </div> 
     <div style="margin-left:0px;"> 
      <span style="color:#333333;"><span style="color:#5499de;"><span style="color:#317cc5;">2</span></span></span> 
     </div> 
     <div style="margin-left:0px;"> 
      <span style="color:#333333;"><span style="color:#5499de;">3</span></span> 
     </div> 
     <div style="margin-left:0px;"> 
      <span style="color:#333333;"><span style="color:#5499de;"><span style="color:#317cc5;">4</span></span></span> 
     </div> 
     <div style="margin-left:0px;"> 
      <span style="color:#333333;"><span style="color:#5499de;">5</span></span> 
     </div> 
     <div style="margin-left:0px;"> 
      <span style="color:#333333;"><span style="color:#5499de;"><span style="color:#317cc5;">6</span></span></span> 
     </div> 
     <div style="margin-left:0px;"> 
      <span style="color:#333333;"><span style="color:#5499de;">7</span></span> 
     </div> 
     <div style="margin-left:0px;"> 
      <span style="color:#333333;"><span style="color:#5499de;"><span style="color:#317cc5;">8</span></span></span> 
     </div> 
     <div style="margin-left:0px;"> 
      <span style="color:#333333;"><span style="color:#5499de;">9</span></span> 
     </div> 
     <div style="margin-left:0px;"> 
      <span style="color:#333333;"><span style="color:#5499de;"><span style="color:#317cc5;">10</span></span></span> 
     </div> 
     <div style="margin-left:0px;"> 
      <span style="color:#333333;"><span style="color:#5499de;">11</span></span> 
     </div> 
     <div style="margin-left:0px;"> 
      <span style="color:#333333;"><span style="color:#5499de;"><span style="color:#317cc5;">12</span></span></span> 
     </div> 
     <div style="margin-left:0px;"> 
      <span style="color:#333333;"><span style="color:#5499de;">13</span></span> 
     </div> 
     <div style="margin-left:0px;"> 
      <span style="color:#333333;"><span style="color:#5499de;"><span style="color:#317cc5;">14</span></span></span> 
     </div> 
     <div style="margin-left:0px;"> 
      <span style="color:#333333;"><span style="color:#5499de;">15</span></span> 
     </div> 
     <div style="margin-left:0px;"> 
      <span style="color:#333333;"><span style="color:#5499de;"><span style="color:#317cc5;">16</span></span></span> 
     </div> 
     <div style="margin-left:0px;"> 
      <span style="color:#333333;"><span style="color:#5499de;">17</span></span> 
     </div> 
     <div style="margin-left:0px;"> 
      <span style="color:#333333;"><span style="color:#5499de;"><span style="color:#317cc5;">18</span></span></span> 
     </div> 
     <div style="margin-left:0px;"> 
      <span style="color:#333333;"><span style="color:#5499de;">19</span></span> 
     </div> 
    </div> </td> 
   <td style="text-align:left;vertical-align:top;width:593px;"> 
    <div style="margin-left:0px;"> 
     <div style="margin-left:0px;"> 
      <span style="color:#333333;"><span style="color:#000000;"><span style="color:#004ed0;">SELECT </span><span style="color:#000000;">V1</span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color:#008000;">"Total Commits"</span><span style="color:#333333;">,</span></span></span> 
     </div> 
     <div style="margin-left:0px;"> 
      <span style="color:#333333;"><span style="color:#000000;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <span style="color:#000000;">V2</span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color:#008000;">"Total Rollbacks"</span><span style="color:#333333;">,</span></span></span> 
     </div> 
     <div style="margin-left:0px;"> 
      <span style="color:#333333;"><span style="color:#000000;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <span style="color:#000000;">V3</span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color:#008000;">"Total User Calls"</span><span style="color:#333333;">,</span></span></span> 
     </div> 
     <div style="margin-left:0px;"> 
      <span style="color:#333333;"><span style="color:#000000;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <span style="color:#000000;">T1</span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color:#008000;">"Uptime in days"</span><span style="color:#333333;">,</span></span></span> 
     </div> 
     <div style="margin-left:0px;"> 
      <span style="color:#333333;"><span style="color:#000000;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <span style="color:#002d7a;">S1</span> <span style="color:#006fe0;">/</span> <span style="color:#000000;">T1</span>&nbsp;&nbsp;&nbsp;&nbsp; <span style="color:#008000;">"Avg Daily DML Transactions"</span><span style="color:#333333;">,</span></span></span> 
     </div> 
     <div style="margin-left:0px;"> 
      <span style="color:#333333;"><span style="color:#000000;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <span style="color:#002d7a;">V3</span> <span style="color:#006fe0;">/</span> <span style="color:#000000;">T1</span>&nbsp;&nbsp;&nbsp;&nbsp; <span style="color:#008000;">"Avg Daily User Calls"</span></span></span> 
     </div> 
     <div style="margin-left:0px;"> 
      <span style="color:#333333;"><span style="color:#000000;">&nbsp;&nbsp;<span style="color:#004ed0;">FROM</span> <span style="color:#333333;">(</span><span style="color:#004ed0;">SELECT </span><span style="color:#004ed0;">VALUE&nbsp;&nbsp;&nbsp;&nbsp; </span><span style="color:#004ed0;">V1</span></span></span> 
     </div> 
     <div style="margin-left:0px;"> 
      <span style="color:#333333;"><span style="color:#000000;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color:#000000;">FROM</span> <span style="color:#002d7a;">V</span><span style="color:#333333;">$</span><span style="color:#004ed0;">SYSSTAT</span></span></span> 
     </div> 
     <div style="margin-left:0px;"> 
      <span style="color:#333333;"><span style="color:#000000;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <span style="color:#004ed0;">WHERE </span><span style="color:#002d7a;">NAME</span> <span style="color:#006fe0;">=</span> <span style="color:#008000;">'user commits'</span><span style="color:#333333;">)</span><span style="color:#333333;">,</span></span></span> 
     </div> 
     <div style="margin-left:0px;"> 
      <span style="color:#333333;"><span style="color:#000000;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <span style="color:#333333;">(</span><span style="color:#004ed0;">SELECT </span><span style="color:#004ed0;">VALUE&nbsp;&nbsp;&nbsp;&nbsp; </span><span style="color:#004ed0;">V2</span></span></span> 
     </div> 
     <div style="margin-left:0px;"> 
      <span style="color:#333333;"><span style="color:#000000;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color:#000000;">FROM</span> <span style="color:#002d7a;">V</span><span style="color:#333333;">$</span><span style="color:#004ed0;">SYSSTAT</span></span></span> 
     </div> 
     <div style="margin-left:0px;"> 
      <span style="color:#333333;"><span style="color:#000000;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <span style="color:#004ed0;">WHERE </span><span style="color:#002d7a;">NAME</span> <span style="color:#006fe0;">=</span> <span style="color:#008000;">'user rollbacks'</span><span style="color:#333333;">)</span><span style="color:#333333;">,</span></span></span> 
     </div> 
     <div style="margin-left:0px;"> 
      <span style="color:#333333;"><span style="color:#000000;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <span style="color:#333333;">(</span><span style="color:#004ed0;">SELECT </span><span style="color:#004ed0;">SUM</span> <span style="color:#333333;">(</span><span style="color:#002d7a;">VALUE</span><span style="color:#333333;">)</span>&nbsp;&nbsp;&nbsp;&nbsp; <span style="color:#004ed0;">S1</span></span></span> 
     </div> 
     <div style="margin-left:0px;"> 
      <span style="color:#333333;"><span style="color:#000000;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color:#000000;">FROM</span> <span style="color:#002d7a;">V</span><span style="color:#333333;">$</span><span style="color:#004ed0;">SYSSTAT</span></span></span> 
     </div> 
     <div style="margin-left:0px;"> 
      <span style="color:#333333;"><span style="color:#000000;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <span style="color:#004ed0;">WHERE </span><span style="color:#004ed0;">NAME </span><span style="color:#800080;">IN</span> <span style="color:#333333;">(</span><span style="color:#008000;">'user commits'</span><span style="color:#333333;">,</span> <span style="color:#008000;">'user rollbacks'</span><span style="color:#333333;">)</span><span style="color:#333333;">)</span><span style="color:#333333;">,</span></span></span> 
     </div> 
     <div style="margin-left:0px;"> 
      <span style="color:#333333;"><span style="color:#000000;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <span style="color:#333333;">(</span><span style="color:#004ed0;">SELECT </span><span style="color:#004ed0;">VALUE&nbsp;&nbsp;&nbsp;&nbsp; </span><span style="color:#004ed0;">V3</span></span></span> 
     </div> 
     <div style="margin-left:0px;"> 
      <span style="color:#333333;"><span style="color:#000000;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color:#000000;">FROM</span> <span style="color:#002d7a;">V</span><span style="color:#333333;">$</span><span style="color:#004ed0;">SYSSTAT</span></span></span> 
     </div> 
     <div style="margin-left:0px;"> 
      <span style="color:#333333;"><span style="color:#000000;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <span style="color:#004ed0;">WHERE </span><span style="color:#002d7a;">NAME</span> <span style="color:#006fe0;">=</span> <span style="color:#008000;">'user calls'</span><span style="color:#333333;">)</span><span style="color:#333333;">,</span></span></span> 
     </div> 
     <div style="margin-left:0px;"> 
      <span style="color:#333333;"><span style="color:#000000;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <span style="color:#333333;">(</span><span style="color:#004ed0;">SELECT </span><span style="color:#002d7a;">SYSDATE</span> <span style="color:#006fe0;">-</span> <span style="color:#004ed0;">STARTUP_TIME </span><span style="color:#004ed0;">T1 </span><span style="color:#000000;">FROM</span> <span style="color:#002d7a;">V</span><span style="color:#333333;">$</span><span style="color:#002d7a;">INSTANCE</span><span style="color:#333333;">)</span><span style="color:#333333;">;</span></span></span> 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

## 每日平均提交次数 ##

## 最近24小时的交易计数 ##