Shulou(Shulou.com)06/01 Report--

Oracle provides 22 different sql data types for our use:

1 、 char:

This is a fixed-length string that is filled with spaces to reach its maximum length. The char field can store up to 2000 bytes of information

2 、 nchar:

This is a fixed-length string that contains data in Unicode format. Unlike the char type, the char type can define a length of bytes or characters. The nchar field can store up to 2000 bytes of information

3 、 varchar2:

At present, this is also synonymous with varchar. It is a variable-length string that, unlike the char type, does not fill fields or variables to the maximum length with spaces. It can store up to 4000 bytes of information. Since Oracle12C, it can store up to 32767 bytes of information

4 、 nvarchar2:

This is a variable length string that contains data in Unicode format. It can store up to 4000 bytes of information. Since Oracle12C, it can store up to 32767 bytes of information

5 、 raw:

This is a variable-length binary data type, that is, data stored in this data type will not have character set conversion. It stores up to 2000 bytes of information. Since Oracle12C, it can store up to 32767 bytes of information

6 、 number:

This data type can store numbers with a precision of up to 38 digits, and this type of data is stored in a variable length with a length of 0,022 bytes (the length of the null value is 0). Its precision is very high.

7 、 binary_float:

This is a new type that Oracle10 has just begun. It is a 32-bit single-precision floating-point number that supports at least 6 bits of precision and takes up 5 bytes of storage space on disk.

8 、 binary_double:

This is a new type that has just begun with Oracle10. It is a 64-bit double-precision floating-point number that supports at least 15 bits of precision and takes up 9 bytes of disk storage.

9 、 long:

This type can store up to 2 gigabytes of 2GB character data (2GB refers to 2 gigabytes instead of 2 gigabytes, because some characters may occupy multiple bytes in a multi-byte character set). This type is not recommended and should be converted from long type to CLOB type in existing applications.

10 、 long raw:

How much binary information of 2GB can be stored by this type? for the same reason as long type, it is recommended to use BLOB type in application.

11 、 date:

This is a 7-byte date / time data type with a fixed width, which contains seven attributes: century, year in the century, month, day, hour, minute and second

12 、 timestamp:

This is a 7-byte or 11-byte fixed-width date-time data type (11 bytes are used for high precision). It is different from the date data type because timestamp can contain decimal seconds, and timestamp with decimal seconds can retain up to 9 decimal places

13 、 timestamp with time zone:

This is a 13-byte fixed-width timestamp, but it also provides time zone support. Because the time zone information is stored with the timestamp, the time zone information of the data at the time of insertion is retained with the time

14 、 timestamp with local time zone:

Compared to the timestamp type, this is a fixed width date-time data type of 7 bytes or 11 bytes (11 bytes are used for high precision); however, this type is sensitive to time zones

15 、 interval year 10 month:

This is a 5-byte fixed-width data type used to store a period of time. This type stores the period as years and months; we can use this period in the date operation to increase or decrease a date or timestamp type of data for a period of time.

16 、 interval day 10 second:

This is an 11-byte fixed-width data type used to store a period of time. This type stores time periods as days, hours, minutes, seconds, and decimal seconds up to 9 digits.

17 、 BLOB:

Before Oracle9i, you can store up to 4GB data, while since Oracle10, you can store up to (4GB-1) X (database block size) bytes of data; blob contains binary data that does not require character set conversion.

18 、 CLOB:

Before Oracle9i, you can store up to 4GB data, while since Oracle10, you can store up to (4GB-1) X (database block size) bytes of data; when character set conversion occurs, CLOB will be affected. This data type is suitable for storing large chunks of plain text information.

19 、 NCLOB:

Oracle9i can store up to 4GB data before, and up to (4GB-1) X (database block size) bytes since Oracle10; NVLOB stores information encoded in the database national character set, and, like CLOB, this type is affected when character set conversion occurs

20 、 BFILE:

This data type can store an Oracle directory object and a file name in the database column, which we can use to read the file.

21 、 rowid:

Rowid is actually the address of a row in a database table, and it is 10 bytes long. The information encoded in rowid is not only sufficient to locate each row on disk, but also to identify the object (table, etc.) to which the row of data pointed by rowid belongs.

22 、 urowid:

Urowid is a generic rowid for tables that do not have a fixed rowid. Urowid is usually represented as the value of the primary key. So the size of the urowid varies depending on the object you point to.

Note: starting with Oracle12C, the varchar2, nvarchar2, and raw types can store up to 32767 bytes of information, but the extended data type needs to be enabled. This feature is not enabled by default.

Starting from Oracle12C, the maximum length of varchar2 and nvarchar2 types has changed from 4000 bytes to a maximum of 32767 bytes. 12C does not enable extended data types by default and needs to be started manually, as follows:

Shutdown immediate

Startup upgrade

Alter system set max_string_size=extended

@? / rdbms/admin/utl32k.sql

Shutdown immediate

Startup

-follow the above command to activate the extended data type

Note: once the extended data type is activated, it cannot be rolled back unless an incomplete recovery is done to restore the data to the state before the changed point in time.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.