SQL language is divided into four major categories: data query language DQL, data control language DML, data definition language DDL, data control language DCL. The structure used to define data, such as creating, modifying, or deleting a database; DCL is used to define permissions for database users; in this article, I will tell the use of these two languages in Oracle. DML language DML is a subset of SQL, mainly used to modify data, which lists the DML statements supported by Oracle.
Statement Use INSERT Add Row Update Update Add Row Update Update Store Data Delte Delete Row Select for Update Prohibits Other Users from Accessing the DML statement is being processed. LOCK TABLE prohibits other users from using DML statements in the table
Inserting data INSERT statements are often used to insert a row in the table, and there can be special data fields in the line, or you can use subquers to create new rows from existing data. Column directory is optional, the default column directory is all column names, including comlumn_id, and Comlumn_ID can be found in Data Dictionary view all_tab_columns, user_tab_columns, or dba_tab_columns. The quantity of the data inserted and the data type must match the number of columns and data types. The data type that does not meet the column definition will implement implicit data conversion to the insert value. NULL strings Insert a null value into the appropriate column. Keyword NULL is often used to indicate that a column is defined as NULL value. The following two examples are equivalent.
INSERT INTO CUSTOMERS (Cust_ID, State, Post_code) Value ('Ariel', NULL, '94501');
or
INSERT INTO CUSTOMERS (Cust_ID, State, Post_code) Value ('Ariel', '94501');
Update the data update command to modify the data in the table.
Update ORDER_ROLLUPSET (Qty, Price) = (SELECT SUM (QTY), SUM (Price) from Order_Lines Where Customer_ID = 'Kohl'where Cust_Id =' Kohl'and Order_Period = To_Date ('01 -OCT-2000 ')
Deleting the data delete statement is used to delete a row or multi-line data from the table, which contains two statements: 1. Key words delete from follow the deletion of data. 2, WHERE follows the delete condition
Delete from PO_LINESWHERE SHIP_TO_STATE IN ('TX', 'NY', 'IL') AND ORDER_DATE
Cleaning Table If you want to delete all the data in the table, you can take into account the TRUNCATE statement using the DDL language. Truncate is just like a delete command without a WHERE clause. Truncate will delete all rows in the table. TRUNCATE is not a DML statement is a DDL statement, he and DELETE different features.
Truncate Table (Schema) Table Drop (Reuse) Storage
The Storage substring is optional and the default is the Drop Storage. When using the DROP Storage, the table and table index will be shortened, shrink the table to the minimum range, and reset the next parameter. Reuse Storage does not shorten the table or adjust the next parameter. Truncate and Delete have the following differences 1. Truncate is very fast in various forms, whether it is big or small. If there is a rollback command delete will be revoked, and Truncate will not be revoked. 2, Truncate is a DDL language, like all other DDL languages, he will be implicit, and cannot use the rullback command to Truncate. 3, Truncate will reset the high level line and all indexes. When the entire table and index are fully browsed, the TRUNCATE operation is much more faster than the table after the DELETE operation. 4. Truncate cannot trigger any DELETE trigger. 5. You cannot grant any person to empty the table of the table. 6. When the table is emptied, the index of the table and the table is reset to the initial size, while Delete can not. 7, can't empty the parent table. The SELECT FOR UPDATE SELECT FOR UPDATE statement is used to lock rows to prevent other users from modifying data on that line. When the row is locked, other users can query the data of the line with the SELECT statement, but cannot modify or lock the row. Lock table LOCK statements are often used to lock the entire table. Most DML languages cannot be used on this table when the table is locked. The LOCK syntax is as follows: Lock Schema Table in Lock_Mode
Where Lock_Mode has two options: Share Sharing Method Exclusive Unique way:
Lock Table Intentory in Exclusive Mode
Dead lock When two transactions are locked, and they are waiting for another unlock, which is called dead lock. When there is a deadlock, Oracle will detect deadlock conditions and return an exception.
Transaction control transaction control includes a plurality of synchronization access to the same data. When a user changed the data being used by another user, Oracle uses transaction to control any data. A basic unit of the business represents work is a series of SQL statements that are successful or unsuccessful as a unit. There are many statements in SQL and PL / SQL to allow programmers to control transactions. Programmers can: 1. Explicitly start a thing, select the sentence level consistency or transaction-level consistency 2, set the revocation rollback point, roll back to the rollback point 3, complete the transaction to change the data or give up the modification. Transaction control statement
Statement Use Commit Commit transaction, data modification successfully and opens the ROLLBACK revocation of other users, revoke all operations of Rollback to SavePoint Removal of the Roll Rolleout point after setting the SET Transaction response transaction or statement consistency; especially for transaction segment
example:
BEGINUPDATE checkingSET balance = balance-5000WHERE account = 'Kieesha'; INSERT INTO checking_log (action_date, action, amount) VALUES (SYSDATE, 'Transfer to brokerage', - 5000); UPDATE brokerageSET cash_balance = cash_balance 5000WHERE account = 'Kiesha'; INSERT INTO brokerage_log (action_date, action, amount) VALUES (SYSDATE, 'Tracfer from checking', 5000) COMMITEXCEPTIONWHEN OTHERSROLLBACKENDSavepoint and partial rollbacks (Partial rollback) in SQL and PL / SQL is an intermediate in Savepoint flag within a scope of a transaction. It is often used to divide a long transaction into small parts. Reserve any point in the point SavePoint flag-long transaction, allows you to return to the operation after the point. SavePoint frequently in the application; 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 start of each function. After rolling back to a SavePoint, the data blocked after the SavePoint is released. In order to achieve partial rollback, you can roll back the transaction back to the specified location with the ROLLBACK statement with the To SavePoint clause. example
BEGIN INSERT INTO ATM_LOG (who, when, what, where) VALUES ( 'Kiesha', SYSDATE, 'Withdrawal of $ 100', 'ATM54') SAVEPOINT ATM_LOGGED; UPDATE checkingSET balance = balance-100RETURN balance INTO new_balance; IF new_balance <0THENROLLBACK TO ATM_Logged; Commitraise Insufficient_funda; end ifend
Keyword SavePoint is optional, so the following two statements are equivalent:
Rollback to atm_logged; rollback to savepoint atm_logged;
Consistency and transaction consistency are key attractions to control. Mastering Oracle's consistency model can make you better, more appropriate use of transaction control. Oracle guarantees data by consistency only after the transaction is complete, it can be seen and used by the user. This technology has a huge role in multi-user databases. Oracle often uses state-level consistency, ensuring that data is visible between the life of the statement but cannot be changed. The transaction consists of multiple statements. When using transactions, transaction-level consistency ensures that data is visible to all statements throughout the transaction period. Oracle consistently consistent via SFEM CHANGE NUMBER. An SCN is a time-oriented database internal key. The SCN will only increase, and the SCN indicates a point on time, and each data block has an SCN, and the operation is performed by comparing this point implementation. One function of transaction-level consistency Set Transaction is to ensure that the transaction-level or a statement level is implemented. Oracle uses these terms: Isolation Level Read Commit Represents the uniform ISOLELSLALALALALALALALALAALIZABLE in the statement. example:
Set Transaction Isolation Level Read Commit; SET Transaction Isolation Level Read Commits The following statements can also ensure that transaction is consistent:
Set Transcation Read Only
Any an attempt to modify the data in a read only transaction will throw an exception. However, the Read Only transaction can only be used in the following statements:
SELECT (no for Update clause) Lock TableSet RolealTer SystemalTer Alarm
Even if you don't change any data, the read only transaction still must use a commit or rollback to end the entire transaction. Another application of SET Transction is directly using the Rollback Segment. The rollback segment is a special data object in Oracle, and the header of the roll band contains information that is using the returning segment transaction. When the user rolls back the transaction (ROLLBACK), Oracle will use the previous image in the rollover segment to restore the modified data to the original value. Oracle uses Round-Robin to randomly assign a rollback segment. A big transaction can allocate any rollback segments, which may result in a large size of the rollback segment. Therefore, we must avoid randomly allocate back segments. The transaction begins with SET Transaction, like this:
Set Transaction Use Rollback Segment RB_LARGE;
RB_LARGE is a name of a large rollback segment, and now a large returning segment is assigned a big transaction, and other small returns will not be managed by dynamic space, which is more efficient. Let's take an example. We have a split table space size of 2G, which requires 10 rebounds in the peak period to meet the needs of users, these peak online users have only small transactions. One week we have run four big matters, these transactions need to delete and load data, each withdraws 1G, and the size of the roll band is as follows:
rb_large (initial 100M minextenta 2) rb1 (initial 1M next minextents 5) rb2 (initial 1M next minextents 5) rb3 (initial 1M next minextents 5) rb4 (initial 1M next minextents 5) rb5 (initial 1M next minextents 5) rb6 (initial 1M NEXT MINEXTENTS 5) RB7 (Initial 1M Next Mineltnce 5) RB9 (Initial 1M Next Mineltents 5) RB10 (Initial 1M Next Mineltnce 5)
All of the very appropriate arrangements in 2G table space, if our default Round-Robin gives the transaction back to the segment, 4 big transactions will have four separate rollback segments, the size of each rollback segment Will be 1G, if so our 2G table space is insufficient, and the database administrator has to work at 2 o'clock at night, and each transaction begins with the following statement:
Set Transaction Use Rollback Segment RB_LARGE
Now 4 transactions reuse the same table space, keeping 4 returns to the tablespace within 2g. Database administrators can sleep to dawn.
Establishing and Modifying User Create User statements will establish a user. It must be verified when a user is connected to an Oracle database. There are three types in Oracle: Database External Global default is a database authentication. When the user is connected to the database, Oracle will detect if the user is a legitimate user of the database, and to provide the correct password.external verification, Oracle will only detect the user It is legal user, Password has been verified by the network or system. Global verification also only detects whether it is a legitimate user, Password is verified by OraclesECurity Server. Database verifies that the user account database verification account is a good default type, and is the most common type. Creating an account is Piyush, the password is the account of Welcome, just execute the following command: Create Use Piyush Identified by Welcome
Piyush can change the password to Saraswatt by the following statement:
Alter User Piyush Identified by Saraswati;
External verification User account user account can not provide passwords when entering the database, in which case the database identification password is the client operating system. External verification account is sometimes called an OPS $ account. When they initially introduced in Oracle6, the Oracle account has a keyword prefix OPS $, which is why INIT.ORA parameter OS_AUTHENT_PREFIX is OPS $ - the default feature is consistent with Oracle6. The string defined by the OS_AUTHENT_PREFIX must be preprocessed as an operating system account name for Oracle external identification account. Creating an operating system User Appl is:
Create User OPS $ Appl Identified Eaternally
But in general, Os_Authent_prefix will be set to empty, like this:
Create User Appl Identified Eaternally
This effect is the same, the keyword identified externally tells Oracle This is an external identification account. Global User Account Global Type User Account Database does not detect the password, but is detected by the X.509 directory server. The method of creating a user account for a Global type is:
Create User Scott Identified Globally AS "CN = Scott, Ou = DiSional, O = Sybex, C = US"
Keyword Identified Globally AS represents a user account for a global type. Create and change user account CREATE User to establish a user account and assign a value to the user account. ALTER USER is used to change user accounts and properties. But the CREATE User statement must include user names and passwords. Some qualifications are set with Creater User and ALTER User statements. The following is specific to these properties: Give User Assignment Default Table Space Table Spaces (TABLESPACE) is a placement table, an index, and a user object. If there is no table space in the CREATE User statement, then the default is the system table space.
Create User Piyush Identified by SaraswiaFAulte TableSpace User_Data; ALTER USER Manoj Default TableSpace DEV1_DATA;
Assign Up Time Table Space Temporary Table Spaces to users, as the name refer to user objects such as temporary storage tables, indexes. Establish a method
Create User Piyush Identified by Saraswatitemporary TableSpace User_Data; ALTER USER Manoj Temporary TableSpace DEV1_DATA; Using Quota for Using Quota Using Quota Using Quota Using Quota Using Quota Using Disks in Table Spaces. Quota can be set by bytes, kilobytes, megabytes or unlimited.
Create User Piyush Identified by SaraswiaFault TableSpace User_Dataquota Unlimited on User_Dataquota 20M on Tools; ALTER USER Manoj Quota 2500k on tools;
Assigning a brief watch for users to restrict resources that users consume during sessions. These resources include: Time, idle time, idle time, number of logical read data of each session, etc., default briefs are unlimited.
Create User Piyush Identified by SaraswatiProfile TableSpace User_Data; ALTER USER Manoj Temporary TableSpace dev1_data;
The specified role for the user can only be set by the ALTER USER statement, trying to return back to one exception to the CREATE User statement setting.
Alter User Manoj Default Role All Except Salary_ADM;
Set the expiration time for the user to change when the user is logged in, when the user's Password expires, when the next login will force the Password, Oracle prompts the user to enter the old Password, and enter the new Password. This feature is often used in new users, and you must modify Password immediately when new users use default Password logins.
Alter User Manoj Identified by Welcome; ALTER USER Manoj Password Expire;
Lock the account, is the user cannot log in
Alter User QL AC Count Lock
Unlock the account so that users can log in to the database
Alter User QL Account UNLOCK
Permissions and role permissions allow users to access objects or execution programs belonging to other users, and the Oracle system provides three permissions: Object Object-Level System System-Level Role Role level These permissions can be granted to users, special user public, or roles if grant a permission Special user "public" (user public is Oracle predefined, each user enjoys the permissions of this user), then it means that the permission will be granted to all users of the database. For administrative privileges, the role is a tool, permissions can be granted to a role, and the role can also be granted to another role or user. The user can inherit the permissions through the role, except for the management privilege, the role service has no other purpose. Permissions can be granted or canceled in the same way. Establishing and using the role as previously claimed, the purpose of the role is to make the management of the permissions easier. Establish a role using the Create Role statement, his syntax is as follows:
Create Role Role_name Identified by PasswordCreate Role Role_name Identified External Role Role_Name Identified Globally
The role established by default is not Password or other identification. If you use the Identified By clause, the role does not respond automatically and must be activated with SET ROLE.
Set role role_name Identified by PasswordExternal The role of the GLOBALLY type is verified by the operating system and Oracle Service Server. Usually the user needs permissions to modify data in the form used in the application, but only when the application is running rather than using the AD HOC tool, this context-sensitive security can be implemented by the role of Password. When the user connects the database inside the application, the code will execute the set role command, by security verification. So the user does not need to know the character's Password, nor does it need to enter the set role command yourself. Object rights object permissions refer to the right to perform special actions on tables, views, sequences, processes, functions, or packets. There are nine different types of permissions that can be granted to the user or role. As follows:
Permissions ALTERDELETEEXECUTEINDEXINSERTREAD REFERENCESELECT UPDATEDirectoryno no no no no yesnono nofunctionnonoyesno nononononoprocedureno noyesnonono nononopackagenonoyesnonononononoDB Objectno no yesnono nonononoLibary nonoyes nonono no nonoOperation no no yesno no no no no noSequenceyes no no no no no no no noTable yes yes noyesyes no yes yes yesTypeno no yes no no no no No no no no yes yes yes yes yes yes yes yes
Objects are made by more than one permissions, and the special permissions ALL can be granted or revoked. If a table's all permissions include: SELECT, INSERT, UPDATE, and DELETE, and INDEX, ALTER, and REFERENCE. How do I see this table With ALTER privileges as an example, Alter Permissions allow for ALTER TABLE and LOCK TABLE operations, Alter Table can do the following: Change the table name. Add or delete columns. Change the data type or size of the column. Table Change to the partition table ALTER permission on Sequence allows the ALTER SEQUENCE statement to be executed, re-assigning minimum values, increment, and buffer sizes to Sequence. System Permissions System Permissions need to be granted to have system-level activities, such as connecting to databases, changing user sessions, establish a table, or build a user, and so on. You can get complete system permissions on the Data Dictionary View System_PrivileGe_map. Object privileges and system permissions grants users or roles via the GRANT statement. It should be noted that the statement should be the WITH GRANT Option clause when granted object permission, but the statement is WITH Admin Option when granting the system power, so when you try to grant system permissions, use the statement with the grant option system to report an error. : Only Admin Option Can Be Specified. Pay special attention to this syntax and error message in the exam. Role and Role Permissions Role Permissions are granting a role that belongs to the user. Any permissions can be granted to one role. Granting system permissions Give awarder must use the use_admin_option clause, grant or revoke roles through the SET ROLE statement during the session. However, role privileges cannot rely on privileges stored in SQL. If functions, programs, packages, triggers, or methods use another plan, you must authorize the owner of the object, because the permissions will not change between sessions. Grant and revoking permissions to the user or role grant permission using the GRANT statement, the grammar of the GRANT statement is as follows:
Grant role (or system privilege) to user (optional) object permission is granted with Grant Option, Permissions, and Data Dictionary Data Dictionary is a place for Oracle storage about database structure information, the data itself is stored in other Place, data dictionary consists of tables and views. The most prone to data dictionary in the exam is: View that type of permissions have been granted. For example, DBA_TAB_PRIV contains information about object permissions to another user and whether it is with the WITHT OTPION substrs when granted. Note that DBA_TAB_PRIV contains not only the relationship of the permissions of the table, but he also includes the relationship between functions, packages, queues, etc. The following table lists all permissions and roles of data dictionary views: Table: Data Dictionary View of Permissions
View action all_col_privs represents the authorization on the column, the user, and public is authorized to be authorized by the author all_col_privs_made, the user is the authority and the authorization ALL_COL_RECD represents the authorization on the column, the user, and public is the authorized ALL_TAB_PRIVS represents the object. Authorization, the user is a public or awarder or user who is the owner ALL_TAB_PRIVS_MADE to represent the permissions on the object, the user is the author or the authority all_tab_privs_recd represents the permissions on the object, the user is all authorized DBA_ROLE_PRIVs on the PUBLIC or the DBA_ROLE_PRIVS on the Database column Displaying the Role DBA_SYS_PRIVS that has been granted to the user or other role has granted all permissions on the user or role DBA_TAB_RIVS database object Role_Role_Privs Display the granted user Role Role_sys_privs Display system permission Role_TAB_PRIVS displayed by the role Role to grant the user's object authority SESSION_PRIVS Displays the permissions on all system authority USER_COL_PRIVS display columns available now, the user is the author, the author, or the authorized user_col_privs_made display column, the user is the owner or awarder user_col_privs_recd display column has been granted Permissions, the user is the owner or awarder user_role_privs display all the roles of the authorized to the user. User_sys_privs displays all system permissions that have been granted to the user USER_TAB_PRIVS Display All object authority for the user has been granted to the user User_tab_privs_made display has been granted to other users Object privileges The user is the owner_tab_privs_recd display that the object authority has been granted to other users, and the user is granted.
