创建外部表

创建文件目录 SQL> create directory upload_dir as '/storagepool/upload'; Directory created. -- 2. 创建外部表 SQL> create table analog_tmp_entrypage_ext_1( PROFILE_ID NUMBER(22), REPORT_TIME NUMBER(22), SESSION_ID NUMBER(22), URL VARCHAR2(2048) ) organization external ( type oracle_loader default directory upload_dir access parameters (records delimited by newline fields terminated by X'05' missing field values are null (PROFILE_ID, REPORT_TIME, SESSION_ID, URL) ) location('weblog.analog_tmp_entrypage_0.dat') ); Table created. -- 3. 查询报错。因为外部表和sqlldr原理类似，加载外部数据时需要创建log文件，无法加载的数据，会创建bad文件。 -- 这里出错的原因是：oracle对路径'/storagepool/upload'没有访问权限，无法在该路径下创建log文件和bad文件。 SQL> select * from analog_tmp_entrypage_ext_1 where rownum < 2; select * from analog_tmp_entrypage_ext_1 where rownum < 2 * ERROR at line 1: ORA-29913: error in executing ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error KUP-04063: unable to open log file ANALOG_TMP_ENTRYPAGE_EXT_1_12190.log OS error Permission denied ORA-06512: at "SYS.ORACLE_LOADER", line 19 -- 4. 创建log文件和bad文件的路径，指向tmp分区。 SQL> create directory upload_log_dir as '/tmp/upload_log'; Directory created. -- 5. 重建外部表，将log和bad指向新路径 SQL> drop table analog_tmp_entrypage_ext_1 purge; Table dropped. SQL> create table analog_tmp_entrypage_ext_1( PROFILE_ID NUMBER(22), REPORT_TIME NUMBER(22), SESSION_ID NUMBER(22), URL VARCHAR2(2048) ) organization external ( type oracle_loader default directory upload_dir access parameters ( records delimited by newline badfile upload_log_dir:'analog_tmp_entrypage_ext_1%A_%P.bad' logfile upload_log_dir:'analog_tmp_entrypage_ext_1%A_%P.log' fields terminated by X'05' missing field values are null (PROFILE_ID, REPORT_TIME, SESSION_ID, URL) ) location('weblog.analog_tmp_entrypage_0.dat') ); Table created.