同一資料庫資料SCN號與時間的轉化
SCN_TO_TIMESTAMP :
Purpose
SCN_TO_TIMESTAMP takes as an argument a number that evaluates to a system change number (SCN), and returns the approximate timestamp associated with that SCN. The returned value is of TIMESTAMP data type. This function is useful any time you want to know the timestamp associated with an SCN. For example, it can be used in conjunction with the ORA_ROWSCN pseudocolumn to associate a timestamp with the most recent change to a row
Notes:
■ The usual precision of the result value is 3 seconds.
■ The association between an SCN and a timestamp when the SCN is generated is remembered by the database for a limited period of time. This period is the maximum of the auto-tuned undo retention period, if the database runs in the Automatic Undo Management mode, and the retention times of all flashback archives in the database, but no less than 120 hours. The time for the association to become obsolete elapses only when the database is open. An error is returned if the SCN specified for the argument to SCN_TO_TIMESTAMP is too old.
Examples:
The following example uses the ORA_ROWSCN pseudocolumn to determine the system change number of the last update to a row and uses SCN_TO_TIMESTAMP to convert that SCN to a timestamp:
SELECT SCN_TO_TIMESTAMP(ORA_ROWSCN) FROM employees WHERE employee_id = 188;
You could use such a query to convert a system change number to a timestamp for use in an Oracle Flashback Query:
SELECT salary FROM employees WHERE employee_id = 188;
SALARY
----------
3800
UPDATE employees SET salary = salary*10 WHERE employee_id = 188;
COMMIT;
SELECT salary FROM employees WHERE employee_id = 188;
SALARY
----------
38000
SELECT SCN_TO_TIMESTAMP(ORA_ROWSCN) FROM employees WHERE employee_id = 188;
SCN_TO_TIMESTAMP(ORA_ROWSCN)
---------------------------------------------------------------------------
28-AUG-03 01.58.01.000000000 PM
FLASHBACK TABLE employees TO TIMESTAMP TO_TIMESTAMP('28-AUG-03 01.00.00.000000000 PM');
SELECT salary FROM employees WHERE employee_id = 188;
SALARY
----------
3800
TIMESTAMP_TO_SCN :
Purpose
TIMESTAMP_TO_SCN takes as an argument a timestamp value and returns the approximate system change number (SCN) associated with that timestamp. The returned value is of data type NUMBER. This function is useful any time you want to know the SCN associated with a particular timestamp
Note: The association between an SCN and a timestamp when the SCN is generated is remembered by the database for a limited period of time. This period is the maximum of the auto-tuned undo retention period, if the database runs in the Automatic Undo Management mode, and the retention times of all flashback archives in the database, but no less than 120 hours. The time for the association to become obsolete elapses only when the database is open. An error is returned if the timestamp specified for the argument to TIMESTAMP_TO_SCN is too old.
Examples:
The following example inserts a row into the oe.orders table and then uses TIMESTAMP_TO_SCN to determine the system change number of the insert operation. (The actual SCN returned will differ on each system.)
INSERT INTO orders (order_id, order_date, customer_id, order_total) VALUES (5000, SYSTIMESTAMP, 188, 2345);
1 row created.
COMMIT;
Commit complete.
SELECT TIMESTAMP_TO_SCN(order_date) FROM orders WHERE order_id = 5000;
TIMESTAMP_TO_SCN(ORDER_DATE)
----------------------------
574100
Purpose
SCN_TO_TIMESTAMP takes as an argument a number that evaluates to a system change number (SCN), and returns the approximate timestamp associated with that SCN. The returned value is of TIMESTAMP data type. This function is useful any time you want to know the timestamp associated with an SCN. For example, it can be used in conjunction with the ORA_ROWSCN pseudocolumn to associate a timestamp with the most recent change to a row
Notes:
■ The usual precision of the result value is 3 seconds.
■ The association between an SCN and a timestamp when the SCN is generated is remembered by the database for a limited period of time. This period is the maximum of the auto-tuned undo retention period, if the database runs in the Automatic Undo Management mode, and the retention times of all flashback archives in the database, but no less than 120 hours. The time for the association to become obsolete elapses only when the database is open. An error is returned if the SCN specified for the argument to SCN_TO_TIMESTAMP is too old.
Examples:
The following example uses the ORA_ROWSCN pseudocolumn to determine the system change number of the last update to a row and uses SCN_TO_TIMESTAMP to convert that SCN to a timestamp:
SELECT SCN_TO_TIMESTAMP(ORA_ROWSCN) FROM employees WHERE employee_id = 188;
You could use such a query to convert a system change number to a timestamp for use in an Oracle Flashback Query:
SELECT salary FROM employees WHERE employee_id = 188;
SALARY
----------
3800
UPDATE employees SET salary = salary*10 WHERE employee_id = 188;
COMMIT;
SELECT salary FROM employees WHERE employee_id = 188;
SALARY
----------
38000
SELECT SCN_TO_TIMESTAMP(ORA_ROWSCN) FROM employees WHERE employee_id = 188;
SCN_TO_TIMESTAMP(ORA_ROWSCN)
---------------------------------------------------------------------------
28-AUG-03 01.58.01.000000000 PM
FLASHBACK TABLE employees TO TIMESTAMP TO_TIMESTAMP('28-AUG-03 01.00.00.000000000 PM');
SELECT salary FROM employees WHERE employee_id = 188;
SALARY
----------
3800
TIMESTAMP_TO_SCN :
Purpose
TIMESTAMP_TO_SCN takes as an argument a timestamp value and returns the approximate system change number (SCN) associated with that timestamp. The returned value is of data type NUMBER. This function is useful any time you want to know the SCN associated with a particular timestamp
Note: The association between an SCN and a timestamp when the SCN is generated is remembered by the database for a limited period of time. This period is the maximum of the auto-tuned undo retention period, if the database runs in the Automatic Undo Management mode, and the retention times of all flashback archives in the database, but no less than 120 hours. The time for the association to become obsolete elapses only when the database is open. An error is returned if the timestamp specified for the argument to TIMESTAMP_TO_SCN is too old.
Examples:
The following example inserts a row into the oe.orders table and then uses TIMESTAMP_TO_SCN to determine the system change number of the insert operation. (The actual SCN returned will differ on each system.)
INSERT INTO orders (order_id, order_date, customer_id, order_total) VALUES (5000, SYSTIMESTAMP, 188, 2345);
1 row created.
COMMIT;
Commit complete.
SELECT TIMESTAMP_TO_SCN(order_date) FROM orders WHERE order_id = 5000;
TIMESTAMP_TO_SCN(ORDER_DATE)
----------------------------
574100
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29802484/viewspace-2121018/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 時間序列化資料庫選型?時序資料庫的選擇?資料庫
- 實時資料庫與時序資料庫資料庫
- BBED 修改oracle 資料檔案的 SCN 號來做資料庫不完全恢復。Oracle資料庫
- 透過修改控制檔案scn推進資料庫scn資料庫
- Oracle資料庫中的多種SCN彙總Oracle資料庫
- 備份與恢復:Polardb資料庫資料基於時間點恢復資料庫
- 時間和空間的完美統一!阿里雲時空資料庫正式商業化阿里資料庫
- 關於Oracle資料庫的時間查詢Oracle資料庫
- 從實時資料庫轉戰時序資料庫,他陪伴 TDengine 從 1.0 走到 3.0資料庫
- 【資料庫資料恢復】SQL Server資料庫磁碟空間不足的資料恢復案例資料庫資料恢復SQLServer
- MySQL 資料庫與 SQL 優化MySql資料庫優化
- 材料與化學資料庫分享資料庫
- PostgreSQL資料庫匯入大量資料時如何最佳化SQL資料庫
- 變更OS時間對資料庫的影響資料庫
- 模型取到的資料庫時間created_at慢8小時模型資料庫
- Prometheus時序資料庫-資料的查詢Prometheus資料庫
- [轉帖]達夢資料庫-統計資料表資料量及空間表大小資料庫
- 資料庫轉換工具,不同資料庫之前任意轉換資料庫
- 【clickhouse專欄】資料庫、資料倉儲之間的區別與聯絡資料庫
- 用OMF來簡化資料庫管理(轉)資料庫
- 資料庫優化之臨時表優化資料庫優化
- 從零寫一個時間序列資料庫資料庫
- 資料庫與資料庫管理系統概述資料庫
- NoSQL資料庫概念與NoSQL資料庫家族SQL資料庫
- 資料庫正規化與例項資料庫
- 關於同一個連線不同資料庫之間的 Eloquent 關聯查詢資料庫
- 時序資料庫資料庫
- 做資料庫分離讀寫時,sqlServer資料庫資料同步的問題:資料庫SQLServer
- 圖資料庫——大資料時代的高鐵資料庫大資料
- Hive 資料更新時間Hive
- 時間序列資料的處理
- 玩轉資料庫索引資料庫索引
- mongodb資料庫備份與恢復(資料庫資料遷移)MongoDB資料庫
- 資料庫優化資料庫優化
- C++資料格式化3 - 格式化時間區間(使用時長)C++
- ∑co時間 | 夯實資料基礎 推動高校數字化轉型
- MSSQL資料庫超時的原因與解決方法SQL資料庫
- 資料庫查不出資料:傳參不要加引號資料庫
- 大事務導致資料庫恢復時間長資料庫