In Oracle databases, Oracle data types define the kind of data that can be stored in a column, variable, or parameter. Choosing the appropriate data type ensures data integrity, optimizes storage, improves query performance, and enhances overall database efficiency.
Oracle provides a variety of Oracle database types to efficiently store different types of data. These Oracle database data types can be broadly categorized as follows:
Character Data Types (e.g., CHAR, VARCHAR2) – Used to store text strings.
Numeric Data Types (e.g., NUMBER, BINARY_FLOAT) – Used to store integers and floating-point numbers.
Date and Time Data Types (e.g., DATE, TIMESTAMP) – Used to store date and time information.
Large Object (LOB) Data Types (e.g., BLOB, CLOB) – Used to store large text or binary data.
Composite Data Types (e.g., VARRAY, NESTED TABLE) – Used to store complex data structures.
Special Data Types (e.g., JSON, ROWID, BOOLEAN) – Designed for specific use cases, such as storing JSON data, row identifiers, or logical values.
Selecting the right Oracle data type not only optimizes storage but also improves query performance, reduces storage costs, and ensures data consistency. The following sections will provide a detailed explanation of each data type, including its characteristics and usage.
1.Character Data Types
Character data types are used to store text strings.
Data Type | Description | Maximum Length |
CHAR(n) | Fixed-length string | 2000 bytes |
VARCHAR2(n) | Variable-length string | 4000 bytes |
NCHAR(n) | Fixed-length Unicode string | 2000 bytes |
NVARCHAR2(n) | Variable-length Unicode string | 4000 bytes |
Example
The following example creates an employees table where emp_name is stored as a variable-length string using VARCHAR2(50) and emp_code is stored as a fixed-length string using CHAR(10):
CREATE TABLE employees (
emp_name VARCHAR2(50),
emp_code CHAR(10)
);
2.Numeric Data Types
Numeric data types are used to store integers and floating-point numbers.
Data Type | Description |
NUMBER(p,s) | Stores fixed-point or floating-point numbers. p is the precision (total number of digits), and s is the scale (number of digits to the right of the decimal point). |
BINARY_FLOAT | Stores single-precision floating-point numbers. |
BINARY_DOUBLE | Stores double-precision floating-point numbers. |
Example
The following example creates a products table where price is stored as a number with 8 total digits and 2 decimal places using NUMBER(8,2), and weight is stored as a single-precision floating-point number using BINARY_FLOAT:
CREATE TABLE products (
price NUMBER(8,2),
weight BINARY_FLOAT
);
3.Date and Time Data Types
Oracle provides various data types to store date and time values, allowing users to manage and manipulate time-related data efficiently. These data types offer a range of precision, from seconds to fractional seconds, and support different formats, including time zones and intervals.
Data Type | Description |
TIMESTAMP(n) | Stores date and time with fractional seconds. n specifies the number of digits for fractional seconds. |
TIMESTAMP WITH TIME ZONE | Stores date and time with time zone information. |
TIMESTAMP WITH LOCAL TIME ZONE | Stores date and time related to the session's time zone. |
INTERVAL YEAR TO MONTH | Represents an interval of time in years and months. |
INTERVAL DAY TO SECOND | Represents an interval of time in days, hours, minutes, and seconds. |
Example
The following example demonstrates how to use DATE and TIMESTAMP data types in an orders table. The order_date is stored as a DATE with a default value of the current system date, while the delivery_time stores the timestamp with fractional seconds.
CREATE TABLE orders (
order_date DATE DEFAULT SYSDATE,
delivery_time TIMESTAMP(3)
);
4.Large Object (LOB) Data Types
Oracle provides Large Object (LOB) data types to store large amounts of binary data or text efficiently. These data types are commonly used for handling multimedia files, documents, and other unstructured data.
Data Type | Description |
BLOB | Stores binary data such as images and videos. |
CLOB | Stores large text data. |
NCLOB | Stores large Unicode text data. |
BFILE | Stores the file path of an external binary file. |
Example
The following example creates a documents table where doc_id is a primary key and content is stored as a CLOB to accommodate large text data.
CREATE TABLE documents (
doc_id NUMBER PRIMARY KEY,
content CLOB
);
5.Composite Data Types
Oracle provides composite data types to store more complex data structures. These types allow for the definition of collections and user-defined object types, making it easier to model real-world entities.
Data Type | Description |
VARRAY | Stores a variable-length array. |
NESTED TABLE | Stores a table within another table. |
OBJECT | Defines a user-defined data type with multiple attributes. |
Example
The following example creates a VARRAY type named phone_list, which can store up to five phone numbers as VARCHAR2(20).
CREATE TYPE phone_list AS VARRAY(5) OF VARCHAR2(20);
6.Special Data Types
Oracle provides special data types designed for unique use cases, such as storing row identifiers, structured data, and object references.
Data Type | Description |
ROWID | Stores the physical address of a row. |
UROWID | Stores a logical row identifier. |
JSON | Stores JSON-formatted data (introduced in Oracle 21c). |
XMLTYPE | Stores and processes XML documents. |
REF | Stores a reference to an object type. |
BOOLEAN | Stores TRUE or FALSE (only available in PL/SQL). |
NULL | Represents an unknown or missing value. |
Example
The following example creates a customers table with a JSON column to store structured customer information.
CREATE TABLE customers (
customer_id NUMBER PRIMARY KEY,
customer_info JSON
);
Data Integrity and Efficient Management-Vinchin Backup & Recovery
In Oracle databases, choosing the appropriate Oracle databases data types optimizes storage, enhances query performance, and ensures data consistency. However, to further safeguard data integrity and business continuity, relying solely on the database's design is not enough. Businesses need robust backup and recovery solutions to mitigate the impact of data loss, hardware failure, or unforeseen disasters.
Vinchin Backup & Recovery offers enterprise-level protection for Oracle databases, ensuring the safety of mission-critical business data. Its key advantages include:
✅ Agentless Backup: Supports agentless hot backups, ensuring no disruption to database operations and maintaining business continuity.
✅ Multiple Recovery Methods: Offers full database recovery, table-level recovery, and even point-in-time recovery (PITR) for precise data restoration.
✅ Storage Efficiency: Utilizes deduplication and compression technologies to reduce storage consumption and lower costs.
✅ Automated Backup Strategies: Enables scheduled backup tasks with full, incremental, and differential backup options, improving backup efficiency.
✅ Security Encryption and Access Control: Built-in AES encryption and fine-grained access management to ensure data security.
✅ Disaster Recovery Support: Vinchin's cross-platform recovery capabilities allow for quick Oracle database recovery to different environments, enhancing disaster recovery readiness.
Vinchin Backup & Recovery's operation is very simple, just a few simple steps.
1️⃣ Just select the database on the host
2️⃣ Then select backup destination
3️⃣ Select strategies
4️⃣ Finally submit the job
Vinchin offers a free 60-day trial❗❗❗ for users to experience the functionality in a real-world environment. For more information, please contact Vinchin.
Oracle database types FAQs
Q1: How to choose the right data type?
A1: The nature of the data, the expected size, and the performance impact should be considered when choosing a data type. For example, for text data, if the length varies greatly, it is more efficient to use VARCHAR2 rather than CHAR; for time records that require high precision, it may be necessary to use TIMESTAMP rather than DATE.
Q2: What is VARCHAR2?
A2: VARCHAR2 is a variable-length character data type suitable for storing non-fixed-length strings. Its maximum length can be up to 32767 bytes, but the actual space used depends on the number of characters stored.
Conclusion
Oracle provides a wide range of data types to help businesses efficiently manage their data. By integrating Vinchin Backup & Recovery, businesses can further enhance their data security and ensure rapid recovery in case of any unforeseen events. For organizations looking to comprehensively protect their Oracle databases, Vinchin is a trusted choice.
