Chapter 8. Regular expression (정규 표현 식)
















Oracle Database 10g: Administration Workshop l


 - System Global Area (SGA) : Shared by all server and background processes
 - Program Global Area (PGA) : Private to each server and background process. There is one PGA for each process.
 The SGA includer the following data structure:
• Database buffer cache: Caches blocks of data retrieved from the database
    Pinned : Multiple sessions are kept from writing to the same block at the same time. Other sessions wait to access the block. (server process에 기록을 하고 있는것) 
    Clean : 재사용 할 수 있는 후보(Free의 후보)
    Free(사용 가능한 것) or unused(한번도 사용하지 않는 것)
    Dirty
• Redo log buffer: Caches redo information (used for instance recovery) until it can be written to the physical redo log files stored on the disk :: 데이터베이스 변경 기록 저장 (DML 정보와 변경 사항을 기억하고 있음)
• Shared pool: Caches various constructs that can be shared among users
• Large pool: Is an optional area that provides large memory allocations for certain large processes, such as Oracle backup and recovery operations, and I/O server processes
• Java pool: Is used for all session-specific Java code and data within the Java Virtual Machine (JVM)
• Streams pool: Is used by Oracle Streams

Oracle Instance Management (오라클 필수 프로세스... 하나라도 죽어버리면 데이터베이스가 죽어버리게 됨.)
An Oracle database server consists of an Oracle database and an Oracle instance. An Oracle instance is made up of memory structures, known as the System Global Area (SGA), and background processes that handle much of the behind-the-scenes work involved in running an instance. The most common background processes are the following:
. System Monitor (SMON): Performs crash recovery when the instance is started following a failure
. Process Monitor (PMON): Performs process cleanup when a user process fails (특정 접속만 끈긴 세션에 대해 rollback을 시켜주고, lock이 걸린 부분을 관리하는 management)
. Database Writer (DBWn): Writes modified blocks from the database buffer cache to the data files on the disk
. Checkpoint (CKPT): Updates all the data files and control files of the database to indicate the most recent checkpoint (어디까지 입력되어있는지 확인해서 syncronize 등을 맡는 역할)
. LogWriter (LGWR): Writes redo log entries to the disk (redo log 에서 가지고 있는 내용을 write 하는 management)
. Archiver (ARCn): Copies redo log files to the archival storage when a log switch occurs (relo 내용을 수시로 백업해주는 프로세서)
 . show sga : 현재 사용중인 용량 확인


Planning the Database
As a DBA, you must plan:
  • The logical storage structure of the database and its physical implementation:
    – How many disk drives do you have for this?
    – How many data files will you need? (Plan for growth.)
    – How many tablespaces will you use?
    – Which type of information will be stored?
    – Are there any special storage requirements due to type or size?
  • The overall database design
  • A backup strategy for the database

Management Framework
The three components of the Oracle Database 10g management framework are:
  • Database instance
  • Listener
  • Management interface
    – Database Control
    – Management agent (when using Grid Control)


데이터베이스가 꺼지면..
  1. $ lsnrctl start
  2. $ sqlplus / as sysdba
     SQL> startup
  3. $ emctl start dbconsole


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

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
OCP license.E03.130626  (0) 2013.07.06

Comment

ALTER TABLE Statement
Use the ALTER TABLE statement to add, modify, or drop columns:
ALTER TABLE table
ADD         (column datatype [DEFAULT expr]
                [, column datatype]...);

ALTER TABLE table
MODIFY    (column datatype [DEFAULT expr]
                [, column datatype]...);

ALTER TABLE table
DROP      (column);

SET UNUSED Option
• You use the SET UNUSED option to mark one or more columns as unused.
• You use the DROP UNUSED COLUMNS option to remove the columns that are marked as unused.
ALTER TABLE <table_name>
SET UNUSED(<column_name>);
OR
ALTER TABLE <table_name>
SET UNUSED COLUMN <column_name>;

ALTER TABLE <table_name>
DROP UNUSED COLUMNS;


Adding a Constraint Syntax
Use the ALTER TABLE statement to:
• Add or drop a constraint, but not modify its structure
• Enable or disable constraints
• Add a NOT NULL constraint by using the MODIFY clause

ON DELETE CASCADE : FK 가 참조하는 데이터가 지워지면, 자식테이블의 데이터도 같이 지워진다.
ON DELETE SET NULL : FK 가 참조하는 데이터가 지워지면, 자식테이블의 컬럼 데이터가 NULL로 변경된다.

Types of Multitable INSERT Statements
The different types of multitable INSERT statements are:
  • Unconditional INSERT : 조건 없이 여러개의 table에 value를 넣을 때
    exmp) INSERT ALL
INTO sal_history VALUES(EMPID,HIREDATE,SAL)
INTO mgr_history VALUES(EMPID,MGR,SAL)
SELECT employee_id EMPID, hire_date HIREDATE,
salary SAL, manager_id MGR
FROM employees
WHERE employee_id > 200;
 • Conditional ALL INSERT : 조건을 가지고 여러개의 table에 value를 넣을 때 
    exmp ) INSERT ALL
WHEN SAL > 10000 THEN
INTO sal_history VALUES(EMPID,HIREDATE,SAL)
WHEN MGR > 200 THEN
INTO mgr_history VALUES(EMPID,MGR,SAL)
SELECT employee_id EMPID,hire_date HIREDATE,
salary SAL, manager_id MGR
FROM employees
WHERE employee_id > 200;
 • Conditional FIRST INSERT : FIRST는 ALL 과 달리 값을 대입하되, 위에서 값을 대입하면 삭제
    exmp ) INSERT FIRST      
WHEN SAL > 25000 THEN
INTO special_sal VALUES(DEPTID, SAL)
WHEN HIREDATE like ('%00%') THEN
INTO hiredate_history_00 VALUES(DEPTID,HIREDATE)
WHEN HIREDATE like ('%99%') THEN
INTO hiredate_history_99 VALUES(DEPTID, HIREDATE)
ELSE
INTO hiredate_history VALUES(DEPTID, HIREDATE)
SELECT department_id DEPTID, SUM(salary) SAL,
MAX(hire_date) HIREDATE
FROM employees
GROUP BY department_id;
 • Pivoting INSERT : 축을 변경하여 
    exmp ) INSERT ALL
INTO sales_info VALUES (employee_id,week_id,sales_MON)
INTO sales_info VALUES (employee_id,week_id,sales_TUE)
INTO sales_info VALUES (employee_id,week_id,sales_WED)
INTO sales_info VALUES (employee_id,week_id,sales_THUR)
INTO sales_info VALUES (employee_id,week_id, sales_FRI)
SELECT EMPLOYEE_ID, week_id, sales_MON, sales_TUE,
sales_WED, sales_THUR,sales_FRI
FROM sales_source_data;
SQL> select * from SALES_SOURCE_DATA;

EMPLOYEE_ID    WEEK_ID     SALES_MON  SALES_TUE  SALES_WED SALES_THUR  SALES_FRI
---------------    ----------        ----------         ----------        ----------     ----------        ----------
     176                   6                 2000             3000             4000           5000            6000

SQL> SELECT * FROM sales_info;

EMPLOYEE_ID       WEEK         SALES
-----------           ----------      ----------
     176                    6              2000
     176                    6              3000
     176                    6              4000
     176                    6              5000
     176                    6              6000


MERGE StatementMERGE Statement
• Provides the ability to conditionally update or insert data into a database table
• Performs an UPDATE if the row exists, and an INSERT if it is a new row:
 – Avoids separate updates
 – Increases performance and ease of use
 – Is useful in data warehousing applications
   * syntax
MERGE INTO table_name table_alias
  USING (table|view|sub_query) alias
  ON (join condition)
  WHEN MATCHED THEN
     UPDATE SET
     col1 = col_val1,
     col2 = col2_val
  WHEN NOT MATCHED THEN
     INSERT (column_list)
     VALUES (column_values);

Flashback Version Query
SQL>  SELECT versions_starttime, versions_endtime, salary
        FROM employees
        VERSIONS BETWEEN scn minvalue and maxvalue
        where employee_id = 100;

VERSIONS_STARTTIME       VERSIONS_ENDTIME          SALARY
-----------------       -----------------         ----------
28-JUN-13 12.33.49 PM                                   2000
28-JUN-13 12.33.43 PM  28-JUN-13 12.33.49 PM            1000
                       28-JUN-13 12.33.43 PM           24000

ROLLUP Operator
• ROLLUP is an extension to the GROUP BY clause.
• Use the ROLLUP operation to produce cumulative aggregates, such as subtotals
SQL> SELECT department_id, job_id, SUM(salary)
FROM employees
WHERE department_id < 60
GROUP BY (department_id, job_id)
order by 1;

DEPARTMENT_ID   JOB_ID       SUM(SALARY)
-------------            ----------         -----------
        10                AD_ASST            4400
        20                MK_MAN            13000
        20                MK_REP             6000
        30                PU_CLERK          13900
        30                PU_MAN             11000
        40                HR_REP              6500
        50                SH_CLERK          64300
        50                ST_CLERK          55700
        50                ST_MAN             36400

9 rows selected.

SQL>
SQL> SELECT department_id, job_id, SUM(salary)
FROM employees
WHERE department_id < 60
GROUP BY ROLLUP(department_id, job_id);

DEPARTMENT_ID         JOB_ID         SUM(SALARY)
-------------      ----------       -----------
        10           AD_ASST             4400
        10                               4400
        20            MK_MAN            13000
        20            MK_REP             6000
        20                              19000
        30            PU_MAN            11000
        30          PU_CLERK            13900
        30                              24900
        40            HR_REP             6500
        40                               6500
        50            ST_MAN            36400
        50          SH_CLERK            64300
        50          ST_CLERK            55700
        50                             156400
                                        211200

15 rows selected.

CUBE Operator
• CUBE is an extension to the GROUP BY clause.
• You can use the CUBE operator to produce crosstabulation values with a single SELECT statement.



 GROUPING SETS

 - 동일한 퀴리에 대해서 다양한 그룹핑을 정의 가능

 - 개별로 그룹핑하여 UNION ALL한 경우와 동일하나, 성능적인 면에서 뛰어남

 

Scalar Subquery Expressions
• A scalar subquery expression is a subquery that returns exactly one column value from one row.
• Scalar subqueries can be used in:
– Condition and expression part of DECODE and CASE
– All clauses of SELECT except GROUP BY


WITH Clause : 별칭을 만들어서 재활용함으로써 좀더 효율적으로 쿼리를 관리함.
• Using the WITH clause, you can use the same query block in a SELECT statement when it occurs more than once within a complex query.
• The WITH clause retrieves the results of a query block and stores it in the user’s temporary tablespace.
• The WITH clause improves performance.

* Using the WITH clause, write a query to display the department name and total salaries for those departments whose total salary is greater than the average salary across departments.
WITH
dept_costs AS (
SELECT d.department_name, SUM(e.salary) AS dept_total
FROM employees e JOIN departments d
ON e.department_id = d.department_id
GROUP BY d.department_name),
avg_cost AS (
SELECT SUM(dept_total)/COUNT(*) AS dept_avg
FROM dept_costs)
SELECT *
FROM dept_costs
WHERE dept_total >
(SELECT dept_avg
FROM avg_cost)
ORDER BY department_name;








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

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
OCP license.E03.130626  (0) 2013.07.06
OCP license.E02.130625  (0) 2013.07.06

Comment

What Is a View?
You can present logical subsets or combinations of data by creating views of tables. A view is a
logical table based on a table or another view. A view contains no data of its own but is like a
window through which data from tables can be viewed or changed. The tables on which a view
is based are called base tables. The view is stored as a SELECT statement in the data dictionary.



l. Controlling User Access

 Objectives
After completing this lesson, you should be able to do the following:
• Differentiate system privileges from object privileges
• Grant privileges on tables
• View privileges in the data dictionary
• Grant roles
• Distinguish between privileges and roles Objectives

 Privileges
• Database security:
     – System security
     – Data security
• System privileges: Gaining access to the database
• Object privileges: Manipulating the content of the database objects
• Schemas: Collection of objects such as tables, views, and sequences

 System Privileges
• More than 100 privileges are available.
• The database administrator has high-level system
  privileges for tasks such as:
     – Creating new users
     – Removing users
     – Removing tables      
     – Backing up tables

Creating Users
The DBA creates users with the CREATE USER statement.
CREATE USER user
IDENTIFIED BY password;

CREATE USER USER1 IDENTIFIED BY USER1;
User created.

conn system/oracle
CREATE USER USER1 IDENTIFIED BY USER1;           // USER1/USER1 으로 계정 생성
conn user1/user1 (x)
conn system/oracle
grant create session to user1;                     // user1에게 'session' 권한을 부여하여 접속할 수 있도록 함
conn user1/user1
create table test (id number); (x)                
conn system/oracle
grant create table to user1;                          // user1에게 'create' 권한 부여
conn user1/user1
create table test (id number); (x)                // user1이 사용하는 공간이 부족하여 불가능
conn system/oracle
alter user user1 quota 10m on users;           // user1에게 10MB의 공간 할당

 User System Privileges
• After a user is created, the DBA can grant specific system privileges to that user.
GRANT privilege [, privilege...]
TO user [, user| role, PUBLIC...];
• An application developer, for example, may have the following system privileges:
     – CREATE SESSION
     – CREATE TABLE
     – CREATE SEQUENCE
     – CREATE VIEW
     – CREATE PROCEDURE

 What Is a Role?



 Object Privileages
Object
Privilege
TableViewSequenceProcedure
ALTERV
V
DELETEVV

EXECUTE


V
INDEXV


INSERTVV

REFERENCESV


SELECTVVV
UPDATEVV



Revoking Object Privileges
• You use the REVOKE statement to revoke privileges granted to other users.
• Privileges granted to others through the WITH GRANT OPTION clause are also revoked
REVOKE {privilege [, privilege...]|ALL}
ON object
FROM {user[, user...]|role|PUBLIC}
[CASCADE CONSTRAINTS];
[CASCADE CONSTRAINTS] :: 다른 table로 foreign key로 쓰고 있을 때 끊어버리고 권한 회수..

user1의 lock 권한 풀고 패스워드 재설정
alter user user1 identified by user1 account unlock;

ROLE_TAB_PRIVS
USER_ROLE_PRIV
USER_TAB_PRIVS_MADE
USER_TAB_PRIVS_RECD
USER_COL_PRIVS_MADE
USER_SYS_PRIVS


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

OCP license.E06.130701  (0) 2013.07.06
OCP license.E05.130628  (0) 2013.07.06
OCP license.E04.130627  (0) 2013.07.06
OCP license.E03.130626  (0) 2013.07.06
OCP license.E02.130625  (0) 2013.07.06
OCP license.E01.130624  (0) 2013.07.06

Comment



티스토리 툴바