| □从Oracle数据库9i开始，Oracl...... 从Oracle数据库9i开始，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 - Production With the Partitioning option JServer 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 a30 SQL> col owner for a10 SQL> 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_loader 4 default directory BDUMP 5 access parameters ( 6 records delimited by newline 7 nobadfile 8 nodiscardfile 9 nologfile 10 ) 11 location('alert_eygle.log') 12 ) 13 reject limit unlimited 14 / Table created. 然后我们就可以通过外部表进行查询警告日志的内容: SQL> select * from alert_log where rownum < 51; TEXT --------------------------------------------------- Mon Jun 26 12:00:24 2006 Starting ORACLE instance (normal) Mon Jun 26 12:00:25 2006 WARNING: EINVAL creating segment of size 0x0000000008c00000 fix shm parameters in /etc/system or equivalent LICENSE_MAX_SESSION = 0 LICENSE_SESSIONS_WARNING = 0 SCN scheme 2 Using log_archive_dest parameter default value LICENSE_MAX_USERS = 0 SYS auditing is disabled Starting up ORACLE RDBMS Version: 9.2.0.4.0. System parameters with non-default values: processes = 150 timed_statistics = TRUE shared_pool_size = 104857600 large_pool_size = 0 java_pool_size = 0 control_files = /opt/oracle/oradata/eygle/control01.ctl db_block_size = 8192 db_cache_size = 16777216 db_cache_advice = ON compatible = 9.2.0.0.0 db_file_multiblock_read_count= 16 fast_start_mttr_target = 300 log_checkpoints_to_alert = TRUE undo_management = AUTO undo_tablespace = UNDOTBS1 undo_retention = 10800 remote_login_passwordfile= EXCLUSIVE db_domain = instance_name = eygle job_queue_processes = 10 hash_join_enabled = TRUE background_dump_dest = /opt/oracle/admin/eygle/bdump user_dump_dest = /opt/oracle/admin/eygle/udump core_dump_dest = /opt/oracle/admin/eygle/cdump sort_area_size = 524288 db_name = eygle open_cursors = 500 star_transformation_enabled= FALSE query_rewrite_enabled = FALSE pga_aggregate_target = 52428800 aq_tm_processes = 0 PMON started with pid=2 DBW0 started with pid=3 LGWR started with pid=4 CKPT started with pid=5 SMON started with pid=6 RECO 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 TEMP ORA-1113 signalled during: alter database open... ORA-1113 signalled during: alter database datafile 3 online... ORA-09968: scumnt: unable to lock file ORA-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 file ORA-01110: data file 3: '/opt/oracle/oradata/eygle/users01.dbf' ORA-27037: unable to obtain file status ORA-01157: cannot identify/lock data file 3 - see DBWR trace file ORA-01110: data file 3: '/opt/oracle/oradata/eygle/users01.dbf' TEXT ----------------------------------------------------- ORA-01157: cannot identify/lock data file 4 - see DBWR trace file ORA-01110: data file 4: '/opt/oracle/oradata/eygle/eygle01.dbf' ORA-27037: unable to obtain file status ORA-01157: cannot identify/lock data file 4 - see DBWR trace file ORA-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 file ORA-01110: data file 4: '/opt/oracle/oradata/eygle/eygle01.dbf' ORA-27037: unable to obtain file status ORA-01157: cannot identify/lock data file 4 - see DBWR trace file ORA-01110: data file 4: '/opt/oracle/oradata/eygle/eygle01.dbf' TEXT --------------------------------------------------- ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL... ORA-1113 signalled during: alter database open... ORA-01157: cannot identify/lock data file 4 - see DBWR trace file ORA-01110: data file 4: '/opt/oracle/oradata/eygle/eygle01.dbf' ORA-27037: unable to obtain file status ORA-01157: cannot identify/lock data file 4 - see DBWR trace file ORA-01110: data file 4: '/opt/oracle/oradata/eygle/eygle01.dbf' ORA-1113 signalled during: alter database open... ORA-1122 signalled during: alter database open... ORA-283 signalled during: ALTER DATABASE RECOVER database ... ORA-1122 signalled during: alter database open... TEXT ------------------------------------------------ ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL... ORA-1113 signalled during: ALTER DATABASE OPEN... ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL... ORA-1122 signalled during: ALTER DATABASE OPEN... ORA-1503 signalled during: CREATE CONTROLFILE REUSE DATABASE "EYGLE" NORESETL... ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL... ORA-1991 signalled during: ALTER DATABASE MOUNT... ORA-01110: data file 4: '/opt/oracle/oradata/eygle/eygle01.dbf' ORA-01115: IO error reading block from file 4 (block # 1) ORA-27069: skgfdisp: attempt to do I/O beyond the range of the file ORA-01122: database file 1 failed verification check TEXT ---------------------------------------------------- ORA-01110: data file 1: '/opt/oracle/oradata/eygle/system01.dbf' ORA-01207: file is more recent than controlfile - old controlfile ORA-1122 signalled during: alter database open... ORA-283 signalled during: ALTER DATABASE RECOVER database using backup cont... ORA-01110: data file 4: '/opt/oracle/oradata/eygle/eygle01.dbf' ORA-01115: IO error reading block from file 4 (block # 1) ORA-27069: skgfdisp: attempt to do I/O beyond the range of the file ORA-01194: file 1 needs more recovery to be consistent ORA-01110: data file 1: '/opt/oracle/oradata/eygle/system01.dbf' ORA-1194 signalled during: alter database open resetlogs... ORA-283 signalled during: ALTER DATABASE RECOVER datafile 1 ... TEXT ---------------------------------------------------- ORA-283 signalled during: ALTER DATABASE RECOVER database using backup cont... ORA-01110: data file 4: '/opt/oracle/oradata/eygle/eygle01.dbf' ORA-01115: IO error reading block from file 4 (block # 1) ORA-27069: skgfdisp: attempt to do I/O beyond the range of the file ORA-01194: file 1 needs more recovery to be consistent ORA-01110: data file 1: '/opt/oracle/oradata/eygle/system01.dbf' ORA-1194 signalled during: alter database open resetlogs... ORA-283 signalled during: ALTER DATABASE RECOVER datafile 1 ... ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL... ORA-1589 signalled during: ALTER DATABASE OPEN... ORA-01110: data file 4: '/opt/oracle/oradata/eygle/eygle01.dbf' TEXT --------------------------------------------------- ORA-01115: IO error reading block from file 4 (block # 1) ORA-27069: skgfdisp: attempt to do I/O beyond the range of the file ORA-01194: file 1 needs more recovery to be consistent ORA-01110: data file 1: '/opt/oracle/oradata/eygle/system01.dbf' ORA-1194 signalled during: alter database open resetlogs... ORA-1109 signalled during: alter database close... ORA-1503 signalled during: CREATE CONTROLFILE REUSE DATABASE "EYGLE" NORESETL... ORA-1507 signalled during: ALTER DATABASE CLOSE NORMAL... ORA-1113 signalled during: alter database open... ORA-00202: controlfile: '/opt/oracle/oradata/eygle/control01.ctl' ORA-27037: unable to obtain file status TEXT ------------------------------------------------ ORA-205 signalled during: ALTER DATABASE MOUNT... ORA-1507 signalled during: ALTER DATABASE CLOSE NORMAL... ORA-01501: CREATE DATABASE failed ORA-01526: error in opening file '?/rdbms/admin/sql.bsq' ORA-07391: sftopn: fopen error ORA-01526: error in opening file '' ORA-1092 signalled during: CREATE DATABASE eygle ORA-1079 signalled during: ALTER DATABASE MOUNT... ORA-1507 signalled during: alter database open... ORA-214 signalled during: alter database mount... ORA-1507 signalled during: ALTER DATABASE CLOSE NORMAL... TEXT ----------------------------------------------- ORA-214 signalled during: ALTER DATABASE MOUNT... ORA-214 signalled during: alter database mount... ORA-1113 signalled during: ALTER DATABASE OPEN... ORA-01157: cannot identify/lock data file 3 - see DBWR trace file ORA-01110: data file 3: '/opt/oracle/oradata/eygle/eygle02.dbf' ORA-27037: unable to obtain file status ORA-1113 signalled during: ALTER DATABASE OPEN... ORA-01157: cannot identify/lock data file 3 - see DBWR trace file ORA-01110: data file 3: '/opt/oracle/oradata/eygle/eygle02.dbf' ORA-27037: unable to obtain file status ORA-1113 signalled during: alter database open... TEXT ------------------------------------------------------ ORA-01157: cannot identify/lock data file 3 - see DBWR trace file ORA-01110: data file 3: '/opt/oracle/oradata/eygle/eygle02.dbf' ORA-27037: unable to obtain file status ORA-283 signalled during: ALTER DATABASE RECOVER database ... ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL... ORA-1100 signalled during: alter database mount... ORA-1178 signalled during: alter database create datafile '/opt/oracle/produc... ORA-1516 signalled during: alter database create datafile '/opt/oracle/oradat... ORA-1991 signalled during: ALTER DATABASE MOUNT... ORA-01157: cannot identify/lock data file 3 - see DBWR trace file ORA-01110: data file 3: '/opt/oracle/oradata/eygle/eygle02.dbf' TEXT ------------------------------------------------------ ORA-27037: unable to obtain file status ORA-283 signalled during: ALTER DATABASE RECOVER database ... ORA-01157: cannot identify/lock data file 3 - see DBWR trace file ORA-01110: data file 3: '/opt/oracle/oradata/eygle/eygle02.dbf' ORA-27037: unable to obtain file status ORA-283 signalled during: ALTER DATABASE RECOVER database using backup cont... ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL... ORA-1991 signalled during: ALTER DATABASE MOUNT... ORA-01157: cannot identify/lock data file 3 - see DBWR trace file ORA-01110: data file 3: '/opt/oracle/oradata/eygle/users01.dbf' ORA-27037: unable to obtain file status TEXT ------------------------------------------------------ ORA-283 signalled during: ALTER DATABASE RECOVER database ... ORA-01157: cannot identify/lock data file 3 - see DBWR trace file ORA-01110: data file 3: '/opt/oracle/oradata/eygle/users01.dbf' ORA-27037: unable to obtain file status ORA-283 signalled during: ALTER DATABASE RECOVER database using backup cont... ORA-01157: cannot identify/lock data file 3 - see DBWR trace file ORA-01110: data file 3: '/opt/oracle/oradata/eygle/users01.dbf' ORA-27037: unable to obtain file status ORA-283 signalled during: ALTER DATABASE RECOVER database using backup cont... ORA-279 signalled during: ALTER DATABASE RECOVER database using backup cont... ORA-308 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ... TEXT ------------------------------------------------- ORA-308 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ... ORA-1547 signalled during: ALTER DATABASE RECOVER CANCEL ... ORA-1589 signalled during: alter database open... ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL... ORA-1503 signalled during: CREATE CONTROLFILE REUSE DATABASE "EYGLE" NORESETL... ORA-1178 signalled during: alter database create datafile '/opt/oracle/produc... ORA-1991 signalled during: ALTER DATABASE MOUNT... ORA-01157: cannot identify/lock data file 3 - see DBWR trace file ORA-01110: data file 3: '/opt/oracle/oradata/eygle/eygle02.dbf' ORA-27037: unable to obtain file status ORA-1157 signalled during: alter database open... TEXT -------------------------------------------------- ORA-1113 signalled during: alter database open... ORA-1991 signalled during: ALTER DATABASE MOUNT... ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL... ORA-1031 signalled during: alter database open... ORA-3217 signalled during: ALTER TABLESPACE TEMP DEFAULT STORAGE (INITIAL 10M... ORA-1507 signalled during: alter database close... ORA-1507 signalled during: ALTER DATABASE CLOSE NORMAL... ORA-1507 signalled during: alter database close normal... ORA-1507 signalled during: ALTER DATABASE CLOSE NORMAL... ORA-1106 signalled during: alter database dismount... ORA-1531 signalled during: alter database open... TEXT ------------------------------------------------- ORA-1531 signalled during: alter database open... ORA-1531 signalled during: alter database open... ORA-1531 signalled during: alter database open... ORA-1531 signalled during: alter database open... ORA-1109 signalled during: alter database close... ORA-1507 signalled during: alter database close... ORA-1507 signalled during: ALTER DATABASE CLOSE NORMAL... ORA-1185 signalled during: alter database add logfile group 6 ORA-350 signalled during: alter database drop logfile group 3... 163 rows selected. SQL> |
|小黑屋|最新主题|手机版|微赢网络技术论坛
( 苏ICP备08020429号 )
GMT+8, 2024-9-29 13:30 , Processed in 0.285172 second(s), 12 queries , Gzip On, MemCache On.
Powered by Discuz! X3.5
© 2001-2023 Discuz! Team.
