Oracle Foundation-Data Type Summary

Oracle provides 22 different types of SQL data for our use:

1, Char:

This is a fixed-length string that is padded 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 Unicode format data. Unlike the char type, the char type defines a length that can be either a byte or a character. The nchar field can store up to 2000 bytes of information;

3, VARCHAR2:

This is now a synonym for varchar. It is a variable-length string that, unlike the char type, does not fill a field or variable with a space to the maximum length. 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 Unicode format data. 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, meaning that no character set conversion occurs with data stored in this data type. 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 up to 38 bits in precision, and this type of data is stored in a variable-length manner, in the 0~22 bytes (null value length is 0). Its precision is very high;

7, Binary_float:

This is a new type that Oracle10 only starts with. It is a 32-bit single-precision floating-point number that can support at least 6 bits of precision, occupying 5 bytes of storage space on disk;

8, Binary_double:

This is a new type of Oracle10 only started, it is a 64-bit double precision floating point number, can support at least 15 bits of precision, Occupy disk 9 bytes of storage space;

9, Long:

This type can store up to 2GB of character data (2GB refers to 2000 megabytes instead of 2000 trillion characters, because some characters may occupy multiple bytes in a multibyte character set), this type is not recommended, and a long type should be converted to the CLOB type in an existing application;

10, Long Raw:

This type can store up to 2GB of binary information, and the same reason as the long type suggests using BLOB types in your application;

11. Date:

This is a 7-byte fixed-width date/Time data type with a total of 7 properties: Century, Year of the century, month, day, hour, minute and second;

12, Timestamp:

This is a 7-byte or 11-byte fixed-width date-time data type (high precision uses 11 bytes). It differs from the date data type because timestamp can contain fractional seconds, and timestamp with fractional 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 for the data when it is inserted is preserved together with the Times;

14. Timestamp with local time zone:

With the timestamp type, which is a fixed-width date-time data type with 7 bytes or 11 bytes (high precision uses 11 bytes); However, this type is sensitive to time zones;

15. Interval Year Month:

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

16, Interval Day second:

This is a 11-byte fixed-width data type that is used to store a time period. This type stores the period as days, hours, minutes, seconds, and a fractional second of up to 9 bits;

17. BLOB:

You can store up to 4GB of data before oracle9i, and you can store up to (4gb-1) X (database block size) bytes of data from Oracle10; The blob contains binary data that does not require character set conversion.

18, CLOB:

You can store up to 4GB of data before oracle9i, and you can store up to (4gb-1) X (database block size) bytes of data from Oracle10, and Clob are affected when character set conversions occur. This data type is ideal for storing large chunks of plain text information.

19, NCLOB:

You can store up to 4GB of data before oracle9i, and you can store up to (4gb-1) X (database block size) bytes of data from Oracle10, Nvlob stores information encoded in the database national character set, and, like Clob, when character set conversion occurs, This type can also be affected;

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, which is 10 bytes long. The information encoded in the ROWID is not only sufficient to locate each row on the disk, but also to identify the object (table, etc.) to which the row of data that the ROWID points to belongs.

22, Urowid :

The Urowid is a generic rowid for tables that do not have a fixed rowid. The urowid usually behaves as the value of the primary key. Therefore, the size of the urowid varies depending on the object to which it is directed;

Note: From oracle12c onwards, VARCHAR2, NVARCHAR2, and raw types can store up to 32767 bytes of information, but you need to enable the extended data type. This feature is not enabled by default.

From oracle12c onwards, the maximum length of the VARCHAR2 and NVARCHAR2 types is changed from the original 4000 bytes to now up to 32767 bytes, 12C by default, the extended data type is not enabled 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

---The extended data type can be activated according to the above command;

Note: Once the extended data type is activated, it cannot be rolled back unless a non-full recovery is made to restore the data to the state before the change point in time;

This article is from the "stupid Child's DBA path" blog, please be sure to keep this source http://fengfeng688.blog.51cto.com/4896812/1934806

Oracle Foundation-Data Type Summary