oracle外部表kup-04023,sqlldr如何添加忽律错误得参数

最新推荐文章于 2021-04-16 02:33:48 发布

落地后的阿飞们

最新推荐文章于 2021-04-16 02:33:48 发布

阅读量562

点赞数

文章标签： oracle外部表kup-04023

Oracle外部表使用方法

1、版本信息

SQL> conn / as sysdba;

已连接。

SQL> select * from v$version;

BANNER

--------------------------------------------------------

Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production

PL/SQL Release 9.2.0.1.0 - Production

CORE 9.2.0.1.0 Production

TNS for 32-bit Windows: Version 9.2.0.1.0 - Production

NLSRTL Version 9.2.0.1.0 – Production

2、创建外部目录

SQL> host

Microsoft Windows 2000 [Version 5.00.2195]

D:\mkdir d:\external

3、创建数据库外部表目录

SQL> create directory oasis_ext as 'd:\external';

目录已创建。

4、分配访问权限

SQL> grant read on directory oasis_ext to scfc;

授权成功。

SQL> grant write on directory oasis_ext to scfc;

授权成功。

5、生成外部文件、并读取信息

SQL> host

Microsoft Windows 2000 [Version 5.00.2195]

D:\>cd external

D:\external>more a.dat

a,b

c,d

6、建立外部表

SQL> conn scfc/scfc

已连接。

SQL> drop table oasis_ext;

SQL> create table oasis_ext ( a varchar2(2),b varchar2(2))

organization external

( type oracle_loader

default directory oasis_ext

access parameters

( records delimited by newline

badfile 'oasis_ext1.bad'

logfile 'oasis_ext1.log'

fields terminated by ','

)

location ('a.dat'));

表已创建。

7、访问创建好的外部表

SQL> select * from oasis_ext;

A B

-- --

a b

c d

SQL> select a from oasis_ext ;

SQL> select b from oasis_ext;

SQL> select a,b from oasis_ext where rownum<2;

A B

-- --

a b

注意对于delete,insert,update这些DML操作暂时不支持。外部表不支持索引

功能。

在系统外部目录下，如果不制定badfile和logfile文件，系统会自动产生一日

志文件，可以在查询期内察看俩文件，以诊断某些问题。

下面是本例产生的日志文件：

LOG file opened at 05/08/05 09:32:49

Field Definitions for table A_EXT

Record format DELIMITED BY NEWLINE

Data in file has same endianness as the platform

Rows with all null fields are accepted

Fields in Data Source:

A CHAR (255)

Terminated by ","

Trim whitespace same as SQL Loader

B CHAR (255)

Terminated by ","

Trim whitespace same as SQL Loader

8、涉及到的一些系统信息

SQL> select * from tab where tname like '%LOAD%';

TNAME TABTYPE CLUSTERID

------------------------------ ------- ----------

GV_$LOADISTAT VIEW

GV_$LOADPSTAT VIEW

LOADER_COL_INFO VIEW

LOADER_CONSTRAINT_INFO VIEW

LOADER_DIR_OBJS VIEW

LOADER_FILE_TS VIEW

LOADER_OID_INFO VIEW

LOADER_PARAM_INFO VIEW

LOADER_PART_INFO VIEW

LOADER_REF_INFO VIEW

LOADER_TAB_INFO VIEW

LOADER_TRIGGER_INFO VIEW

SNAP_LOADERTIME$ TABLE

V_$LOADISTAT VIEW

V_$LOADPSTAT VIEW

DBA_EXTERNAL_TABLES VIEW

ALL_EXTERNAL_TABLES VIEW

SQL> DESC LOADER_TAB_INFO

名称是否为空? 类型

----------------------------------------- -------- ------------

NAME NOT NULL VARCHAR2(30)

NUMCOLS NOT NULL NUMBER

OWNER NOT NULL VARCHAR2(30)

OBJECTNO NOT NULL NUMBER

TABLESPACENO NOT NULL NUMBER

PARTITIONED VARCHAR2(3)

SQL> SELECT * FROM LOADER_TAB_INFO WHERE NAME LIKE '%OASIS%';

NAME NUMCOLS OWNER OBJECTNO TABLESPACENO PAR

----------------------------------------- ------------ ---

OASIS_EXT 2 SCFC 30032 0 NO

SQL> SELECT * FROM LOADER_COL_INFO WHERE TBLNAME LIKE '%EXT%';

TBLNAME COLNAME CSFORM

------------------------------ ------------------------------ ----------

OASIS_EXT AA 1

OASIS_EXT BA 1

SQL> desc LOADER_DIR_OBJS

名称是否为空? 类型

----------------------------------------- -------- --------------

NAME VARCHAR2(30)

PATH VARCHAR2(4000)

READ VARCHAR2(5)

WRITE VARCHAR2(5)

SQL> SELECT * FROM LOADER_DIR_OBJS WHERE NAME LIKE 'OASIS_EXT';

NAME PATH READ WRITE

--------------------------------------------------

OASIS_EXT d:\external TRUE TRUE

SQL> DESC EXTERNAL_TAB$

名称是否为空? 类型

----------------------------------------- -------- -------------

OBJ# NOT NULL NUMBER

DEFAULT_DIR NOT NULL VARCHAR2(30)

TYPE$ NOT NULL VARCHAR2(30)

NR_LOCATIONS NOT NULL NUMBER

REJECT_LIMIT NOT NULL NUMBER

PAR_TYPE NOT NULL NUMBER

PARAM_CLOB CLOB

PARAM_BLOB BLOB

SQL> SELECT OBJ#,DEFAULT_DIR,TYPE$,REJECT_LIMIT,PAR_TYPE FROM EXTERNAL_TAB$;

OBJ# DEFAULT_DIR TYPE$ REJECT_LIMIT PAR_TYPE

28771 DATA_FILE_DIR ORACLE_LOADER 2147483647 2

30020 ORAEXT ORACLE_LOADER 0 2

30032 OASIS_EXT ORACLE_LOADER 0 2

其他一些信息

SQL> desc oracle_loader

名称是否为空? 类型

----------------------------------------- -------- --------------------

XTCTX RAW(4)

METHOD

------

STATIC FUNCTION ODCIGETINTERFACES RETURNS NUMBER

参数名称类型输入/输出默认值？

------------------------------ ----------------------- ------ --------

IFCLIST ODCIOBJECTLIST OUT DEFAULT

METHOD

------

STATIC FUNCTION ODCIEXTTABLEOPEN RETURNS NUMBER

参数名称类型输入/输出默认值？

------------------------------ ----------------------- ------ --------

LCTX ORACLE_LOADER IN/OUT DEFAULT

XTI ODCIEXTTABLEINFO IN DEFAULT

XRI ODCIEXTTABLEQCINFO OUT DEFAULT

PCL ODCICOLINFOLIST2 OUT DEFAULT

FLAG NUMBER IN/OUT DEFAULT

STRV NUMBER IN/OUT DEFAULT

ENV ODCIENV IN DEFAULT

METHOD

------

MEMBER FUNCTION ODCIEXTTABLEFETCH RETURNS NUMBER

参数名称类型输入/输出默认值？

------------------------------ ----------------------- ------ --------

GNUM NUMBER IN DEFAULT

CNVERR NUMBER IN/OUT DEFAULT

FLAG NUMBER IN/OUT DEFAULT

ENV ODCIENV IN DEFAULT

METHOD

------

MEMBER FUNCTION ODCIEXTTABLEPOPULATE RETURNS NUMBER

参数名称类型输入/输出默认值？

------------------------------ ----------------------- ------ --------

FLAG NUMBER IN/OUT DEFAULT

ENV ODCIENV IN DEFAULT

METHOD

------

MEMBER FUNCTION ODCIEXTTABLECLOSE RETURNS NUMBER

参数名称类型输入/输出默认值？

------------------------------ ----------------------- ------ --------

FLAG NUMBER IN/OUT DEFAULT

ENV ODCIENV IN DEFAULT

9、外部表的其他一些用途

可以利用外部表察看一些文件。比如日志等等

SQL> create directory dumpdest as 'd:\oracle\admin\oasis\udump';

目录已创建。

SQL> drop table dumpdest;

drop table dumpdest

ERROR 位于第 1 行:

ORA-00942: 表或视图不存在

SQL> create table dumpdest ( a varchar2(1000))

organization external

( type oracle_loader

default directory dumpdest

access parameters

(

records delimited by newline

badfile 'dumpdest.bad'

logfile 'dumpdest.log'

)

location ('oasis_ora_992.trc')

) REJECT LIMIT 9999;

SQL> select * from dumpdest;

Dump file d:\oracle\admin\oasis\udump\oasis_ora_992.trc

Thu Mar 03 08:33:06 2005

ORACLE V9.2.0.1.0 - Production vsnsta=0

vsnsql=12 vsnxtr=3

Windows 2000 Version 5.0 Service Pack 4

Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production

With the Partitioning

JServer Release 9.2.0.1.0 - Production

Windows 2000 Version 5.0 Service Pack 4

Instance name: oasis

Redo thread mounted by this instance: 0

Oracle process number: 12

Windows thread id: 992

*** 2005-03-03 08:33:06.000

*** SESSION ID

9.1) 2005-03-03 08:33:06.000

(blkno = 0xe3

(blkno = 0xe4

Thread checkpoint rba:0x000025.00000002.0010 scn:0x0000.00297315

On-disk rba:0x000025.0000d0aa.0000 scn:0x0000.002a975f

Use incremental checkpoint cache-low RBA

Thread 1 recovery from rba:0x000025.0000c2d1.0000 scn:0x0000.00000000

----- Redo read statistics for thread 1 -----

Read rate (ASYNC) = 1772Kb/sec => 3545 blocks in 1s

Read buffer = 8192Kb (16384 blocks)

Longest record = 0Kb

Record moves = 0/7743 (0%)

----------------------------------------------

*** 2005-03-03 08:33:13.000

KCRA: start recovery claims for 126 data blocks

*** 2005-03-03 08:33:13.000

KCRA: buffers claimed = 126/126

已选择31行。

SQL> select * from dumpdest where rownum<4;

Dump file d:\oracle\admin\oasis\udump\oasis_ora_992.trc

Thu Mar 03 08:33:06 2005

ORACLE V9.2.0.1.0 - Production vsnsta=0

已选择3行。

日志如下：

LOG file opened at 05/09/05 10:05:06

Field Definitions for table DUMPDEST

Record format DELIMITED BY NEWLINE

Data in file has same endianness as the platform

Rows with all null fields are accepted

Fields in Data Source:

A CHAR (1000)

Terminated by ","

Trim whitespace same as SQL Loader

KUP-04021: field formatting error for field A