oracle外部表创建以及收集统计信息以及临时表

最新推荐文章于 2021-08-28 11:31:21 发布

cuanwu6401

最新推荐文章于 2021-08-28 11:31:21 发布

阅读量233

点赞数

sql> create table trace_log(
log_text varchar2(4000)
)
organization external
(
type oracle_loader
default directory dumpdir
access parameters
(
records delimited by newline
nobadfile
nodiscardfile
nologfile
)
location('orcl_ora_1976.trc')
)
reject limit unlimited
/

SQL> alter table trace_log location('orcl_ora_3428.trc');

外部表定义分析

CREATE TABLE tablename

(

column1 VARCHAR2(100),

column2 NUMBER,

....

)

ORGANIZATION EXTERNAL

(

TYPE ORACLE_LOADER

DEFAULT DIRECTORY "xx"

ACCESS PARAMETERS

(

RECORDS DELIMITED BY 0X'0A'

SKIP 1

BADFILE 'bad.txt'

FIELDS TERMINATED BY ','

OPTIONALLY ENCLOSED BY '"'

LRTRIM MISSING FIELD VALUES ARE NULL

REJECT ROWS WITH ALL NULL FIELDS

)

LOCATION

("CJ_DIR":'data.txt')

)REJECT LIMIT UNLIMITED;

外部表定义的几个重点

a.ORGANIZATIONEXTERNAL关键字，必须要有。以表明定义的表为外部表。

b.重要参数外部表的类型

ORACLE_LOADER：定义外部表的缺省方式，只能只读方式实现文本数据的装载。

ORACLE_DATAPUMP：支持对数据的装载与卸载，数据文件必须为二进制dump文件。可以从外部表提取数据装载到内部表，也可以从内部表卸载数据作为二进制文件填充到外部表。

c.DEFAULT DIRECTORY：缺省的目录指明了外部文件所在的路径

d.LOCATION：定义了外部表的位置

f.ACCESS PARAMETERS：描述如何对外部表进行访问

RECORDS关键字后定义如何识别数据行

DELIMITEDBY'XXX'——换行符，常用newline定义换行，并指明字符集。对于特殊的字符则需要单独定义，如特殊符号，可以使用OX'十六位值'，例如tab(/t)的十六位是9，则DELIMITEDBY0X'09'； cr(/r)的十六位是d，那么就是DELIMITEDBY0X'0D'。

SKIP X ——跳过X行数据，有些文件中第一行是列名，需要跳过第一行，则使用SKIP 1。

FIELDS关键字后定义如何识别字段，常用的如下：

FIELDS：TERMINATEDBY'x'——字段分割符。

ENCLOSEDBY'x'——字段引用符，包含在此符号内的数据都当成一个字段。

例如一行数据格式如："abc","a""b,""c,"。使用参数TERMINATED BY',' ENCLOSED BY'"'后，系统会读到两个字段，

第一个字段的值是abc，第二个字段值是a"b,"c,。

LRTRIM ——删除首尾空白字符。

MISSING FIELD VALUES ARE NULL——某些字段空缺值都设为NULL。

对于字段长度和分割符不确定且准备用作外部表文件，可以使用UltraEdit、Editplus等来进行分析测试，如果文件较大，则需要考虑将文件分割成小文件并从中提取数据进行测试。

外部表对错误的处理

REJECT LIMIT UNLIMITED

在创建外部表时最后加入LIMIT子句，表示可以允许错误的发生个数。默认值为零。设定为UNLIMITED则错误不受限制

BADFILE和NOBADFILE子句

用于指定将捕获到的转换错误存放到哪个文件。如果指定了NOBADFILE则表示忽略转换期间的错误

如果未指定该参数，则系统自动在源目录下生成与外部表同名的.BAD文件

BADFILE记录本次操作的结果，下次将会被覆盖

LOGFILE和NOLOGFILE子句

同样在accessparameters中加入LOGFILE'LOG_FILE.log'子句，则所有Oracle的错误信息放入'LOG_FILE.log'中

而NOLOGFILE子句则表示不记录错误信息到log中，如忽略该子句，系统自动在源目录下生成与外部表同名的.LOG文件

注意以下几个常见的问题

1.外部表经常遇到BUFFER不足的情况，因此尽可能的增大READSIZE

2.换行符不对产生的问题。在不同的操作系统中换行符的表示方法不一样，碰到错误日志提示如是换行符问题，可以使用

UltraEdit打开，直接看十六进制

3.特定行报错时，查看带有"BAD"的日志文件,其中保存了出错的数据,用记事本打开看看那里出错,是否存在于外部表定义相冲突

oracle收集统计信息

dbms_stats.gather_table_stats('TEST','T');

ORACLE 10G以后，自动收集统计信息，在select * from dba_scheduler_jobs里面，时间是select * from dba_scheduler_window_groups where window_group_name in (select schedule_name from dba_scheduler_jobs)

create golbal temprary table tmpt1 on commit preserver rows

as select * from dba_objects where 1=2;

create golbal temprary table tmpt1 on commit delete rows

as select * from dba_objects where 1=2;

----- 查看当前日志产生的日志量

select a.name,b.value,b.sid from v$statname a,v$sesstat b where a.statistic# in (133,134)
and a.statistic#=b.statistic# and b.sid=(select distinct sid from v$mystat);

先看一下select 1 from dual和select sysdate from dual的区别。

10G:
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0    Production
TNS for HPUX: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> set serveroutput on
SQL> declare
  2 i pls_integer;
  3 b pls_integer;
  4 e pls_integer;
  5 c pls_integer;
  6 d date;
  7  begin
  8 select value
  9    into b
10    from v$mystat t, v$statname x
11    where t.STATISTIC# = x.STATISTIC#
12    and x.NAME = 'CPU used by this session';
13
14 for i in 1 .. 100000 loop
15    select 1 into c from dual;
16 end loop;
17
18 select value
19    into e
20    from v$mystat t, v$statname x
21    where t.STATISTIC# = x.STATISTIC#
22    and x.NAME = 'CPU used by this session';
23 dbms_output.put_line(e-b);
24  end;
25  /
236
PL/SQL 过程已成功完成。
SQL> declare
  2 i pls_integer;
  3 b pls_integer;
  4 e pls_integer;
  5 c pls_integer;
  6 d date;
  7  begin
  8 select value
  9    into b
10    from v$mystat t, v$statname x
11    where t.STATISTIC# = x.STATISTIC#
12    and x.NAME = 'CPU used by this session';
13
14 for i in 1 .. 100000 loop
15    select sysdate into d from dual;
16 end loop;
17
18 select value
19    into e
20    from v$mystat t, v$statname x
21    where t.STATISTIC# = x.STATISTIC#
22    and x.NAME = 'CPU used by this session';
23 dbms_output.put_line(e-b);
24  end;
25  /
276
PL/SQL 过程已成功完成。

11G:
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0    Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
SQL> set serveroutput on
SQL> declare
  2 i pls_integer;
  3 b pls_integer;
  4 e pls_integer;
  5 c pls_integer;
  6 d date;
  7  begin
  8 select value
  9    into b
10    from v$mystat t, v$statname x
11    where t.STATISTIC# = x.STATISTIC#
12    and x.NAME = 'CPU used by this session';
13
14 for i in 1 .. 100000 loop
15    select 1 into c from dual;
16 end loop;
17
18 select value
19    into e
20    from v$mystat t, v$statname x
21    where t.STATISTIC# = x.STATISTIC#
22    and x.NAME = 'CPU used by this session';
23 dbms_output.put_line(e-b);
24  end;
25  /
155
PL/SQL 过程已成功完成。
SQL> declare
  2 i pls_integer;
  3 b pls_integer;
  4 e pls_integer;
  5 c pls_integer;
  6 d date;
  7  begin
  8 select value
  9    into b
10    from v$mystat t, v$statname x
11    where t.STATISTIC# = x.STATISTIC#
12    and x.NAME = 'CPU used by this session';
13
14 for i in 1 .. 100000 loop
15    select sysdate into d from dual;
16 end loop;
17
18 select value
19    into e
20    from v$mystat t, v$statname x
21    where t.STATISTIC# = x.STATISTIC#
22    and x.NAME = 'CPU used by this session';
23 dbms_output.put_line(e-b);
24  end;
25  /
168
PL/SQL 过程已成功完成。

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

转载于:http://blog.itpub.net/22374393/viewspace-710394/