Oracle data manipulation and control of language Xiang Jie

Mar 31 posted in Oracle (RSS), 101 views.

SQL language is divided into four main categories: data query language DQL, data manipulation language DML, data definition language DDL, Data Control Language DCL. Which is used to define the data structure, such as create, modify, or delete the database; DCL is used to define the database user's permissions; In this article I will describe in detail the two languages in use in Oracle.

DML language

DML is a subset of SQL is mainly used to modify data, the following table lists the ORACLE support for DML statements.

Statement	Uses
INSERT	Add a row to the table
UPDATE	Update the data stored in the table
DELETE	Delete row
SELECT FOR UPDATE	DML statements to prohibit other users from accessing the row being processed.
LOCK TABLE	To prohibit other users to use DML statements in the table

Insert Data

INSERT statement is often used to insert the table row, row can have a special data field, or you can use sub-queries from the data that already exists to create a new line.

Column directory is optional, the default directory listed all the column names, including comlumn_id, comlumn_id in the data dictionary view ALL_TAB_COLUMNS, USER_TAB_COLUMNS, or DBA_TAB_COLUMNS found.

Insert the number of rows of data and data types must be the number of columns and data types match. Do not meet the definition of the column data type will be introduced into the value of implicit data conversion. NULL string to a NULL value is inserted into the appropriate column. Keyword NULL is often used to indicate to a column defined as NULL values.

The following two examples are equivalent.

INSERT INTO customers (cust_id, state, post_code)
VALUE ( 'Ariel', NULL, '94501 ');

INSERT INTO customers (cust_id, state, post_code)
VALUE ( 'Ariel',, '94501 ');

Update data

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

UPDATE order_rollup
SET (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 ')

Delete data

DELETE statement is used to remove the row from the table one or more rows of data, the order contains two statements:

1, keyword DELETE FROM followed by the preparation removed from the data table name.

2, WHERE conditions, followed by the deletion

DELETE FROM po_lines
WHERE ship_to_state IN ( 'TX', 'NY', 'IL')
AND order_date

Empty Table

If you want to delete all the data in the table, clear the table, you can consider using the TRUNCATE statement DDL language. TRUNCATE is like there is no WHERE clause of the DELETE commands. TRUNCATE removes all rows in the table. TRUNCATE is not a DML statement is a DDL statement, he and DELETE the right different characteristics.

TRUNCATE TABLE (schema) table DROP (REUSE) STORAGE

STORAGE sub-string is optional, default is to DROP STORAGE. When using the DROP STORAGE will shorten the table and the table index, shrink to a minimum range of the table and re-set the NEXT parameter. REUSE STORAGE will not shorten the table or adjust the NEXT parameter.

TRUNCATE and DELETE have the following differences

1, TRUNCATE in a variety of form, whether big or small, are very fast. If there is ROLLBACK command DELETE will be withdrawn, but TRUNCATE will not be revoked.

2, TRUNCATE is a DDL language, the DDL to all other languages, he would be implicitly submitted TRUNCATE can not use the ROLLBACK command.

3, TRUNCATE will be re-set the high line and all the indexes. Throughout the tables and indexes in the full browsing through a table after the TRUNCATE operation than in the table after the DELETE operation much faster.

4, TRUNCATE does not fire any DELETE triggers.

5, can not be granted to any person permission to clear the table of others.

6, when the table is cleared the table after the table and talk about indexes re-set the initial size, while the delete can not.

7, can not empty the parent table.

SELECT FOR UPDATE

select for update statement is used to lock the line to prevent other users to modify data on the line. When the row is locked other users can use SELECT statement to query the bank's data, but can not modify or lock the row.

Lock Table

LOCK statement is often used to lock the entire table. When the table is locked, the majority of DML language can not be used on the table. LOCK syntax is as follows:

LOCK schema table IN lock_mode

Which lock_mode There are two options:

share share method

The only way to exclusive

Example:

LOCK TABLE intentory IN EXCLUSIVE MODE

Deadlock

When the two panels have been locked, and each other are waiting for another is unlocked, this situation is called deadlock.

When the deadlock when, ORACLE will detect deadlock conditions, and returns an exception.
Transaction Control

Transaction control, including the coordination of the same data multiple simultaneous access. When a user changes another user is using the data, oracle who can be manipulated using the transaction control data.

Services

Affairs said that the work of a basic unit is a series as a unit has been successful or unsuccessful operation of the SQL statement. In the SQL and PL / SQL statements so that programmers have a lot of control transaction. Programmers can:

1, explicitly start a thing, choose the statement-level consistency or transaction-level consistency

2, set the revocation of rollback point, and roll back to the rollback point 3 to complete the transaction or abandon the changes forever change the data.

Transaction Control Statements

Statement	Uses
Commit	Complete the transaction, data and other users to modify the success of open
Rollback	Revocation services, remove all operations
rollback to savepoint	To withdraw the set-point after the rollback operation
set transaction	Response transaction or statement consistency; especially for the use of rollback transaction

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 rollback (Partial Rollback)

In the SQL and PL / SQL in the Savepoint in the middle of a transaction within the symbol. Often used to a long transaction into smaller parts. Reservation point Savepoint can sign a long transaction at any point, allowing to roll back to the point of the operation. Often used in the application Savepoint; such a procedure contains several functions, each function can be established before a reservation point, if the function fails, it's easy to return to the beginning of each function. After the roll back to a Savepoint the Savepoint data obtained after the blockade was released. In order to achieve partial rollback can be used with a TO Savepoint clause ROLLBACK statements roll back transactions to the specified location.

Cases

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
THEN
ROLLBACK TO ATM_LOGGED;
COMMIT
RAISE insufficient_funda;
END IF

END

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

ROLLBACK TO ATM_LOGGED;
ROLLBACK TO SAVEPOINT ATM_LOGGED;

Consistency and Services

Consistency is the key to control the generous concept of things. Mastered the oracle of the consistency model can make you better and more appropriate use of transaction control. oracle guarantee that the data is only through the consistency in the transaction upon completion in order to be a user to see and use. The technology for multi-user database has a huge role.

oracle often used statement-level (state-level) consistency, ensure that the data between the life cycle in the statement is visible but can not be changed. Affairs, composed by a number of statements, when using transactions, things-level (transaction-level) coherence in the life of the whole transaction period to ensure data is visible to all statements.

oracle through the SCN (syatem change number) the implementation of consistency. A SCN is a time-oriented database internal key. SCN will only increase, not decrease, SCN expressed a point of time, each data block has an SCN, by comparing the point of implementation of the action.

Transaction-level consistency

One role of the SET TRANSACTION is to ensure that the same or statement-level transaction-level, there is a consensus to implement. ORACLE to use these terms:

ISOLATION LEVEL READ COMMIT statement-level agreement that

ISOLATION LEVEL SERIALIZABLE transaction-level, said the same.

Example:

SET TRANSACTION ISOLATION LEVEL READ COMMIT;

SET TRANSACTION ISOLATION LEVEL READ COMMIT

The following statement is also to ensure the transaction-level agreement:

SET TRANSCATION READ ONLY

Any attempt to read-only (READ ONLY) transaction will modify the operation of the data throws an exception. However, READ ONLY transaction can only be used in the following statement:

SELECT (without FOR UPDATE clause)
LOCK TABLE
SET ROLE
ALTER SYSTEM
ALTER ALARM

Even if no change in any data, READ ONLY transaction still must use a COMMIT or ROLLBACK to end the whole transaction.

SET TRANSCTION Another application is in the direct use of rollback rollback (ROLLBACK SEGMENT). Rollback segment is a special ORACLE data object, the head contains the rollback segment is being used in the rollback segment transaction information. When a user transaction rollback (ROLLBACK), when, ORACLE will use the data in the rollback segment to be modified before the image data recovery to the original value. oracle with the round-robin to the transaction were randomly assigned to rollback segments. A large transaction can be assigned to any rollback segment, which may lead to rollback the size becomes large. Therefore, to avoid large transaction were randomly assigned to rollback segments.

SET TRANSACTION transaction to start, like this:

SET TRANSACTION USE ROLLBACK SEGMENT rb_large;

rb_large is a big rollback segment name, and now Jiuji allocated a large transaction of a large rollback segment, the other will not help dynamic small rollback segment space management, so that more efficient.

Here we look at an example. We have a rollback table space size is 2G, at peak times require 10 rollback in order to meet the needs of users, these peak online users only a small business. Week, we ran four consecutive major services, which need to remove and load the data, each one needs to withdraw 1G, the size of rollback segments are 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 minextents 5)
rb8 (initial 1M next minextents 5)
rb9 (initial 1M next minextents 5)
rb10 (initial 1M next minextents 5)

All are very appropriate to the table in the 2G space, if we default round-robin to the distribution of services rollback, four large transaction there will be four separate rollback segments, each the size of rollback segments will be 1G, so that our 2G if the table space is not enough, and database administrators will have to work at night, up 2 points for each transaction by beginning with the following statement:

SET TRANSACTION USE ROLLBACK SEGMENT rb_large

Now four matters to reuse the same table space, Yasumasa 4 rollback table space in less than 2G. Database administrator can Shuidaotianliang.
To establish and modify user

CREATE USER statement will create a user. When a user connects to the ORACLE database, it must be verified. ORACLE verify there are three types:

Database

external

Global

The default is the database authentication, when users connect to the database, oracle database will detect whether the user is a legitimate user, and to provide the correct password.external validation, oracle will only detect whether the user is a legitimate user, password has been the network or system verified. global certification is only to determine whether the legitimate user, password from oraclesecurity server authentication.

Database user account validation

Database, validate the default type of account is a good Zhang is also the most common type. The establishment of an account is piyush, the account password is welcome, simply execute the following command:

CREATE USE piyush IDENTIFIED BY welcome

piyush by the following statement will be the password change saraswatt:

ALTER USER piyush IDENTIFIED BY saraswati;

External authenticate a user account

Entered into the database user account can not provide a password, in which case instead of a database identifying the client operating system password. Account the external validation are sometimes also called OPS $ account, when they initially oracle6 outset of his introduction, oracle account has a keyword prefix OPS $, which is why the init.ora parameter os_authent_prefix is OPS $ - default characteristics and oracle6 consistent. os_authent_prefix must be pre-defined string to identify the account used for Oracle outside of the operating system account names. To create the operating system user appl statement is:

CREATE USER ops $ appl IDENTIFIED EATERNALLY

However, in normal circumstances, os_authent_prefix will be set to empty, like this:

CREATE USER appl IDENTIFIED EATERNALLY

This effect is the same, the keyword IDENTIFIED EXTERNALLY to tell ORACLE This is an external recognition account.

GLOBAL user account

GLOBAL types of user accounts database does not detect passwords, but by the X.509 directory server password test. GLOBAL types to create a user account of the methods are:

CREATE USER scott IDENTIFIED GLOBALLY AS "CN = scott, OU = divisional, O = sybex, C = US"

Keyword IDENTIFIED GLOBALLY AS said that the establishment is a GLOBAL types of user accounts.

To create and change user accounts

CREATE USER for the establishment of user accounts and assign attributes to the user account. ALTER USER is used to change user accounts and attributes. But the CREATE USER statement must include the user name and password.

Some properties can be used CREATER USER and ALTER USER statement is set, the following pairs are specific description of these attributes:

Assigned to the user default tablespace

Tablespace (tablespace) is placed tables, index, cluster and other user object. If you create user statement is not included in the table space, then the default is the system table space.

CREATE USER piyush IDENTIFIED BY saraswati
DEFAULTE TABLESPACE user_data;
ALTER USER manoj DEFAULTE TABLESPACE dev1_data;

Assigned to the user temporary table space

Temporary table space, suggests that it is the temporary storage of tables, indexes and other user object temporary segment. The same way as the establishment of

CREATE USER piyush IDENTIFIED BY saraswati
Temporary TABLESPACE user_data;
ALTER USER manoj Temporary TABLESPACE dev1_data;

Assigned to the user table space using quotas

The use of quotas to restrict users to use the disk space in the table amount. Fixed can be bytes, kilobytes, megabytes, or unlimited to develop.

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;

Assigned to the user a summary table

Summary tables can restrict user sessions consume resources. These resources include: Connect to the database of time, idle time, each session is a logical reading of the amount of data, etc., the default summary table resource limit.

CREATE USER piyush IDENTIFIED BY saraswati
PROFILE TABLESPACE user_data;
ALTER USER manoj Temporary TABLESPACE dev1_data;

Assigned to a role for the user response

This attribute can only be set by the ALTER USER statement, trying to use CREATE USER statement is set to return to return an exception.

ALTER USER manoj DEFAULT ROLE ALL EXCEPT salary_adm;

To set the expiration time of the user's password in order to change the next time a user login

When the user's password expires, the next time you log on will be forced to change password, oracle prompts the user to enter the old password, then enter a new password. This feature is commonly used in new users, when a new user with the default password login must be modified to immediately amend the password.

ALTER USER manoj IDENTIFIED BY welcome;
ALTER USER manoj PASSWORD EXPIRE;

Lock account number, a user can not log on

ALTER USER ql AC
COUNT LOCK

On account unlock, so that users can log on the database

ALTER USER ql ACCOUNT UNLOCK

Permissions and roles

Permission allows users to access an object belonging to other users or perform procedures, ORACLE system provides three kinds of permissions:

Object object-level

System system-level

Role Role Level

These permissions can be granted to users, special user public or role, if granted a permission to a particular user "Public" (the user is the oracle predefined public, and each user to enjoy the privileges enjoyed by the user), then implies that the authority to make granted to all users of the database.

The management authority, the role is a tool that permission can be granted to a role, the role can also be granted to another role or user. Users can access through the role of inheritance, in addition to management of permissions, the role of services, for no other purpose. Permissions can be granted, can also be used to withdraw the same way.

The establishment and use of the role of

As previously v, role exists for the purpose is to enable the management of permissions easier. To establish the role of using the CREATE ROLE statement, his syntax is as follows:

CREATE ROLE role_name IDENTIFIED BY password
CREATE ROLE role_name IDENTIFIED EXTERNALLY
CREATE ROLE role_name IDENTIFIED GLOBALLY

By default, there is no password to establish the role or other identification. If you use the IDENTIFIED BY clause, the establishment, then the role will not automatically respond to, we must use SET ROLE to activate.

SET ROLE role_name IDENTIFIED BY password

EXTERNALLY and the type of role from GLOBALLY operating system and ORACLE Service server authentication. Usually users need permissions to modify the application form used in the data, but only if the application is running and sometimes not in the use of ad hoc tools, such context-sensitive security roles can be achieved with PASSWORD. When the user in the application of internal link to the database, the code will execute SET ROLE command, through the secure authentication. Therefore, users do not need to know the role of the password, do not need to enter the SET ROLE command itself.

Object Permissions

Object privileges refers to the tables, views, sequences, procedures, functions, objects or packages such as the right to perform special actions. There are nine different types of privileges can be granted to the user or role. 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

Objects by more than one permission, special permission can be granted or revoked ALL. If TABLE permissions on the ALL include:

SELECT, INSERT, UPDATE, and DELETE, are INDEX, ALTER, and REFERENCE.

How do I look at this table as an example we illustrate ALTER privileges

ALTER permission

To allow implementation of the ALTER TABLE and LOCK TABLE operation, ALTER TABLE can do the following:

. Change the table name

. Add or remove columns

. To change column data type or size of

. The table into a partition table

ALTER permission on the SEQUENCE to allow implementation of the ALTER Sequence statement to the sequence re-distribution of the minimum, incremental, and buffer size.

System privileges

System privileges granted to those who need to have the ability to conduct system-level activities, such as connect to the database, change the user's session, the establishment of the table or create a user and so on. You can view the data dictionary SYSTEM_PRIVILEGE_MAP gain full system privileges. Object privileges and system permissions are granted through the GRANT statement, a user or role. Note that in granting permission to an object when the statement should be the WITH GRANT OPTION clause, but the right to grant system, as when the statement is WITH ADMIN OPTION, so you're trying to grant system privileges, use the WITH GRANT OPTION statement, the system will report an error : ONLY ADMIN OPTION can be specified. In the examination should pay special attention to this syntax and error message.

Roles and role privileges

The role of authority is to belong to the user's authority to grant a role. Any permission can be granted to a role. To grant system privileges to be granted to those who must use WITH_ADMIN_OPTION clause, during the session through the SET ROLE statement to grant or revoke permissions to roles. However, the role of authority can not rely on the permissions are stored in the SQL. If the function, procedures, packages, triggers, or ways to use an object owned by another plan, then the object must be directly authorized by the owner, it is because the permissions are not changed between sessions.

The grant and revoke privileges

Or to grant permission to the user using the GRANT role statement, GRANT statement syntax is as follows:

GRANT ROLE (or the system privilege) TO user (role, Public) WITH ADMIN OPTION (optional)

Object permissions are granted WITH GRANT OPTION,

Permissions, and data dictionary

ORACLE data dictionary that stores information about the database structure where the data itself is stored in other places, the data dictionary tables and views from the composition. A data dictionary in the examinations on the content of the easiest test: Check the type of permission has been granted. For example DBA_TAB_PRIV contains the user object to grant permissions to another user and whether to grant sub-string with the WITH GRANT OTPION information. Note that not only contains a table DBA_TAB_PRIV authority relations, he also includes the function, package, queue, and so the permissions on the relationship. The following table lists all of the rights and roles of the data dictionary view:

Table: rights data dictionary view

View	Effect
ALL_COL_PRIVS	That the column on the authority, users, and PUBLIC is given by
ALL_COL_PRIVS_MADE	That the column on the authorization, the user is an owner and have been granted to those who
ALL_COL_RECD	That the column on the authority, users, and PUBLIC is given by
ALL_TAB_PRIVS	Said the object of the authorized user is granted to PUBLIC or owner or user is
ALL_TAB_PRIVS_MADE	Said the object's permissions, the user is owner or grantor
ALL_TAB_PRIVS_RECD	Said the object's permissions, the user is granted to PUBLIC or those who
DBA_COL_PRIVS	Database column of all authorized
DBA_ROLE_PRIVS	Show has been granted the role of users or other roles
DBA_SYS_PRIVS	Has been granted to a user's system privileges or roles
DBA_TAB_PRIVS	All the permissions on the database objects
ROLE_ROLE_PRIVS	Shows the user's role has been granted
ROLE_SYS_PRIVS	Show through the user's system privileges granted to roles
ROLE_TAB_PRIVS	Show through the user's object privileges granted to roles
SESSION_PRIVS	Show users now can use all the system privileges
USER_COL_PRIVS	Display the column on the permissions, the user is owner, grantor or the grantor
USER_COL_PRIVS_MADE	Show columns have been granted permissions, the user is owner or grantor
USER_COL_PRIVS_RECD	Show columns have been granted permissions, the user is owner or grantor
USER_ROLE_PRIVS	Show all the roles granted to the user
USER_SYS_PRIVS	Show has been granted to the user all the system privileges
USER_TAB_PRIVS	Display to the user has been granted permission to all objects
USER_TAB_PRIVS_MADE	Show the object to grant permissions to other users, the user is on a main
USER_TAB_PRIVS_RECD	Show the object to grant permissions to other users, the user is given by