The Oracle tutorial you are looking at is: Oracle data manipulation and control language.
SQL language is divided into four categories: Data Query Language DQL, Data manipulation language DML, data definition language DDL, Data Control Language DCL. It is used to define the structure of the data, such as creating, modifying, or deleting the database; DCL is used to define the permissions of the database user; In this article I will describe in detail how these two languages are used in Oracle.
DML language
DML is a subset of SQL that is used primarily to modify data, and the following table lists the DML statements supported by Oracle.
Inserting data
Insert statements are often used to insert rows into a table, there can be special data fields in rows, or you can use subqueries to create new rows from existing data.
The column directory is optional, and the default column's directory is all column names, including comlumn_id,comlumn_id can be found in the data dictionary view all_tab_columns,user_tab_columns, or Dba_tab_columns.
The number of data inserted into the row and the data type must match the number of columns and the data type. Data types that do not conform to the column definition implement implicit data conversion for the inserted value. The null string inserts a null value into the appropriate column. Keyword NULL is often used to indicate that a column is defined as a null value.
The following two examples are equivalent.
Or
Update data
The update command is used to modify the data in the table.
Delete data
The DELETE statement is used to delete one or more rows of data from a table, which contains two statements:
1, keyword Delete from followed by the name of the table from which you want to delete data.
2, where followed by the deletion condition
Empty table
If you want to delete all the data in the table and clear the table, consider using the TRUNCATE statement in the 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 that is a DDL statement, and he differs from the delete right.
The STORAGE substring is optional and the default is drop STORAGE. When you use drop storage, the table and table indexes are shortened, the table is shrunk to a minimum, 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 on all kinds of tables, whether large or small are very fast. If a rollback command delete is revoked, the truncate is not revoked.
2, truncate is a DDL language, like all other DDL languages, he will be implicitly submitted, not to truncate use rollback command.
3, TRUNCATE will reset the high-level line and all indexes. When you fully 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, can not give anyone the right to empty other people's table.
6, when the table is emptied after the table and table index is reset to the initial size, and delete is not.
7, can not empty the parent table.
SELECT for UPDATE
The Select FOR UPDATE statement is used to lock the row, preventing other users from modifying the data on that row. When the row is locked, other users can query the row's data with a SELECT statement, but cannot modify or lock the row.
Lock table
Lock statements are often used to lock an entire table. When a table is locked, most DML languages cannot be used on that table. The lock syntax is as follows:
Where Lock_mode has two options:
Share sharing mode
Exclusive Only Way
Cases:
Dead lock
This is known as a deadlock when two transactions are locked and each other is waiting for another to be unlocked.
When a deadlock occurs, Oracle detects the deadlock condition and returns an exception.
[NextPage]
Transaction control
Transaction control includes coordinating access to multiple synchronizations of the same data. When a user changes the data that another user is using, Oracle uses transaction control who can manipulate the data.
Transaction
A transaction represents a basic unit of work that is a series of SQL statements that are successfully or unsuccessfully manipulated as a unit. There are many statements in SQL and pl/sql that allow programmers to control transactions. Programmers can:
1, explicitly start a thing, 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 change data forever or discard changes.
Transaction CONTROL Statement
Cases:
SavePoint and partial rollback (Partial Rollback)
In SQL and Pl/sql, SavePoint is the intermediate flag within the scope of a transaction. Often used to divide a long transaction into small parts. The reservation point savepoint any point in a long transaction, allowing the action to be rolled back after that point. SavePoint is often used in applications, for example, a process contains several functions, a reservation can be established before each function, and if the function fails, it is easy to return to the beginning of each function. After the rollback to a savepoint, the data blockade obtained after the SavePoint is released. In order to implement partial rollback, the transaction can be rolled back to the specified location with the rollback statement with the to savepoint clause.
Cases
The keyword savepoint is optional, so the following two statements are equivalent:
Consistency and transactions
Consistency is the key concept of control of things. Mastering Oracle's consistency model enables you to better and more appropriately use transaction control. Oracle guarantees that data can be seen and used by users only when the transaction is complete. This technology has a huge effect on multi-user databases.
Oracle often uses statement-level (State-level) consistency to ensure that data is visible between statements ' lifetimes, but cannot be changed. A transaction consists of multiple statements, and when a transaction is used, the object-level (Transaction-level) consistency guarantees that the data is visible to all statements throughout the transaction lifecycle.
Oracle enforces consistency through the SCN (syatem change number). A SCN is a time-oriented database internal key. The SCN only increases and does not decrease, the SCN represents a point in time, each data block has a SCN, which is implemented by comparing this point.
Transaction-level consistency
One of the functions 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 represents statement-consistent
The isolation level SERIALIZABLE represents a transactional consistency.
Cases:
The following statement also ensures transactional-level consistency:
Any attempt to modify data in a read only transaction throws an exception. However, the READ only transaction can only be used in the following statements:
Even without changing any data, the READ only transaction must still use a commit or rollback to end the entire transaction.
Another application of SET transction is to use the rollback segment (ROLLBACK SEGMENT) directly on rollback. The rollback segment is a special data object for Oracle, and the header of the rollback segment contains information that is in use for the rollback segment transaction. When the user rolls back the transaction (ROLLBACK), Oracle will use the data before the rollback segment to restore the modified data to its original value. Oracle uses Round-robin to randomly allocate rollback segments to transactions. A large transaction can be assigned any rollback segment, which may cause the rollback segment to become larger in size. Therefore, avoid having large transactions randomly allocate rollback segments.
The transaction starts with set transaction, as follows:
Rb_large is the name of a large rollback segment that now assigns a large rollback segment to a large transaction, and other small rollback segments are not managed by dynamic space, which is more efficient.
Let's take a look at an example. We have a rollback segment with a table space size of 2G, which requires 10 rollback segments at peak times to meet the needs of the user, these peak online users have only small transactions. In a week we ran 4 large transactions that required deletion and loading of data, 1G for each undo, and the size of the rollback segment as follows:
All of them are properly arranged in the
[1] [2] [3] Next page
The Oracle tutorial you are looking at is: Oracle data manipulation and control language. 2G of the table space, if our default round-robin is to assign a rollback segment to a transaction, 4 large transactions will have 4 separate rollback segments and each rollback segment will be 1G in size, so our 2G table space is not enough and the database administrator will have to work at 2 O ' night. Each transaction starts with the following statement:
Now 4 transactions reuse the same table space, and the table space for 4 rollback segments is within 2G. The database administrator can sleep until dawn.
[NextPage]
Create and modify Users
The CREATE USER statement will establish a users. When a user connects to an Oracle database, it must be validated. There are three types of authentication in Oracle:
Database
External
Global
The default is database validation, when the user connects to the database, Oracle detects whether the user is a legitimate user of the database, and to provide the correct password.external authentication, Oracle will only detect whether the user is a legitimate user, password has been authenticated by the network or system. Global authentication is also the only test for legitimate users, and password is validated by Oraclesecurity server.
Database Authentication user Account
The database verification account is a good default type and is the most common type. Create an account is Piyush, password is welcome account, just execute the following command:
Piyush can change the password to Saraswatt by using the following statement:
External Authentication User account
The user account can enter the database without providing a password, in which case the client operating system is used instead of the database to identify the password. External authentication account is sometimes called ops$ account, when they first introduced in Oracle6, Oracle account has a keyword prefix ops$, which is why Init.ora parameter os_authent_prefix is ops$-- Default features are consistent with Oracle6. The string defined by Os_authent_prefix must be preprocessed to the name of the operating system account used for the Oracle external identification account. The statement that creates the operating system user APPL is:
In general, however, Os_authent_prefix will be set to NULL, as follows:
The effect is the same, the keyword identified externally tells Oracle this is an external identification account.
Global user Account
The global type's user account database does not detect the password, but rather the password is detected by the X.509 directory server. The method for creating a global type of user account is:
The keyword identified globally as indicates that a global type user account is established.
Create and change user accounts
Create user is used to create a username and assign a property to a user account. The 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 with the creater user and ALTER user statements, with the following attributes specifically described:
Assigning default table space to users
A table space (tablespace) is a user object that places tables, indexes, bundles, and so on. If the table space is not included in the CREATE USER statement, the default is the system tablespace.
Assigning temporary table space to users
A temporary table space, as the name suggests, is a temporary segment of a user object that temporarily holds tables, indexes, and so on. Create a method like
Assigning a user quota to a table space
Use quotas to limit the number of disks that users use in a table space. Quotas can be made in bytes, kilobytes, megabytes, or unrestricted.
Assign a summary to a user
A summary table can limit the resources that users consume during a session. These resources include: time to connect to the database, idle time, number of logical read data per session, and so on, and the default summary table is unrestricted for resources.
Assigning roles to user responses
This property can only be set by the ALTER USER statement, attempting to use the Create USER statement setting to return an exception.
Set expiration time for user's password to change at next logon
When the user's password expires, the next logon will force the modification Password,oracle prompt the user to enter the old password, and then enter the new password. This feature is commonly used for new users and must modify password immediately when a new user logs on with the default password.
Locked account, is the user can not log in
Unlock the account so that users can log on to the database
Permissions and Roles
Permissions allow users to access objects or execute programs that belong to other users, and the Oracle system provides three types of permissions:
Object-Level
System-Level
Role level
These permissions can be granted to a user, to a special user, to public or to a role, and if a permission is granted to "public" for a particular user (user public is Oracle predefined, and each user has the rights that the user has), it means that the permission is granted to all users of the database.
For administrative permissions, a role is a tool that can be granted to a role, and the role can be granted to another role or user. Users can inherit permissions through roles, and there is no other purpose than to administer the role service outside of a permission. Permissions can be granted, or they can be revoked in the same way.
Creating and using Roles
As previously mentioned, the purpose of the role exists is to make the administration of permissions easier. To create a role using the Create roles statement, his syntax is as follows:
The roles established by default do not have password or other recognition. If established by using the identified by clause, the role does not respond automatically and must be activated with set roles.
Roles for externally and globally types are validated by the operating system and Oracle Service server. Typically, a user needs permissions to modify data in a form used in an application, but this context-sensitive security can be achieved through a password role only if the application is running and not using the ad hoc tool. When a user connects a database within an application, the code executes the set role command and passes security validation. So the user does not need to know the character's password, nor does he need to enter the set role command himself.
Object permissions
Object permissions refer to the right 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. As shown in the following table:
An object has more than one permission, and special permission all can be granted or revoked. The all permissions on the table include:
Select,insert,update and Deletes, and Index,alter, and reference.
How to look at this table we take ALTER permission as an example to explain
ALTER permission
Allows ALTER TABLE and lock table operations to be performed, ALTER TABLE can do the following:
. Change table name
. Add or Remove Columns
. Change the data type or size of a column
. Turn a table into a partitioned table
Alter permission on the sequence allows the execution of the ALTER SEQUENCE statement to be reassigned to the sequence by assigning a minimum, increment, and buffer size.
System permissions
System permissions require the ability of the grantee to perform system-level activities, such as connecting to a database, changing a user's session, setting up a table or creating a user, and so on. You can get full system permissions on the data dictionary view System_privilege_map. Both object permissions and system permissions are granted to the user or role through the GRANT statement. Note that the statement should be with the GRANT OPTION clause when granting permission to the object, but the statement is with ADMIN option when granting the system right, so when you try to grant system permissions, use the statement with Grant Option system will report an error: only ADMIN OPTION can is specified. Pay special attention to this syntax and error message in your exams.
Roles and Role permissions
Role permissions grant a role to the user who belongs to it. Any permission can be granted to a role. Granting system permissions to the grantee must use the with_admin_option clause, during the session
prev [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 roles statement. However, role permissions cannot depend on the permissions stored in SQL. If a function, program, package, trigger, or method uses another object that is scheduled, it must be granted directly to the owner of the object, because permissions do not change between sessions.
Grant and REVOKE permissions
Grant permissions to a user or role using the GRANT statement, the syntax of the GRANT statement is as follows:
Object permissions are granted with Grant OPTION,
Permissions and Data Dictionaries
The data dictionary is where Oracle stores information about the structure of the database, the data itself is stored elsewhere, and the data dictionary consists of tables and views. The easiest thing to test about a data dictionary in an exam is to see what kind of permissions have been granted. For example, Dba_tab_priv contains information about the object permissions granted to another user and whether the WITH Grant Otpion substring is granted. Note that Dba_tab_priv not only contains the relationship to the permissions of the table, but also the permissions on functions, packages, queues, and so on. The following table lists the data dictionary views for all permissions and roles:
Table: Data dictionary view of permissions
Previous page
prev [1] [2] [3]
