ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 使用Oracle的外部表查询警告日志

使用Oracle的外部表查询警告日志

原创 Linux操作系统 作者:yhj20041128001 时间:2011-06-04 00:17:19 0 删除 编辑
从Oracle9i开始,Oracle的外部表技术(Oracle External Tables)被极大的增强,通过外部表访问外部数据增强了Oracle数据库和外部数据源进行数据交互的能力,对于数据仓库和ETL来说,这些增强极大的方便了数据访问。
  对于DBA来说,最常见一个例子是可以使用外部表来访问警告日志文件或其他跟踪文件.以下一个例子用来说明外部表的用途。
  首先需要创建一个Directory:
  [oracle@jumper oracle]$ sqlplus "/ as sysdba"
  SQL*Plus: Release 9.2.0.4.0 - Production on Sun Oct 15 21:42:28 2006
  Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
  Connected to:Oracle9i Enterprise Edition Release 9.2.0.4.0 - ProductionWith the Partitioning optionJServer Release 9.2.0.4.0 - Production
  SQL create or replace directory bdump 2 as ‘/opt/oracle/admin/eygle/bdump‘;
  Directory created.
  SQL col DIRECTORY_PATH for a30SQL col owner for a10SQL select * from dba_directories;
  OWNER DIRECTORY_NAME DIRECTORY_PATH---------- ------------------------------ ------------------------------SYS BDUMP /opt/oracle/admin/eygle/bdump
  然后创建一个外部表:
  SQL create table alert_log ( text varchar2(400) )2 organization external (3 type oracle_loader4 default directory BDUMP5 access parameters (6 records delimited by newline7 nobadfile8 nodiscardfile9 nologfile10 )11 location(‘alert_eygle.log‘)12 )13 reject limit unlimited14 /
  Table created.
  然后我们就可以通过外部表进行查询警告日志的内容:
  SQL select * from alert_log where rownum < 51;
  TEXT-----------------------------------------------------------------------------------------Mon Jun 26 12:00:24 2006Starting ORACLE instance (normal)Mon Jun 26 12:00:25 2006WARNING: EINVAL creating segment of size 0x0000000008c00000fix shm parameters in /etc/system or equivalentLICENSE_MAX_SESSION = 0LICENSE_SESSIONS_WARNING = 0SCN scheme 2Using log_archive_dest parameter default valueLICENSE_MAX_USERS = 0SYS auditing is disabledStarting up ORACLE RDBMS Version: 9.2.0.4.0.System parameters with non-default values:processes = 150timed_statistics = TRUEshared_pool_size = 104857600large_pool_size = 0java_pool_size = 0control_files = /opt/oracle/oradata/eygle/control01.ctldb_block_size = 8192db_cache_size = 16777216db_cache_advice = Ncompatible = 9.2.0.0.0db_file_multiblock_read_count= 16fast_start_mttr_target = 300log_checkpoints_to_alert = TRUEundo_management = AUTOundo_tablespace = UNDOTBS1undo_retention = 10800remote_login_passwordfile= EXCLUSIVEdb_domain =instance_name = eyglejob_queue_processes = 10hash_join_enabled = TRUEbackground_dump_dest = /opt/oracle/admin/eygle/bdumpuser_dump_dest = /opt/oracle/admin/eygle/udumpcore_dump_dest = /opt/oracle/admin/eygle/cdumpsort_area_size = 524288db_name = eygleopen_cursors = 500star_transformation_enabled= FALSEquery_rewrite_enabled = FALSEpga_aggregate_target = 52428800aq_tm_processes = 0PMON started with pid=2DBW0 started with pid=3LGWR started with pid=4CKPT started with pid=5SMON started with pid=6RECO started with pid=7
  50 rows selected.
  SQL
  如果我们需要查看数据库中曾经出现过的ORA-错误,那么可以执行如下查询:
  SQL select * from alert_log where text like ‘ORA-%‘;
  TEXT-----------------------------------------------------------------------------------ORA-1652: unable to extend temp segment by 128 in tablespace TEMPORA-1113 signalled during: alter database open...ORA-1113 signalled during: alter database datafile 3 online...ORA-09968: scumnt: unable to lock fileORA-1102 signalled during: ALTER DATABASE MOUNT...ORA-1507 signalled during: ALTER DATABASE CLOSE NORMAL...ORA-01157: cannot identify/lock data file 3 - see DBWR trace fileORA-01110: data file 3: ‘/opt/oracle/oradata/eygle/users01.dbf‘ORA-27037: unable to obtain file statusORA-01157: cannot identify/lock data file 3 - see DBWR trace fileORA-01110: data file 3: ‘/opt/oracle/oradata/eygle/users01.dbf‘
  TEXT-----------------------------------------------------------------------------------ORA-01157: cannot identify/lock data file 4 - see DBWR trace fileORA-01110: data file 4: ‘/opt/oracle/oradata/eygle/eygle01.dbf‘ORA-27037: unable to obtain file statusORA-01157: cannot identify/lock data file 4 - see DBWR trace fileORA-01110: data file 4: ‘/opt/oracle/oradata/eygle/eygle01.dbf‘ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...ORA-01157: cannot identify/lock data file 4 - see DBWR trace fileORA-01110: data file 4: ‘/opt/oracle/oradata/eygle/eygle01.dbf‘ORA-27037: unable to obtain file statusORA-01157: cannot identify/lock data file 4 - see DBWR trace fileORA-01110: data file 4: ‘/opt/oracle/oradata/eygle/eygle01.dbf‘

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/23757700/viewspace-697161/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 外部表比对
请登录后发表评论 登录
全部评论

注册时间:2010-09-17

  • 博文量
    163
  • 访问量
    357490