【赛迪网－it技术报道】从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>
|
闽公网安备 35060202000074号