Scripts:查询控制文件的信息dba_controlfile_records.sql
来源:互联网 发布:http协议 json 编辑:程序博客网 时间:2024/05/21 06:30
-- +----------------------------------------------------------------------------+
-- | Jeffrey M. Hunter |
-- | jhunter@idevelopment.info |
-- | www.idevelopment.info |
-- |----------------------------------------------------------------------------|
-- | Copyright (c) 1998-2012 Jeffrey M. Hunter. All rights reserved. |
-- |----------------------------------------------------------------------------|
-- | DATABASE : Oracle |
-- | FILE : dba_controlfile_records.sql |
-- | CLASS : Database Administration |
-- | PURPOSE : Query information information about the control file record |
-- | sections. |
-- | 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(instance_name, 17) current_instance FROM v$instance;
SET TERMOUT ON;
PROMPT
PROMPT +------------------------------------------------------------------------+
PROMPT | Report : Control File Records |
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 type FORMAT a30 HEADING "Record Section Type"
COLUMN record_size FORMAT 999,999 HEADING "Record Size|(in bytes)"
COLUMN records_total FORMAT 999,999 HEADING "Records Allocated"
COLUMN bytes_alloc FORMAT 999,999,999 HEADING "Bytes Allocated"
COLUMN records_used FORMAT 999,999 HEADING "Records Used"
COLUMN bytes_used FORMAT 999,999,999 HEADING "Bytes Used"
COLUMN pct_used FORMAT B999 HEADING "% Used"
COLUMN first_index HEADING "First Index"
COLUMN last_index HEADING "Last Index"
COLUMN last_recid HEADING "Last RecID"
BREAK ON report
COMPUTE sum OF records_total ON report
COMPUTE sum OF bytes_alloc ON report
COMPUTE sum OF records_used ON report
COMPUTE sum OF bytes_used ON report
COMPUTE avg OF pct_used ON report
SELECT
type
, record_size
, records_total
, (records_total * record_size) bytes_alloc
, records_used
, (records_used * record_size) bytes_used
, NVL(records_used/records_total * 100, 0) pct_used
, first_index
, last_index
, last_recid
FROM v$controlfile_record_section
ORDER BY type
/
-- | Jeffrey M. Hunter |
-- | jhunter@idevelopment.info |
-- | www.idevelopment.info |
-- |----------------------------------------------------------------------------|
-- | Copyright (c) 1998-2012 Jeffrey M. Hunter. All rights reserved. |
-- |----------------------------------------------------------------------------|
-- | DATABASE : Oracle |
-- | FILE : dba_controlfile_records.sql |
-- | CLASS : Database Administration |
-- | PURPOSE : Query information information about the control file record |
-- | sections. |
-- | 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(instance_name, 17) current_instance FROM v$instance;
SET TERMOUT ON;
PROMPT
PROMPT +------------------------------------------------------------------------+
PROMPT | Report : Control File Records |
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 type FORMAT a30 HEADING "Record Section Type"
COLUMN record_size FORMAT 999,999 HEADING "Record Size|(in bytes)"
COLUMN records_total FORMAT 999,999 HEADING "Records Allocated"
COLUMN bytes_alloc FORMAT 999,999,999 HEADING "Bytes Allocated"
COLUMN records_used FORMAT 999,999 HEADING "Records Used"
COLUMN bytes_used FORMAT 999,999,999 HEADING "Bytes Used"
COLUMN pct_used FORMAT B999 HEADING "% Used"
COLUMN first_index HEADING "First Index"
COLUMN last_index HEADING "Last Index"
COLUMN last_recid HEADING "Last RecID"
BREAK ON report
COMPUTE sum OF records_total ON report
COMPUTE sum OF bytes_alloc ON report
COMPUTE sum OF records_used ON report
COMPUTE sum OF bytes_used ON report
COMPUTE avg OF pct_used ON report
SELECT
type
, record_size
, records_total
, (records_total * record_size) bytes_alloc
, records_used
, (records_used * record_size) bytes_used
, NVL(records_used/records_total * 100, 0) pct_used
, first_index
, last_index
, last_recid
FROM v$controlfile_record_section
ORDER BY type
/
0 0
- Scripts:查询控制文件的信息dba_controlfile_records.sql
- Scripts:按日期查询归档信息的SQL check_archive.sql
- Scripts:查询参数信息的脚本parms.sql
- Scripts:查询所有参数修改信息的脚本parm_mods.sql
- Scripts:查询没有统计信息的对象perf_objects_without_statistics.sql
- Scripts:查询回滚段信息的脚本rollback_segments.sql
- Scripts:显示数据库中闪回区文件的信息fra_files.sql
- Scripts:根据sid,ospid来查询进程信息的脚本os_pid.sql
- Scripts:查询数据库中参数文件的信息(在重新建库或者克隆数据库时经常使用)dba_cr_init.sql
- Scripts:报告数据库中数据文件控制文件临时文件redo文件的使用情况dba_file_use.sql
- EBS查询sql scripts
- Scripts:显示数据库中的控制文件dba_controlfiles.sql
- Scripts:查询排序最多的10个SQL hsort10.sql
- Scripts:查询等待事件的SQL脚本owi_event_names.sql
- Scripts:检查ASMM的各个动态组件信息asmm_components.sql
- Scripts:比较表的统计信息diff_table_stats.sql
- Scripts:诊断DRM信息的脚本DRMDIAG.SQL
- Scripts:显示数据库中闪回区的相关信息 fra_status.sql
- PHP Ajax Xml/Json形式
- Mongodb VS Hbase
- 征服C指针-NULL
- 要点Java20 java.util.Collections
- PHPExcel 导入导出 excel 文件 实例
- Scripts:查询控制文件的信息dba_controlfile_records.sql
- vware server+redhat5.4+asm+rac
- Excel2013 破解(编辑工作表受保护)密码
- 企业内训存在的八大误区(一)
- 线程和进程区别
- java生成md5校验码
- android 关于Make sure the plugin is properly configured问题的解决办法
- Hibernate 集合排序
- Linux DDos(SYN Flood)攻击的检测和防范