|
13. 인덱스 다루기 (Managing Indexes)
[b]Reverse Key Index[/b]
- Key 값을 뒤집어서 sort 한다음 사용. (예, 원래값이 1234라면 --> reverse index는 4321)
- I/O bottleneck 을 해결할 수 있다.
- 사용자들이 같은 테이블을 자주 update 할 때 reverse index를 사용하면 좋다.
* 단점 : = 조건은 빨리 찾을 수 있지만... <, > 연산자를 사용하면 reverse index가
적용되지 않는다. 그래서, 속도의 향상은 어렵다.
[b]Bitmap Index[/b]
- 구조는 0,1로 구성되어 있다.
- 연산속도가 빠르다.
- index segment의 크기가 매우 작다.
- column에 있는 data의 종류가 몇개 안되는 곳에 적용하면 좋다. (예, 성별(남/여)컬럼)
- read-only 나 update가 자주 일어나지 않는 컬럼에 적용하면 좋다.
B-Tree 와 Bitmap Index 의 비교
B-tree Bitmap
Suitable for high-cardinality colums Suitable for low-cardinality columns
Updates on keys relatively inexpensive Update to key columns very expensive
inefficient for queries using or predicates Efficient for queries using or predicates
Useful for OLTP Useful for DSS
[b]Creating Normal B-Tree Index[/b]
CREATE [UNIQUE] INDEX [schema.] index ON [schema.] table
(column [ ASC | DESC ] [, column [ ASC | DESC ] ] ...)
[ TABLESPACE tablespace ]
[ PCTFREE integer ]
[ INITRANS integer ]
[ MAXTRANS integer ]
[ storage-clause ]
[ LOGGING | NOLOGGING ]
[ NOSORT]
- NOSORT : sort 안하고 index를 만든다. table 에 data가 이미 sort 되어 있을 때 사용한다.
- INITRANS 의 default 값은 2이다.
ex) CREATE INDEX scott.emp_name_idx ON scott.employee (name)
PCTFREE 30
STORAGE ( INITIAL 200K NEXT 200K
PCTINCREASE 0 MAXEXTENTS 50)
TABLESPACE indx01 ;
[b]Creating Indexes : Guidelines[/b]
- 데이터 조회와 DML 의 balance를 생각하라.
- tablespace를 따로 만들어라.
- 같은 크기의 extent를 사용하라.
- large index에는 NOLOGGIN을 적용 시켜라.
[b]Creating Reverse Key Indexes[/b]
CREATE [ UNIQUE ] INDEX [schema.]index
ON [schema.]table
(column [ASC | DESC] [,column [ASC | DESC] ]...)
[ TABLESPACE tablespace ]
[ PCTFREE integer ]
[ INITRANS integer ]
[ MAXTRANS integer ]
[ storage-clause ]
[ LOGGING | NOLOGGING ]
REVERSE
- NOSORT 사용 못함.
ex) CREATE UNIQUE INDEX scott.ord_ord_no.idx
ON scott.ord(ord_no) RVERSE
PCTFREE 30
STORAGE (INITIAL 200K NEXT 200K
PCTINCREASE 0 MAXEXTENTS 50)
TABLESPACE indx01 ;
[b]Creating Bitmap Indexes[/b]
CREATE BITMAP INDEX [schema.]index
ON [schema.]table
(column [ASC | DESC ] [, column [ASC | DESC] ]...)
[ TABLESPACE tablespace ]
[ PCTFREE integer ]
[ INITRANS integer ]
[ MAXTRANS integer ]
[ storage-clause ]
[ LOGGING | NOLOGGING ]
[ NOSORT ]
ex) CREATE BITMAP INDEX scott.ord_region_id_idx
ON scott.ord(region_id)
PCTFREE 30
STORAGE (INITIAL 200K NEXT 200K
PCTINCREASE 0 MAXEXTENTS 50 )
TABLESPACE indx01 ;
- CREATE_BEIMAP_AREA_SIZE : bitmap index size 설정 parameter. Default는 8MB
[b]Reorganizing Indexes[/b]
ALTER INDEX [schema.]index
[ storage-clause ]
[ INITRANS integer ]
[ MAXTRANS integer ]
ex) ALTER INDEX scott.emp_name_idx
STORAGE (NEXT 400K MAXEXTENTS 100 ) ;
[b]Allocating and Deallocating Index Space[/b]
- Allocating
ALTER INDEX scott.ord_region_id_idx
ALLOCATE EXTENT (SIZE 200K DATAFILE '/DISK6/indx01.dbf' ) ;
- Deallocating
ALTER INDEX scott.ord_ord_no_idx
DEALLOCATE UNUSED ;
[b]Rebuiling Index[/b]
- index를 재구성하라.
ALTER INDEX scott.ord_region_id_idx
REBUILD
TABLESPACE indx02 ;
- DML이 많이 일어나면 빈 공간이 많이 생기기 때문에 다시 index를 rebuild 해준다.
- DROP 하고 새로 만드는 것보다 속도가 빠름.
[b]Checking Index Validity[/b]
SVRMGR> ANALYZE INDEX scott.ord_region_id_idx
2 VALIDATE STRUCTURE ;
--> INDEX_STATS data dictionary에 정보가 저장된다.
SVRMGR> SELECT blocks, pct_used, distinct_keys
2 lf_rows, del_lf_rows
3 FROM index_stats ;
- PCT_USED : 현재 사용되고 있는 공간의 %
- LF_ROWS : leaf 블럭의 row 수
- DEL_LF_ROWS : leaf 블럭의 delete된 row 수
* lf_rows 와 del_lf_rows를 비교해서 del_lf_rows가 30%를 넘으면 index를 REBUILD 해주는게 좋다.
[b]DROP index[/b]
DROP INDEX scott.dept_dname_idx;
Index 정보를 갖고 있는 Data Dictionary
DBA_INDEXES : owner, index_name, index_type, table_owner, tble_name, uniqueness,
logging, status, tablespace_name
DBA_IND_COLUMNS : index_owner, index_name, table_owner, table_name, column_name,
column_position, column_length
- Checking Indexes and Their Validity
SVRMGR> SELECT index_name, tablespace_name, index_type,
2 uniquenss, status
3 FROM dba_indexes
4 WHERE owner = 'SCOTT' ;
SVRMGR> SELECT o.object_name
2 FROM dba_objects o
3 WHERE owner = 'SCOTT'
4 AND o.object_id IN (SELECT i.obj#
5 FROM ind$ i
6 WHERE BITAND( i.property, 4) = 4 ) ;
- Finding Columns in an Index
SVRMGR> SELECT index_name, table_owner, table_name, column_name
2 FROM dba_ind_columns
3 WHERE index_owner = 'SCOTT'
4 ORDER BY index_name, column_position ;
[b]연습[/b]
CUSTOMERS 테이블의 NAME과 REGION column의 인덱스를 생성하려고 할 때, 각 column에 대한 적절한 인덱스 유형은 무엇입니까?
인덱스를 생성할 때, CUST_NAME_IDX와 CUST_REGION_IDX 각각을 적절한 테이블스페이스에 두도록 하라.
SQL> create index cust_nmae_idx
2 on customers(name)
3 pctfree 30
4 tablespace indx01;
SQL> create bitmap index cust_region_idx
2 on customers(region)
3 tablespace index01;
CUST_REGION_IDX 인덱스를 다른 테이블스페이스로 옮겨라.
SQL> alter index cust_region_idx rebuild tablespace dta02 ;
(a) system 계정을 사용하여 NUMBERS 테이블을 생성하고 데이터를 입력하는 스크립트 cr_numb.sql을 실행 하십시오.
- cr_number.sql
CREATE TABLE system.numbers(
no NUMBER,
odd_even varchar2(1))
TABLESPACE data01
/
BEGIN
FOR i IN 1..10000
LOOP
IF mod(i,2)=1
THEN
INSERT INTO system.numbers
VALUES(i,'O');
ELSE
INSERT INTO system.numbers
VALUES(i,'E');
END IF;
IF mod(i,500)=0
THEN
COMMIT;
END IF;
END LOOP;
END;
/
SQL> @cr_number
SQL> select * from numbers
2 where rownum < 20 ;
(b) 테이블의 각 column 에서 유일한 값의 개수를 알기 위해 NUMBERS 테이블을 질의 하라.
SQL> select count(distinct no0, count(distinct odd_even)
2 from numbers ;
(c) extent 크기를 4K로 동일하게 하여 NUMBERS 테이블의 ODD_EVEN 과 NO 열에서 B-tree 인덱스
NUMB_OE_IDX 와 NUMB_NO_IDX 를 각각 생성하고 인덱스의 전체 크기를 검사하라.
SQL> create index numb_oe_idx on numbers(odd_even)
2 tablespace indx01
3 storage(initial 4k next 4k pctincrease 0) ;
SQL> create index numb_no_idx on numbers(no)
2 tablespace indx01
3 storage(initial 4k next 4k pctincrease 0) ;
SQL> select segment_name, blocks
2 from dba_segments
3 where segment_name like 'NUMB%'
4 and segment_type='INDEX' ;
(d) 다시 extent 크기를 4K 로 동일하게 하여 NUMBERS 테이블의 ODD_EVEN 과 NO 열에서 bitmap 인덱스
NUMB_OE_IDX 와 NUMB_NO_IDX 를 각각 생성하고 인덱스의 전체 크기를 검사하라.
SQL> drop index numb_oe_idx ;
SQL> drop index numb_no_idx ;
SQL> create bitmap index numb_oe_idx on numbers(odd_even)
2 tablespace indx01
3 storage(initial 4k next 4k pctincrease 0) ;
SQL> create bitmap index numb_no_idx on numbers(no)
2 tablespace indx01
3 storage(initial 4k next 4k pctincrease 0) ;
SQL> select segment_name, blocks
2 from dba_segments
3 where segment_name like 'NUMB%'
4 and segment_type='INDEX' ;
15. 데이터 일관성 다루기 (Maintaining Data Integrity)
[b]Deferred Constraint[/b]
- Constraint Check를 지연시킨다.
- commit 할 때 check함.
ex1) ALTER SESSION
SET CONSTRAINT[S] = { IMMEDIATE | DEFERRED | DEFAULT }
ex2) SET CONSTRAINT[S]
{ constraint [, constraint ] ... | ALL }
{ IMMEDIATE | DEFERRED }
[b]Database Triggers[/b]
CREATE TRIGGER scott.emp_conv_ln
BEFORE INSERT OR UPDATE OF last_name ON scott.employees
FOR EACH ROW
BEGIN
:NEW.last_name := INITCAP(:NEW.last_name) ;
END ;
--> scott.employees 테이블의 last_name에 각 row 마다 INSERT나 UPDATE가 일어나기 전에,BEGIN 절을 수행하라.
[types]
- INSERT or UPDATE or DELETE
- BEFORE or AFTER
- ROW or STATEMENT
[b]테이블 생성하면서 constraint 정의.[/b]
CREATE TABLE scott.employees
( empno NUMBER(4) CONSTRAINT emp_pk PRIMARY KEY
DEFERRABLE
USING INDEX
STORAGE(INITIAL 100K NEXT 100K)
TABLESPACE indx01,
last_name VARCHAR2(30) CONSTRAINT emp_ln_nn NOT NULL,
deptno NUMBER(2) )
TABLESPACE data01 ;
- DEFERRABLE : SET CONSTRAINT 명령을 사용해서 transaction이 끝날때까지 constraint check를 지연한다.
[b]DISABLE CONSTRAINT[/b]
ALTER TABLE scott.departments
DISABLE CONSTRAINT dept_pk CASCADE;
- unique index는 drop 되지만, nonunique index는 유지된다.
[b]ENABLE NOVALIDATE CONSTRAINT[/b]
ALTER TABLE scott.departments
ENABLE NOVALIDATE CONSTRAINT dept_pk;
- primary key는 반드시 nonunique index를 사용해야 한다.
- 속도가 빠르다.
- table에 lock이 없어야 한다.
[b]ENABLE VALIDATE CONSTRAINT[/b]
ALTER TABLE scott.employees
ENABLE VALIDATE CONSTRAINT emp_dept_fk ;
- 테이블에 lock 이 걸려있다.
- unique or nonunique index를 사용할 수 있다.
[b]EXCEPTIONS table 사용법[/b]
1) utlexcpt.sql을 실행시킨다.
SVRMGR> @?/rdbms/admin/utlexcpt.sql
Statement processed
SVRMGR> DESC exceptions
2) ALTER TABLE 명령을 EXCEPTIONS와 같이 실행한다.
SVRMGR> ALTER TABLE scott.employees
2 ENABLE VALIDATE CONSTRAINT emp_dept_fk
3 EXCEPTIONS INTO system.exceptions ;
ALTER TABLE scott.employees
*
ORA-02298: cannot enable (SCOTT.EMP_DEPT_FK) - parent keys not found
SVRMGR>
3) EXCEPTIONS table을 적용해서 invalid data를 확인한다.
SVRMGR> SELECT rowid, empno, last_name, deptno
2 FROM scott.employees
3 WHERE ROWID in (SELECT row_id FROM exceptions )
4 FOR UPDATE ;
ROWID E
MPNO LAST_NAME DEPTNO
------------------- ----- ----------------- -------
AAAAeyAADAAAAA1AAA 1003 Pirie 50
1 row seleted.
4) error를 잡는다.
SVRMGR> UPDATE scott.employees
2 SET deptno = 10
3 WHERE rowid = 'AAAAeyAADAAAAA1AAA' ;
1 row processed.
SVRMGR> commit ;
Statement processed.
5) reenable을 한다.
SVRMGR> TRUNCATE TABLE exceptions ;
Statement processed.
SVRMGR> ALTER TABLE scott.employees
2 ENABLE VALIDATE CONSTRAINT emp_dept_fk
3 EXCEPTIONS INTO system.exceptions ;
Statemnet processed.
[b]Trigger enable 만들기[/b]
- 한개의 trigger에...
ALTER TRIGGER scott.emp_conv_ln DISABLE ;
- 그 테이블에 걸려있는 모든 trigger에...
ALTER TABLE scott.employees
ENABLE ALL TRIGGERS ;
[b]CONSTRAINT DROP[/b]
ALTER TABLE scott.employees
DROP CONSTRAINT emp_ln_uk ;
--> constraint가 drop 된다.
DROP TABLE departments
CASCADE CONSTRAINTS ;
--> table이 drop 되고, foreign key 도 제거됨.
[b]TRIGGER 제거하기 [/b]
DROP TRIGGER scott.audit_dept ;
[b]Constraint 정보를 갖고 있는 data dictionary[/b]
- DBA_CONSTRAINTS : owner, constraint_name, constraint_type, table_name, search_condition,
r_owner, r_constraint_name, delete_rule, status, deferrable, deferred,
validated, generated, bad, last_change
- DBA_CONS_COLUMNS : owner, constraint_name, table_name, column_name, position
* Constraints and Their Status
SQL> SELECT constraint_name, constraint_type, deferrable, deferred, validated
2 FROM dba_constraints
3 WHERE owner='SCOTT'
4 AND table_name='EMPLOYEES' ;
* Columns in Constraints
SQL> SELECT c.constraint_name, c.constraint_type, cc.column_name
2 FROM dba_constraints c, dba_cons_columns cc
3 WHERE c.owner='SCOTT'
4 AND c.table_name='EMPLOYEES'
5 AND c.owner=cc.owner
6 AND c.constraint_name=cc.constraint_name
7 ORDER BY cc.position ;
* Finding Primary Key-Foreign Key Relationships
SQL> SELECT c.constraint_name AS "Foreign Key",
2 p.constraint_name AS "Referenced Key",
3 p.constraint_type,
4 p.owner,
5 p.table_name
6 FROM dba_constraints c, dba_constraints p
7 WHERE c.owner='SCOTT'
8 AND c.table_name='EMPLOYEES'
9 AND c.constraint_type='R'
10 AND c.r_owner=p.owner
11 AND c.r_constraint_name=p.constraint_name ;
[b]Trigger에 대한 정보를 갖고 있는 data dictionary[/b]
- DBA_TRIGGERS : owner, trigger_name, trigger_type, triggering_event, table_owner,
table_name, status, description, trigger_body
- DBA_TRIGGER_COLS : trigger_owner, trigger_name, table_owner, table_name, column_name
- DBA_OBJECTS : owner, object_name, object_type, status
SQL> SELECT t.owner, t.trigger_name, t.trigger_type,
2 t.triggering_event, t.status,
3 c.column_name, o.status as "VALIDITY"
4 FROM dba_triggers t, dba_trigger_cols c,
5 dba_objects o
6 WHERE t.owner=c.trigger_name(+)
7 AND t.trigger_name=c.trigger_name(+)
8 AND t.owner=o.owner
9 AND t.trigger_name=o.object_name
10 AND o.object_type='TRIGGER'
11 AND t.table_owner='SCOTT'
12 AND t.table_name='EMPLOYEES' ;
[b]연습 [/b]
1. 스크립트 cr_cons.sql을 점검하십시오. 제약조건을 생성하기 위해서 스크립트를 실행하십시오.
- cr_cons.sql
CONNECT system/manager
ALTER TABLE system.customers
ADD (CONSTRAINT cust_pk PRIMARY KEY(cust_code)
DEFERRABLE INITIALLY IMMEDIATE
USING INDEX TABLESPACE indx01,
CONSTRAINT cust_region_ck
CHECK (region in ('East','West','North','South')))
/
ALTER TABLE system.orders
ADD(CONSTRAINT ord_pk PRIMARY KEY(ord_id)
USING INDEX TABLESPACE indx01,
CONSTRAINT ord_cc_fk FOREIGN KEY(cust_code)
REFERENCES customers(cust_code)
DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT ord_dod_ck CHECK (date_of_dely >= ord_date))
/
ALTER TABLE system.products
ADD CONSTRAINT prod_uk UNIQUE(prod_code)
DEFERRABLE DISABLE
/
SQL> @cr_cons
2. (a) 제약조건의 deferrable 상태와 활성화 상태를 질의하십시오.
SQL> select constraint_name, table_name, constraint_type, deferrable, status
2 from dba_constraints
3 where table_name in ('PRODUCTS','ORDERS','CUSTOMERS') ;
(b) 제약조건을 검증하기 위해서 생성된 인덱스를 확인하십시오.
SQL> select index_name, table_name, uniqueness
2 from dba_indexes
3 where index_name in
4 (select constraint_name
5 from dba_constraints
6 where table_name in ('PRODUCTS','ORDERS','CUSTOMERS') ) ;
3. 다음의 두 레코드를 PRODUCTS 테이블에 삽입하십시오.
--------------------------------------------------
PROD_CODE DESCRIPTION PRICE
--------------------------------------------------
100860 Ace Tennis Racket 36
100860 Ace Tennis Ball 3-Pack 20
--------------------------------------------------
SQL> insert into products values (100860, 'Ace Tennis Racket', 36) ;
SQL> insert into products values 9100860, 'Ace Tennis Ball 3-Pack', 20) ;
4. PRODUCTS 테이블의 유일한 제약조건 (unique constraint)을 활성화(enable) 하십시오. 성공적으로 수행됩니까?
SQL> alter table products enable constraint prod_uk ; ==> error 발생 확인!!!
SQL> delete from products where rownum = 1 ;
SQL> select * from products ;
SQL> alter table products enable constraint prod_uk ;
5. Deferrable Initially Immediate Constraints 확인
SQL> insert into products values (100860, 'Ace Tennis Ball 3-Pack', 2.40) ;
==> Error 발생 확인!!!
SQL> alter session set constraints = deferred ;
SQL> inset into products values (100860, 'Ace Tennis Ball 3-Pack', 2.40) ;
SQL> /
SQL> /
SQL> commit ; ==> error 발생 확인 !!!
15. 클러스터 와 인덱스오거나이즈드 테이블 다루기 (Using clusters and index-Organized Tables(IOT))
[b][color=BLUE]Cluster[/color][/b]
- 자주 select 되는 컬럼을 지정하면 관련된 data들을 Group화 해서 저장한다.
- 데이터를 찾을 때 I/O를 일으키는 block수를 줄일 수 있다.
IOT (Index-Organized Table)
- 테이블 전체를 index화 한다.
- index와 table을 합친 구조.
- ROWID가 없다.
* 단점 : index size가 커져서, block access가 많아진다.
--> 해결책 : block을 잘라서 처리한다. (OVERFLOW)
[b]Cluster type[/b]
- Index Cluster : index를 만들어서 저장하는 block을 지정한다.
- Hash Cluster : hash 함수를 사용해서 위치를 지정한다. 속도가 빠름.
[b]Creating Cluster[/b]
- Create a cluster
CREATE CLUSTER scott.ord_clu
( ord_no NUMBER(3) )
SIZE 200 TABLESPACE data01
STORAGE (INITIAL 5M NEXT 5M PCTINCREASE 0) ;
- Create a cluster index
CREATE INDEX scott.ord_clu_idx
ON CLUSTER scott.ord_clu
TABLESPACE INDX01
STORAGE (INITIAL 1M NEXT 1M PCTINCREASE 0) ;
- Create tables in the cluster
CREATE TABLE scott.ord
(ord_no NUMBER(3)
CONSTRAINT ord_pk PRIMARY KEY,
ord_dt DATE, cust_cd VARCHAR2(3) )
CLUSTER scott.ord_clu(ord_no) ;
CREATE TABLE scott.item
(ord_no NUMBER(3) CONSTRAINT item_ord_fk
REFERENCES scott.ord,
prod VARCHAR2(5), qty NUMBER(3),
CONSTRAINT item_pk PRIMARY KEY(ord_no, prod) )
CLUSTER scott.ord_clu(ord_no) ;
<<< syntax >>>
CREATE CLUSTER [schema.]cluster (column datatype [, column datatype ] ... )
HASHKEYS integer
[ PCTFREE integer ]
[ PCTUSED integer ]
[ INITRANS integer ]
[ MAXTRANS integer ]
[ SIZE integer [K | M] ]
[ storage-clause ]
[ TABLESAPCE tablespace ]
[ INDEX ]
[b]Creating Hash Cluster[/b]
- Create a cluster
CREATE CLUSTER scott.off_clu
(country VARCHAR2(2), postcode VARCHAR2(8)
SIZE 500 HASHKEYS 1000 <-- 0 ~ 1000 까지의 종류로 output을 보여준다.
TABLESPACE data01
STORAGE(INITIAL 5M NEXT 5M PCTINCREASE 0) ;
- Create tables in a cluster
CREATE TABLE scott.office(
office_cd NUMBER(3),
cost_ctr NUMBER(3),
country VARCHAR2(2),
postcode VARCHAR2(8) )
CLUSTER scott.off_clu (country, postcode) ;
<<< syntax >>>
CREATE CLUSTER [schema.]cluster (column datatype [, column datatype ] ... )
HASHKEYS integer
[ HASH IS expression ]
[ PCTFREE integer ]
[ PCTUSED integer ]
[ INITRANS integer ]
[ MAXTRANS integer ]
[ SIZE integer [K | M] ]
[ storage-clause ]
[ TABLESAPCE tablespace ]
[b]ALTER CLUSTER[/b]
- index cluster 에대한 SIZE를 변경한다.
- allocate and deallocate
- storage 와 block 공간을 변경한다.
ALTER CLUSTER scott.ord_clu
SIZE 300 STORAGE (NEXT 2M) ;
* Hash cluster는 SIZE, HASE IS, HASHKEYS 를 alter 할 수 없다.
[b]DROP CLUSTER 하는 방법[/b]
- DROP CLUSTER scott.ord_clu
INCLUDING TABLES ;
- DROP TABLE scott.ord ;
DROP TABLE scott.item ;
DROP CLUSTER scott.ord_clu ;
[b]Cluster 정보를 갖고 있는 data dictionary[/b]
- DBA_CLUSTERS : owner, cluster_name, tablespace_name, key_size, cluster_type, function, hashkeys
- DBA_TAB_COLUMNS : owner, table_name, column_name, data_type, data_length, data_precision, data_scale
- DBA_CLU_COLUMNS : owner, cluster_name, clu_column_name, table_name, tab_column_name
- DBA_CLUSTER_HASH_EXPRESSIONS : owner, cluster_name, hash_expression
* Cluster와 Cluster key 정보 보기
SVRMGR> SELECT c.cluster_name, c.cluster_type, c.key_size,
2 cc.column_name, cc.data_type,
3 DECODE(cc.data_type,'NUMBER',
4 DECODE(cc.data_precision,NULL,NULL,
5 cc.data_precision||','||cc.data_scale),
6 'DATE',NULL,cc.data_length) AS "COLSIZE"
7 FROM dba_clusters c, dba_tab_columns cc
8 WHERE c.owner=cc.owner
9 AND c.cluster_name=cc.table_name
10 AND c.owner='SCOTT' ;
* Matching Cluster Key Columns with Columns in the Tables
SVRMGR> SELECT *
2 FROM dba_clu_columns
3 WHERE owner='SCOTT'
4 ORDER BY cluster_name, table_name ;
* Getting Additional Information for Hash Clusters
SVRMGR> SELECT c.cluster_name, c.hashkeys, c.function, h.hash_expression
2 FROM dba_clusters c, dba_cluster_hash_expressions h
3 WHERE c.owner=h.owner(+)
4 AND c.cluster_name=h.cluster_name(+)
5 AND c.owner='SCOTT'
6 AND c.cluster_type='HASH' ;
[b][color=BLUE]Index-Oraganized Tables[/color][/b]
[b]Creating Index-Organized Tables[/b]
CREATE TABLE scott.sales
( office_cd NUMBER(3),
qtr_end DATE,
revenue NUMBER(10,2),
CONSTRAINT sales_pk
PRIMARY KEY (office_code, qtr_end) )
ORGANIZATION INDEX TABLESPACE data01
PCTTHRESHOLD 20 <-- 한 block에 들어갈 수 있는 row size 지정 (50% 까지 가능하다.)
OVERFLOW TABLESPACE data02 ; <-- threshold 가 20%를 넘었을때 data를 data02에 저장하라.
[b]IOT 정보를 갖고 있는 Data Dictionary[/b]
- DBA_TABLES : owner, table_name, iot_type, iot_name, tablespace_name
- DBA_INDEXES : owner, table_name, index_name, index_type, pct_threshold, include_column
SVRMGR> SELECT t.table_name AS "IOT", o.table_name AS "Overflow",
2 i.index_name AS "Index",
3 o.tablespace_name AS "Overflow TS",
4 i.tablespace_name AS "Index TS", i.pct_threshold
5 FROM dba_tables t, dba_tables o, dba_indexes i
6 WHERE t.owner=o.owner
7 AND t.table_name=o.iot_name
8 AND t.owner=i.owner
9 AND t.table_name=i.table_name
10 AND t.owner='SCOTT' ;
[b] 연습 [/b]
ORDERS 테이블과 ITEMS 테이블을 저장하기 위해서 ORD_CLU 라는 클러스터를 생성하십시오.
SQL> create cluster ord_clu(ord_id number(3))
2 size 200 tablespace data01 ;
SQL> create index ord_clu_idx
2 on cluster ord_clu ;
SQL> create table ord_temp
2 cluster ord_clu(ord_id)
3 as select * from orders ;
SQL> create table items
2 (ord_id number(3),
3 prod_code number(6),
4 qty number(4) )
5 cluster ord_clu(ord_id) ;
SQL> drop table orders ;
SQL> rename ord_temp to orders ;
(a) 객체들이 적절하게 생성되었는지를 검사하기 위해서 데이터 사전을 질의 하십시오.
SQL> select segment_name, segment_type
2 from dba_segments
3 where segment_name in ('ORD_CLU','ORD_CLU_IDX') ;
SQL> select table_name, cluster_name
2 from dba_tables
3 where table_name in ('ORDERS','ITEMS') ;
SQL> select cluster_name, cluster_type, key_size
2 from dba_clusters
3 where owner='SYSTEM'
SQL> select index_name, index_type
2 from dba_indexes
3 where owner='SYSTEM' and table_name='ORD_CLU' ;
(b) ORDERS 와 ITEMS 테이블에 행을 삽입할 수 있는지 테스트 하십시오.
SQL> insert into orders
2 select * from orders2 ;
SQL> @ins_item
- ins_item.sql
INSERT INTO system.items VALUES(600,100861,1);
INSERT INTO system.items VALUES(610,100890,1);
INSERT INTO system.items VALUES(611,100861,1);
INSERT INTO system.items VALUES(612,100860,100);
INSERT INTO system.items VALUES(601,200376,12);
INSERT INTO system.items VALUES(601,100860,1);
INSERT INTO system.items VALUES(602,100870,20);
INSERT INTO system.items VALUES(604,100890,3);
INSERT INTO system.items VALUES(604,100861,2);
INSERT INTO system.items VALUES(604,100860,12);
INSERT INTO system.items VALUES(603,100860,7);
INSERT INTO system.items VALUES(610,100860,1);
INSERT INTO system.items VALUES(610,100870,3);
INSERT INTO system.items VALUES(613,200376,200);
INSERT INTO system.items VALUES(614,100860,444);
INSERT INTO system.items VALUES(614,100870,1000);
INSERT INTO system.items VALUES(612,100861,20);
INSERT INTO system.items VALUES(612,101863,150);
INSERT INTO system.items VALUES(510,103131,20);
특정 회사에서 공급한 제품의 가격을 살펴보는데 사용될 수 있는 테이블을 Index-organized table PRODUCT_COST로 생성하십시오.
PRODUCT_COST table
PROD_CODE NUMBER(6)
VENDOR_CODE VARCHAR2(3)
COST NUMBER(8,2)
SQL> create table product_cost
2 (prod_code number(6),
3 vendor_code varchar2(3),
4 cost number(8,2),
5 constraint pc_pk primary key(prod_code, vendor_code))
6 organization index
7 tablespace data01 ;
SQL> select t.table_name, t.iot_type, i.index_name, i.tablespace_name
2 from dba_tables t, dba_indexes i
3 where t.owner='SYSTEM'
4 and t.table_name='PRODUCT_COST'
5 and t.table_name=i.table_name
6 and t.owner=i.owner ;
Index-organized table PRODUCT_COST에 insert를 수행하십시오.
SQL> insert into product_cost
2 values (100860, 'A51', 1.5) ;
Index-organized table PRODUCT_COST에 인덱스를 생성하려고 시도해 보십시오.
SQL> create index pc_pc_co_idx
2 on product_cost(prod_code,cost)
3 tablespace indx01 ; ===> error 발생 확인!!!
16. 데이터 호출과 재구성 (Loading and Reorganizing Data)
[b]Direct-Load Insert 사용법[/b]
SQL> INSERT /*+APPEND*/ INTO scott.emp
2 NOLOGGING
3 SELECT * FROM scott.old_emp;
- /*+APPEND*/ : HINT 옵션, Direct-Load를 실행하라.
- NOLOGGING : redo log를 남기지 말라.
* Direct-Load Insert는 High-Water-Mark 위쪽으로 data를 모두 load 한 후에 High-Water-Mark를 data 끝에 찍어준다.
[b]Parallel Direct-Load Insert[/b]
- 작업을 여러개의 process로 나눠서 동시에 작업한다.
SQL> ALTER SESSION ENABLE PARALLEL DML;
SQL> INSERT /*+PARALLEL(scott.emp, 2) */ <-- 2는 2개의 process로 처리하라는 뜻이다.
2 INTO scott.emp
3 NOLOGGING
4 SELECT * FROM scott.old_emp ;
[b]SQL*Loader 사용법[/b]
- Text File을 읽어서 DB에 저장해주는 Tool
$ sqlldr scott/tiger \
> control=ulcase6.ctl \
> log=ulcase6.log direct=true
[syntax]
$ sqlldr [keyword=] value [ [ [,] keyword=] value] ...
[b]USERID[/b]
Oracle username and password(If password is not specified, the user will be prompted for the password.)
[b]CONTROL[/b]
Control file name
[b]LOG[/b]
Log file name(name defaults to contolfile.log
[b]BAD[/b]
Bad file that stores all rejected records(name defaults to contolfile.bad)
[b]DATA[/b]
Input data file names
[b]DISCARD[/b]
Optional discard file where records not selected are stored
[b]DISCARDMAX[/b]
Maximum number of discards to allow(The default is to allow all discards. Use this parameter as a safety measure to stop the run if the wrong input files are specified.)
[b]SKIP[/b]
The number of records to skip, primarily used to continue a load that failed(Use this option only if loading to a single table or to skip an identical number of records for all the tables loaded.)
[b]LOAD[/b]
Specifies the number of records to load, after skipping the records specified by SKIP
[b]ERRORS[/b]
Maximum number of bad records to allow
[b]ROWS[/b]
Specifies the number of rows in the array to be built before each insert for conventional loads(For direct path loads this defines the approximate number of rows read from input for each data save. Direct load builds full blocks, and then rejects discards and invalid rows, before a data save.)
[b]BINDSIZE[/b]
Specifies the maximum number of bytes to be used for building an array of rows to be inserted in each database call, for conventional loads(If the ROWS parameter is also specified, SQL*Loader will build as many rows as defined by ROWS, subject to the limit imposed by BINDSIZE.)
[b]DIRECT[/b]
SQL*Loader uses the direct path if this parameter is set to TRUE.
Conventional path, which is the default, is used otherwise.
[b]PARFILE[/b]
Specifies the name of the file that contains all the load parameters(Parameters defined in the command line will override any values defined in the parameter file.)
[b]PARALLEL[/b]
This parameter, which is only valid for direct loads, specifies that multiple parallel direct loads are to be carried out.
[bFILE[/b]
Specifies the file in which the temporary segement for a parallel direct load is to be created
[b]Export 사용법 (exp scott/tiger file=/backup/orabackup.dmp owner=scott)[/b]
$ exp scott/tiger tables=(dept,emp) \
> file=emp.dmp log=exp.log compress=n \
> direct=y recordlength=32768
[syntax]
$ exp [keyword=]{value | (value, value ...)}
[ [ [,] keyword=]{value | (value, value ...)} ] ...
Keyword
Default
Meaning
[b]USERID[/b]
Oracle username and password(If password is not specified, the user will be prompted for the password.)
[b]BUFFER[/b]
OS specific
Size of the buffer that will be used for storing the rows fetched before they are written to the export file
[b]COMPRESS[/b]
Y
A value of Y specifies that on import the initial extent size will be set to a value that is equal to the current size of the segment. A value of N will cause the current extent sizes to be retained. The choice has to be made at export because the information gets written to the export file.
LOB segments are not compressed.
[b]CONSISTENT[/b]
N
A value of Y specifies that the whole export operation be performed in one read-only transaction. Export will attempt to get a read consistent image of all the objects exported A value of N specifies that only table level consistency need to be maintained.
[b]CONSTRAINTS[/b]
Y
A value of Y specifies that constraints are to be exported with the table. A value of N causes constraints not to be exported.
[b]DIRECT[/b]
N
A value of Y specifies that direct path be used for the export. A value of N uses conventional path.
[b]FEEDBACK[/b]
0
This parameter is specified as an integer n to request for a dot(.) to be displayed when n rows are exported
[b]FILE[/b]
expdat.emp
Output file name
[b]FULL[/b]
N
A value of Y specifies full database export.
[b]GRANTS[/b]
Y
A value of Y specifies that all the grants on objects exported must also be preserved on import.
[b]HELP[/b]
N
A value of Y will display a list of the parameters and their meaning. This parameter is not combined with other parameters.
[b]INDEXES[/b]
Y
A value of Y causes indexes to be exported.
[b]LOG[/b]
NULL
The name of the file to store all export messages
[b]OWNER[/b]
The names of the users for user level export
[b]PARFILE[/b]
Specifies the name of the file that contains a list of export parameters
[b]RECORDLENGTH[/b]
OS specific
The size of the output record
[b]ROWS[/b]
Y
A value of Y specifies that data is to be exported.
[b]STATISTICS[/b]
ESTIMATE
Specifies the analyze method to be used on import
[b]TABLES[/b]
schema.table for table mode export
[b]Import 사용법 (imp scott/tiger file=/backup/orabackup.dmp full=y commit=y)[/b]
$ imp scott/tiger tables=(dept, emp) \
> file=emp.dmp log=imp.log ignore=y
Keyword
Default
Meaning
[b]USERID[/b]
Oracle username and password(If password is not specified, user will be prompted for the password.)
[b]BUFFER[/b]
O/S specfic
Size, in bytes, of the buffer through which data rows are transferred
[b]COMMIT[/b]
N
A value of Y specifies that import should commit after each array insert. A value of N will no explicit commits ;
an implicit commit is executed by the Oracle server when the next DDL command is executed by the import, after inserting rows for a table. Specifying COMMIT=Y prevents rollback segments from growing inordinately large.
[b]FEEDBACK[/b]
0
This parameter is specified as an integer n to request for a dot(.) to be displayed when n rows are imported.
[b]FILE[/b]
expdat.dmp
Input file name
[b]FROMUSER[/b]
NULL
A list of users whose objects to import.
[b]FULL[/b]
N
A value of Y specifies full database import.
[b]GRANTS[/b]
Y
A value of Y specifies that all the grants on objects imported must also be imported.
[b]HELP[/b]
N
A value of Y will display a list of the parameters and their meaning. This parameter is not combined with other parameters.
[b]IGNORE[/b]
N
If tye value is set to Y, import overlooks object creation errors when it attempts to create database objects. In this case, import continues without reporting the error. For tables, IGNORE=Y causes rows to be imported into existing tables. No message is given. IGNORE=N causes an error to be reorted, and the table is skipped if it already exists.
Note that only creation errors are ignored; other errors, such as operating system, database, and SQl errors, are not ignored and may cause processing to stop.
[b]INDEXES[/b]
Y
A value Y causes indexes to be imported.
[b]INDEXFILE[/b]
NULL
Specifies a file to receive index-creation commands. When this parameter is specified, index-creation commands for the requested mode are extracted and written to the specified file, rather than used to create indexes in the database. Tables and other database objects are not imported.
The file can then be edited(for example, to change storage parameters) and used as a SQL script to create the indexes.
[b]LOG[/b]
NULL
The name of the file to store all import messages.
[b]PARFILE[/b]
Specifies the name of the file that contains a list of import parameters
[b]PARCORDLENGTH[/b]
O/S specific
The size of the input record. This is only becessary if data was exported on an operating system with a different record size.
[b]ROWS[/b]
Y
A value of Y specifies that data is to be imported.
[b]SHOW[/b]
N
If the value is Y, the contents of the export file are listed to the display and not imported. The SQL statements-contained in the export are displayed in the order in which import will execute them. If SHOW=Y, the only other parameters that can be set are FROMUSER, TOUSER, FULL and TABLES.
[b]TABLES[/b]
NULL
Names of the tables to import
[b]TOUSER[/b]
NULL
A list of usernames to import tables. Only users with IMP_FULL_DATABASE role can use this parameter to import objects into another user's account.
[b] 연습 [/b]
Variable length data 의 loading
$ sqlplus system/manager
SQL> create user load identified by load
2 default tablespace users
3 temporary tablespace temp ;
SQL> grant connect,resource to load ;
SQL> connect load/load
SQL> @ ulcase1.sql
- ulcase1.sql
set termout off
set feedback off
drop table emp;
drop table dept;
create table emp
(empno number(4) not null,
ename char(10),
job char(9),
mgr number(4),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2));
create table dept
(deptno number(2),
dname char(14) ,
loc char(13) ) ;
exit
$ sqlldr load/load ulcase1.ctl
- ulcase1.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(DEPTNO, DNAME, LOC)
BEGINDATA
12,"RESEARCH","SARATOGA"
10,"ACCOUNTING","CLEVELAND"
11,"ART","SALEM"
13,"FINANCE","BOSTON"
21,"SALES","PHILA"
22,"SALES","ROCHESTER"
42,"INT'L","SAN FRAN"
$ sqlplus load/load
SQL> select * from dept ;
Fixed-format records 의 loading
$ sqlldr load/load ulcase2.ctl
- ulcase2.ctl
LOAD DATA
INFILE '$ORACLE_HOME/rdbms/demo/ulcase2.dat'
append
INTO TABLE EMP
( EMPNO POSITION(01:04) INTEGER EXTERNAL,
ENAME POSITION(06:15) CHAR,
JOB POSITION(17:25) CHAR,
MGR POSITION(27:30) INTEGER EXTERNAL,
SAL POSITION(32:39) DECIMAL EXTERNAL,
COMM POSITION(41:48) DECIMAL EXTERNAL,
DEPTNO POSITION(50:51) INTEGER EXTERNAL)
- ulcase2.dat
7782 CLARK MANAGER 7839 2572.50 10
7839 KING PRESIDENT 5500.00 10
7934 MILLER CLERK 7782 920.00 10
7566 JONES MANAGER 7839 3123.75 20
7499 ALLEN SALESMAN 7698 1600.00 300.00 30
7654 MARTIN SALESMAN 7698 1312.50 1400.00 30
7658 CHAN ANALYST 7566 3450.00 20
ITEMS2 테이블을 생성하고, export/import utility를 이용하여 reorganizing 하라.
$ sqlplus system/manager
SQL> create table items2
2 tablespace data01
3 ad select * from items ;
SQL> insert into items2
2 select * from items ;
SQL> !
$ exp system/manager tables=items2
$ exit
SQL> drop table items2 ;
SQL> !
$ imp system/manager tables=items2
$ exit
SQL> select * from items2 ;
17. 사용자 다루기 (Managing Users)
[b]새로운 User 생성[/b]
SQL> CREATE USER peter - user 이름
2 IDENTIFIED BY my1stson - password
3 DEFAULT TABLESPACE data01 - data01을 tablespace로 사용하라.
4 TEMPORARY TABLESPACE temp - temp을 temporary tablespace로 사용하라.
5 QUOTA 15M ON data01 - data01의 tablespace 중에서 15M만 사용하라.
6 ACCOUNT LOCK - lock을 풀어줄때까지는 login이 안된다.
7 PASSWORD EXPIRE; - 처음 접속했을때 password를 바꿀 수 있게 메세지를 뿌려준다.
[b]ALTER USER : user 정보 수정하기[/b]
- password 바꾸기
SQL> ALTER USER peter
2 IDENTIFIED BY hisgrandpa
3 PASSWORD EXPIRE;
- quota 수정하기 <-- 늘리는건 불가능하다.
SQL> ALTER USER peter
2 QUOTA 0 ON data01 ;
[b]DROP USER[/b]
SQL> DROP USER peter CASCADE;
[b]User 정보를 갖고 있는 Data Dictionary[/b]
- DBA_USERS : username, user_id, created, account_status, lock_date, expiry_date, default_tablespace, temporary_tablespace
- DBA_TS_QUOTAS : username, tablespace_name, bytes, max_bytes, blocks, max_blocks
* Tablespace Quotas
SVRMGR> SELECT tablespace_name, blocks, max_blocks, bytes, max_bytes
2 FROM dba_ts_quotas
3 WHERE username = 'SCOTT
TABLESPACE_NAME BLOCKS MAX_BLOCKS BYTES MAX_BYTES
--------------- ------ ---------- ------- ---------
DATA01 10 -1 20480 -1
- blocks, bytes는 현재 사용중인 양을 말한다.
- max_blocks, max_bytes는 QUOTA 받은 양을 말한다.
- -1 로 나온것은 UNLIMITED 권한을 말한다.
* User Account Status
SVRMGR> SELECT username, account_status, temporary_tablespace
2 FROM dba_users;
[b] 연습 [/b]
패스워드가 ALONG 인 사용자 BOB을 생성하십시오. BOB 에 의해 생성된 객체와 임시 세그먼트가 시스템 테이블스페이스에 생성되지 않도록 하십시오.
또한 , BOB 이 로그인하여 DATA01과 INDX01 테이블 스페이스에서 1MB 까지 객체를 생성할 수 있도록 하십시오.
$ sqlplus system/manager
SQL> create user bob identified by along
2 default tablespace data01
3 temporary tablespace temp
4 quota 1m on data01
5 quota 1m on indx01;
SQL> grant create session to bob;
(a) 패스워드가 MARY인 사용자 KAY 를 생성하십시오. KAY에 의해 생성된 객체와 정렬 세그먼트가 시스템 테이블스페이스에 생성되지 않도록 하십시오.
SQL> create user kay identified by mary
2 default tablespace data01
3 temporary tablespace temp;
(b) SYSTEM 스키마에서 ORDERS 테이블을 KAY 의 계정으로 복사하십시오.
SQL> alter user kay quota unlimited on data01 ;
SQL> create table kay.orders
2 as
3 select * from system.orders;
data dictionary 로부터 BOB과 KAY 의 정보를 디스플레이 하십시오.
SQL> select username, default_tablespace, temporary_tablespace
2 from dba_users
3 where username in ('BOB','KAY');
data dictionary 로부터 BOB 이 테이블스페이스에서 사용할 수 있는 공간의 정보를 디스플레이 하십시오.
SQL> select * from dba_ts_quotas where username='BOB';
데이터베이스에서 KAY 의 계정을 제거하십시오.
SQL> drop user kay cascade;
BOB 이 자신의 패스워드를 잊어 버렸습니다. 패스워드를 OLINK 로 지정하고 다음 번에 로그온할 때 BOB 의 패스워드 변경을 요구하십시오.
SQL> alter user bob
2 identified by olink
3 password expire;
$ sqlplus bob/olink ==> new password를 oracle 로 설정하십시오.
18. 프로파일 다루기 (Managing Profiles)
[b]Profile[/b]
- resource 와 password 제한을 설정할 수 있다.
- CREATE USER, ALTER USER로 assign 한다.
- ENABLE, DISABLE 할 수 있다.
- DEFAULT profile을 특별히 만들지 않으면 제한 사항이 전혀 없다.
- session level 과 call level로 제한 할 수 있다.
[ 제한사항 ]
1) resource limit
- CPU time
- I/O operations
- Idle time
- Connect time
- Memory space
Concurrent sessions
2) password limit
- Password aging and expiration
- Password history
- Password complexity verification
- Account locking
[ Profile 만들어서 적용하는 순서 ]
1. Profile을 만든다.
2. User에게 profile을 assign 한다.
3. RESOURCE_LIMIT 을 enable 한다.
[b]CREATE PROFILE[/b]
CREATE PROFILE profile LIMIT
[SESSIONS_PER_USER max_value]
[CPU_PER_SESSION max_value]
[CPU_PER_CALL max_value]
[CONNECT_TIME max_value]
[IDLE_TIME max_value]
[LOGICAL_READS_PER_SESSION max_value]
[LOGICAL_READS_PER_CALL max_value]
[COMPOSITE_LIMIT max_value]
[PRIVATE_SGA max_bytes]
max_value :== { integer | UNLIMITED | DEFAULT }
max_bytes :== { integer[K|M] | UNLIMITED | DEFAULT }
- DEFAULT : DEFAULT profile에 설정된 값을 적용한다.
- DEFALUT profile을 보고 싶으면...
SQL> select * from dba_profiles
2 where profile='DEFAULT'
예) CREATE PROFILE developer_prof LIMIT
SESSIONS_PER_USER 2
CPU_PER_SESSION 10000
IDLE_TIME 60
CONNECT_TIME 480 ;
* Session Level
CPU_PER_SESSION : total cpu time (단위: 1/100초)
SESSIONS_PER_USER : 각 user에게 허용되는 concurent session 수
CONNECT_TIME : connect time (단위: 분)
IDLE_TIME : server process가 몇분동안 작업을 안하고 있으면 session을 종료한다. (단위: 분)
LOGICAL_READS_PER_SESSION : data block 수
PRIVATE_SGA : SGA에 있는 private 공간.
* Call Level
CPU_PER_CALL : call 하는데 걸리는 cpu time (단위: 1/100초)
LOGICAL_READS_PER_CALL : data bolock 수
[b]User에게 Profile assign 하는 방법[/b]
1) CREATE USER
SQL> CREATE USER user3 IDENTIFIED BY user3
2 DEFAULT TABLESPACE data01
3 TEMPORATY TABLESPACE temp
4 QUOTA unlimited ON data01
5 PROFILE developer_prof ;
2) ALTER USER
SQL> ALTER USER scott
2 PROFILE developer_prof ;
* 현재 접속한 session에는 적용되지 않는다.
[b]RESOURCE LIMIT Enable 하는 방법[/b]
1) $ORACLE_HOME/dbs/initSID.ora initial file에 parameter 수정.
RESOURCE_LIMIT=TRUE
--> startup 하면 적용된다.
2) ALTER SYSTEM
SQL> ALTER SYSTEM SET RESOURCE_LIMIT=TRUE ;
--> shutdown 하면 FALSE가 된다.
[b]ALTER PROFILE : profile 수정[/b]
[syntax]
ALTER PROFILE profile LIMIT
[SESSIONS_PER_USER max_value]
[CPU_PER_SESSION max_value]
[CPU_PER_CALL max_value]
[CONNECT_TIME max_value]
[IDLE_TIME max_value]
[LOGICAL_READS_PER_SESSION max_value]
[LOGICAL_READS_PER_CALL max_value]
[COMPOSITE_LIMIT max_value]
[PRIVATE_SGA max_bytes]
SQL> ALTER PROFILE default LIMIT
2 SESSION_PER_USER 5
3 CPU_PER_CALL 3600
4 IDLE_TIME 30 ;
[b]DROP PROFILE[/b]
1) DROP PROFILE developer_prof ;
--> 현재 누군가 사용하고 있다면 drop 되지 않는다.
2) DROP PROFILE developer_prof CASCADE ;
--> developer_prof profile을 사용하고 있는 모든 user에게 DEFAULT profile을 넘겨주고 drop 된다.
* DEFAULT profile은 DROP 되지 않는다.
* session을 종료하기 전까지는 drop이 되어 있지 않는다.
[b]RESOURCE LIMIT profile 정보를 갖고 있는 data dictionary[/b]
DBA_USERS : profile, username
DBA_PROFILES : profile, resource_name, resource_type(=KERNEL), limit
* DBA_PROFILES의 resource_type='KERNEL' 조건을 갖아야 한다.
SVRMGR> SELECT p.profile, p.resource_name, p.limit
2 FROM dba_users u, dba_profiles p
3 WHERE p.profile=u.profile
4 AND username='SCOTT'
5 AND p.resource_type='KERNEL' ;
[b]Password limit[/b]
- Account locking : user가 login을 설정한 값만큼 실패하면 계정에 lock을 건다.
- Password aging and expiry : 설정한 기간이 지나면 password를 바꾸게 만들어 준다.
- Password history
- Password complexity verification
* Password 제한은 RESOURCE_LIMIT와는 무관한다.
* 즉, password profile을 만들면 바로 적용된다.
[b]CREATE PROFILE 에서 password limit 설정 방법[/b]
[syntax]
CREATE PROFILE profile LIMIT
[FAILED_LOGIN_ATTEMPTS max_value]
[PASSWORD_LIFE_TIME max_value]
[ {PASSWORD_REUSE_TIME | PASSWORD_REUSE_MAX} max_value]
[ACCOUNT_LOCK_TIME max_value]
[PASSWORD_GRACE_TIME max_value]
[PASSWORD_VERIFY_FUNCTION {function | NULL | DEFAULT} ]
SQL> CREATE PROFILE grace_5 LIMIT
2 FAILED_LOGIN_ATTEMPTS 3 -- pw 실패를 3번까지만...
3 PASSWORD_LIFE_TIME 30 -- 30일동안만 pw를 사용하게 한다.
4 PASSWORD_REUSE_TIME 30 -- 한번 사용한 pw를 다시 사용하려면 30일 후부터 재사용 가능.
5 PASSWORD_VERIFY_FUNCTION verify_function
6 PASSWORD_GRACE_TIME 5 ; -- life time이 끝나고 5일동안 message를 보여준다.
[b]verify_function[/b]
; 오라클에 기본적으로 들어 있는 password 제한 profile function
[아래의 내용들이 들어있다.]
- 4문자 이상이어야 한다.
- username과 같아선 안된다.
- 한개이상의 alpha, numeric, special 문자로 구성되어야 한다.
- 이전 password보다 최소한 3문자 이상 같아선 안된다.
* utlpwdmg.sql에 이 function script가 들어있다.
[b]Password 정보를 볼수 있는 data dictionary[/b]
DBA_USERS : profile, username, account_status, lock_date, expiry_date
DBA_PROFILES : profile, resource_name, resource_type(=PASSWORD), limit
SVRMGR> SELECT username, password, account_status, lock_date, expiry_date
2 FROM dba_users ;
SVRMGR> SELECT * FROM dba_profiles
2 WHERE resource_type = 'PASSWORD'
3 AND profile = 'DEFAULT' ;
--> default profile에 있는 password limit 정보만 보여준다.
[b] 연습 [/b]
현재 system 에 어떠한 profile이 존재하는지 조회하십시오.
$ sqlplus system/manager
SQL> select distinct profile from dba_profiles ;
각 user 에게 어떤 profile 이 assign 되어 있는지 확인하십시오.
SQL> select username, profile from dba_users ;
profile 을 이용할 수 있도록 환경을 설정하고, profile을 만들어 보십시오.
initSID.ora file에 RESOURCE_LIMIT=TRUE로 설정하고, DB를 재기동한다.
또는, alter system set resource_limit=true ;
SQL> create profile test_profile limit
2 sessions_per_user 2
3 idle_time 1 ;
SQL> alter user bob
2 profile test_profile ;
(a) profile 에서 설정한 대로 resource 를 제한하는지 알아보십시오.
$ sqlplus bob/oracle
$ sqlplus bob/oracle
$ sqlplus bob/oracle ==> error 발생 확인!!!
(b) SQL*Plus 에서 아무런 작업도 하지 않고, 1분이 지난 후에, select 문을 수행하십시오. 어떤 결과가 나타납니까? TEST_PROFILE 프로파일이 할당된 사용자 BOB 에 대해서 다음 사항이 적용되도록 하십시오.
- 두 번 로그인을 시도한 다음에 계정은 LOCK 되어야 합니다.
- 패스워드는 30일이 지나면 만료되어야 합니다.
SQL> alter profile test_profile limit
2 failed_login_attempts 2
3 password_life_time 30
$ sqlplus bob/ttt ==> 틀린 password로 login 시도
$ sqlplus bob/ppp ==> 틀린 password로 login 시도
$ sqlplus bob/oracle ==> 올바른 password로 login을 시도해도, account 가 LOCK 됨을 확인!!!
위문제에서 LOCK 된 account를 UNLOCK 하십시오.
$ sqlplus system/manager
SQL> alter user bob
2 account unlock ;
$ sqlplus bob/oracle ==> 성공적으로 login
test_profile을 삭제하십시오.
$ sqlplus system/manager
SQL> select username, profile from dba_users ;
SQL> drop profile test_profile ;
==> 어떤 error가 발생하는가?
SQL> select username, profile from dba_users ;
19. 권한 다루기 (Managing Privileges)
- Privilege는 SYSTEM privilege와 OBJECT privilege 가 2가지가 있음.
SYSTEM privilege
- 80개정도가 있다.
- 보통 ANY-keyword가 들어 있는 privilege는 system privilege임.
INDEX관련 : CREATE ANY INDEX, ALTER ANY INDEX, DROP ANY INDEX
TABLE관련 : CREATE TABLE, CREATE ANY TABLE, ALTER ANY TABLE, DROP ANY TABLE, SELECT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE
SESSION관련 : CREATE SESSION, ALTER SESSION, RESTRICTED SESSION
TABLESPACE관련 : CREATE TABLESPACE, ALTER TABLESAPCE, DROP TABLESPACE, UNLIMITED TABLESPACE
- SYSTEM_PRIVILEGE_MAP data dictionary로 정보를 볼 수 있다.
Granting System Privileges
SQL> GRANT CREATE SESSION, CREATE TABLE
2 TO user1;
SQL> GRANT CREATE SESSION TO scott
2 WITH ADMIN OPTION;
- PUBLIC : 모든 사용자(user) 에게 권한 주기
- WITH ADMIN OPTION : 남에게 자기가 받은 권한을 줄 수 있다.
- session 중에 있는 사용자(user)에게도 바로 적용된다.
SYSDBA와 SYSOPER 권한
SYSOPER : STARTUP
SHUTDOWN
ALTER DATABASE OPEN | MOUNT
ALTER DATABAE BACKUP CONTROLFILE
ALTER TABLESPACE BEGIN/END BACKUP
RECOVER DATABASE
ALTER DATABASE ARCHIVELOG
RESTRICTED SESSION
SYSDBA : SYSOPER의 권한을 모두 갖고,
CREATE DATABASE
RECOVER DATABASE UNTIL 을 할 수 있다.
Password file을 이용해서 권한주기
1) password file을 만든다.
$ orapwd file=$ORACLE_HOME/dbs/orapwSID password=123456 entries=5
2) $ORACLE_HOME/dbs/initSID.ora 파일을 수정한다.
REMOTE_LOGIN_PASSWORD_FILE=EXCLUSIVE
3) sysoper, sysdba 권한을 준다.
SVRMGR> grant sysdba, sysoper to scott;
4) V$PWFILE_USERS 로 password file의 member들을 확인 할 수 있다.
SVRMGR> select * from v$pwfile_users;
5) 접속하는 방법.
SVRMGR> connect scott/123456 as sysdba
System privilege 에 대한 정보를 갖고 있는 view
DBA_SYS_PRIVS
- grantee
- privilege
- admin_option
SVRMGR> SELECT * FROM dba_sys_privs;
- session privilege 에 대한 정보를 갖고 있는 view
; 현재 접속한 user의 권한을 보여준다.
SESSION_PRIVS
- privilege
SVRMGR> SELECT * FROM session_privs;
System privilege revoke 하기
REVOKE CREATE TABLE FROM user1;
REVOKE CREATE SESSION FROM scott;
* with admin option 으로 권한을 받은 user1가 다른 user2에게 권한을 나눠 줬을때,
revoke ... from user1 을 하더라도 user2의 권한은 살아 있다.
Granting Object Privileges
SQL> GRANT EXECUTE ON dbms_pipe
2 TO public;
SQL> GRANT UPDATE(ename, sal) ON emp
2 TO user1 WITH GRANT OPTION
- WITH GRANT OPTION : 다른 user에게 자신이 받은 권한을 나눠 줄 수 있다.
* 모든 object를 모든 유저에게 권한 주기
SQL> GRANT ALL ON emp TO PUBLIC;
Object Privilege에 대한 테이블, 컬럼 정보를 갖고 있는 view
1) 테이블 정보
DBA_TAB_PRIVS
- grantee <-- 권한 받은 사람
- owner
- table_name
- grantor <-- 권한 준 사람
- privilege
- grantable
SQL> SELECT * FROM dba_tab_privs
2 WHERE GRANTEE='SCOTT';
2) 컬럼 정보
DBA_COL_PRIVS
- grantee
- owner
- table_name
- column_name
- grantor
- privilege
- gratable
SQL> SELECT * FROM dba_col_privs;
Object privilege revoke 하기
SQL> REVOKE execute ON dbms_pipe
2 FROM scott ;
* with grant option 으로 권한을 받은 user1가 다른 user2에게 권한을 나눠 줬을때,
revoke ... from user1 을 하면 user2의 권한도 제거된다.
연습
With admin option 실습
SQL> grant create user to scott with admin option;
SQL> connect scott/tiger
SQL> grant create user to bob;
SQL> connect system/manager
SQL> select * from dba_sys_privs
2 where grantee in ('SCOT','BOB');
SQL> revoke create user from scott;
SQL> select * from dba_sys_privs where grantee in ('SCOTT','BOB');
With grant option 실습
SQL> connect scott/tiger
SQL> grant select on test to bob with grant option ;
SQL> connect bob/oracle
SQL> grant select on scott.test to system ;
SQL> connect scott/tiger
SQL> select * from all_tab_privs
2 where table_name='TEST';
SQL> revoke select on test from bob ;
SQL> select * from all_tab_privs
2 where table_name='TEST';
[/pre]
20. 롤 다루기 (Managing Roles)
[b]Role의 장점[/b]
- grant 횟수를 줄일 수 있음.
- 동적인 privilege 관리가 가능함.
- cascade revoke가 필요 없음.
- performance 를 증가 시킴.
- privilege를 enable, disable 할 수 있음.
- OS를 통해서 grant를 줄 수 있음.
[b]Role을 만들어서 사용하는 절차[/b]
1) role을 만든다.
2) privilege 를 role에 grant 한다.
3) role을 사용자(user)들에게 grant 한다.
[b]CREATE ROLE[/b]
SQL> CREATE ROLE sales_clerk;
SQL> CREATE ROLE hr_clerk
2 IDENTIFIED BY bonus;
--> role을 enable 할 때, password를 bonus로 입력하게 한다.
SQL> CREATE ROLE hr_manager
2 IDENTIFIED EXTERNAL;
--> user의 OS 정보를 보고 확인한다.
- role은 만들어지면 자동 enable 된다.
[b]오라클에 기본적으로 미리 생성된 roles[/b]
CONNECT
RESOURCE
DBA : WITH ADMIN OPTION을 포함한 모든 system 권한을 주낟.
EXP_FULL_DATABASE : DB export 권한을 준다.
IMP_FULL_DATABASE : DB import 권한을 준다.
DELETE_CATALOG_ROLE : data dictionary table의 delete 권한을 준다.
EXECUTE_CATALOG_ROLE : data dictionary package의 execute 권한을 준다.
SELECT_CATALOG_ROLE : data dictionary table의 select 권한을 준다.
[b]ALTER ROLE[/b]
SQL> ALTER ROLE sales_clerk
2 IDENTIFIED BY commission;
--> role에 commission 이라는 password를 걸어 준다.
SQL> ALTER ROLE hr_clerk
2 IDENTIFIED EXTERNALLY;
--> user의 OS 정보를 체크하게 한다.
SQL> ALTER ROLE hr_manager
2 NOT IDENTIFIED;
--> role에 걸려있는 password 없애기
[b]Role assign 하기[/b]
SQL> GRANT sales_clerk TO scott;
--> scott에게 sales_clerk role을 부여한다.
SQL> GRANT hr_clerk TO hr_manager;
--> hr_manager role에 hr_clerk role을 부여한다.
SQL> GRANT hr_manager TO scott
2 WITH ADMIN OPTION;
--> scott 에게 hr_manager role을 부여하면서 다른 사람에게 이 role을 줄 권한도 준다.
[b]DEFAULT ROLE [/b]
- 접속했을때 enable할 role을 지정한다.
- dba 권한이다.
- ALTER USER 할때만 가능하다.
SQL> ALTER USER scott
2 DEFAULT ROLE hr_clerk, sales_clerk;
--> 현재 갖고 있는 role 중에서 hr_clerk, sales_clerk role만 enable 시킨다.
SQL> ALTER USER scott DEFAULT ROLE ALL;
--> 부여된 모든 role을 enable 시킨다.
SQL> ALTER USER scott DEFAULT ROLE ALL
2 EXCEPT hr_clerk;
--> hr_clerk role을 제외한 모든 role을 enable 시킨다.
SQL> ALTER USER scott DEFAULT ROLE NONE;
--> 부여된 모든 role을 disable 시킨다.
현재 접속한 user가 자신의 role을 enable 만들기
SQL> SET ROLE sales_clerk
2 IDENTIFIED BY commission;
--> sales_clerk에 commission이라는 password를 체크하고, enable 시킨다.
SQL> SET ROLE hr_clerk;
--> 부여된 role 중에 hr_clerk만 enable 시킨다.
SQL> SET ROLE ALL EXCEPT sales_clerk;
--> 부여된 role 중에서 sales_clerk 를 제외한 모든 role을 enable 시킨다.
SQL> SET ROLE NONE;
--> 부여된 모든 role을 disable 시킨다.
SQL> SET ROLE ALL;
--> 부여된 모든 role을 enable 시킨다.
[b]ROLL 권한 빼앗기[/b]
SQL> REVOKE sales_clerk FROM scott;
--> scott에게 있는 sales_clerk role을 제거한다.
SQL> REVOKE hr_manager FROM PUBLIC;
--> 모든 user에게서 hr_manager role의 권한을 없앤다.
[syntax]
REVOKE role [,role]...
FROM {user | role | PUBLIC}
[, {user | role | PUBLIC} ]...
[b]ROLE 제거하기[/b]
DROP ROLE hr_manager;
[b]Role 정보를 갖고 있는 data dictionary[/b]
DBA_ROLES : All roles which exist in the database
DBA_ROLE_PRIVS : Roles granted to users and roles
ROLE_ROLE_PRIVS : Roles which are granted to roles
DBA_SYS_PRIVS : System privileges granted to users and roles
ROLE_SYS_PRIVS : System privileges granted to roles
ROLE_TAB_PRIVS : Table privileges granted to roles
SESSION_ROLES : Roles which the user currently has enabled
SVRMGR> SELECT role, password_required
2 FROM dba_roles;
[b] 연습 [/b]
RESOUCE role에 부여되어 있는 시스템 권한을 나열하십시오.
$ sqlplus system/manager
SQL> select * from dba_sys_privs where grantee='RESOURCE';
Role을 생성하여, user에게 default role로 부여해 보십시오.
SQL> connect system/manager
SQL> grant create table, create view to dev;
SQL> connect scott/tiger
SQL> grant select on emp to dev;
SQL> connect system/manager
SQL> grant dev, resource to bob;
SQL> select * from dba_role_privs
2 where grantee='BOB';
SQL> alter user bob default role resoure;
SQL> connect bob/oracle
SQL> select * from session_roles;
SQL> select * from scott.emp; ==> error 발생 확인!!!
SQL> set role all;
SQL> select * from session_roles;
SQL> select * from scott.emp;
21. 감사 (Auditing)
- 누가 무엇을 했는지 trace 하는 방법.
- DataBase Auditing :
언제, 누가, 어디에, 무슨작업을 했는지를 보여준다. 값 변경에 대한 auditing은 할 수 없다.
- Value-based or application auditing : 값 변경에 대한 auditing을 trigger나 application 차원에서 해줄 수 있다.
Value-Based Auditing 예 : trigger 사용 예
CREATE TRIGGER scott.audit_employee
AFTER INSERT OR DELETE OR UPDATE
ON scott.emp
FOR EACH ROW
BEGIN
INSERT INTO scott.audit_employee
VALUES ( :OLD.empno, :OLD.name, ... ,
:NEW.empno, :NEW.name, ... ,
USER, SYSDATE) ;
END ;
Database Auditing을 enable 하는 방법
$ORACLE_HOME/dbs/initSID.ora file에 있는...
AUDIT_TRAIL parameter를 setting 해야한다.
- AUDIT_TRAIL=DB : db에 sys.aud$ table에 저장한다.
- AUDIT_TRAIL=TRUE : AUDIT_TRAIL=DB 와 같다.
- AUDIT_TRAIL=OS : os에 file로 저장한다. (경로는... $ORACLE_HOME/rdbms/audit/ directory에 저장.)
- AUDIT_TRAIL=NONE : audit 설정을 disable한다.
Auditing Option 3가지
1) 문장을 대상으로...
AUDIT user ;
2) 권한을 대상으로...
AUDIT select any table
BY scott BY ACCESS ;
--> scott가 어떤 테이블을 select한 것에 대해 audit를 한다.
access할때마다 기록이 남는다.
3) Object 대상으로...
AUDIT LOCK ON scott.emp
BY ACCESS WHENEVER SUCCESSFUL ;
--> access가 성공했을때 lock 이 걸리면 audit를 한다.
- BY SESSION : session에 한번만 audit한다.
- BY ACCESS : access 할 때마다 audit한다.
- WHENEVER SUCCESSFUL : 성공 했을때만...
- WHENEVER NOT SUCCESSFUL : 실패 했을경우만...
여기서 실패란 실행시에 error난 부분만 audit를 하는 것이다.
* audit는 current session에서는 적용되지 않는다.
Auditing Option에 대한 정보를 갖고 있는 view
ALL_DEF_AUDIT_OPTS : Default audit options
DBA_STMT_AUDIT_OPTS : Statement auditing options
DBA_PRIV_AUDIT_OPTS : Privilege auditing options
DBA_OBJ_AUDIT_OPTS : Schema object auditing options
SVRMGR> SELECT * FROM dba_priv_audit_opts ;
Auditing Disable 하기
NOAUDIT user WHENEVER SUCCESSFUL ;
NOAUDIT create table BY scott ;
NOAUDIT LOCK ON emp ;
Auditing 결과에 대한 정보를 갖고 있는 view
DBA_AUDIT_TRAIL : All audit trail entries
DBA_AUDIT_EXISTS : Records for AUDIT EXISTS/NOT EXISTS
DBA_AUDIT_OBJECT : Records concerning schema objects
DBA_AUDIT_SESSION : All connect and disconnect entries
DBA_AUDIT_STATEMENT : Statement auditing records
SVRMGR> SELECT username, obj_name, action_name, priv_used
2 FROM sys.dba_audit_object
3 WHERE owner='SCOTT'
4 AND obj_name='EMP' ;
연습
audit_trail=true 라는 문장을 initSID.ora file에 등록하여 auditing을 활성화 하십시오.
$ svrmgrl
SVRMGR> shutdown immediate ;
SVRMGR> startup
SVRMGR> show parameters audit (audit_trail=true를 확인한다.)
사용자가 login 하는 것을 감시하십시오.
$ sqlplus system/manager
SQL> audit connect ;
SQL> select * from dba_stmt_audit_opts ;
SQL> connect scott/tiger
SQL> connect scott/test
감시된 사항을 monitor 하십시오.
SQL> connect system/manager
SQL> select username, timestamp, action_name, logoff_time, returncode
2 from dba_audit_session ;
return code 1017 은 login 이 성공하지 못했음을 나타냄.
[/pre]
22. 자연어 지원 이용 (Using National Language Support)
[b]NLS(National Language Support)[/b]
- Instance or DB level
parameter --> $ORACLE_HOME/dbs/initSID.ora
* DB character set 은 DB 생성시 결정된다.
- Client level
OS 환경변수 --> $ORACLE_HOME/.profile
NLS_LANG=americal_america.us7ascii
(NSL_LANG=_.
- language : korean으로 써주면 한국어로 메세지등을 뿌려준다.
- territory : korea로 바꿔주면 날짜, 화폐단위 등을 한국 실정에 맞게 보여준다.
- char_set : ko16ksc5601 로 바꿔주면 object 생성할때 한국어로 만들수 있다.
* server와 client의 character set이 일치해야 된다.
- session level
alter session set nls_date_format = 'YYYY-MM-DD' ;
[b]각종 NLS 환경변수들...[/b]
- NLS_DATE_FORMAT : 날짜 형식을 바꿔준다.
- NLS_DATE_LANGUAGE : 날짜를 보여줄 때, 세팅한 언어로 보여준다.
- NLS_SORT : 소트할 때, 세팅한 언어의 sort 순에 맞게 보여준다.
- NLS_CURRENCY : 화폐단위를 바꾼다.
- NLS_ISO_CURRENCY : ISO 화폐단위를 바꾼다.
- NLS_CALENDAR
- NLS_NUMERIC_CHARACTERS
[b]session 안에서만 NLS setting 하기[/b]
ALTER SESSION SET
NLS_DATE_FORMAT='YYYY-MM-DD' ;
또는,
DBMS_SESSION.SET_NLS('NLS_DATE_FORMAT' , '''YYYY-MM-DD''') ;
두가지 방법은 같은 결과를 보여준다.
* Sorting 설정하기.
ALTER SESSION SET NLS_SORT=GERMAN ;
[b]SQL-Function 안에서 NLS parameter 사용하기[/b]
SQL> SELECT TO_CHAR(hiredate, 'YYYY-MM-DD',
2 'NLS_DATE_LANGUAGE=GERMAN') FROM emp ;
SQL> SELECT ename, TO_CHAR(sal, '9G999D99',
2 'NLS_NUMERIC_CHARACTERS='',.''')
3 FROM emp ;
[b]Character Set에 대한 정보를 갖고 있는 view[/b]
NLS_DATABASE_PARAMETERS
- parameter (NLS_CHARACTERSET, NLS_NCHAR_CHARACTERSET)
- value
SVRMGR> SELECT parameter, value
2 FROM nls_database_parameters
3 WHERE parameter LIKE '%CHARACTERSET%' ;
[b]NLS initial parameter( initSID.ora 파일) 에 대한 정보를 갖고 있는 view[/b]
NLS_INSTANCE_PARAMETERS
- parameter
- value
SVRMGR> SELECT * FROM nls_instance_parameters ;
[b]NLS session parameter에 대한 정보를 갖고 있는 view[/b]
NLS_SESSION_PARAMETERS
- parameter
- value
SVRMGR> SELECT * FROM nls_session_parameters ;
[b]NLS parameter 에 대한 모든 valid 값을 보여주는 view[/b]
V$NLS_VALID_VALUES
- parameter(LANGUAGE, SORT, TERRITORY, CHARACTERSET)
SVRMGR> SELECT * FROM v$nls_valid_values
2 WHERE parameter='LANGUAGE' ;
[b]NLS parameter에 대한 현재 값을 보여주는 view[/b]
V$NLS_PARAMETERS
- parameter
- value
SVRMGR> SELECT * FROM v$nls_parameters ;
[b] 연습 [/b]
데이터베이스의 national character set 을 검사하십시오.
SVRMGR> connect / as sysdba
SVRMGR> select parameter, value
2 from nls_database_parameters
3 where parameter like '%CHARACTERSET' ;
데이터베이스의 character set 에 valid 한 값들에는 어떤 것이 있습니까?
SVRMGR> select value
2 from v$nls_valid_values
3 where parameter='CHARACTERSET' ;
현재 세션의 모든 데이터를 네 자리 수 연도를 사용하여 디스플레이 되도록 하십시오.
SVRMGR> alter session set nls_date_format='DD-MM-YYYY' ;
SVRMGR> select sysdate from dual ;
|
