Oracle learning: data dictionaries and database objects

ArticleDirectory

• 1. Table
• 2. View
• 3. Synonyms
• 4. Sequence

An important part of Oracle databases is data dictionary. It is the knowledge base of all object information stored in the database. The Oracle database management system uses the data dictionary to obtain object information and security information. Users and DBAs use it to view database information. The data dictionary stores the information of Data Objects and segments. It is a read-only object stored in the system tablespace and cannot be modified by anyone. Database objects stored in the data dictionary include: User, role, tablespace, table, view, cluster, type, synonym, sequence, index, database connection, stored procedure, function, package, triggers. Oracle combines these database objects into a set called schema. A schema can only be owned by one data user, and its name is the same as that of the user. This article briefly introduces common database types such as tables, sequences, and views. Others will be introduced in subsequent articles.

1. Table

In Oracle, the table concept, creation, deletion, and modification syntax are basically the same as those in other databases. However, the data types of Oracle database tables are different from those of other database management systems. Table 1.1 lists the Data Types of columns in the Oracle database table.

Table 1.1 Data Types of columns in an Oracle database

 

Data Type	Description
Char	Stores fat UNICODE character data of a fixed length, up to 2000 bytes
Varchar2	Stores variable-length character data, up to 4000 bytes
Nchar	Stores UNICODE character data of a fixed length, up to 2000 characters
Nvarchar	Stores variable-length UNICODE character data, up to 4000 characters
Byte	Store 1 byte of data
Long	Stores variable-length character data with a maximum length of 2 GB
Number Decimal Numberic	The three are equivalent. Number of the storage type, including fixed and floating point, positive and negative, and zero. All numeric types are essentially number types, but the precision is different from the number of decimal places. When used directly, the value range of number can be 10e38 ~ 10e126-1
Integer, smallint	The two are equivalent. The storage range is-10e38 ~ 10e38 integer
Float, real	The storage range is-10e38 ~ 10e38 floating point Precision Digital Data
Double Precision	Stores 126-bit binary precision floating point Precision Digital Data
Bfile	A pointer to an external binary file with a storage length greater than 4 GB
Blob	Stores large binary objects with a maximum length of 4 GB
Long raw	Stores pure binary data with a maximum length of 2 GB
Raw	Stores pure binary data, up to 2000 bytes
Clob	Storage of single-byte character data, up to 4 GB support for fixed and variable-width character sets
Nclob	Is the Unicode equivalent of COLB.
Rowid	Stores a hexadecimal string value, representing the address of a row in the table
Urowid	Stores a hexadecimal string value, representing the logical address of a row sorted by index in the table
Date	Date and Time data stored in 01/01/4712 BC to 12/31/9999 a.m.
Timestamp	Used to represent the year, month, day, hour, and second of a date.
Interval year to month	The time period between two dates in years or months
Interval day to second	Storage interval by day, time, minute, or second

2. View

A view is a virtual table generated from one or more tables using query statements. You can think that a view is the result of a query statement, but it is saved in the database in a table format. The view is different from its underlying table. It is a virtual table, that is, the data of the view is not actually stored in the database, and only the number view definition is saved in the database.

View can be used for the following purposes:

• Focus your attention on specific data to protect data security. Because the view can restrict the content retrieved from the table, rather than all the data stored in the table.
• The data query and processing operations are simplified. database developers can design the relevant content into a view so that users can query and process data as easily as they want to process tables, instead of writing complex query statements, users can view more friendly data display by using names that are easier to understand than those used in complex databases.
• It facilitates data exchange operations. We can define a view to centralize the data to be exchanged into a view to simplify the data exchange operation.
• This helps simplify the management of user permissions. The database owner can authorize other users to query data through views, rather than limiting the user's permission to query specific columns in the basic table, in this way, you can modify the basic table design without affecting user queries.

3. Synonyms

In the development of database applicationsProgramTo avoid directly referencing tables, views, or other database objects. Otherwise, after DBA modifies and changes the database object, such as changing the table name and table structure, it is necessary to update and recompile the application. Oracle uses synonym to solve this problem.

Synonyms are the aliases of database objects. You can create two synonyms in Oracle:

• Public synonyms: public synonyms are owned by a special user group. All users in the database table can use public synonyms.
• Private synonym: a user or schema that creates a private synonym. Users can control whether other Users have the right to use their own private synonyms.

4. Sequence

Sequence is a special object in Oracle. It can provide applications with continuous and unique values. sequences are used to automatically increase the primary key columns of database tables.

Syntax:

Create sequence name increment by incremental seed start with start number maxvalue maximum;

Table dual is a special table with only one row and one column. The column name is virtual and the data type is Char (1 ). This table is used when we want to quickly obtain information irrelevant to a specific table but related to external sources or functions. For example, to query the current value of a sequence, another example of using the dual table segment is select sysdate from dual to obtain the current time, or calculate a mathematical function similar to select power (2.5) from dual.