Oracle 判斷欄位資料連續

判斷兩筆資料中同一欄位時是否連續
Lead(欄位名稱) 取得下一筆資料該欄位的值
Over(partition by 欄位名稱 以該欄位為group by 標準 order by 欄位名稱 資料已該欄位排序
這是Oracle 的內建函數,別的DB不能用喔~

select * from (
select
rownum,lead(rownum) over(partition by OWNER order by rownum) rownext

  from dba_objects w
  ) a
  where rownum <> rownext-1

Excel-時間相減後轉成秒數

=TEXT(A2-A1,"[ss]")  可以強制把時間轉換為秒數 mm 則轉換為分鐘
不足數無條件捨去

A欄位
20110629 15:12
20110629 17:11
結果
119

查詢SQL執行狀況

可至V$SQLAREA 查詢
該V會顯示出所有執行過的SQL 的執行頻率,IO耗用..等等資訊
詳細資料如ORACLE對該TABLE的說明

有另一個V$SQL 這個View執行效率會比較好
差別在於V$SQLAREA會把不同版本的SQL Groupby 在一起
而V$SQL不會

Oracle DATA GUARD 模式

Maximum Availability

This protection mode provides the highest level of data protection that is possible without compromising the availability of a primary database. Transactions do not commit until all redo data needed to recover those transactions has been written to the online redo log and to at least one synchronized standby database. If the primary database cannot write its redo stream to at least one synchronized standby database, it operates as if it were in maximum performance mode to preserve primary database availability until it is again able to write its redo stream to a synchronized standby database.
This mode ensures that no data loss will occur if the primary database fails, but only if a second fault does not prevent a complete set of redo data from being sent from the primary database to at least one standby database.

Maximum Performance

This protection mode provides the highest level of data protection that is possible without affecting the performance of a primary database. This is accomplished by allowing transactions to commit as soon as all redo data generated by those transactions has been written to the online log. Redo data is also written to one or more standby databases, but this is done asynchronously with respect to transaction commitment, so primary database performance is unaffected by delays in writing redo data to the standby database(s).
This protection mode offers slightly less data protection than maximum availability mode and has minimal impact on primary database performance.
This is the default protection mode.

Maximum Protection

This protection mode ensures that zero data loss occurs if a primary database fails. To provide this level of protection, the redo data needed to recover a transaction must be written to both the online redo log and to at least one synchronized standby database before the transaction commits. To ensure that data loss cannot occur, the primary database will shut down, rather than continue processing transactions, if it cannot write its redo stream to at least one synchronized standby database.
Because this data protection mode prioritizes data protection over primary database availability, Oracle recommends that a minimum of two standby databases be used to protect a primary database that runs in maximum protection mode to prevent a single standby database failure from causing the primary database to shut down.


Oracle 說明DataGuard文件

Oracle 修改Archive歸檔時間

archive_lag_target  單位是秒 預設是0(不啟動)
低於30分鐘可能影響資料庫效能
建議值落於 1800~7200之間
alter system set archive_lag_target=1800 scope=both;




oracle的說明 Oracle針對redo log的說明

Setting the ARCHIVE_LAG_TARGET Initialization Parameter

When you set the ARCHIVE_LAG_TARGET initialization parameter, you cause Oracle to examine an instance's current online redo log periodically. If the following conditions are met the instance will switch the log:

• The current log was created prior to n seconds ago, and the estimated archival time for the current log is m seconds (proportional to the number of redo blocks used in the current log), where n + mexceeds the value of the ARCHIVE_LAG_TARGET initialization parameter.
• The current log contains redo records.

In an Oracle Real Application Clusters environment, the instance also nudges other threads into switching and archiving logs if they are falling behind. This can be particularly useful when one instance in the cluster is more idle than the other instances (as when you are running a 2-node primary/secondary configuration of Oracle Real Application Clusters).

Initialization parameter ARCHIVE_LAG_TARGET specifies the target of how many seconds of redo the standby could lose in the event of a primary shutdown or crash if the Data Guard environment is not configured in a no-data-loss mode. It also provides an upper limit of how long (in the number of seconds) the current log of the primary database can span. Because the estimated archival time is also considered, this is not the exact log switch time.

The following initialization parameter setting sets the log switch interval to 30 minutes (a typical value).

ARCHIVE_LAG_TARGET = 1800

A value of 0 disables this time-based log switching functionality. This is the default setting.

You can set the ARCHIVE_LAG_TARGET initialization parameter even if there is no standby database. For example, the ARCHIVE_LAG_TARGET parameter can be set specifically to force logs to be switched and archived.

ARCHIVE_LAG_TARGET is a dynamic parameter and can be set with the ALTER SYSTEM SET statement.

Caution: The ARCHIVE_LAG_TARGET parameter must be set to the same value in all instances of an Oracle Real Application Clusters environment. Failing to do so results in unspecified behavior and is strongly discouraged.

Oracle 取得第幾週

to_char(mk_date,'YYYYWW')

取得ORACL Archived_log 大小

取得所歸檔的Archived Log 大小及歸檔時間
Select first_time,sum(blocks*block_size)
   FROM v$archived_log
   group by rollup(first_time)
欄位說明