1Z0-51 QUESTION 20 表的数据类型应该注意的地方

QUESTION 20
Which three statements are true regarding the data types in Oracle Database 10g/11g? (Choose three.)
A. Only one LONG column can be used per table.
B. A TIMESTAMP data type column stores only time values with fractional seconds.
C. The BLOB data type column is used to store binary data in an operating system file.
D. The minimum column width that can be specified for a VARCHAR2 data type column is one.

E. The value for a CHAR data type column is blank-padded to the maximum defined column width.

答案：ADE

解析：

A选项，一个表中不能创建两个long：

SQL> create table t1(
  2  id long
  3  ,
  4  d1 long);
d1 long)
*
ERROR at line 4:
ORA-01754: a table may contain only one column of type LONG

B选项，TIMESTAMP类型不但能存储带小数秒的数值，还能存储其他数值官方文档：

http://docs.oracle.com/cd/E11882_01/server.112/e40540/tablecls.htm#CNCPT1841

The TIMESTAMP data type is an extension of the DATE data type. It stores fractional seconds in addition to the information stored in the DATE data type. TheTIMESTAMP data type is useful for storing precise time values, such as in applications that must track event order.

The DATETIME data types TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE are time-zone aware. When a user selects the data, the value is adjusted to the time zone of the user session. This data type is useful for collecting and evaluating date information across geographic regions.

C选项，BLOB数据类型存储非结构化的二进制大对象，不是系统文件，存储系统文件的可以用BFILE类型：

The BLOB data type stores unstructured binary large objects. BLOB objects can be thought of as bitstreams with no character set semantics. BLOB objects can store binary data up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage). If the tablespaces in your database are of standard block size, and if you have used the default value of the CHUNK parameter of LOB storage when creating a LOB column, then this is equivalent to (4 gigabytes - 1) * (database block size).

BLOB objects have full transactional support. Changes made through SQL, the DBMS_LOB package, or Oracle Call Interface (OCI) participate fully in the transaction. BLOB value manipulations can be committed and rolled back. However, you cannot save a BLOB locator in a PL/SQL or OCI variable in one transaction and then use it in another transaction or session.

The BFILE data type enables access to binary file LOBs that are stored in file systems outside Oracle Database.

D选项，解析如下：

SQL> create table t3 (t1 varchar2(1));

Table created.

E选项，虽然char(10)可能只存了一个A，但长度却是10：

SQL> create table t4 (t1 char(10));

Table created.

SQL> insert into t4 values('A');

1 row created.

SQL> select t1,length(t1) from t4;

T1                   LENGTH(T1)
-------------------- ----------
A                            10