10g 学习四：oracle 外部表

最新推荐文章于 2024-10-02 00:27:17 发布

clugcpne10995

最新推荐文章于 2024-10-02 00:27:17 发布

阅读量51

点赞数

文章标签：数据库网络操作系统

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