时间戳

首页 > 解决方案 > 在 Oracle 11g 中加载外部表数据

oracle - 在 Oracle 11g 中加载外部表数据

问题描述

我想知道您是否可以帮助我完成我正在尝试适应的过程，以从跟踪文件中获取一些有趣的数据。

我有一个名为 的外部表cursor，它通过 Perl 中的程序执行数据加载cursor.pl；load 适用于所有这些字段，但在程序中发生了一些事情，lineno即保持行数的字段似乎与字符串数据相交。

我所做的是尝试将字段设置lineno为 VARCHAR(100) 以查看发生了什么，然后加载：

结果 select * from cursor

如果我想省略该sql_text字段，它会在重新创建表并执行 SELECT 时下降。

外部表

CREATE TABLE cursor (
    lineno            NUMBER,
    crsr              NUMBER,
    crsr_length       NUMBER,
    call_depth        NUMBER,
    parsing_user_id   NUMBER,
    command_type      NUMBER,
    parsing_schema_id NUMBER,
    tim               NUMBER,
    hash_value        NUMBER,
    address           VARCHAR2(50),
    sqlid             VARCHAR2(20),
    sql_text          VARCHAR2(4000)
)

ORGANIZATION EXTERNAL (

    type oracle_loader
    default directory "TRACE_DIR"

    access parameters (

        records delimited by newline
        preprocessor exe_trc_dir:'cursor.pl'
        logfile trace_dir:'cursor.log'
        badfile trace_dir:'cursor.bad'
        fields terminated by '!' optionally enclosed by "'"

        MISSING FIELD VALUES ARE NULL (
            lineno            CHAR,          
            crsr              CHAR,
            crsr_length       CHAR,
            call_depth        CHAR,
            parsing_user_id   CHAR,
            command_type      CHAR,
            parsing_schema_id CHAR,
            tim               CHAR,
            hash_value        CHAR,
            address           CHAR,
            sqlid             CHAR,
            sql_text          CHAR(4000)
        )
    )

    location ('bdutec01_ora_15431.trc')
)

光标.pl

#!/usr/bin/perl

use strict;
use warnings;

while (<>) {

    if ( /^PARSING IN CURSOR/../END OF STMT/ ) {

        if ( /^PARSING IN CURSOR/ ) {

            s/^PARSING IN CURSOR \#//;
            s/ [a-z]+=/!/g;
            s/\n$/!/;

            $_ = "$.!$_";
        }
        unless ( /^END OF STMT/ ) {
            print;
        }
    }
}

标签： oracleperl

解决方案

从 oracle 跟踪文件 cursor.pl 文件

*** 2018-08-07 21:45:39.760
*** SESSION ID:(91.29067) 2018-08-07 21:45:39.760
*** CLIENT ID:() 2018-08-07 21:45:39.760
*** SERVICE NAME:(SYS$USERS) 2018-08-07 21:45:39.760
*** MODULE NAME:(JDBC Thin Client) 2018-08-07 21:45:39.760
*** ACTION NAME:() 2018-08-07 21:45:39.760

CLOSE #140422479054456:c=0,e=4,dep=2,type=0,tim=1533696339760472
=====================
PARSING IN CURSOR #140422479054456 len=39 dep=2 uid=0 oct=42 lid=0 tim=1533696339761022 hv=1387085665 ad='0' sqlid='8ucy5sx9augv1'
ALTER SESSION SET NLS_COMP = LINGUISTIC
END OF STMT
PARSE #140422479054456:c=0,e=41,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=0,plh=0,tim=1533696339761021
EXEC #140422479054456:c=0,e=43,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=0,plh=0,tim=1533696339761161
CLOSE #140422479054456:c=0,e=5,dep=2,type=0,tim=1533696339761206
=====================
PARSING IN CURSOR #140422479054456 len=38 dep=2 uid=0 oct=42 lid=0 tim=1533696339761346 hv=2873506939 ad='0' sqlid='2m2yxqapncg3v'
ALTER SESSION SET NLS_SORT = BINARY_AI
END OF STMT
PARSE #140422479054456:c=0,e=42,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=0,plh=0,tim=1533696339761345
EXEC #140422479054456:c=0,e=27,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=0,plh=0,tim=1533696339761452
CLOSE #140422479054456:c=0,e=4,dep=2,type=0,tim=1533696339761492
=====================
PARSING IN CURSOR #140422478993488 len=466 dep=1 uid=0 oct=47 lid=0 tim=1533696339761539 hv=3544542139 ad='1936d9f28' sqlid='c85q9cz9nauxv'
DECLARE
  cmmd1 VARCHAR2(64);
  cmmd2 VARCHAR2(64);
  --cmmd3 VARCHAR2(64);
BEGIN
if USER = 'UTEC' then
execute immediate 'Alter session set events ''10046 trace name context forever, level 8''';
end if;
    --cmmd3 := 'ALTER SESSION SET SQL_TRACE = TRUE';
    cmmd1 := 'ALTER SESSION SET NLS_COMP = LINGUISTIC';
    cmmd2 := 'ALTER SESSION SET NLS_SORT = BINARY_AI';
    --EXECUTE IMMEDIATE cmmd3;
    EXECUTE IMMEDIATE cmmd1;
    EXECUTE IMMEDIATE cmmd2;
END;
END OF STMT
EXEC #140422478993488:c=3000,e=2494,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=0,tim=1533696339761537
CLOSE #140422478993488:c=0,e=36,dep=1,type=1,tim=1533696339761810
WAIT #0: nam='SQL*Net message to client' ela= 3 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1533696339761911
WAIT #0: nam='SQL*Net message from client' ela= 25844 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1533696339787818
WAIT #0: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1533696339787880

推荐阅读