Oracle data manipulation and control language detailed

oracle| Control | data | detailed
Insert 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 all_tab_columns,user_tab_columns in the data dictionary view, or Dba_tab_ Found in the 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 of    are equivalent.    deadlock    When two transactions are locked and each other is waiting for another to be unlocked, this is known as a deadlock.    When a deadlock occurs, Oracle detects the deadlock condition and returns an exception. Transaction control    Transaction control includes the coordination of multiple simultaneous access to the same data. When a user changes the data that another user is using, Oracle uses transaction control who can manipulate the data. A basic unit of    transaction    transaction representation is a series of SQL statements that are successful or unsuccessful operations 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 undo rollback point, and roll back to rollback point 3, complete transaction change data forever or discard modifications. Transaction CONTROL Statement    statement purpose   Commit   Complete transaction, data modification successful and open to other users   Rollback   undo transaction, Undo all operations   rollback to savepoint   undo actions after set rollback point   set transaction respond to transaction or statement consistency , especially for transactions using rollback segments      Example: &NBsp; 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 rollback ( Partial Rollback)    savepoint in SQL and Pl/sql are intermediate flags 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.    example   begin    insert into Atm_log (who,when,what,where) VALUES (' Kiesha ', Sysdate, ' withdrawal of $ ', ' ATM54 ') savepoint atm_logged;   update CheckinG SET balance=balance-100 return balance into new_balance;   if new_balance<0 THEN ROLLBACK to atm_logged; COMMIT RAISE Insufficient_funda; The end IF   END        keyword savepoint is optional, so the following two statements are equivalent:   rollback to atm_logged; ROLLBACK to SavePoint atm_logged;     consistency and transactional    consistency is a key concept in the 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 the lifetime of statements 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. A function of    transaction level consistency    SET TRANSACTION is to ensure that there is one implementation in transaction-level consistency or statement-level consistency. Oracle uses these terms:    isolation level READ COMMIT represents statement-consistent    isolation levels SERIALIZABLE representing transactional-level consistency.    Example:   set TRANSACTION isolation level READ COMMIT;   set TRANSACTION Isolation Level READ commit     The following statements also ensure transactional consistency:   set transcation Read only     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:   select (no for UPDATE clause) LOCK TABLE SET role alter SYSTEM ALTER ALARM&NBSP;&NBSP;&NBS p;  even without changing any data, the READ only transaction must still use a commit or rollback to end the entire transaction. Another application of the    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.    transactions begin with SET TRANSACTION, as follows:   set TRANSACTION use ROLLBACK SEGMENT rb_large;   &　　nbsp 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 look at an example. We have a rollback segment table space size is 2G, in the peak period requires 10 rollback segments to meet the needs of users, these peak online users only small transactions. In a week we ran 4 large transactions that required deletion and loading of data, 1G for each revocation, and the size of the rollback segment as follows:   rb_large (initial 100M Minextenta 2)  &nbsP;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 Minextents 5) Rb8 (initial 1M next minextents 5) rb9 (initial 1M next minextents 5) RB10 (initial 1M next minextents 5) &NB Sp     all are properly arranged in the 2G 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 tablespace is not enough, The database administrator had to work at 2 O ' night, and each transaction started with the following statement:   set TRANSACTION use ROLLBACK SEGMENT rb_large       Now 4 transactions reuse the same tablespace, preserving the table space of 4 rollback segments within 2G. The database administrator can sleep until dawn. Creating and modifying the user    Create USER statement will create a user. When a user connects to an Oracle database, it must be validated. There are three types of authentication in Oracle:    database    external    Global    default is database validation, when a user connects to a database, or Acle will detect whether the user is a legitimate user of the database, and to provide the correct password.external authentication, Oracle will only detect if the user is a legitimate user and 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    DB verification account is a good default type and the most common type.　　Create an account is Piyush, password is welcome account, just execute the following command:   create use Piyush identified by welcome    　　Piyush can change the password to Saraswatt by using the following statement:   alter USER Piyush identified by saraswati;     External authentication user account    user account entered the database can not provide a password, in this case, instead of the database to identify the password is the client operating system. 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 to create the operating system user APPL is:   create user ops$appl identified eaternally     but under normal circumstances, Os_authent_ Prefix will be set to NULL, as follows:   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 passwords, but is detected by the X.509 directory server. The method to create a global type user account is:   create user Scott identified globally as "Cn=scott,ou=divisional,o=sybex,c=us"      CloseThe key word identified globally as indicates that a global type user account is established.    Create and change user account    create user to set up your account and assign values to the 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 of the properties can be set with the creater user and ALTER user statements, with the following attributes specifically described:    assigning the default table space to the user    tablespace (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.   create USER Piyush identified by Saraswati Defaulte Tablespace; ALTER user Manoj Defaulte tablespace dev1_data;     assigns temporary table spaces    temporary tablespaces to the user, as the name implies, temporary storage tables, A temporary segment of a user object such as an index. Create a method like   create USER Piyush identified by Saraswati temporary tablespace;　　ALTER user Manoj Temporary tablespace dev1_data;     use quotas for user-allocated table spaces    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.   create USER Piyush identified by Saraswati DEFAULT tablespace user_data QUOTA Unlimited on user_data QUOTA 20M on tools; ALTER user Manoj QUOTA 2500K on tools;     assigns a summary to the user &The nbsp;  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.   create USER Piyush identified by Saraswati profile tablespace User_data; Alter user manoj temporary tablespace dev1_data;     Specify roles for user response    This property can only be set by the ALTER USER statement, try Figure using the Create USER statement setting returns an exception.　　  alter USER Manoj DEFAULT role all EXCEPT salary_adm;     Set the expiration time for the user's password to change at the next user logon    when the user's password expires, the password,oracle will be forced to prompt the user to enter the old password at the next logon. 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.   alter USER Manoj identified by welcome; ALTER user Manoj PASSWORD expire;     lock account, the user cannot log on   alter user QL Ac  count LOC k      unlock accounts so that users can log on to the database   alter user QL account unlock     permissions and Roles &nbs p;  permissions allow users to access objects or execute programs that belong to other users, and Oracle systems provide three types of permissions:    Object-level    system-level    role Corner Color Level  &nbsP 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 in which permissions 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.    Establish and use roles    as previously sued, the purpose of role existence is to make the management of permissions easier. Set up a role using the Create roles statement, his syntax is as follows:   create Role_name identified by password creation role Role_name identified Exte rnally Create role Role_name identified globally     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.   set role Role_name identified by password     externally and globally types of roles by the operating system and Oracle Service server Validation. 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:    permissions ALTER DELETE EXECUTE INDEX inserT read  reference select  update  directory no  no  no   no  no  yes no No  no  function No no no no no no no no no no  no no& Nbsp; procedure No  no No no no no no no no no no no no no no no no no no no no no no no no no no no no&nbsp  db Object No  no  yes No No  no no no no  libary  no no yes& Nbsp; 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 &Nbsp;no  no  no  no  view  no  yes  no   no  yes  no  no  yes  yes      object has more than one permission, special permissions all can be granted or revoked. The all permissions on the table include:    select,insert,update and Delete, as well as index,alter, and reference.    How to look at this table we take ALTER permission as an example to explain    ALTER permission    allow execute ALTER TABLE and LOCK table operations, ALTER TABLE can do the following :   . Change the table name   . Add or Remove column   . Change the data type or size of the column   . Converting a table to a partitioned table    ALTER permission on sequence allows you to execute the ALTER SEQUENCE statement, reassigning the sequence to the minimum, increment, and buffer sizes.    System permissions    system permissions require that the grantee have the ability to perform system-level activities, such as connecting to a database, changing a user's session, creating 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.    role and Role permissions    role permissions are thePermissions that belong to the user are granted a role. Any permission can be granted to a role. Granting system permissions to the grantee must use the WITH_ADMIN_OPTION clause to grant or revoke role permissions through the set roles statement during the session. 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 the user or role using the GRANT statement, the syntax for the GRANT statement is as follows:   grant role (or system privilege) to user (Role,public) With ADMIN option (optional)      object permissions are granted with Grant OPTION,    Permissions and data dictionary    data Dictionary is Oracle Save Where the database structure information is stored, the data itself resides 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: Permissions data dictionary View    View    action   all_col_privs represents authorization on a column, The user and public are authorized by the grantee   ALL_COL_PRIVS_MADE   on the column, and the user is the owner and the grantee &NBSP;&NBSP;ALL_COL_RECD the authorization on the column, The user and public are the   all_tab_privs of the grantee representing the authorization on the object, the user is public or the grantee or the user is the owner   ALL_TAB_PRIVS_MADE   Represents the permissions on an object, the user is the owner or the grantee   all_tab_privs_recd   represents the permissions on the object, the user is public or grantee   DBA_COL_PRIVS   all authorization on the database column   DBA_ROLE_PRIVS    Show roles that have been granted to users or other roles   DBA_SYS_PRIVS   system permissions granted to a user or role   DBA_TAB_PRIVS   All permissions on the database object   ROLE_ROLE_PRIVS   display the roles that have been granted to the user   role_sys_privs displays the system permissions granted to the user through the role   &NBSP;ROLE_TAB_PRIVS displays the object permissions granted to the user through the role   SESSION_PRIVS   displays all system permissions that users now have access to   user_col_ PRIVS Displays the permissions on the column, the user is the owner, the grantee, or the grantee   user_col_privs_made the permissions that have been granted on the display column, and the user is the owner or the grantee &NBSP;&NBSP;USER_COL_PRIVS_RECD    displays the permissions granted on the column, the user is the owner or the grantee   USER_ROLE_PRIVS   displays all roles that have been granted to the user   user_sys_ privs   Displays all system permissions that have been granted to the user   user_tab_privs display all object permissions granted to the user   USER_TAB_PRIVS_MADE    Displays the object permissions that have been granted to other users, the user is the primary &NBSP;&NBSP;USER_TAB_PRIVS_RECD displays the object permissions that have been granted to other users, the user is the grantee