Oracle data manipulation and control la

  • 2020-05-17 06:49:48
  • OfStack

The ORACLE tutorial you are looking at is :Oracle data manipulation and control details.

The SQL language is divided into four categories: data query language DQL, data manipulation language DML, data definition language DDL, and data control language DCL. It is used to define the structure of the data, such as creating, modifying, or deleting a database. DCL is used to define permissions for database users; In this article, I will describe in detail how both languages are used in Oracle.

DML language

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

Insert data

The INSERT statement is often used to insert rows into a table, which can have special data fields, or you can use subqueries to create new rows from existing data.

The column directory is optional, and the default column directory is all column names, including comlumn_id,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 rows inserted must match the number and data type of columns. Data types that do not conform to the column definition perform implicit data conversion on the inserted values. The NULL string inserts an NULL value into the appropriate column. The keyword NULL is often used to indicate defining a column as an NULL value.

The following two examples are equivalent.


Update the data

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

Delete the data

The DELETE statement is used to delete 1 or more rows of data from a table. This command consists of two statements:

1. The keyword DELETE FROM is followed by the table name from which data is ready to be deleted.

2. WHERE followed by delete condition

Clear the table

If you want to delete all the data in the table and empty the table, consider using the TRUNCATE statement in the DDL language. TRUNCATE is like DELETE command 1 without an WHERE clause. TRUNCATE will delete all the rows in the table. TRUNCATE is not DML statement but DDL statement, which differs from DELETE on the right.

The STORAGE substring is optional and the default is DROP STORAGE. When DROP STORAGE is used, the table and table indexes are shortened, the table is shrunk to the minimum range, and the NEXT parameter is reset. REUSE STORAGE does not shorten the table or adjust the NEXT parameter.

There are several differences between TRUNCATE and DELETE

1. TRUNCATE is very fast on all kinds of watches, large and small. DELETE will be revoked if there is an ROLLBACK command, while TRUNCATE will not be revoked.

2. TRUNCATE is an DDL language. It will be submitted implicitly to all other DDL languages, and the ROLLBACK command cannot be used on TRUNCATE.

3. TRUNCATE will reset the high horizontal lines and all indexes. When the entire table and index are fully browsed, the TRUNCATE table is much faster than the DELETE table.

4. TRUNCATE cannot trigger any DELETE trigger.

5. No one can be granted permission to clear someone else's table.

6. When the table is empty, the table and its index will be reset to the original size, but delete cannot.

7. Cannot empty parent table.


The select for update statement is used to lock a row and prevent other users from modifying data on that row. When the row is locked, other users can query the row's data with the SELECT statement, but cannot modify or lock the row.

Lock the 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 syntax of LOCK is as follows:

There are two options for lock_mode:

share share mode

exclusive mode 1 only

Ex. :

A deadlock

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

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


Transaction control

Transaction control involves coordinating multiple synchronized access to the same data. When one user changes the data that another user is using, oracle USES transactions to control who can manipulate the data.

The transaction

A transaction represents a basic unit of work, an SQL statement of the 1 series that was successfully or unsuccessfully operated on as a unit. There are many statements in SQL and PL/SQL that let programmers control transactions. Programmers can:

1, explicitly start a thing, select statement level 1 specificity or transaction level 1 specificity

2. Set the undo rollback point and roll back to the rollback point

3. Complete the transaction and change the data forever or abandon the modification.

Transaction control statement

Ex. :

Savepoint and partial rollback (Partial Rollback)

In SQL and PL/SQL, Savepoint is the intermediate flag within the scope of 1 transaction. It is often used to divide a long transaction into smaller parts. The reservation point Savepoint marks any point in a long transaction, allowing operations after that point to be rolled back. Use Savepoint frequently in your applications; For example, procedure 1 contains several functions, and a reservation point can be established before each function. If the function fails, it is easy to return to the beginning of each function. After rolling back to an Savepoint, the data blocks obtained after that Savepoint are released. To achieve a partial rollback, the transaction can be rolled back to the specified location using the ROLLBACK statement with the TO Savepoint clause.


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

1 sexuality and transactions

1 tropism is the key concept of the control of things. Mastering the 1 specificity model of oracle allows you to better and more appropriate use of transaction control. oracle ensures that the data can only be seen and used by the user after the transaction has completed. This technology has huge implications for multi-user databases.

oracle often USES statement level (state-level)1 specificity to ensure that the data is visible but cannot be changed between the life of the statement. Transactions consist of multiple statements, and when transactions are used, the transaction level (transaction-level)1 uniqueness ensures that the data is visible to all statements throughout the transaction lifetime.

oracle is implemented through SCN(syatem change number). An SCN is a time-oriented database internal key. SCN will only increase, but not decrease. SCN represents a point in time, and each data block has an SCN. The operation is performed by comparing this point.

Transaction level 1 parity

One of the functions of SET TRANSACTION is to ensure that one of the transaction level 1 leads or one of the statement level 1 leads is implemented. ORACLE USES these terms:

ISOLATION LEVEL READ COMMIT means statement level 1

ISOLATION LEVEL SERIALIZABLE means transaction level 1.

Ex. :

The following statement also ensures that transaction level 1 is:

Any attempt to modify data in a read-only (READ ONLY) transaction throws an exception. However, READ ONLY transactions can only be used in the following statements:

Even if no data is changed, an READ ONLY transaction must end with either an COMMIT or an ROLLBACK transaction.

Another application of SET TRANSCTION is to use the rollback segment directly on rollback (ROLLBACK SEGMENT). The rollback segment is a special data object of ORACLE, and the header of the rollback segment contains information about transactions that are using the rollback segment. When the user rolls back the transaction (ROLLBACK), ORACLE will use the data front image in the rollback segment to restore the modified data to its original value. oracle randomly assigns a rollback segment to the transaction using round-robin. A large transaction can allocate any rollback segment, which may cause the size of the rollback segment to become very large. So avoid having large transactions randomly assign rollback segments.

The transaction begins with SET TRANSACTION, as follows:

rb_large is the name of a large rollback segment. A large rollback segment is now assigned to a large transaction. Other small rollback segments will not be managed in dynamic space, which is more efficient.

Let's look at an example. We have a rollback segment with a tablespace size of 2G, and we need 10 rollback segments to meet the needs of users at peak times. These peak online users only have small transactions. In one week, we ran four large transactions in a row, which required data deletion and loading. Each cancellation required 1G. The size of the rollback segment is as follows:

All are very well arranged in

[1] [2] [3] next page

The ORACLE tutorial you are looking at is :Oracle data manipulation and control language. 2 G table space, if we default round - robin assign a transaction rollback segments, four large transactions will have four separate rollback segments, each the size of the rollback segment will be 1 G, if so, we 2 G table space is not enough, and the database administrator will have to work at 2 o 'clock at night, every transaction begins with the following statement:

Four transactions now reuse the same table space, with four rollback segments within 2G. Database administrators can sleep through the night.


Create and modify users

The CREATE USER statement will create one user. When a user connects to the ORACLE database, it must be validated. There are three types of validation in ORACLE:




The default is database validation. When the user connects to the database, oracle will check whether the user is a legitimate user of the database and provide the correct password.external authentication. oracle will only check whether the user is a legitimate user. The global validation also detects only legitimate users, while password is verified by oraclesecurity server.

Database authenticates user accounts

Database validation accounts are the default and most common types. To create an account piyush and password welcome, simply execute the following command:

piyush can change the password to saraswatt with the following statement:

Externally verify user accounts

User account access to the database can not provide a password, in this case, the replacement of the database password is the client operating system. External validation accounts are sometimes also called OPS$accounts. When they were first introduced in oracle6, oracle accounts had the keyword prefix OPS$, which is why the os_authent_prefix parameter os_authent_prefix is OPS$-- the default feature is oracle6 keeping 1. The string defined by os_authent_prefix must be preprocessed to the operating system account name for the Oracle external identification account. The statement to create operating system user appl is:

But normally, os_authent_prefix will be set to empty, as follows:

The keyword IDENTIFIED EXTERNALLY tells ORACLE that this is an external identification account.

GLOBAL user account

The GLOBAL type user account database does not detect passwords, but the X.509 directory server does. To create a user account of type GLOBAL:

The keyword IDENTIFIED GLOBALLY AS indicates that a user account of type GLOBAL has been established.

Create and change user accounts

CREATE USER is used to create user accounts and assign values to user account properties. ALTER USER is used to change user accounts and properties. However, the CREATE USER statement must include the username and password.

Some properties can be set by CREATER USER and ALTER USER statements. The following is the detailed description of these properties:

Assign default table Spaces to users

Table Spaces (tablespace) are where user objects such as tables, indexes, and clusters are placed. If no table space is included in the create user statement, the default is the system table space.

Assign temporary table Spaces to users

A temporary table space, as the name implies, is a temporary segment that temporarily stores user objects such as tables, indexes, etc. Create method 1 sample

Assign users quotas for table space usage

Use quotas to limit the number of disks a user can use in a table space. Quotas can be fixed in bytes, kilobytes, megabytes, or unlimited.

Assign a summary table to the user

The summary table can limit the resources consumed by the user during the session. These resources include: time to connect to the database, free time, number of logical reads per session, and so on. The default summary table has no limits on the resources.

Specify roles for user responses

This property can only be set by the ALTER USER statement, and an attempt to use the CREATE USER statement setting will return 1 exception.

Set an expiration date for the user's password to be changed the next time the user logs in

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

To lock the account, the user cannot log in

Unlock the account so that the user can log into the database

Permissions and roles

Permissions allow users to access objects or programs that belong to other users. ORACLE system provides three types of permissions:

Object object level

System system level

Role level role

These permissions can be granted to a user, a special user public, or a role. If you grant a special user "Public" (user public is predefined by oracle, and each user has the same privileges as that user), then you are granting that privilege to all users of the database.

For administrative permissions, a role is a tool, and permissions can be granted to one role, and roles can be granted to another role or user. Users can inherit permissions through roles, which serve no other purpose than managing permissions. Permissions can be granted or revoked in the same way.

Create and use roles

As previously stated, roles exist to make it easy to manage permissions. Create a role using the CREATE ROLE statement, which has the following syntax:

The roles created by default have no password or other recognition. If created using the IDENTIFIED BY clause, the role will not automatically respond and must be activated using SET ROLE.

Roles of type EXTERNALLY and GLOBALLY are validated by the operating system and ORACLE Service server. Usually users need permission to modify the data in the forms used in the application, but only when the application is running and not using the ad hoc tool, this context-sensitive security can be achieved by having the role of PASSWORD. When a user connects to a database within the application, the code executes the SET ROLE command and passes security verification. So the user does not need to know the password of the role, nor does he need to enter the SET ROLE command himself.

Object permissions

Object permissions are the rights to perform special actions on objects such as tables, views, sequences, procedures, functions, or packages. There are nine different types of permissions that can be granted to users or roles. The following table:

Object has more than one permission, special permission ALL can be granted or revoked. For example, the ALL permissions of TABLE include:


How do we look at this table using the ALTER permissions as an example

ALTER permissions

ALTER TABLE and LOCK TABLE operations are allowed, and ALTER TABLE can do the following:

.change the table name

Add or delete columns

. Changing the data type or size of a column

. Convert the table to a partitioned table

The ALTER permission on SEQUENCE allows the ALTER Sequence statement to be executed to reassign the minimum, increment, and buffer size to sequence.

System permissions

System permissions require the ability of the grantor to perform system-level activities, such as connecting to a database, changing user sessions, creating tables, or creating users, and so on. You can get full system permissions on the data dictionary view SYSTEM_PRIVILEGE_MAP. Both object and system permissions are granted to users or roles through GRANT statements. Note that the statement should be WITH GRANT OPTION clause when granting object permissions, but WITH ADMIN OPTION when granting system permissions, so when you try to grant system permissions, the statement WITH GRANT OPTION system will report an error: ONLY ADMIN can be specified. Pay special attention to this grammar and error message during the exam.

Roles and role permissions

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

Previous page [1] [2] [3] next page

The ORACLE tutorial you are looking at is :Oracle data manipulation and control language. Grant or revoke role permissions through the SET ROLE statement. 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, the owner of the object must be authorized directly, because permissions do not change from session to session.

Grant and revoke permissions

Grant permission to users or roles using GRANT statement. The syntax of GRANT statement is as follows:

Object permissions are granted to WITH GRANT OPTION,

Permission and data dictionaries

The data dictionary is where ORACLE stores information about the database structure, the data itself is stored elsewhere, and the data dictionary consists of tables and views. The easiest thing to test on a data dictionary is to see which class of permissions have been granted. For example, DBA_TAB_PRIV contains the object permissions granted by the user to another user and the information about whether or not the WITH GRANT OTPION substring is present at the time of grant. Note that DBA_TAB_PRIV does not just include permissions on tables, it also includes permissions on functions, packages, queues, and so on. The following table lists the data dictionary views of all permissions and roles:

Table: data dictionary view of permissions

On 1 page

Previous page [1] [2] [3]

Related articles: