Oracle Data Operation and Control Language

Oracle Data Operation and Control Language

Oracle Data Operation and Control Language

The ORACLE tutorial is: Detailed description of Oracle Data Operations and Control Language.

There are four types of SQL languages: DQL, DML, DDL, and DCL. It is used to define the data structure, such as creating, modifying, or deleting a database; DCL is used to define the permissions of database users; in this article, I will detail how these two languages are used in Oracle.

DML Language

DML is a subset of SQL statements used to modify data. The following table lists the DML statements supported by ORACLE.


Insert data

INSERT statements are often used to INSERT rows into a table. A row may contain special data fields, or you can use a subquery to create a new row from existing data.

The column directory is optional. The default column directory is all column names, including comlumn_id, which can be found in the data dictionary view ALL_TAB_COLUMNS, USER_TAB_COLUMNS, or DBA_TAB_COLUMNS.

The number and Data Type of the inserted row must match the number of columns and data type. If the data type does not conform to the column definition, the inserted values are implicitly converted. A NULL String inserts a NULL value into an appropriate column. The keyword NULL is often used to define a column as a NULL value.

The two examples below are equivalent.




Or


Update Data

The UPDATE command is used to modify data in a table.


Delete data

The DELETE statement is used to DELETE one or more rows of data from a table. The command contains two statements:

1. The key word "delete from" is followed by the name of the table FROM which the data is to be deleted.

2. WHERE followed by deletion Conditions


Clear table

If you want to delete all the data in the table and clear the table, you can use the TRUNCATE statement in DDL language. TRUNCATE is like a DELETE command without a WHERE clause. TRUNCATE deletes all rows in the table. TRUNCATE is not a DML statement, but a DDL statement. It is different from DELETE on the right.


The STORAGE substring is optional. The default value is drop storage. When drop storage is used, the table and table index are shortened, the table is reduced to the minimum range, and the NEXT parameter is reset. Reuse storage does not shorten the table or adjust the NEXT parameter.

TRUNCATE and DELETE have the following differences:

1. TRUNCATE is very fast on various tables, whether large or small. If the ROLLBACK command DELETE is used, the TRUNCATE command is not used.

2. TRUNCATE is a DDL language. Like all other DDL languages, it will be implicitly submitted and cannot use the ROLLBACK command for TRUNCATE.

3. TRUNCATE will reset the high horizontal line and all indexes. When you completely browse the entire table and index, the table after the TRUNCATE operation is much faster than the table after the DELETE operation.

4. TRUNCATE cannot trigger any DELETE trigger.

5. You cannot grant anyone the permission to clear tables of others.

6. After the table is cleared, the index of the table and the table is reset to the initial size, while the delete statement is not.

7. The parent table cannot be cleared.

SELECT FOR UPDATE

The select for update statement is used to lock rows and prevent other users from modifying data on the row. When the row is locked, other users can use the SELECT statement to query the data of the row, but cannot modify or lock the row.

Lock table

The LOCK statement is often used to LOCK the entire table. When a table is locked, most DML languages cannot be used on the table. The LOCK syntax is as follows:


Lock_mode has two options:

Share

Exclusive unique mode

Example:


Deadlock

When both transactions are locked and each other is waiting for the other to be unlocked, this situation is called a deadlock.

When a deadlock occurs, ORACLE checks the deadlock condition and returns an exception.

[NextPage]

Transaction Control

Transaction control includes coordinating multiple synchronous accesses to the same data. When a user changes the data that another user is using, oracle uses transactions to control who can operate the data.

Transactions

A transaction represents a basic unit of work. It is a series of SQL statements that are successfully or fail to be operated as a unit. There are many statements in SQL and PL/SQL that allow programmers to control transactions. Programmers can:

1. Start a transaction explicitly and select statement-level consistency or transaction-level consistency.

2. Set the Undo rollback point and roll back to the rollback point.
　
3. Complete the transaction to Change Data forever or discard the modification.
　　
Transaction control statement


Example:


Savepoint and Partial Rollback)

In SQL and PL/SQL, Savepoint is an intermediate sign within a transaction range. It is often used to divide a long transaction into small parts. A reserved point Savepoint can mark any point in a long transaction and allow operations after the point to be rolled back. Savepoint is often used in applications. For example, a process contains several functions, and a reserved point can be created before each function. If the function fails, it is easy to return to the beginning of each function. After a Savepoint is rolled back, the data obtained after the Savepoint is blocked and released. TO implement partial ROLLBACK, you can use the ROLLBACK statement with the TO Savepoint clause TO roll back the transaction TO the specified position.

Example


The keyword SAVEPOINT is optional, so the following two statements are equivalent:


Consistency and transactions

Consistency is the key concept of thing control. Having mastered the oracle consistency model, you can use transaction control more appropriately. Oracle ensures that data can be viewed and used only after all transactions are completed through consistency. This technology has a huge effect on multi-user databases.

Oracle often uses state-level consistency to ensure that data is visible but cannot be changed between statement lifecycles. A transaction is composed of multiple statements. When a transaction is used, transaction-level consistency ensures that data is visible to all statements throughout the transaction lifecycle.

Oracle implements consistency through SCN (syatem change number. An SCN is a time-oriented internal database key. The SCN only increases and does not decrease. The SCN indicates a point in time, and each data block has an SCN. operations are performed by comparing this point.

Transaction-level consistency

One function of set transaction is to ensure that there is an implementation in TRANSACTION-level consistency or statement-level consistency. ORACLE uses these terms:

Isolation level read commit indicates statement-LEVEL consistency

Isolation level serializable indicates transaction-LEVEL consistency.

Example:


The following statement can also ensure transaction-level consistency:


Any operation that attempts to modify data in a READ-ONLY transaction throws an exception. However, READ-ONLY transactions can ONLY be used in the following statements:


Even if no data is changed, the read only transaction must still use a COMMIT or ROLLBACK to end the entire transaction.

Another application of set transction uses ROLLBACK segments directly during ROLLBACK ). A rollback segment is a special data object of ORACLE. The rollback segment header contains information about transactions using This rollback segment. When you roll back a transaction, ORACLE uses the data pre-image in the ROLLBACK segment to restore the modified data to the original value. Oracle uses round-robin to randomly allocate rollback segments to transactions. A large transaction can allocate any rollback segment, which may lead to a large size of the rollback segment. Therefore, we should avoid randomly allocating rollback segments for large transactions.

The TRANSACTION starts with set transaction, as shown below:


Rb_large is the name of a large rollback segment. Now a large rollback segment is allocated to a large transaction. Other small rollback segments cannot be managed by dynamic space, this makes it more efficient.

The following is an example. we have a rollback segment with a tablespace size of 2 GB. during peak hours, we need 10 rollback segments to meet users' needs. These peak online users only have small transactions. We run four major transactions in a week consecutively. These transactions need to delete and load data. Each revocation requires 1 GB. The rollback segment size is as follows:


All of them are properly arranged in

[1]

The ORACLE tutorial is: Detailed description of Oracle Data Operations and Control Language. In a 2G tablespace, if our default round-robin assigns a rollback segment to the transaction, four large transactions will have four independent rollback segments, the size of each rollback segment will be 1 GB. If so, our 2 GB tablespace will not be enough, and the database administrator will have to work at 2 o'clock in the evening, each transaction starts with the following statement:


Currently, four transactions reuse the same tablespace, And the tablespace with four rollback segments should be within 2 GB. The database administrator can sleep until dawn.

[NextPage]

Create and modify users

The create user statement creates a USER. When a user connects to the ORACLE database, it must be verified. There are three types of authentication in ORACLE:

Database

External

Global

The default value is database verification. When a user connects to the database, oracle checks whether the user is a legal user of the database and provides the correct password. external verification, oracle will only check whether the user is a valid user, and the password has been verified by the network or system. Global authentication only checks whether a user is valid and the password is verified by oraclesecurity server.

Database verification User Account

The database verification account is the default type and the most common type. To create an account that is piyush and whose password is welcome, run the following command:


Piyush can use the following statement to change the password to saraswatt:


External User Account Verification

The user account does not provide a password when entering the database. In this case, the client operating system is used to identify the password instead of the database. The external verification account is also called the OPS $ account. When they first introduced oracle6, oracle accounts all had the keyword prefix OPS $, Which is why init. the ora parameter OS _authent_prefix is OPS $ -- the default feature is consistent with oracle6. The string defined by OS _authent_prefix must be preprocessed to the operating system account name used for external Oracle recognition accounts. The statement for creating an operating system user appl is:


However, in general, OS _authent_prefix will be set to null, as shown below:


The results are the same. The keyword identified externally tells ORACLE that this is an external recognition account.

GLOBAL user account

The password of the GLOBAL user account database is detected by the X.509 Directory Server. To create a GLOBAL account, follow these steps:


The keyword identified globally as indicates that a GLOBAL user account is created.

Create and Change User Accounts

Create user is used to CREATE a USER account and assign values to the attributes of the USER account. Alter user is used to change USER accounts and attributes. However, the create user statement must contain the USER name and password.

Some attributes can be set using the creater user and alter user statements. The following describes the attributes:

Allocate default tablespace to users

Tablespace is used to place user objects such as tables, indexes, and clusters. If the create user statement does not contain a tablespace, the system tablespace is used by default.


Allocate temporary tablespace to users

Temporary tablespace, as its name implies, is a temporary segment for temporarily storing tables, indexes, and other user objects. Same Method


Allocate table space quota to users

Quota is used to limit the number of disks used by users in tablespaces. The quota can be set by byte, kilobytes, megabytes, or unlimited.


Assign a simple table to the user

A simple table can limit the resources consumed by users during sessions. These resources include the database connection time, idle time, and the number of logical reads of data in each session. The default table has no limit on the resources.


Specify role for user response

This attribute can only be set by the alter user statement. An exception is returned if you try to use the create user statement.


Set the expiration time for the user's password so that the user can change it upon next login

When the user's password expires, the password will be forced to be modified during the next login. oracle prompts the user to enter the old password and then enter the new password. This function is often used by new users. When new users log on with the default password, they must modify the password immediately.


Locked account, the user cannot log on


Unlock the account so that the user can log on to the database


Permissions and Roles

Permissions allow users to access objects or programs belonging to other users. The ORACLE system provides three permissions:

Object-level

System level

Role-level

These permissions can be granted to users and special users as public or roles. If you grant a permission to a special user "Public" (the user public is predefined by oracle, each user has the permissions of this user), which means that the permission is granted to all users of the database.

For management permissions, a role is a tool that can be granted to one role, and a role can also be granted to another role or user. Users can inherit permissions through roles. Besides management permissions, role services have no other purpose. Permissions can be granted or revoked in the same way.

Create and use roles

As mentioned above, a role is designed to make permission management easier. CREATE a ROLE using the create role statement. Its syntax is as follows:


By default, the created role does not have a password or other identifiers. If you use the identified by clause to create a ROLE, the ROLE does not automatically respond and must be activated with set role.


EXTERNALLY and GLOBALLY roles are verified by the operating system and ORACLE Service server. Users usually need the permission to modify the data in the form used in the application, but only when the application is running and not using the ad hoc tool, this type of context-sensitive security can be achieved through the role of PASSWORD. When you connect to the database within the application, the code runs the set role command and passes security verification. Therefore, you do not need to know the ROLE password or enter the set role command.

Object permission

Object permission is the right to execute special actions on objects such as tables, views, sequences, processes, functions, and packages. There are nine different types of permissions that can be granted to users or roles. See the following table:


An object has more than one permission. special permissions can be granted or revoked. For example, TABLE's ALL permissions include:

SELECT, INSERT, UPDATE, and DELETE, as well as INDEX, ALTER, and REFERENCE.

The ALTER permission is used as an example to describe how to view the table.

ALTER permission

The alter table and lock table operations are allowed. The alter table operation can be performed as follows:

. Change table name

. Add or delete Columns

. Change the data type or size of a column.

. Convert a table into a partitioned table

The ALTER permission on SEQUENCE allows you to execute the ALTER Sequence statement and assign the minimum value, increment, and buffer size to the sequence again.

System Permissions

System permissions must be granted to system-level activities, such as database connection, user session modification, table creation, and user creation. You can obtain complete system permissions on SYSTEM_PRIVILEGE_MAP. Both the object permission and system permission are granted to the user or role through the GRANT statement. Note that the statement should be the with grant option clause when granting object permission, but the statement is with admin option when granting system permission, so when you try to GRANT system permission, when you use the with grant option statement, the system reports an error: only admin option can be specified. Pay special attention to this syntax and error message during the exam.

Roles and permissions

Role permissions are used to grant permissions of a user to a role. Any permission can be granted to a role. The system permission must be granted to the grantee using the WITH_ADMIN_OPTION clause during the session.

[2]

The ORACLE tutorial is: Detailed description of Oracle Data Operations and Control Language. Use the set role statement to grant or revoke ROLE permissions. However, role permissions cannot rely on permissions stored in SQL. If a function, program, package, trigger, or method uses an object owned by another plan, you must directly authorize the owner of the object because the permission will not change between sessions.

Grant and revoke permissions

GRANT permissions to a user or role to use the GRANT statement. The syntax of the GRANT statement is as follows:


Object permission is granted with grant option,

Permission and Data Dictionary

A data dictionary is a place where ORACLE stores information about the database structure. data is stored elsewhere. A data dictionary consists of tables and views. The easiest way to take a data dictionary test during the test is to view the permissions that have been granted. For example, DBA_TAB_PRIV contains the object permission granted to another user by the user and whether the permission is granted WITH the with grant otpion substring. Note that DBA_TAB_PRIV not only includes the relationship between table permissions, but also permissions on functions, packages, queues, and so on. The following table lists the data dictionary views of all permissions and roles:

Table: Permission data dictionary View

Previous Page

[3]