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. 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 Committed Transaction, Data Modification Successful and Open Rollback Revocation to Other Users, Undo All Operations Rollback To SavePoint Removal The SET TRANSAACTION SETRY SETTIVENT SETRY use rollback Example: BEGIN UPDATE checking SET balance = balance-5000 WHERE account = 'Kieesha'; INSERT INTO checking_log (action_date, action, amount) VALUES (SYSDATE, 'Transfer to brokerage', - 5000); UPDATE brokerage SET cash_balance = cash_balance 5000 WHERE account = 'Kiesha'; INSERT INTO brokerage_log (action_date, action, amount) VALUES (SYSDATE, 'Tracfer from checking', 5000) COMMIT EXCEPTION WHEN OTHERS rOLLBACK END Savepoint and partial rollbacks (Partial rollback) in SQL And PL / SQL SavePoint is an intermediate flag within 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 checking SET balance = balance-100 RETURN balance INTO new_balance; IF new_balance <0 the rollback to atm_logged; commit raise insufficient_funda; end if End keyword savepoint is optional, so the following two statements are equivalent: rollback to savepoint atm_logged; consistency and transaction consistency is things control Key. 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 ensure that the transaction class is consistent: Set Transcation Read Only Any an attempt to modify the data in the read only transaction will throw an exception. However, read only transactions can only be used in the following statements: SELECT (no for Update clause) Lock Table Set Role ALTER SYSTEM ALARM Even if there is no 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 big returning segment, and now give a big transaction allocated a big returns, other small rollback The paragraph 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 withdrawal requires 1G, the size of the rollback segment is as follows: RB_LARGE (Initial 100M MineXTenta 2) RB1 (Initial 1M Next Minexts 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 minextents 5) rb8 (initial 1M next Mineltnts 5) RB9 (Initial 1M Next Mineltents 5) RB10 (Initial 1M Next Mineltents 5) All very properly arranged in 2G tablespace, if our default Round-Robin gives a returned segment to the transaction, 4 The big business will have four independent returns, and each returning segment will be 1G. If so our 2G table space is not enough, and the database administrator has to work at 2 o'clock at night, every business Start by the following statement: SET TRANSACTION USE ROLLBACK Segment RB_LARGE now reuses the same tablespace, 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: ALTER USER PIYUSH Identified by Saraswati; External Verify User Account User Account When entering the database, you may not provide a password. In this case, the client operating system is replaced by the database.
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 normal case, os_authent_prefix will be set to empty, like this: Create User Appl Identified Eaternally is the same, 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 = DiVisional, O = Sybex, C = US" Keyword Identified Global AS Represents a user account for a global type. Create and change user account CREATE USER to create user accounts and assign values to the properties of 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 Saraswati DEFAULTE TABLESPACE User_Data; ALTER USER Manoj Default TableSpace DEV1_DATA; Assign User Temperature Temporal Temporal Space for Upon Misites, Demonstration of User Objects. Establish a method, as Create User PiyushiTified by Saraswati Temporary TableSpace User_Data; ALTER USER Manoj Temporary TableSpace DEV1_DATA; To assign the user's use of the quota to use the quota limit user using the number of disks in the tablespace. Quota can be set by bytes, kilobytes, megabytes or unlimited. Create User Piyush Identified by Saraswati Default TableSpace User_Data Quota Unlimited on Tools; ALTER USER Manoj Quota 2500k on tools; assigning a brief mesh for users to limit users who 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 Saraswati PROFILE TABLESPACE USER_DATA; ALTER USER Manoj Temporary TableSpace Dev1_data; This property can only be set by the ALTER USER statement to the user response, trying to return back to an exception to the CREATE User statement setting. ALTER USER MANOJ Default Role All Except Salary_adm; Sets the user's password to the user to change when the user is logged in, when the user's Password expires, the next login will force the modification of Password, Oracle prompts the user to enter the old Password, Then enter the new Password. This feature is often used in new users, and when new users must modify the pathWord, alter user manoj identified by Welcome; ALTER user manoj password expire; lock account, is the user not logged in to 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, the Oracle system provides three privileges: Object Object-Level System System-Level Role Role Charity These privileges can be granted Give the user, special user public, or role if granted a permission to a special user "public" (user public is Oracle predefined, each user has this user enjoyment), then the permission is granted to the database All users. 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 passWord Create Role Role Role_name Identified Globally By default, the role 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 Password External 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.
In the following table: Permissions ALTER DELETE EXECUTE INDEX INSERT READ REFERENCE SELECT UPDATE Directory no no no no no yes no no no function no no yes no no no no no no procedure no no yes no no no no no no package no no yes no no no no no no DB Object no no yes no no no no no no Libary no no yes no no no no no no Operation no no yes no no no no no no Sequence yes no no no no no no no no Table yes yes no yes yes no yes yes yes Type no no yes no no no no no no View no yes no no yes no no yes yes target by more than one authority, ALL special privileges 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 Transition into 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.
