DM7 外部表

DM7 外部表
需指定如下信息:
1. 表名、表所属的模式名;
2. 列定义;
3. 控制文件路径。
语法格式
CREATE EXTERNAL TABLE < 表名定义> < 表结构定义>;
< 表名定义> ::=[< 模式名>.]< 表名>
< 表结构定义> ::= (< 列定义> {,< 列定义>})
< 列定义> ::= < 列名> < 数据类型>
FROM子句 = FROM子句1 | FROM子句2 | FROM子句3| FROM子句4
FROM子句1 ::= FROM ‘< 控制文件路径>‘
FROM子句2 ::= FROM DATAFILE ‘< 数据文件路径>‘ [< 数据文件参数列表>]
FROM子句3 ::= FROM < 控制文件目录> < 控制文件选项>
FROM子句4 ::= FROM DATAFILE < 数据文件目录> < 数据文件选项> [< 数据文件参数列表>]
< 数据文件参数列表> ::= PARMS(< 参数选项> {,< 参数选项>})
< 参数选项> ::= [FIELDS DELIMITED BY < 表达式>] | [RECORDS DELIMITED BY < 表达式>]|[ ERRORS ]|[BADFILE ‘< 数据文件路径>‘]| |[LOG < 日志文件路径>]|[NULL_STR ]|[SKIP < 跳过行数>]|[CHARACTER_CODE < 文件字符集>]
< 控制文件目录> ::= DEFAULT DIRECTORY < 控制文件目录名>
< 控制文件选项> ::= LOCATION (‘< 控制文件名>‘)
< 数据文件目录> ::= DEFAULT DIRECTORY < 数据文件目录名>
< 数据文件选项> ::= LOCATION (‘< 数据文件名>‘)

参数
1. < 模式名> 指明该表属于哪个模式,缺省为当前模式;
2. < 表名> 指明被创建的外部基表名;
3. < 列名> 指明基表中的列名;
4. < 数据类型> 指明列的数据类型,暂不支持多媒体类型;
5. < 控制文件路径> 指明使用的控制文件的路径的字符串;
6. < 数据文件路径> 指明使用的数据文件路径的字符串;
7. < 参数选项> FIELDS表示列分隔符;RECORDS表示行分隔符;ERRORS表示忽略外部表数据转换中出现错误的行数,取值范围为大于0的正整
数,缺省为0,表示不忽略错误。此处ERRORS 和控制文件中的ERRORS写一个就行,如果都写,以控制文件中的为主;LOG表示日志文件路径,
默认日志文件名为fldr.log;NULL_STR指定数据文件中NULL值的表示字符串,默认忽略此参数;SKIP指定跳过数据文件起始的逻辑行数,默
认为0;CHARACTER_CODE指定数据文件中数据的编码格式,默认为GBK,可选项有GBK,UTF-8,SINGLE_BYTE和EUC-KR;
8. < 表达式> 字符串或十六进制串类型表达式,列分隔符只允许字符串类型;
9. < 控制文件目录名> 指数据库对象目录的名称。
图例



语句功能
供DBA或具有CREATE_TABLE权限的用户定义外部基表。MPP环境下不支持创建外部表。
使用说明
1. < 表名>指定了所要建立的外部基表名。如果< 模式名>缺省,则缺省为当前模式。表名需要是合法的标识符。且满足SQL语法要求;
2. 外部表的表名最大长度为128个字符;
3. 所建外部基表至少要包含一个< 列名>指定的列,在一个外部基表中,各< 列名>不得相同。一张外部基表中至多可以包含2048列;
4. 外部基表不能存在大字段列;
5. 外部基表不能存在任何约束条件;
6. 外部基表不能为临时表,不能建立分区;
7. 外部基表上不能建立任何索引;
8. 外部基表是只读的,不存在表锁,不允许任何针对外部表的增删改数据操作,不允许truncate外部表操作;
9. 控制文件路径,以及数据文件路径建议采用绝对路径;
10. 控制文件的格式为:

[OPTIONS(
errors=
)]
LOAD [DATA]
INFILE [LIST] |
INTO TABLE tablename
FIELDS 

其中:
OPTIONS选项:可选部分,目前OPTIONS中仅支持errors选项,用于指定忽略数据转换出现错误的行数;此处errors 和< 参数选项>中的errors写一个就行,如果都写,以此处控制文件中的为主。 :指明使用的数据文件列表;
tablename:指明表名,创建外部表时,表名可以与此不同;
:指明同一行中各个列的分隔符;
11. 数据文件中一行数据必须以回车结束;
12. 外部表支持查询ROWID、USER和UID伪列,不支持查询TRXID伪列。

举例说明
例1 指定操作系统的一个文本文件作为数据文件,编写控制文件及建表语句。数据文件(/home/dmdba/data.txt),数据如下:

a|abc|varchar_data|12.34|12.34|12.34|12.34|0|1|1|1234|1234|1234|100|11|1234|1|1|14.2|12.1|12.1|1999-10-01|9:10:21|2002-12-12|15

控制文件(/home/dmdba/ctrl.txt)如下:

load data
infile '/home/dmdba/data.txt'
into table ext
fields '|'

建表:

SQL> create external table ext (
2   l_char char(1),
3   l_character character(3),
4   l_varchar varchar(20),
5   l_numeric numeric(6,2),
6   l_decimal decimal(6,2),
7   l_dec dec(6,2),
8   l_money decimal(19,4),
9   l_bit bit,
10  l_bool bit,
11  l_boolean bit,
12  l_integer integer,
13  l_int int,
14  l_bigint bigint,
15  l_tinyint tinyint,
16  l_byte byte,
17  l_smallint smallint,
18  l_binary binary,
19  l_varbinary varbinary,
20  l_float float,
21  l_double double,
22  l_real real,
23  l_date date,
24  l_time time,
25  l_timestamp timestamp,
26  l_interval interval year
27  )from '/home/dmdba/ctrl.txt';
executed successfully
used time: 62.123(ms). Execute id is 312.

系统执行建表语句后,就在数据库中建立了相应的外部基表。查询ext表:

SQL> select * from ext;

LINEID     L_CHAR L_CHARACTER L_VARCHAR    L_NUMERIC L_DECIMAL L_DEC L_MONEY L_BIT L_BOOL L_BOOLEAN L_INTEGER   L_INT       L_BIGINT             L_TINYINT   L_BYTE      L_SMALLINT  L_BINARY L_VARBINARY L_FLOAT                   L_DOUBLE                  L_REAL             L_DATE     L_TIME          L_TIMESTAMP                 L_INTERVAL           
---------- ------ ----------- ------------ --------- --------- ----- ------- ----- ------ --------- ----------- ----------- -------------------- ----------- ----------- ----------- -------- ----------- ------------------------- ------------------------- ------------------ ---------- --------------- --------------------------- ---------------------
1          a      abc         varchar_data 12.34     12.34     12.34 12.3400 0     1      1         1234        1234        1234                 100         11          1234        0x01     0x01        1.420000000000000E+01     1.210000000000000E+01     1.2100000E+01      1999-10-01 09:10:21        2002-12-12 00:00:00.000000  INTERVAL '15' YEAR(2)

used time: 7.248(ms). Execute id is 314.

例 2 指定操作系统的一个文本文件作为数据文件(/home/dmdba/data1.txt),数据如下:

10|9|7
4|3|2

建表:

SQL> create external table ext_2(c1 int,c2 int,c3 int) from datafile '/home/dmdba/data1.txt' parms(fields delimited by '|');
executed successfully
used time: 11.008(ms). Execute id is 331.

查询结果:

SQL> select * from ext_2;

LINEID     C1          C2          C3         
---------- ----------- ----------- -----------
1          10          9           7
2          4           3           2

used time: 8.222(ms). Execute id is 332.

DM7 AWR报告

DM有类似于Oracle的AWR报告,这里将给大家介绍如何生成数据库快照以及AWR报告。本文演示环境:DM Database Server x64 V7.1.6.46-Build(2018.02.08-89107)ENT,启用DM快照需要调用DBMS_WORKLOAD_REPOSITORY包(DM MPP环境下不支持DBMS_WORKLOAD_REPOSITORY包)
1.创建DBMS_WORKLOAD_REPOSITORY系统包。

SQL> call SP_INIT_AWR_SYS(1);
DMSQL executed successfully
used time: 647.666(ms). Execute id is 254.

2.启用状态检测。

SQL> SELECT SF_CHECK_AWR_SYS;

LINEID     SF_CHECK_AWR_SYS
---------- ----------------
1          1

used time: 5.590(ms). Execute id is 255.

3.下面语句设置间隔为60分钟,也可以是其他值:

SQL> CALL DBMS_WORKLOAD_REPOSITORY.AWR_SET_INTERVAL(60);
DMSQL executed successfully
used time: 18.797(ms). Execute id is 256.

4.手动创建快照:

SQL> call DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
DMSQL executed successfully
used time: 61.528(ms). Execute id is 258.

等待几分钟后再次执行

SQL> call DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
DMSQL executed successfully
used time: 49.152(ms). Execute id is 259.

这里我们可以多执行几遍试试,创建几个不同的快照。

5.查看创建的快照信息,包括快照id:

SQL> SELECT * FROM SYS.WRM$_SNAPSHOT;

LINEID     SNAP_ID     DBID        INSTANCE_NUMBER STARTUP_TIME                BEGIN_INTERVAL_TIME         END_INTERVAL_TIME           SNAP_LEVEL 
---------- ----------- ----------- --------------- --------------------------- --------------------------- --------------------------- -----------
1          1           NULL        1               2020-02-04 18:25:32.000000  NULL                        2020-02-04 18:28:35.586240  1
2          2           NULL        1               2020-02-04 18:25:32.000000  NULL                        2020-02-04 18:31:21.207610  1

如果此时我们想知道,对应各个时点数据库的运行状况,我们可以利用DBMS_WORKLOAD_REPOSITORY包中的AWR_REPORT_HTML方法来生成HTML格式的AWR报表。
6.生成 snapshot 的 id 在 1-2 范围内的 AWR 分析报告的带 html 格式的内容。

SELECT * FROM TABLE (DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(1,2));

7.把snapshot的id在1-2范围内的AWR分析报告生成到/home/dmdba目录下的awr1_2.html文件内。

SQL> call sys.awr_report_html(1,2,'/home/dmdba','awr1_2.html');
DMSQL executed successfully
used time: 317.395(ms). Execute id is 260.

生成的报表格式如下



从AWR报告来说,熟悉Oracle的朋友基本上可以无缝衔接到DM数据库

Oracle 12C Formatted Block Dump

Oracle 12C Formatted Block Dump
.可以从RDBMS中dump数据块
.在用户dump目录中会生成一个跟踪文件包含dump信息

对7号文件中的135号数据块执行dump

SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/shardcat/shardcat/trace/shardcat_ora_13182.trc
SQL> alter system dump datafile 7 block 135;

System altered.

对于任何数据块dump都有一个数据块头信息。它决定了剩余dump的格式。在任何Oracle块dump的头部应该可以看到块头信息:
Rdba:数据块的相对DBA
Scn:SCN序列号
Seq:序列号:
SEQ->0 /* non-logged changes – do not advance seq# */
SEQ->(UB1MAXVAL-1) /* maximum possible sequence number */
SEQ->(UB1MAXVAL) /* seq# to indicate a block is corrupt, equal to FF */

Flg: Flag(在kcbh.h中定义)
Tail:一致性数据用来验证数据块的开始与结束是否是相同版本(它由SCNBase的低阶两字节加上数据块类型加上SCN的序列号)
Frmt:数据块格式:在Oracle 12c中是2
Chkval数据块的选项检查值:如果db_block_checksum=true
Type:数据块类型(在kcb.h中定义)
Rdba:相对数据块地址
Seg/Obj:Seg/Obj ID dump输出的Seg/Obj为0x1af27

SQL> select to_number('1af27','xxxxxx') from dual;

TO_NUMBER('1AF27','XXXXXX')
---------------------------
                     110375

SQL> select object_id,object_name from dba_objects where owner='JY' and object_name='T1';

 OBJECT_ID OBJECT_NAME
---------- ------------------------------
    110375 T1

Csc:最近数据块清除
itc: itl插槽数量
Flg: E
Typ: 1=DATA;2=INDEX
Itl:相关事务列表索引(ITLs由initrans和maxtrans来决定)
Xid:事务ID(UndoSeg.Slot.Wrap)
Uba:Undo段地址(UndoDBA.SeqNo.RecordNo)
Flg:C=Committed;U=Commit Upper Bound;T=Active at CSC;B=Rollback of this UBA gives before image of the ITL.
Lck:这个事务所影响的行数
Scn/Fsc:Scn=提交事务的SCN号;Fsc=Free space credit(bytes)
例如:

*** 2020-02-07T14:35:53.082746+08:00
Start dump data blocks tsn: 4 file#:7 minblk 135 maxblk 135
Block dump from cache:
Dump of buffer cache at level 4 for pdb=0 tsn=4 rdba=29360263
Block dump from disk:
buffer tsn: 4 rdba: 0x01c00087 (7/135)
scn: 0x1e1174a seq: 0x09 flg: 0x06 tail: 0x174a0609
frmt: 0x02 chkval: 0xba74 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
......省略......

Block header dump:  0x01c00087
 Object id on Block? Y
 seg/obj: 0x1af27  csc:  0x0000000001e11748  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x1c00080 ver: 0x01 opc: 0
     inc: 0  exflg: 0

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0008.003.00006d30  0x010001c2.2499.09  --U-    8  fsc 0x0000.01e1174a
0x02   0x0008.01b.00003a9a  0x010002b5.114f.20  C---    0  scn  0x0000000001286184
bdba: 0x01c00087

Tsiz:总的数据区域大小

SQL> select to_number('1f98','xxxxxx') from dual;

TO_NUMBER('1F98','XXXXXX')
--------------------------
                      8088

Hsiz:数据块头大小

SQL> select to_number('22','xxxxxx') from dual;

TO_NUMBER('22','XXXXXX')
------------------------
                      34

Pbl:持有数据块的缓存指针
Flag:N=pctfree hit(clusters);F=do not put on free list;K=flushable cluster keys
Ntab:表的数量(在clusters中大于1)
Nrow:行记录数
Frre:第一个可用行索引项;-1=没有添加
Fsbo:可用空间开始偏移量
Fseo:可用空间结束偏移量
Avsp:数据块中可用空间
Tosp:当所有事务提交后总的可用空间
Nrow:第一个表的行数
例如:

data_block_dump,data header at 0x7f799676f064
===============
tsiz: 0x1f98
hsiz: 0x22
pbl: 0x7f799676f064
     76543210
flag=--------
ntab=1
nrow=8
frre=-1
fsbo=0x22
fseo=0x1efa
avsp=0x1f29
tosp=0x1f2b
0xe:pti[0]      nrow=8  offs=0
0x12:pri[0]     offs=0x1f47
0x14:pri[1]     offs=0x1f3d
0x16:pri[2]     offs=0x1f33
0x18:pri[3]     offs=0x1f29
0x1a:pri[4]     offs=0x1f1f
0x1c:pri[5]     offs=0x1f15
0x1e:pri[6]     offs=0x1f08
0x20:pri[7]     offs=0x1efa

Oracle Formatted Block Dump: Data Layer
Tab: tab 0,row 0,offset
Cc:在这个行片段中的列数
Lb:锁字节:这个行被锁定的ITL条目
Fb:Flag字节
H=行片段头;K=集簇键;C=集簇表成员;D=删除的行记录;F=第一个数据片;L=最后数据片;P=紧接前一个数据片的第一个列
N=紧接下一个数据片的最后一个列
Tl:行大小(字节数加上数据)
Col:列数据
例如:

block_row_dump:
tab 0, row 0, @0x1f47
tl: 10 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 02
col  1: [ 3]  6d 5f 41
tab 0, row 1, @0x1f3d
tl: 10 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 03
col  1: [ 3]  6d 5f 42
tab 0, row 2, @0x1f33
tl: 10 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 04
col  1: [ 3]  6d 5f 43
tab 0, row 3, @0x1f29
tl: 10 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 05
col  1: [ 3]  6d 5f 44
tab 0, row 4, @0x1f1f
tl: 10 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 06
col  1: [ 3]  6d 5f 45
tab 0, row 5, @0x1f15
tl: 10 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 07
col  1: [ 3]  6d 5f 46
tab 0, row 6, @0x1f08
tl: 13 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 08
col  1: [ 6]  6d 5f 4a 59 48 59
tab 0, row 7, @0x1efa
tl: 14 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 09
col  1: [ 7]  6d 5f 4a 59 59 59 4c
end_of_block_dump
End dump data blocks tsn: 4 file#: 7 minblk 135 maxblk 135
Proudly powered by WordPress | Indrajeet by Sus Hill.