ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 10g 学习四:oracle 外部表

10g 学习四:oracle 外部表

原创 Linux操作系统 作者:wuft2003 时间:2011-04-11 17:18:39 0 删除 编辑

1. 外部表:           
   外部表是指不在数据库中的表,只能对外部数据表进行读,不能修改。           
   1)需要先建立目录对象,但数据库不会确认目录是否真的存在,需要手工创建           
   2)系统会在访问的文件同目录下生成一个日志文件,可以根据这个文件查看访问的频度,成功情况           
   3)删除的时候先要删除外部表,然后再删除目录对象。           
"   4) select * from dba_external_locations; 通过查询这张表,系统会反映当前所有的目录对象以及相关的外部表,
       还会查询出这些外部表所对应的操作系统文件的名字" 
SQL> show parameters dump
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
background_core_dump                 string      partial
background_dump_dest                 string      C:\ORACLE\PRODUCT\10.2.0\ADMIN\KEBC\BDUMP
core_dump_dest                       string      C:\ORACLE\PRODUCT\10.2.0\ADMIN\KEBC\CDUMP
max_dump_file_size                   string      UNLIMITED
shadow_core_dump                     string      partial
user_dump_dest                       string      C:\ORACLE\PRODUCT\10.2.0\ADMIN\KEBC\UDUMP

SQL> create or replace directory bdump as 'C:\ORACLE\PRODUCT\10.2.0\ADMIN\KEBC\BDUMP';
 
Directory created
 
SQL> select * from dba_directories;
 
OWNER                          DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ ------------------------------ --------------------------------------------------------------------------------
SYS                            WORK_DIR                       C:\oracle\product\10.2.0\db_1/work
SYS                            BDUMP                          C:\ORACLE\PRODUCT\10.2.0\ADMIN\KEBC\BDUMP
SYS                            DATA_PUMP_DIR                  C:\oracle\product\10.2.0\admin\kebc\dpdump\
SYS                            ADMIN_DIR                      C:\oracle\product\10.2.0\db_1/md/admin
 
SQL> create table alert_log(text varchar2(4000))
  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_kebc.log')
 12  )
 13  reject limit unlimited
 14  /
         
SQL> select * from alert_log where rownum <50;
 
TEXT
--------------------------------------------------------------------------------
Dump file c:\oracle\product\10.2.0\admin\kebc\bdump\alert_kebc.log
Fri Apr 08 10:56:22 2011
ORACLE V10.2.0.3.0 - Production vsnsta=0
vsnsql=14 vsnxtr=3
Windows NT Version V6.0 Service Pack 1
CPU                 : 4 - type 586
Process Affinity    : 0x00000000
Memory (Avail/Total): Ph:3314M/4086M
Fri Apr 08 10:56:22 2011
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Shared memory segment for instance monitoring created
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE=BR
ILAT =18
LICENSE_MAX_USERS = 0
SYS auditing is disabled
 
TEXT
--------------------------------------------------------------------------------
ksdpec: called for event 13740 prior to event group initialization
Starting up ORACLE RDBMS Version: 10.2.0.3.0.
System parameters with non-default values:
  processes                = 150
  sga_target               = 612368384
  control_files            = C:\ORACLE\PRODUCT\10.2.0\ORADATA\KEBC\CONTROL01.CTL
  db_block_size            = 8192
  compatible               = 10.2.0.3.0
  db_file_multiblock_read_count= 16
  db_recovery_file_dest    = C:\oracle\product\10.2.0\flash_recovery_area
  db_recovery_file_dest_size= 2147483648
  undo_management          = AUTO
  undo_tablespace          = UNDOTBS1
  remote_login_passwordfile= EXCLUSIVE
  db_domain                = rocfs_kebc
  dispatchers              = (PROTOCOL=TCP) (SERVICE=kebcXDB)
  job_queue_processes      = 10
  audit_file_dest          = C:\ORACLE\PRODUCT\10.2.0\ADMIN\KEBC\ADUMP
  background_dump_dest     = C:\ORACLE\PRODUCT\10.2.0\ADMIN\KEBC\BDUMP
  user_dump_dest           = C:\ORACLE\PRODUCT\10.2.0\ADMIN\KEBC\UDUMP
  core_dump_dest           = C:\ORACLE\PRODUCT\10.2.0\ADMIN\KEBC\CDUMP
 
TEXT
--------------------------------------------------------------------------------
  db_name                  = kebc
  open_cursors             = 300
  pga_aggregate_target     = 203423744
PMON started with pid=2
PSP0 started with pid=3
MMAN started with pid=4
DBW0 started with pid=5
LGWR started with pid=6
 
49 rows selected
 
SQL> select * from dba_external_locations;
 
OWNER                          TABLE_NAME                     LOCATION          DIRECTORY_OWNER DIRECTORY_NAME
------------------------------ ------------------------------ ----------------- --------------- ------------------------------
SYSTEM                         ALERT_LOG                      alert_kebc.log  
SQL> select * from dba_external_tables;
 
OWNER                          TABLE_NAME                     TYPE_OWNER TYPE_NAME                      DEFAULT_DIRECTORY_OWNER DEFAULT_DIRECTORY_NAME         REJECT_LIMIT                             ACCESS_TYPE ACCESS_PARAMETERS                                                                PROPERTY
------------------------------ ------------------------------ ---------- ------------------------------ ----------------------- ------------------------------ ---------------------------------------- ----------- -------------------------------------------------------------------------------- ----------
SYSTEM                         ALERT_LOG                      SYS        ORACLE_LOADER                  SYS                     BDUMP                          UNLIMITED                                CLOB        records delimited by newline                                                     ALL
                                                                                                                                                                                                                    nobadfile                                                                       
                                                                                                                                                                                                                    nodiscardfile                                                                   
 

 

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

上一篇: 小童呓语:杂谈
下一篇: AT 命令
请登录后发表评论 登录
全部评论

注册时间:2009-05-12

  • 博文量
    295
  • 访问量
    342258