User Manager

profile and Users (:: 유저의 profile을 설정한 후 유저 아이디를 관리함 )
Users are assigned only one profile at any given time.
Profile :
 - Control resource consumption
 - Manage account statue and password expiration
Edit Profile ::
  -  General tab  : CPU, Connect time, Idle time, I/O
  -  Password tab : 패스워드를 X 번 틀렸을 경우 T 시간만큼 lock // 일정 기간 후 패스워드 변경 // 일정 패스워드를 기억하여 동일한 데이터로 바꾸지 못하게 하는 history // 암호에 대한 복잡성

oracle user lock / unlock
SQL> alter user user_id account [ unlock | lock ]

Chapter 7. Managing Schema Objects

Naming Database Objects
  • The length of names must be from 1 to 30 bytes, with these exceptions:
    – Names of databases are limited to 8 bytes.
    – Names of database links can be as long as 128 bytes.
  • Nonquoted names cannot be Oracle-reserved words.
  • Nonquoted names must begin with an alphabetic character from your database character set.
  • Quoted names are not recommended.

Understanding Data Integrity
You can use the following integrity constraints to impose restrictions on the input of column values:
• NOT NULL: By default, all columns in a table allow null values. Null means the absence of a value. A NOT NULL constraint requires that a column of a table must contain no null values. For example, you can define a NOT NULL constraint to require that a value be input in the LAST_NAME column for every row of the EMPLOYEES table.
• UNIQUE Key: A UNIQUE key integrity constraint requires that every value in a column or set of columns (key) be unique—that is, no two rows of a table have duplicate values in a specified column or set of columns. For example, a UNIQUE key constraint is defined on the DEPARTMENT_NAME column of the DEPARTMENTS table to disallow rows with duplicate department names. Except for special circumstances, this is enforced with a unique index.
• PRIMARY KEY: Each table in the database can have at most one PRIMARY KEY constraint. The values in the group of one or more columns subject to this constraint constitute the unique identifier of the row. In effect, each row is named by its primary key values.
The Oracle server’s implementation of the PRIMARY KEY integrity constraint guarantees that both the following are true:
  - No two rows of a table have duplicate values in the specified column or set of columns.
  - The primary key columns do not allow nulls. That is, a value must exist for the primary key columns in each row.
Under normal circumstances, the database enforces the PRIMARY KEY constraints by using indexes. The primary key constraint created for the DEPARTMENT_ID olumn in the DEPARTMENTS table is enforced by the implicit creation of:
  - A unique index on that column
  - A NOT NULL constraint for that column
• Referential integrity constraints: Different tables in a relational database can be related by common columns, and the rules that govern the relationship of the columns must be maintained. Referential integrity rules guarantee that these relationships are preserved. 
A referential integrity constraint requires that for each row of a table, the value in the foreign key must match a value in a parent key.
As an example, a foreign key is defined on the DEPARTMENT_ID column of the EMPLOYEES table. It guarantees that every value in this column must match a value in the primary key of the DEPARTMENTS table. Therefore, no erroneous department
numbers can exist in the DEPARTMENT_ID column of the EMPLOYEES table. Another type of referential integrity constraint is called a self-referential integrity constraint. This type of foreign key references a parent key in the same table.
• Check constraints: A CHECK integrity constraint on a column or set of columns requires that a specified condition be true or unknown for every row of the table. If a data manipulation language (DML) statement results in the condition of the CHECK constraint evaluating to false, then the statement is rolled back.

Constraint States
To better deal with situations where data must be temporarily in violation of a constraint, you can designate a constraint to be in various states. An integrity constraint can be enabled (ENABLE) or disabled (DISABLE). If a constraint is enabled, the data is checked
as it is entered or updated in the database. Data that does not conform to the constraint’s rule is prevented from being entered. If a constraint is disabled, then the nonconforming data can be entered into the database. An integrity constraint can be in one of the following states:

• DISABLE NOVALIDATE: New as well as existing data may not conform to the constraint because it is not checked. This is often used when the data is from an already validated source and the table is read-only, so no new data is being entered into the table.
• DISABLE VALIDATE: If a constraint is in this state, then any modification of the constrained columns is not allowed because it would be inconsistent to have validated the existing data and then allow unchecked data to enter the table. This is often used when the existing data must be validated but the data is not going to be modified and the index is not otherwise needed for performance.
• ENABLE NOVALIDATE: New data conforms to the constraint but existing data is in an unknown state. This is frequently used so that existing constraint violations can be corrected, and at the same time, new violations are not allowed to enter the system.
• ENABLE VALIDATE: Both new and existing data conform to the constraint. This is the typical and default state of a constraint.

  • Locks prevent multiple sessions from changing the same data at the same time.
  • They are automatically obtained at the lowest possible level for a given statement.
  • They do not escalate. 
Locking Mechanism
  • High level of data concurrency:
    – Row-level locks for inserts, updates, and deletes
    – No locks required for queries
  • Automatic queue management
  • Locks held until the transaction ends (with the COMMIT or ROLLBACK operation)  // lock 은 해당 row만 하고 있기 때문에 다른 행은 상관없음.

Undo Redo 차이점
Redo는 re + do의 합성어 "다시 하다."
Undo는 un + doㅇ의 합성어 "원상태로 돌리다"

Redo는 오라클에서 기본적으로 복구의 역활을 한다. 오라클 서버에 무슨작업을 하든지 모두 리두에 기록이 된다.
Undo는 작업롤백과 읽기 일관성, 복구를 한다.

redo는 복구를 할때 사용자가 했던 작업을 그대로 다시 하지만
undo는 사용자가 했던 작업을 반대로 한다. 즉 사용자의 작업을 원상태로 돌린다.

[출처] Undo와 Redo의 차이점 |작성자 UNIX

  기록하는 내용
  되돌리기 위한 변화
  재생하기 위한 변화
  롤백, 읽기 일관성, 플래쉬 백
  언두 세그먼트
  리두 로그 파일
  멀티 유저의 읽기 일관성을 보호한다.
  데이터 손실 방지


'STUDY > OCP' 카테고리의 다른 글

OCP license.E09.130704  (1) 2013.07.06
OCP license.E08.130703  (0) 2013.07.06
OCP license.E07.130702  (0) 2013.07.06
OCP license.E06.130701  (0) 2013.07.06
OCP license.E05.130628  (0) 2013.07.06
OCP license.E04.130627  (0) 2013.07.06


티스토리 툴바