Scripts:显示闪回数据库状态的脚本fdb_status.sql
来源:互联网 发布:中国人工智能股票龙头 编辑:程序博客网 时间:2024/06/01 07:25
-- +----------------------------------------------------------------------------+
-- | Jeffrey M. Hunter |
-- | jhunter@idevelopment.info |
-- | www.idevelopment.info |
-- |----------------------------------------------------------------------------|
-- | Copyright (c) 1998-2012 Jeffrey M. Hunter. All rights reserved. |
-- |----------------------------------------------------------------------------|
-- | DATABASE : Oracle |
-- | FILE : fdb_status.sql |
-- | CLASS : Flashback Database |
-- | PURPOSE : Provide an overview of the current state of the Flashback |
-- | database feature. First check that Flashback Database is |
-- | enabled. Next, provide an overview of the retention policy |
-- | settings and estimated size of the Flashback Logs. |
-- | NOTE : As with any code, ensure to test this script in a development |
-- | environment before attempting to run it in production. |
-- +----------------------------------------------------------------------------+
SET TERMOUT OFF;
COLUMN current_instance NEW_VALUE current_instance NOPRINT;
SELECT rpad(sys_context('USERENV', 'INSTANCE_NAME'), 17) current_instance
FROM dual;
SET TERMOUT ON;
PROMPT
PROMPT +------------------------------------------------------------------------+
PROMPT | Report : Flashback Database Status |
PROMPT | Instance : ¤t_instance |
PROMPT +------------------------------------------------------------------------+
SET ECHO OFF
SET FEEDBACK 6
SET HEADING ON
SET LINESIZE 180
SET PAGESIZE 50000
SET TERMOUT ON
SET TIMING OFF
SET TRIMOUT ON
SET TRIMSPOOL ON
SET VERIFY OFF
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
COLUMN dbid HEADING 'DB ID'
COLUMN name FORMAT a15 HEADING 'DB Name'
COLUMN log_mode FORMAT a18 HEADING 'Log Mode'
COLUMN flashback_on FORMAT a18 HEADING 'Flashback DB On?'
SELECT
dbid
, name
, log_mode
, flashback_on
FROM v$database;
COLUMN oldest_flashback_scn HEADING 'Oldest|Flashback SCN'
COLUMN oldest_flashback_time FORMAT a21 HEADING 'Oldest|Flashback Time' JUST right
COLUMN retention_target FORMAT 999,999 HEADING 'Retention|Target (min)'
COLUMN flashback_size FORMAT 9,999,999,999,999 HEADING 'Flashback|Size'
COLUMN estimated_flashback_size FORMAT 9,999,999,999,999 HEADING 'Estimated|Flashback Size'
SELECT
oldest_flashback_scn
, TO_CHAR(oldest_flashback_time, 'DD-MON-YYYY HH24:MI:SS') oldest_flashback_time
, retention_target
, flashback_size
, estimated_flashback_size
FROM v$flashback_database_log;
-- | Jeffrey M. Hunter |
-- | jhunter@idevelopment.info |
-- | www.idevelopment.info |
-- |----------------------------------------------------------------------------|
-- | Copyright (c) 1998-2012 Jeffrey M. Hunter. All rights reserved. |
-- |----------------------------------------------------------------------------|
-- | DATABASE : Oracle |
-- | FILE : fdb_status.sql |
-- | CLASS : Flashback Database |
-- | PURPOSE : Provide an overview of the current state of the Flashback |
-- | database feature. First check that Flashback Database is |
-- | enabled. Next, provide an overview of the retention policy |
-- | settings and estimated size of the Flashback Logs. |
-- | NOTE : As with any code, ensure to test this script in a development |
-- | environment before attempting to run it in production. |
-- +----------------------------------------------------------------------------+
SET TERMOUT OFF;
COLUMN current_instance NEW_VALUE current_instance NOPRINT;
SELECT rpad(sys_context('USERENV', 'INSTANCE_NAME'), 17) current_instance
FROM dual;
SET TERMOUT ON;
PROMPT
PROMPT +------------------------------------------------------------------------+
PROMPT | Report : Flashback Database Status |
PROMPT | Instance : ¤t_instance |
PROMPT +------------------------------------------------------------------------+
SET ECHO OFF
SET FEEDBACK 6
SET HEADING ON
SET LINESIZE 180
SET PAGESIZE 50000
SET TERMOUT ON
SET TIMING OFF
SET TRIMOUT ON
SET TRIMSPOOL ON
SET VERIFY OFF
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
COLUMN dbid HEADING 'DB ID'
COLUMN name FORMAT a15 HEADING 'DB Name'
COLUMN log_mode FORMAT a18 HEADING 'Log Mode'
COLUMN flashback_on FORMAT a18 HEADING 'Flashback DB On?'
SELECT
dbid
, name
, log_mode
, flashback_on
FROM v$database;
COLUMN oldest_flashback_scn HEADING 'Oldest|Flashback SCN'
COLUMN oldest_flashback_time FORMAT a21 HEADING 'Oldest|Flashback Time' JUST right
COLUMN retention_target FORMAT 999,999 HEADING 'Retention|Target (min)'
COLUMN flashback_size FORMAT 9,999,999,999,999 HEADING 'Flashback|Size'
COLUMN estimated_flashback_size FORMAT 9,999,999,999,999 HEADING 'Estimated|Flashback Size'
SELECT
oldest_flashback_scn
, TO_CHAR(oldest_flashback_time, 'DD-MON-YYYY HH24:MI:SS') oldest_flashback_time
, retention_target
, flashback_size
, estimated_flashback_size
FROM v$flashback_database_log;
0 0
- Scripts:显示闪回数据库状态的脚本fdb_status.sql
- Scripts:显示所有闪回日志的脚本fdb_log_files.sql
- Scripts:显示数据库所有锁的脚本locks_blocking.sql
- Scripts:显示数据库中DML锁的脚本 locks_dml_lock_time.sql
- Scripts:显示数据库中长时间运行的SQL的脚本longtime.sql
- Scripts:基于时间显示闪回日志中redo的脚本fdb_redo_time_matrix.sql
- Scripts:显示数据库中每小时每日的redo切换的脚本logswitchsize.sql
- Scripts:显示数据库中长时间运行的操作的脚本longops.sql
- Scripts:显示数据库中锁和等待锁的脚本 locks_blocking2.sql
- Scripts:显示数据库中DML,DDL锁的脚本locks_dml_ddl.sql
- Scripts:显示数据库每小时每日redo切换频率的脚本logswitchfreq.sql
- Scripts:显示AWR中执行计划的脚本dplan_allstats.sql
- Scripts:报告数据库中的top segment的脚本dba_top_segments.sql
- Scripts:显示数据库中闪回区的相关信息 fra_status.sql
- Scripts:显示数据库中闪回区文件的信息fra_files.sql
- Scripts:查看回滚的SQL的脚本 huigun.sql
- Scripts:数据库升级诊断脚本dbupgdiag.sql
- Scripts:列出用户信息的脚本sec_users.sql
- 【大数据】大数据排序或取重或去重相关问题
- 把Win8PE制作成U盘版的万能微型电脑维护系统
- Scripts:基于时间显示闪回日志中redo的脚本fdb_redo_time_matrix.sql
- Maven 核心概念——站点生成和报告
- 从埃及分数看迭代加深搜索
- Scripts:显示闪回数据库状态的脚本fdb_status.sql
- 网址
- linux -- xinput 输入设备管理
- Linux下面开起启动服务命令(chkconfig、ntsysv)
- oracle 分割字符串 截取字符串
- Dozer轻松实现对象间属性复制
- 2014百度之星初赛(第二场)——Scenic Popularity
- 排序算法之快速排序的随机化版本
- 网页生成二维码的例子