퐁숑퐁숑

우선 MERGE 란 뜻은 무엇인가? 충돌나지 않게 합친다는 개념이다.   SVN에서 MERGE는 소스들의 충돌을 방지하고 적절하게  누락되지 않게 통합하기 위해 사용한다.  오라클에서 MERGE 또한 같은 개념이다. TABLE에 존재하는 데이터는 그대로 변경만 하고 없는 데이터는 삽입을 하여 적절하게 통합하기 위한 예약어이다. 즉,

MERGE INTO 구문은 대상 테이블 해당 KEY에 맞는 데이터가 이미 존재하면 UPDATE!!,존재하지 않으면 INSERT를 하여 테이블 ROW가 충돌나지 않으며 데이터를 UPDATE,INSERT 등의 작업을 한번에 해줄 수 있다. 

#오라클 MERGE INTO 문법#

MERGE INTO [1. 테이블 명 혹은 VIEW명] - Update또는 Insert할 테이블 명 혹은 뷰

USING [2. 조회서브쿼리]      --(만약 INTO절의 동일 테이블이라면  dual 사용)

ON [1과2의 조인 조건]  - 조인 조건의 KEY와 일치여부[UPDATE/INSERT 조건은 바로 ON절에 의해 결정]

WHEN MATCHED THEN   -일치되는 경우 UPDATE

UPDATE SET -- ※조인조건(on)절에 사용한 컬럼은 UPDATE가 불가하다!!

[컬럼1] = [값1],

[컬럼2] = [값2]

...

WHEN NOT MATCHED THEN -일치 안 되는 경우 INSERT

INSERT(컬럼1, 컬럼2...)

VALUES(값1, 값2...)

※오라클 9i 버전 이상부터 사용이 가능하다.

※조건절에 사용한 컬럼은 UPDATE가 불가하다!!

그리고 놀라운 사실!! UPDATE 구문만 가능한게 아니라 상황에 따라서는 DELETE 구문도 가능하다! 잘 쓸 경우는 없지만. 존재하는 ROW는 삭제하고 존재하지 않는 ROW는 삽입해야하는 경우라든지 다양하게 응용 하여 한번에 삽입 삭제 변경 처리가 가능하다.

오라클에서 MERGE  INTO 구문은 자주 사용하는 쿼리 구문이니 익혀두고  이용하면 좋을 듯 하다. 단, 9i 이상 오라클에서 사용 가능하다고 했지만 9i에서의 Merge 구문은 불완전 (DELETE구문도 10g 부터 사용가능 )하다. 또한 구문상 제약사항이 많기때문에 ON 구문에서 조건식을 잘못 넣으면 작동이 안된다; 10g 이상부터 구문을 자유롭게 이용할 수있다. 또한 Trigger 발생이 안된다는 것도 주의해야 하겠다. 그리고 당연한 애기겠지만 단순 INSERT 구문이나 UPDATE구문을 써도 문제가 안되는데 궂이 MERGE INTO를 남발? 하는것도 성능이 떨어지기 때문에(100~200만건 이상 TABLE에서는 느려지는듯 ㅜㅜ) 꼭 필요할 때 잘 쓰도록 하자^^

DBMS_JOB이란 오라클에서 주기적으로 수행되는 백업작업이나, 쿼리나 프로시져등의 JOB을 시간단위나 일 단위나 월 단위 등 주기적인 예약 작업으로 등록하여 동작할 수 있도록 하는 스케줄러이다.UNIX에서 CRONTAB을 돌리 듯이 DB에서 스케쥴러를 등록하여 일정 주기별로 원하는 작업을 할 수 있다는 점에서 유사한데 차이점은 CRON JOB은 OS가 직접 관리하고 실행하는 반면 DBMS_JOB에 등록된 JOB은 오라클이 관리한다.

[출처] [Oracle]오라클 잡 스케줄러 생성 - Job Scheduler (DBMS_JOB 패키지)|작성자 티시포스

위의 출처에 잘 정리되어져 있어서 블로그에 정리해 담아보았다.

* DBMS_JOB 패키지 내 프로시저 구성은 다음과 같다.

    1. SUBMIT : DB에 새로운 JOB을 추가하는 프로시저

    2. REMOVE : DB에 추가된 JOB을 삭제하는 프로시저 

    3. CHANGE : DB에 저장되어 있는 JOB의 field들을 변경하는 프로시저

    4. WHAT : JOB이 수행하는 작업을 변경하는 프로시저

    5. NEXT_DATE : JOB이 Schedule되어 Timer에 의해 자동으로 실행될 때를 변경하는 프로시저

    6. INTERVAL : JOB 실행 주기 파라미터를 변경하는프로시저

    7. BROKEN  : DB에 저장되어 있는 JOB의 상태를 정상 or Broken 상태로 설정하는 프로시저

    8.  RUN : JOB을 현재 시점에서 즉시 수행시키는 프로시저

1. SUBMIT

DB에 새로운 JOB을 추가하는 프로시저

오라클 기본 셋팅 확인

 여기에 'JOB_QUEUE_PROCESSES'의 값이 '0' 이면 스케줄러가 작동하지 않는다. (기본셋팅)

아래와 같이 변경

변경이 되었으면 JOB SCHEDULER를 생성한다.

DBMS_JOB 패키지 내 프로시저 이용

* job : 실행할 job number

* what - 실행할 PL/SQL 프로시저(procedure) 명 혹은 psm 문장의 sequence

* next_date - job을 언제 처음 시작할 것인지 지정한다. date type으로 evaluate되는 문자열 입력(SYSDATE)

* interval - job을 수행한 후, 다음 실행시간까지의 시간을 지정한다. 위 셋팅은 매일 오전 3시 마다 실행한다.

* no_parse - true 이면 submit시에 job을 parsing하지 않는다.

마지막으로 로그(Job Number)를 찍어줌으로써 스케줄러가 정상적으로 돌아가는지 확인할 수 있다.

* job Interval 설정

SYSDATE+7 : 7일에 한번 씩 job 수행

SYSDATE+1/24 : 1시간에 한번 씩 job 수행

SYSDATE+30/ : 30초에 한번 씩 job 수행(24: 시간 당, 1440(24x60):분 당, 86400(24x60x60):초 당 )

TRUNC(SYSDATE, 'MI')+8/24 : 최초 job 수행시간이 12:29분 일 경우 매시 12:29분에 job 수행

TRUNC(SYSDATE+1) : 매일 밤 12시에 job 수행

TRUNC(SYSDATE+1)+3/24 : 매일 오전 3시 job 수행

NEXT_DAY(TRUNC(SYSDATE),'MONDAY')+15/25 : 매주 월요일 오후 3시 정각에 job 수행

TRUNC(LAST_DAY(SYSDATE))+1 : 매월 1일 밤 12시에 job 수행

TRUNC(LAST_DAY(SYSDATE))+1+8/24+30/1440 : 매월 1일 오전 8시 30분

* job queue 정보 VIEWING

SELECT * FROM DBA_JOBS

SELECT * FROM USER_JOBS

SELECT * FROM ALL_JOBS

2. REMOVE

DB에 추가된 job을 삭제하는 프로시저

* job : 삭제할 job number

3. CHANGE

DB에 저장되어 있는 job의 field들을 변경하는 프로시저

* job : 실행할 job number

* what : 실행할 PL/SQL procedure 혹은 psm 문장의 sequence

* next_date : job을 다음 수행할 시간

* interval : job을 수행 후 nex_date를 update하기 위한 expression. date type으로 evaluate되는 문자열

4. WHAT

job이 수행하는 작업을 변경하는 프로시저

* job : 실행할 job number

* what : 실행할 PL/SQL procedure 혹은 psm 문장의 sequence

5. NEXT_DATE

job이 schedule되어 Tibero에 의해 자동으로 실행될 때를 변경하는 프로시저

* job : 실행할 job number

* next_date : job이 schedule되어 실행될 시간

6. INTERVAL

job 실행주기 파라미터를 변경하는 프로시저

* job : 실행할 job number

* interval : job을 수행 후 next_date를 update하기 위한 expression. date type으로 evaluate되는 문자열

7. BROKEN

DB에 저장되어 있는 job의 상태를 정상 or Broken 상태로 설정하는 프로시저

* job : 실행할 job number

* broken : job이 broken 된 경우 true, 정상 상태인 경우 false

8. RUN

job을 현재 session에서 즉시 수행시키는 프로시저.

job이 broken되어 있어도 실행하고, 실행에 성공한 경우 job을 정상 상태로 변경한다.

* job : 실행할 job number

Index의 정의를 보면

1) 조회속도를 향상시키기 위한 데이터베이스 검색 기술

2)색인이라는 뜻으로 해당 테이블의 조회결과를 빠르게 하기 위해 사용.

즉 인덱스가 필요한 이유는 인덱스를 생성해 줌으로써 조회 속도를 빠르게 할 수 있다.

INDEX를 테이블의 특정 컬럼에 한개이상 주게 되면 INDEX TABLE이 따로 만들어 지고,

인텍스 컬럼의 로우값과 rowid 값이 저장되며 로우값은 정렬된 트리 구조로 저장시켜 두었다가

검색시 좀더 빠르게 해당 데이타를 찾는데 도움을 준다.

*테이블을 생성하고 컬럼을 만든 후 데이타를 삽입하면 하나의 ROW가 생성되며 이 ROW는 절대적인 주소를 가지게 되는데 이를 ROWID라고 한다.

하지만 DML 명령을 사용 할때는 원본 TABLE은 물론 INDEX TABLE에도 데이타를 갱신시켜 주어야 하기 때문에 UPDATE, INSERT, DELETE (DML) 명령을 쓸때 속도가 느려진다는 단점이 있다. 

그렇기 때문에 무조건 인덱스를 생성한다고 좋은 것 많은 아니고 꼭 필요할 때만 분별하여 생성해 주어야 하는게 KEY POINT라 할 수 있겠다. 그럼 언제 인덱스(INDEX)를 생성해 주면 좋을까?

우선 데이터가 많이 쌓일거라고 예상되는 경우 혹은 많이 쌓여 있어 현재 화면에서 조회 속도가 너무 느릴때 인덱스 생성을 한다. 그리고 조회결과가 전체 데이터수의 3~5% 미만일 경우에는 인덱스 스캔이 효율적이고 적은 COST로 빠르게 데이터를 찾아낼 수 있다.(전체범위 3~5% 이상 되면 인덱스 스캔보다 풀스캔이 훨씬 유리)

쿼리 플랜을 떠보면 다음과 같은 단어로 확인 할 수 있다

인덱스 스캔 = Index Scan

풀 스캔 = Full Scan

반대로 INDEX생성이 불필요한 경우는?

1) 데이터가 적은(수천건 미만) 경우에는 인덱스를 설정하지 않는게 오히려 성능이 좋다.

2) 조회 보다 삽입, 수정, 삭제 처리가 많은 테이블

3) 조회결과가 전체행의 15% 이상 읽어들일 것으로 예상될때

INDEX 생성 문법은 간단하다

1) 단일 인덱스 지정

CREATE INDEX 인덱스 명 ON table_name (컬럼명)

2) 다중 인덱스(복합 인덱스) 지정

CREATE INDEX 인덱스 명 ON table_name (컬럼명1,컬럼명2,컬럼명3)

※ 복합 인덱스로 지정해준 테이블에서 복합 인덱스를 타게 하려면 복합 인덱스로 준 컬럼

을 조회쿼리에서 모두 조회조건에 사용해야 인덱스를 탈 확률이 높아진다.

예를 들면 다음과 같다

create index index_make1_dx on account(id);

create index index_make2_dx on account(name, date, dept_name);

※ 인덱스는 NOT NULL 값에 대해서만 생성되기 때문에 null 여부를 체크하는 비교문은 인덱스를 사용하지 못함.

INDEX 가능 컬럼

인덱스는 모든 컬럼에 적용가능하다.

그런데 오라클은 가공시킨 컬럼에도 적용가능하다. 아래 참고

CREATE INDEX IDX_NAME ON TABLE_NAME(ROUND(PRICE1-PRICE2));

ROUND(PRICE1-PRICE2) 는 컬럼은 아니지만 컬럼을 가공해서 만든 것이다.

이런 가공컬럼은 다음과 같은 SQL 쿼리로 인덱스를 탈수 있다.

SELECT * FROM TABLE_NAME WHERE ROUND(PRICE1-PRICE2) > 0

※ 인덱스 줄때의 가공컬럼과 같아야 한다.

SQL 쿼리의 INDEX SCAN 유무 체크 방법

1. 상용 DB 관리도구를 이용하는 방법

PL/SQL Developer, Toad 같은 도구에서 SQL문을 작성하고 실행하면 Explain plan 에서 확인 가능하다.

INDEX를 사용해야 할 컬럼?

1) where절이나 조인 조건에서 자주 사용되는 열에 생성

2) 열은 광범위한 값을 포함 할 때

3) 열은 많은수의 null값을 포함 할 때

4) 조회결과가 전체행의 2-4% 보다 적게 읽어들일 것으로 예상될 때

--테이블이 클때 적은 양의 로우를 검색할때 인덱스를 준다다. 적은 양을 검색하는데 테이블을 전체 풀스캔하면 시간이 오래 걸려서 꼭 index를 줘야 한다다.

INDEX를 사용하지 말아야할 컬럼?

1) 테이블에 데이타가 작은 경우

2) where절에 자주 사용되지 않는 열은 사용되지 않는다.

3) 조회결과가 전체행의 2-4% 이상을 읽어들일것으로 예상될때

4) 테이블이 자주 갱신될때

INDEX 생성시 고려사항해야 할 사항들은 무엇일까?

인덱스가 적용된 컬럼이 조건식에서 인덱스를 탈수있게 하려면 해당컬럼을 가공하지않거나 연산을 하지 않은 상태에서 비교해야 인덱스를 탄다.

예를들어 연락처컬럼의경우(010-1234-5678) 010 만 따로 문자열을 잘라(가공) 조건검색하면 인덱스를 타지 않는다.

왜냐하면 인덱스 컬럼에 변형이 일어나면 상대값과 비교되기 전에 먼저 가공이 된 후에 비교된다.하지만 인덱스는 가공되기 전의 값으로 생성되어 있기 때문에 당연히 인덱스를 사용할 수 없게 된다. 여기에서 외부적(External) 변형이란 사용자가 인덱스를 가진 컬럼을 어떤 SQL함수나 사용자 지정함수(User Defined Stored Function), 연산, 결합(||) 등으로 가공을 시킨 후에 발생되는 것이며 이러한 경우는 인덱스를 탈수 없어 변형이 일어나지 않도록 제대로 기술해야 한다.

그렇기 때문에 010과 1234과 5678를 각각의 컬럼으로 만들어 저장한 후 각각의 컬럼에 인덱스를 주면 아무런 가공없이 조건 검색이 가능하므로 인덱스를 탈수 있다.

테이블 컬럼에 인덱스가 있따면 테이블 컬럼을 변경하는것보다 비교값을 변경하여

비교해주는데 좋다. 왜냐면 그래야 인덱스를 타기 때문이다.

WHERE to_char(joindate, 'yyyymmdd') = '20150131'

WHERE joindate = TO_DATE('20150131','yyyymmdd')

인덱스를 타지 않는 CASE

SELECT * FROM ACCOUNT WHERE A_DAY+1>2;

SELECT * FROM ACCOUNT WHERE SUBSTR(A_STRDAY,1,1)='월';

SELECT * FROM EMP WHERE EMP_ID = NVL(EMP_ID,'10');

아래는 인덱스를 타는 CASE

SELECT * FROM ACCOUNT WHERE A_STRDAY='월요일';

SELECT * FROM ACCOUNT WHERE A_DAY>2;

SELECT * FROM EMP WHERE EMP_ID = NVL('10','20');

SELECT * FROM ACCOUNT WHERE A_STRDAY like '월요일%';

※ 첫번째 쿼리부터 인덱스효과가 크게 나타나는 순서

INDEX 타는 경우와 안타는 경우

안타는 경우

1. SELECT * FROM emp WHERE empno <> '1010035';

오라클에서 exists를 이용하여 타게 할수있다?

SELECT * FROM emp WHERE not exists

(select empno FROM emp WHERE empno = '1010035' and a.empno = b.empno);

INDEX 삭제 방법은 간단하다.

DROP INDEX INDEX_NAME;

※ TABLE이 삭제되면 INDEX도 삭제된다.

※ 인덱스의 소유자와 DROP ANY INDEX권한을 가진 사람만 인덱스 삭제가 가능합니다.

?이상으로 인덱스에 대한 정리를 마치고자 한다. 나는 위에 정리된 인덱스 개념처럼 조회되는 데이터 양은 3~5%밖에 안되며 엄청 느린.. 그리고  Explain plan 에서 확인 했을때 FULL SCAN을 타고 있는 상황에서 해당 컬럼에 INDEX를 생성해 주어 4분 이상 돌아가던 쿼리 속도를 4초 이내로 줄여 개선 할 수 있었다. 뿌듯한 성과였다. 이제 단순 개발 뿐만이 아니라 시스템의 성능과 품질을 고려할 수 있는 개발자가 되기위한  한걸음 도약이였다.

OCP라는 자격증을 취득 후 그다음 단계는 OCM이라는 자격증을 취득할 수 있다. 2010년도 그당시만 해도 OCM 자격증 취득자가 별로 없었다. 덤프를 외워서 취득할 수 있는 OCP와 달리 OCM은 실습도 있고 상당히 까다롭기 때문에 그만큼 자격증 취득 여부에 따라 회사에 들어갈때 그만큼 가산점을 얻을 수 있었다. OCM을  취득하기 위해 가장 첫번째 실습 시나리오가 바로 DB 수동 설치이다. 오늘은 DB 수동 설치 후 EM을 추가하는 것 까지 정리해 보고자 한다.

[CONTENTS]

1. 수동 DB 생성 (OCM 0 scenario)
2. DBMS_SPACE_ADMIN 을 이용한 Dictionary → Local migration
3. Enterprise Manager 추가

--수동 DB 설치 (SID-testdb)

1. Nomount 수행을 위한 구성

Parameter file (수동으로 생성해야 하므로 pfile 생성.)

$] cd $ORACLE_HOME/dbs

$] vi inittestdb.ora

-다음과 같이 파라메터 값을 입력한다.

#testdb.__db_cache_size=83886080
#testdb.__java_pool_size=4194304
#testdb.__large_pool_size=4194304
#testdb.__shared_pool_size=67108864
#testdb.__streams_pool_size=0
#*.background_dump_dest='/u01/app/oracle/admin/testdb/bdump'
#*.compatible='10.2.0.1.0'
#*.core_dump_dest='/u01/app/oracle/admin/testdb/cdump'
#*.db_domain=''
#*.db_recovery_file_dest='/u01/oradata/flash_recovery_area'
#*.db_recovery_file_dest_size=2147483648
#*.job_queue_processes=10
#*.nls_language='KOREAN'
#*.nls_territory='KOREA'
#*.open_cursors=300
#*.processes=150
#*.remote_login_passwordfile='EXCLUSIVE'
#*.user_dump_dest='/u01/app/oracle/admin/testdb/udump'

--필수 파라메터 값

*.db_name='testdb'

*.db_block_size=8192

*.db_file_multiblock_read_count=16

*.control_files='/u01/oradata/testdb/control01.ctl','/u01/oradata/testdb/control02.ctl'

*.pga_aggregate_target=16777216

*.sga_target=167772160

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

> password file 생성

$ orapwd file = orapwtestdb password=oracle entries=5

>SID 변경

$ export ORACLE_SID=testdb

>필수 경로 사전 생성

ocp@orcl : /home/oracle> cd /u01/oradata/
ocp@orcl : /u01/oradata> mkdir testdb
ocp@orcl : /home/oracle> cd /u01/app/oracle/admin/
ocp@orcl : /u01/app/oracle/admin/> mkdir testdb
ocp@orcl : /u01/app/oracle/admin/> cd testdb
ocp@orcl /u01/app/oracle/admin/testdb> mkdir bdump udump cdump pfile adump

> $ sqlplus sys/oracle as sysdba

SQL> startup nomount

2. create database 구문 작성

$] vi credb.sql

다음과 같이 작성을 한다.

CREATE DATABASE testdb

LOGFILE

GROUP 1 ('/u01/oradata/testdb/redo01.dbf') SIZE 10M reuse,

GROUP 2 ('/u01/oradata/testdb/redo02.dbf') SIZE 10M reuse,

GROUP 3 ('/u01/oradata/testdb/redo03.dbf') SIZE 10M reuse

MAXLOGFILES 5

MAXLOGMEMBERS 5

MAXLOGHISTORY 1

MAXDATAFILES 100

MAXINSTANCES 1

DATAFILE '/u01/oradata/testdb/system01.dbf' SIZE 600M reuse

AUTOEXTEND on NEXT 10M MAXSIZE UNLIMITED

UNDO TABLESPACE undotbs1

DATAFILE '/u01/oradata/testdb/undotbs1.dbf' SIZE 100M reuse

AUTOEXTEND on NEXT 10M MAXSIZE 1000M

sysaux DATAFILE '/u01/oradata/testdb/sysaux01.dbf' SIZE 600M reuse

AUTOEXTEND on NEXT 10M MAXSIZE UNLIMITED

DEFAULT TEMPORARY TABLESPACE temp

TEMPFILE '/u01/oradata/testdb/temp01.dbf' SIZE 50M reuse

CHARACTER SET KO16MSWIN949

;

$ sqlplus sys/oracle as sysdba

SQL> @credb.sql -- 4분정도 소요

> database 상태 확인

SQL> select instance_name,status from v$instance;

> 유저 설정

SQL> alter user sys identified by oracle

SQL> alter user system identified by oracle

3. DATABASE INSTALL

$] vi dbinstall.sql

다음과 같이 입력한다

@@$ORACLE_HOME/rdbms/admin/catalog.sql

@@$ORACLE_HOME/rdbms/admin/catproc.sql

conn system/oracle

@@$ORACLE_HOME/sqlplus/admin/pupbld.sql

$] sqlplus sys/oracle as sysdba

SQL>@dbinstall.sql

--10분정도 소요됨

4. DBMS_SPACE_ADMIN을 이용한 Dictionary ->Local migration

SYS> select tablespace_name,extent_management form dba_tablespaces;

TABLESPACE_NAME EXTENT_MAN
------------------------------ ----------
SYSTEM DICTIONARY
UNDOTBS1 LOCAL
SYSAUX LOCAL
TEMP LOCAL

4 rows selected.

-- 현재 system tablespace가 dictionary 방식으로 되어 있는 걸 확인할 수 있고,
이것을 local 방식으로 이전수행 할 수 있다.

SYS> shutdown immediate

SYS> startup mount exclusive

SYS>alter database open;

-- 나머지 tablespace 중에 dictionary 방식의 tablespace가 있다면
해당 tablespace를 read only로 변경한다. (ex ? alter tablespace xxxx read only; )

SYS>alter tablespace sysaux offline;

--제한모드 설정

SYS>alter system enable restricted session;

SYS>select logins from v$instance;

LOGINS
----------
RESTRICTED

1 row selected.

--이전 수행

SYS>exec DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM');

SYS> alter tablespace sysaux online;

SYS> alter system disable restricted session;

SYS> select tablespace_name,extent_management from dba_tablespaces;

TABLESPACE_NAME EXTENT_MAN
------------------------------ ----------
SYSTEM LOCAL
UNDOTBS1 LOCAL
SYSAUX LOCAL
TEMP LOCAL

4 rows selected.

5. EM (Enterprise Manager Database Control) 수동 구성

ocp@testdb : /home/oracle> export ORACLE_SID=testdb
ocp@testdb : /home/oracle> echo $ORACLE_SID
testdb
ocp@testdb : /home/oracle> cd $ORACLE_HOME

--testdb의 EM설치 여부 확인
ocp@testdb : /u01/app/oracle/product/10.2.0/db_1> ls
assistants has log opmn root.sh
bin hs md oracore root.sh.old
cdata install mesg oraInst.loc slax
cfgtoollogs install.platform mgw ord sqlj
clone inventory network oui sqlnet.log
config javavm nls owm sqlplus
crs jdbc oc4j perl srvm
css jdk ocp.mycorpdomain.com_orcl plsql startup.log
ctx jlib ocp.mycorpdomain.com_prod precomp sysman
dbs jre odbc racg uix
demo ldap olap rdbms wwg
diagnostics lib OPatch relnotes xdk

--현재 testdb는 EM이 없음을 알 수 있다.

--사전 암호 변경

ocp@testdb : /u01/app/oracle/product/10.2.0/db_1> emca -config dbcontrol db -repos create

ocp@testdb : /u01/app/oracle/product/10.2.0/db_1> ls
assistants hs mesg oraInst.loc sqlj
bin install mgw ord sqlnet.log
cdata install.platform network oui sqlplus
cfgtoollogs inventory nls owm srvm
clone javavm oc4j perl startup.log
config jdbc ocp.mycorpdomain.com_orcl plsql sysman
crs jdk ocp.mycorpdomain.com_prod precomp uix
css jlib ocp.mycorpdomain.com_testdb racg wwg
ctx jre odbc rdbms xdk
dbs ldap olap relnotes
demo lib OPatch root.sh
diagnostics log opmn root.sh.old
has md oracore slax

ocp.mycorpdomain.com_testdb확인!!

ocp@testdb : /u01/app/oracle/product/10.2.0/db_1/install> cat portlist.ini
iSQL*Plus HTTP port number =5560
Enterprise Manager Console HTTP Port (orcl) = 1158
Enterprise Manager Agent Port (orcl) = 3938
Enterprise Manager Console HTTP Port (prod) = 5500
Enterprise Manager Agent Port (prod) = 1830
Enterprise Manager Console HTTP Port (testdb) = 5501
Enterprise Manager Agent Port (testdb) = 1831

==> http://192.168.100.100:5501/em

오늘은 예전에 OCP 자격증을 취득하기위해 공부했던 내용 중 TABLESPACE에 관한 내용을 정리해보고자 한다. Tablespace 실습 및 내용을 정리하기 전에 간단히 테이블 스페이스 개념을 집고 넘어가려 한다. 테이블 스페이스는 테이블을 생성할 수 있는 물리적인 파일이다. 데이터베이스는 데이터를  테이블에 담는다. 그 테이블들은 어디에 담기게 되는것일까? 바로  물리적 파일인 테이블 스페이스에 담기게 되는 것이다. 데이터베이스를 만드는 것은 테이블스페이스를 만드는 것이라 보아도 되겠다.

Tablespace 실습 및 내용 정리.

[Tablespace Syntax]

CREATE[undo|tempoary|smallfile] TABLESPACE테이블스페이스 이름
DATAFILE[tempfile]'/경로/파일 이름1.dbf' SIZE integer [T/G/M/K] [reuse]
[autoextend on next n [M] maxsize n [[M]| unlimnited]
,'파일 이름2' SIZE integer [M/K]...
[ENCRYPTION USING '3DES168' DEFAULT STORAGE (ENCRYPTION) ->11gR1~
[MINIMUM EXTENT integer [M/K]]
[BLOCK integer [k]]
[DEFAULT STORAGE(
INITIAL integer [M/K]
NEXT integer [M/K]
MAXEXTENTS integer
PCTINCREASE integer)]
[ONLINE|OFFLINE]
[LOGGING|NOLOGGING]
[PERMANENT|TEMPORARY]
[EXTENT MANAGEMENT
DICTIONARY|LOCAL
AUTOALLOCATE|UNIFORM SIZE integer [M/K]]
[SEGMENT SPACE MANAGEMENT (MANUAL/AUTO)]

- 테이블스페이스 생성하기

SYS> create tablespace sales_tbs
2 datafile '/u01/oradata/orcl/sales_tbs01.dbf' size 10m
3 autoextend on next 1m maxsize 100m
4 online logging
5 extent management local uniform size 128k
6 segment space management auto;

Tablespace created.

- sales_tbs 테이블 스페이스에 table생성
SYS> create table hr.sales00
2 (order# number(10) primary key, order_date date default sysdate)
3 tablespace sales_tbs;

Table created.

- 데이터 입력

SYS> begin
2 for i in 1..1000 loop
3 insert into hr.sales00 (order#) values (i);
4 end loop;
5 commit;
6 end;
7 /

PL/SQL procedure successfully completed.

-- EM을 통해 Sales_tbs의 tablespace용량이 늘어나는 것을 확인할 수 있다.

SYS> ed
Wrote file afiedt.buf

1 begin
2 for i in 1001..10000 loop
3 insert into hr.sales00 (order#) values (i);
4 end loop;
5 commit;
6* end;
SYS> /

PL/SQL procedure successfully completed.

--Viewing Tablespace Information(사용현황조회)

SYS> select * from DBA_TABLESPACE_USAGE_METRICS;

TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
------------------------------ ---------- --------------- ------------
EXAMPLE 8952 4194302 .213432414
SALES_TBS 64 12800 .5
SYSAUX 32632 4194302 .778007878
SYSTEM 60976 4194302 1.45378182
TEMP 0 4194302 0
UNDOTBS1 296 4194302 .007057193
USERS 840 4194302 .02002717

7 rows selected.

SYS>select tablespace_name from DBA_TABLESPACES;

TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS
EXAMPLE
INVENTORY
RMAN_TBS
UTEST02
UTEST03
UTEST04

TABLESPACE_NAME
------------------------------
UTEST05

12 rows selected.

-- tablespace 공간 변경 3가지 방법

SYS> --1. add datafile
SYS> alter tablespace inventory
2 add datafile '/u01/oradata/orcl/inventory02.dbf' size 10m;

Tablespace altered.

SYS> --2. resize
SYS> alter database datafile '/u01/oradata/orcl/inventory02.dbf' resize 20m;
Database altered.

SYS> --3. autoextend
SYS> alter database datafile '/u01/oradata/orcl/inventory02.dbf'
2 autoextend on next 1m maxsize 50m;

Database altered.

--tablespace 이동(datafile)

1. System TBS (offline 설정 안됨)

1) shutdown immediate
2) cp를 통해 복사(이동) - 물리적
3) startup mount
4) alter database rename file '원래' to '새로운'; - 논리적
5) alter database open;
6) 이전 file 삭제

2. Non-System TBS

1) 해당 TBS. offline 설정
2) cp를 통해 복사(이동) - 물리적
3) alter tablespace XXXX rename datafile '원래' to '새로운' ; - 논리적
4) 해당 TBS online설정
5) 이전 file삭제

-- tablespace삭제하기

1. TBS가 비어있는 경우
SQL> drop tablespace XXXXX;

2. TBS에 Object가 존재하는 경우 포함 삭제
SQL> drop tablespace XXXX including contents;

3. OSfile 동반삭제
SQL> drop tablespace XXXX including contents and datafiles;

4. 다른 TBS의 Object와 참조무결성 제약조건에 연결되어 있는 경우.
SQL> drop tablespace XXXX including contents and datafiles cascade constraints;

--close backup

ocp@orcl : /home/oracle> ls
20100823-3.sql Desktop insert.sql lob_04_04.sql sqlnet.log
afiedt.buf dict.sql inst.sql lock_trace.sql test1.sql
backup emp10000.sql jazn.sh lock_trace_user.sql v_scan.sql
const.sql hidden.sql lab_01_07.sql orcl_start.sh xbh.sql
creuser.sql holding.sql labs practice01_02.sql
ocp@orcl : /home/oracle> cd backup
ocp@orcl : /home/oracle/backup> ls
pfile_backup
ocp@orcl : /home/oracle/backup> mkdir close open rman rman_con archive
ocp@orcl : /home/oracle/backup> ls
archive close open pfile_backup rman rman_con
ocp@orcl : /home/oracle/backup> cd close/
ocp@orcl : /home/oracle/backup/close> cp /u01/oradata/orcl/* ./
cp: omitting directory `/u01/oradata/orcl/arch1'
ocp@orcl : /home/oracle/backup/close>

################################
############실습################
################################

1. WS1 Tablespace 생성 관리 실습

########################################
#####실습 1 데이터베이스의 저장구조##########
########################################

SYS>!t_scan.sql
/bin/bash: t_scan.sql: command not found

--자주 사용함으로 스크립트 생성

SYS>!vi t_scan.sql

SYS>!cat t_scan.sql
col tablespace_name format a15;
col file_name format a45;
select tablespace_name,status,contents,
extent_management,segment_space_management
from dba_tablespaces;

SYS>@t_scan

TABLESPACE_NAME STATUS CONTENTS EXTENT_MAN SEGMEN
--------------- --------- --------- ---------- ------
SYSTEM onLINE PERMANENT LOCAL MANUAL
UNDOTBS1 onLINE UNDO LOCAL MANUAL
SYSAUX onLINE PERMANENT LOCAL AUTO
TEMP onLINE TEMPORARY LOCAL MANUAL
USERS onLINE PERMANENT LOCAL AUTO
EXAMPLE onLINE PERMANENT LOCAL AUTO
INVENTORY onLINE PERMANENT LOCAL AUTO
INSA onLINE PERMANENT LOCAL AUTO
RMAN_TBS onLINE PERMANENT LOCAL AUTO

9 rows selected.

SYS>select tablespace_name,bytes,file_name from dba_data_files;

TABLESPACE_NAME BYTES FILE_NAME
--------------- ---------- ---------------------------------------------
USERS 6553600 /u01/oradata/orcl/users01.dbf
SYSAUX 272629760 /u01/oradata/orcl/sysaux01.dbf
UNDOTBS1 36700160 /u01/oradata/orcl/undotbs01.dbf
SYSTEM 513802240 /u01/oradata/orcl/system01.dbf
EXAMPLE 104857600 /u01/oradata/orcl/example01.dbf
INSA 2097152 /u01/oradata/orcl/insa01.dbf
INSA 2097152 /u01/oradata/orcl/insa03.dbf
INVENTORY 104857600 /u01/oradata/orcl/inventory
INSA 2097152 /home/oracle/backup/insa02.dbf
RMAN_TBS 104857600 /u01/oradata/orcl/rman01.dbf

10 rows selected.

SYS>select tablespace_name,bytes,file_name from dba_temp_files;

TABLESPACE_NAME BYTES FILE_NAME
--------------- ---------- ---------------------------------------------
TEMP 41943040 /u01/oradata/orcl/temp01.dbf

########################################
실습 2 사용자용 테이블스페이스 생성#####
########################################

SYS>drop tablespace insa including contents and datafiles cascade constraints;

Tablespace dropped.

SYS>create tablespace insa
2 datafile '/u01/oradata/orcl/insa01.dbf' size 1M
3 segment space management auto;

Tablespace created.

SYS>select tablespace_name,bytes,file_name from dba_data_files;

TABLESPACE_NAME BYTES FILE_NAME
--------------- ---------- ---------------------------------------------
USERS 6553600 /u01/oradata/orcl/users01.dbf
SYSAUX 272629760 /u01/oradata/orcl/sysaux01.dbf
UNDOTBS1 36700160 /u01/oradata/orcl/undotbs01.dbf
SYSTEM 513802240 /u01/oradata/orcl/system01.dbf
EXAMPLE 104857600 /u01/oradata/orcl/example01.dbf
INSA 1048576 /u01/oradata/orcl/insa01.dbf
INVENTORY 104857600 /u01/oradata/orcl/inventory
RMAN_TBS 104857600 /u01/oradata/orcl/rman01.dbf

8 rows selected.

SYS>@t_scan.sql

TABLESPACE_NAME STATUS CONTENTS EXTENT_MAN SEGMEN
--------------- --------- --------- ---------- ------
SYSTEM onLINE PERMANENT LOCAL MANUAL
UNDOTBS1 onLINE UNDO LOCAL MANUAL
SYSAUX onLINE PERMANENT LOCAL AUTO
TEMP onLINE TEMPORARY LOCAL MANUAL
USERS onLINE PERMANENT LOCAL AUTO
EXAMPLE onLINE PERMANENT LOCAL AUTO
INVENTORY onLINE PERMANENT LOCAL AUTO
INSA onLINE PERMANENT LOCAL AUTO
RMAN_TBS onLINE PERMANENT LOCAL AUTO

9 rows selected.

##########################################
실습 3 : 사용자용 테이블스페이스 확장#####
##########################################

SYS>alter database datafile
2 '/u01/oradata/orcl/insa01.dbf' resize 2M;

Database altered.

SYS>select tablespace_name, bytes/1024/1024,
2 file_name from dba_data_files;

TABLESPACE_NAME BYTES/1024/1024 FILE_NAME
--------------- --------------- ---------------------------------------------
USERS 6.25 /u01/oradata/orcl/users01.dbf
SYSAUX 260 /u01/oradata/orcl/sysaux01.dbf
UNDOTBS1 35 /u01/oradata/orcl/undotbs01.dbf
SYSTEM 490 /u01/oradata/orcl/system01.dbf
EXAMPLE 100 /u01/oradata/orcl/example01.dbf
INSA 2 /u01/oradata/orcl/insa01.dbf
INVENTORY 100 /u01/oradata/orcl/inventory
RMAN_TBS 100 /u01/oradata/orcl/rman01.dbf

8 rows selected.

SYS>alter tablespace insa
2 add datafile '/u01/oradata/orcl/insa02.dbf' size 2M;

Tablespace altered.

SYS>select tablespace_name,bytes,file_name from dba_data_files;

TABLESPACE_NAME BYTES FILE_NAME
--------------- ---------- ---------------------------------------------
USERS 6553600 /u01/oradata/orcl/users01.dbf
SYSAUX 272629760 /u01/oradata/orcl/sysaux01.dbf
UNDOTBS1 36700160 /u01/oradata/orcl/undotbs01.dbf
SYSTEM 513802240 /u01/oradata/orcl/system01.dbf
EXAMPLE 104857600 /u01/oradata/orcl/example01.dbf
INSA 2097152 /u01/oradata/orcl/insa01.dbf
INSA 2097152 /u01/oradata/orcl/insa02.dbf
INVENTORY 104857600 /u01/oradata/orcl/inventory
RMAN_TBS 104857600 /u01/oradata/orcl/rman01.dbf

9 rows selected.

SYS>alter tablespace insa add datafile
2 '/u01/oradata/orcl/insa03.dbf' size 2M
3 Autoextend on Next 1M Maxsize 10M;

Tablespace altered.

SYS>select tablespace_name,bytes,file_name from dba_data_files;

TABLESPACE_NAME BYTES FILE_NAME
--------------- ---------- ---------------------------------------------
USERS 6553600 /u01/oradata/orcl/users01.dbf
SYSAUX 272629760 /u01/oradata/orcl/sysaux01.dbf
UNDOTBS1 36700160 /u01/oradata/orcl/undotbs01.dbf
SYSTEM 513802240 /u01/oradata/orcl/system01.dbf
EXAMPLE 104857600 /u01/oradata/orcl/example01.dbf
INSA 2097152 /u01/oradata/orcl/insa01.dbf
INSA 2097152 /u01/oradata/orcl/insa02.dbf
INVENTORY 104857600 /u01/oradata/orcl/inventory
INSA 2097152 /u01/oradata/orcl/insa03.dbf
RMAN_TBS 104857600 /u01/oradata/orcl/rman01.dbf

10 rows selected.

##########################################
실습 4 : 사용자용(일반) 데이터 파일 이동##
##########################################

SYS>alter tablespace insa offline;

Tablespace altered.

SYS>!cp /u01/oradata/orcl/insa02.dbf /home/oracle/backup/

SYS>alter tablespace insa rename
2 datafile '/u01/oradata/orcl/insa02.dbf'
3 to '/home/oracle/backup/insa02.dbf';

Tablespace altered.

SYS>alter tablespace insa online;

Tablespace altered.

SYS>!rm /u01/oradata/orcl/insa02.dbf

SYS>select tablespace_name,bytes,file_name from dba_data_files;

TABLESPACE_NAME BYTES FILE_NAME
--------------- ---------- ---------------------------------------------
USERS 6553600 /u01/oradata/orcl/users01.dbf
SYSAUX 272629760 /u01/oradata/orcl/sysaux01.dbf
UNDOTBS1 36700160 /u01/oradata/orcl/undotbs01.dbf
SYSTEM 513802240 /u01/oradata/orcl/system01.dbf
EXAMPLE 104857600 /u01/oradata/orcl/example01.dbf
INSA 2097152 /u01/oradata/orcl/insa01.dbf
INSA 2097152 /home/oracle/backup/insa02.dbf
INVENTORY 104857600 /u01/oradata/orcl/inventory
INSA 2097152 /u01/oradata/orcl/insa03.dbf
RMAN_TBS 104857600 /u01/oradata/orcl/rman01.dbf

10 rows selected.

#################################################
실습 5 : System 테이블스페이스 데이터 파일 이동##
#################################################

SYS>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS>!cp /u01/oradata/orcl/system01.dbf /home/oracle/backup/system01.dbf

SYS>startup mount;
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1218292 bytes
Variable Size 83888396 bytes
Database Buffers 75497472 bytes
Redo Buffers 7168000 bytes
Database mounted.
SYS>alter database rename
2 file '/u01/oradata/orcl/system01.dbf'
3 to '/home/oracle/backup/system01.dbf';

Database altered.

SYS>!rm /u01/oradata/orcl/system01.dbf

SYS>alter database open;

Database altered.

SYS>select tablespace_name,bytes,file_name from dba_data_files;

TABLESPACE_NAME BYTES FILE_NAME
--------------- ---------- ---------------------------------------------
USERS 6553600 /u01/oradata/orcl/users01.dbf
SYSAUX 272629760 /u01/oradata/orcl/sysaux01.dbf
UNDOTBS1 36700160 /u01/oradata/orcl/undotbs01.dbf
SYSTEM 513802240 /home/oracle/backup/system01.dbf
EXAMPLE 104857600 /u01/oradata/orcl/example01.dbf
INSA 2097152 /u01/oradata/orcl/insa01.dbf
INSA 2097152 /home/oracle/backup/insa02.dbf
INVENTORY 104857600 /u01/oradata/orcl/inventory
INSA 2097152 /u01/oradata/orcl/insa03.dbf
RMAN_TBS 104857600 /u01/oradata/orcl/rman01.dbf

10 rows selected.

==> System Tablespace를 다시 원래 경로로 바꾸기!

SYS>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS>!cp /home/oracle/backup/system01.dbf /u01/oradata/orcl/system01.dbf

SYS>startup mount;
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1218292 bytes
Variable Size 83888396 bytes
Database Buffers 75497472 bytes
Redo Buffers 7168000 bytes
Database mounted.

SYS>alter database rename
2 file '/home/oracle/backup/system01.dbf'
3 to '/u01/oradata/orcl/system01.dbf';

Database altered.

SYS>!rm /home/oracle/backup/system01.dbf

SYS>alter database open;

Database altered.

SYS>select tablespace_name,bytes,file_name from dba_data_files;

TABLESPACE_NAME BYTES FILE_NAME
--------------- ---------- ---------------------------------------------
USERS 6553600 /u01/oradata/orcl/users01.dbf
SYSAUX 272629760 /u01/oradata/orcl/sysaux01.dbf
UNDOTBS1 36700160 /u01/oradata/orcl/undotbs01.dbf
SYSTEM 513802240 /u01/oradata/orcl/system01.dbf
EXAMPLE 104857600 /u01/oradata/orcl/example01.dbf
INSA 2097152 /u01/oradata/orcl/insa01.dbf
INSA 2097152 /home/oracle/backup/insa02.dbf
INVENTORY 104857600 /u01/oradata/orcl/inventory
INSA 2097152 /u01/oradata/orcl/insa03.dbf
RMAN_TBS 104857600 /u01/oradata/orcl/rman01.dbf

10 rows selected.

#################################################
실습 6. 테이블 스페이스의 삭제###################
#################################################

SYS>drop tablespace insa
2 including contents and datafiles cascade constraints;

Tablespace dropped.

SYS>select tablespace_name,bytes,file_name from dba_data_files;

TABLESPACE_NAME BYTES FILE_NAME
--------------- ---------- ---------------------------------------------
USERS 6553600 /u01/oradata/orcl/users01.dbf
SYSAUX 272629760 /u01/oradata/orcl/sysaux01.dbf
UNDOTBS1 36700160 /u01/oradata/orcl/undotbs01.dbf
SYSTEM 513802240 /u01/oradata/orcl/system01.dbf
EXAMPLE 104857600 /u01/oradata/orcl/example01.dbf
INVENTORY 104857600 /u01/oradata/orcl/inventory
RMAN_TBS 104857600 /u01/oradata/orcl/rman01.dbf

7 rows selected.

2. 1552 WS1-1-ch05. Creation Tablespace LAB (OCM-1)

문제1. dictionary를 생성할 수 없음 local로 생성 가능.

문제2.

SYS>create tablespace utest02
2 datafile '/home/oracle/backup/tstest/utest02.dbf' size 1m reuse
3 autoextend on next 1m maxsize 10m
4 extent management local uniform size 100k;

Tablespace created.

문제3.

SYS>create tablespace utest03
2 datafile '/home/oracle/backup/tstest/utest03.dbf' size 1m reuse
3 extent management local uniform size 100k;

Tablespace created.

문제4.

SYS>create tablespace utest04
2 datafile '/home/oracle/backup/tstest/utest04.dbf' size 2m reuse
3 extent management local autoallocate
4 segment space management auto;

Tablespace created.

문제5.

SYS>create temporary tablespace utest05
2 tempfile '/home/oracle/backup/tstest/utest05.dbf' size 10m reuse
3 extent management local uniform size 1m;

Tablespace created.

--tablespace 생성 확인

SYS>exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ocp@orcl : /home/oracle> cd backup/tstest
ocp@orcl : /home/oracle/backup/tstest> ls
utest02.dbf utest03.dbf utest04.dbf utest05.dbf 

1. USER

Database 내에서 Username은 Unique(중복되지 않아야)해야 한다.

User create syntax

CREATE USER user_name

IDENTIFIED {BY password | EXTERNALLY | GLOBALLLY} => 인증

(OS인증방식) (제3의 App을 이용한 인증 ex: Directory/LDAP)

[DEFAULT TABLESPACE tablespace] - 지정 안하면 USER TABLESPACE로 지정

[TEMPORARY TABLESPACE tablespace] - default temporary tablespace : temp

[QUOTA {integer[K|M] | UNLIMITED} on tablespace -Tablespace에서 사용할 수 있는 할당값

[QUOTA {integer[K|M] | UNLIMITED} on tablespace]...]

[ACCOUNT LOCK | UNLOCK] - default UNLOCK

[PASSWORD EXPIRE] - 사용자가 로그인시 passwd만료를 표시해주고 바꾸도록 해주는 옵션

[PROFILE {Profile | DEFAULT}]

- default로 DEFAULT라는 이름의 profile이 있음 자원제한 9개 패스워드제한 6개

CREATE문을 ALTER로 바구면 수정문이 됨

EM에서 사용자 생성시 CONNECT권한 자동 부여

데스알고리즘(DES)으로 오라클의 password를 복구 가능 11g부터는 password컬럼에 내용이 보안상 안보임

사용자 삭제시 사용자가 로그인 시에는 DROP이 안됨!!

사용자가 object 소유시에는 cascade 옵션을 사용해서 DROP시켜야 함!!

SQL> alter database default tablespace 테이블 스페이스 명; - 해주면 Default tablespace가 바뀜

SQL> alter database default temporary tablespace 테이블 스페이스 명;

2. ROLE

CREATE ROLE rolename

role은 일반 사용자에게 권한ㅇ르 부여 및 회수 하듯이 롤에 권한을 부여, 회수할 수 있다.

시스템 권한처럼 사용자나 롤로부터 부여 및 회수가 가능하다.

시스템 권한(System Privilege)과 객체 권한(Object Privilege)으로 구성 될 수 있다.

각 사용자에게 부여된 롤은 활성화 되거나 비 활성화 될 수 있따.

role은 활성화하기 위해 암호가 필요 할 수 있다.

role은 특정 사용자가 소유하는 것이 아니기 때문에 어떤 스키마에도 저장되지 않는다.

권한부여

GRANT privilege on [user.]object TO USER[or ORLENAME] = object privilege

GRANT privilege TO USER[orPROLENAME] = system privilege

ALTER user HR default role {role_name | none};

SET ROLE {vacationdba | all | none};

CREATE ROLE secure_application_role

IDENTIFIED USING <security_procedure_name>; pl/sql로 만든 프로그램을 이용한 롤사용

3. profiles(자원제한 설정)

why?? 한정된 자원을 효과적으로 사용하기 위해

SQL>select * from dba_profiles;

session level = limit(한계) 초과시 session이 강제로 log out 당함 (error message 뜸)

call level = limit초과시 해당 작업이 중단됨(error message 뜸)

자원 제한이 설정되어 있을 때 실행 계획에 의해서 실행과 동시에 작업을 안하고 error를 발생시킴

현재 등록 되어 있는 profiles 조회

SQL> select * from dba_profiles;

PROFILE 만들기

CREATE PROFILE profilename LIMIT

CPU_PER_SESSION - 1/100 초 단위로 측정한 총 CPU이용시간

SESSIONS_PER_USER - 각 사용자마다 허용된 동시 세션의 수

CONNECT_TIME - 분 단위로 측정한, 경과된 접속시간

IDLE_TIME -분 단위로 측정한 비활동 시간
: 서버 프로세스에 대해서만 IDLE_TIME이 계산됩니다.
응용프로그램 작업은 포함되지 않습니다.
IDLE_TIME은 오랜시간 수행되는 질의나 다른 작업들은 포함하지 않습니다.

LOGICAL_READS_PER_SESSION - 데이터 블록 수 (물리적:디스크로부터,논리적:메모리로부터 읽은)
: LOGICAL_READS_PER_SESSION은 메모리와 디스크 모두 에게서
읽는 총 횟수를 제한 합니다.

PRIVATE_SGA - 바이트 단위로 측정한 SGA 내의 전용(private) 공간(MTS만)
: PRIVATE_SGA는 다중 스레드 서버(MTS) 구성 일 때만 적용됩니다.

CPU_PER_SEESION - 하나의 Call 당 CPU 사용시간

SESSIONS_PER_USER - 한 Call 당 I/O를 할 수 있는 데이터 블록 수

COMPOSITE_LIMIT - cpu_per_session, read_per_session, connect_time, private_sga의 합이 설정한 수치를 넘어가면 제한

패스워드 제한설정

권장 : 30일 마다 변경 권장

PASSWORD history : 이전 암호를 기억해 놓았다가 다음에 변경시 동일한 암호사용을 금지함

password_reuse_time : 동일한 password를 적용한 기간동안 사용금지

password_reuse_max : 입력된 value값만큼만 사용가능한 횟수를 제한 ex: 3이라고 입력하면 3번만 사용가능

password_life_time : password 생명주기 ex : 30 -> 30일마다 변경해야함

password_grace_time : password 변경 만료 알림을 value일 전부터 알림

failed_login_attempts : password 입력실패시 재시도 가능횟수 최종 실패시 계정 lock걸림

password_lock_time : lock걸렸을때 value값만큼 잠겨있음 1일단위임 1/24는 1시간 1/1440은 1분

password complexity verification(패스워드 복잡성)password설정시 제약조건

VERIFY_FUNCTION password 복잡도 함수

rdbms/admin/utlpwdmg.sql (이경로에 관리용 스크립트가 700여개 존재)-수정가능

scripts 실행

해제시

SQL>alter profile default limit password_verify_function null

password_reuse_time unlimited

password_reuse_max unlimited;

SQL>drop fuction verify_function;

ORACLE error코드중 20001~21000은 비어있는데 사용자정의 오류코드로 사용자가 만들어 쓴다

지금까지 개발 중에 마스터성 테이블의 데이터를 건들기전에 엑셀파일로 export 해서 백업해놓고 변경해서 사용하곤 했다. 근데 더 간단하게 테이블을 생성해서 백업해놓고 데이터 변경한 후 필요없으면 테이블 삭제하면 좀더 간단한 것 같아 정리해 본다.

1. 백업하고 싶은 테이블을 다른 명으로 해서 만든다

==> create table ppp_role_management_bak as select * from ppp_role_management

    요렇게 하면 ppp_role_management_bak으로 원본 테이블이 복사되어 백업되어진다.

    이렇게 만들어 놓고 ppp_role_management를 수정 하다 혹시 문제가 생기면 ppp_role_management_bak의

    테이블 명을 바꾸어서 사용하면 된다.

2. 백업해 놓은 테이블이 필요 없어지면 삭제한다.

==> drop table ppp_role_management_bak

요기서 DB 테이블 명 및 테이블 컬럼 이름 바꾸는 명령어를 정리해 보고자 한다.

1. DB 테이블 변경하기

ALTER TABLE 현재 테이블 이름 RENAME TO 바꾸고자 하는 테이블 이름

예를 들면

ALTER TABLE ppp_role_management(현재 사용 테이블) RENAME TO ppp_role_management_bak(바꾸고자 하는 테이블 명)

2. DB 테이블 컬럼 수정하기

ALTER TABLE 바꾸고자 하는 테이블 명 RENAME COLUNM 현재 테이블의 컬럼(필드명) TO 바꾸고자 하는 컬럼(필드명)

예를 들면

ALTER TABLE ppp_role_management(컬럼명을 바꾸고자 하는 테이블) RENAME COLUNM ppp_role_id(현재 사용되고 있는 테이블 컬럼명) TO  role_id(현재 사용되고 있는 테이블에 바꾸고자 하는 커럼명)

근데 오렌지나 ,TOAD 혹은 SQL DEVELOPER를 사용한다면 명령어 알 필요 없을 듯 하다 !

Oracle 10g를 리눅스에서 설치하던 기억이 난다. 오라클 양성반 과정에서 오라클 설치를 위한 커널 설정부터

필요한 패키지 설정까지 다 외우고 시험보던 기억들이 새록새록 하다. 오랫만에 리눅스 기반에서 Oracle 10g 설치 방법을 정리해 보았다. 

1. Hosts File

# vi /etc/hosts

127.0.0.1 expert.oracle.com~

192.168.100.100 expert.oracle.com~

2. Kernel Parameters 설정

# Controls the maximum size of a message, in bytes
kernel.msgmnb = 65536

# Controls the default maxmimum size of a mesage queue
kernel.msgmax = 65536

# Controls the maximum shared segment size, in bytes
kernel.shmmax = 2147483648

# Controls the maximum number of shared memory segments, in pages
kernel.shmall = 2097152
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 262144
net.core.rmem_max = 262144
net.core.wmem_default = 262144
net.core.wmem_max = 262144

3. Kernel Parameters를 변경 적용

[root@ocm52 ~]# /sbin/sysctl -p
net.ipv4.ip_forward = 0
net.ipv4.conf.default.rp_filter = 1
net.ipv4.conf.default.accept_source_route = 0
kernel.sysrq = 0
kernel.core_uses_pid = 1
net.ipv4.tcp_syncookies = 1
kernel.msgmnb = 65536
kernel.msgmax = 65536
kernel.shmmax = 2147483648
kernel.shmall = 2097152
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 262144
net.core.rmem_max = 262144
net.core.wmem_default = 262144
net.core.wmem_max = 262144 

4. /etc/security/limits.conf 파일에 아래의 내용을 추가.

[root@ocm52 ~]# vi /etc/security/limits.conf

oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536

5. /etc/pam.d/login 파일에 아래의 내용이 없다면 추가

[root@ocm52 ~]# vi /etc/pam.d/login 

session required pam_selinux.so open
session optional pam_keyinit.so force revoke
session required /lib/security/pam_limits.so

6. /etc/selinux/config 파일에 아래의 내용을 변경.

[root@ocm52 ~]# vi /etc/selinux/config

SELINUX=disabled (enforce를 disable로 변경)

7. RedHat Enterprise linux AS에 오라클 설치시 필요한 package

(oracle 설치 전 반드시 조회한 후 각 CD를 이용하여 설치되지 않은 패키지를 설치해야 함.)

=> 대부분의 패키지가 전부 설치 되어있으니 확인만 하면 됨.

=> RPM 조회 : rpm -qa | grep 패키지명

#From RedHat AS4 Disk 2

cd /media/media/Enterprise linux cd | 20071115/server

[root@ocm52 Server]# rpm -Uvh compat-db-4.2.52-5.1.i386.rpm
warning: compat-db-4.2.52-5.1.i386.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159
Preparing... ########################################### [100%]
1:compat-db ########################################### [100%]
[root@ocm52 Server]# cd
[root@ocm52 ~]# cd /mnt/hgfs/shared
[root@ocm52 shared]# ls
10201_database_linux32.zip libaio-devel-0.3.107-2.i386.rpm
[root@ocm52 shared]# rpm -Uvh libaio-devel-0.3.107-2.i386.rpm
Preparing... ########################################### [100%]
1:libaio-devel ########################################### [100%]

8. Create the new groups and user(새로운 사용자 및 그룹 추가)

[root@ocm52 shared]# groupadd dba
[root@ocm52 shared]# groupadd oinstall
[root@ocm52 shared]# groupadd oper
[root@ocm52 shared]# groupadd asmadmin
[root@ocm52 shared]# useradd -g oinstall -G dba,oper,asmadmin oracle
[root@ocm52 shared]# passwd oracle
Changing password for user oracle.
New UNIX password:
BAD PASSWORD: it is based on a dictionary word
Retype new UNIX password:
passwd: all authentication tokens updated successfully.
[root@ocm52 shared]# mkdir -p /u01/app/oracle/product/10.2.0/db_1
[root@ocm52 shared]# chown -R oracle.oinstall /u01
[root@ocm52 shared]# more /etc/passwd | grep oracle
oracle:x:502:503::/home/oracle:/bin/bash

9. 오라클 설치할 폴더 생성

[root@ocm52 shared]# su - oracle
[oracle@ocm52 ~]$ mkdir -p /u01/app/oracle/product/10.2.0
[oracle@ocm52 ~]$ mkdir -p /u01/app/oracle/tmp
[oracle@ocm52 ~]$ mkdir -p /u01/app/oracle/java

10. DISPLAY 설정 (*root 계정으로 설정)

#xhost+<machine-name | ip>

ex) #xhost +192.168.100.100 or xhost+expert.oracle.com

[root@ocm52 ~]# xhost +192.168.100.100
192.168.100.100 being added to access control list

11. .bash_profile에다가 아래의 내용을 추가 해준다.

[root@ocm52 ~]# su - oracle
[oracle@ocm52 ~]$ vi .bash_profile (or$vi.bashrc)

#User specific envirnment and startup programs

PATH=$PATH:$HOME/bin:.:/u01/app/oracle/product/10.2.0/bin
export PATH
export ORACLE_BASE=/u01/app/oracle
export ORACLE_OWNER=oracle
export ORACLE_HOME=/u01/app/oracle/product/10.2.0
export ORACLE_TERM=vt100
export TMPDIR=$ORACLE_BASE/tmp
export TEMP=$ORACLE_BASE/tmp
export NLS_LANG=AMERICAN_AMERICA.KO16KSC5601
export ORACLE_SID=DB01
export JAVA_HOME=$ORACLE_HOME/jdk
export TNS_ADMIN=$ORACLE_HOME/network/admin
export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
umask 022

:wq

[oracle@ocm52 ~]$ . .bash_profile ==>적용 ($source.bash_profile)

12. profile 변경

[oracle@ocm52 ~]$ su - (root로 접속 변경)

Password:

[root@ocm52 ~]# vi /etc/profile 

if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi
13. X-win 변경

[root@ocm52 ~]# vi /etc/inittab (runlever)

id:5 =============================>id:3으로 변경

[root@ocm52 ~]# reboot

현업으로 시스템 관련 변경 요청사항들이 계속 들어온다.  패스워드 변경 관련 처리를 해주다가 다음에 또 쓰게 될 것같아 정리 해보려 한다.

요청 1 :  패스워드 표시할 때 앞의 두자리는 보이고 나머지는 보안을 위해 *로 표시해달라고 한다. 어떻게 처리하면 될까? 단 12자리 이상 넘어가지 않도록 하고 싶다.

mask를 사용해서 간단하게  처리할 수 있다.

입력된 문자 일부를 Password로 처리하여 '*'의 형태로 출력하고자 할때에는 Password로 출력하고자 하는 영역을 { } 로

지정하면 된다.

mask="@@{@@@@@@@@@}"  요렇게 처리해주면 {} 안에 들어가는  password는 '*'로 표시되게 된다.

소스 코드

  <Form id="test" classname="test" inheritanceid="" position="absolute 0 0 667 364" titletext="New Form">
    <Layouts>
      <Layout>
        <MaskEdit id="MaskEdit01" taborder="0" position2="absolute l:60 w:230 t:50 h:24" positiontype="position2"

                 mask="@@{@@@@@@@@@}" type="string" maskchar=" "/>
      </Layout>
    </Layouts>
  </Form>

요청 2 :  보안을 위해 패스워드 저장시 특수문자를 꼭 넣도록 하고 싶을때 어떻게 처리해주면 될까?

 var strVal = Edit00.text; // abc12345입력시
 //특수문자 정의_간단히 10가지만 정의해 봄.  
 var rtn1 = strVal.indexOf("!");//rtn  값은 !가 존재하지 않으면 -1 존재하면 4 값을 리턴한다.
 var rtn2 = strVal.indexOf("@");//rtn 값은 @가 존재하지 않으면 -1 존재하면 4 값을 리턴한다.
 var rtn3 = strVal.indexOf("#");//rtn 값은 #가 존재하지 않으면 -1 존재하면 4 값을 리턴한다.
 var rtn4 = strVal.indexOf("$");//rtn 값은$가 존재하지 않으면 -1 존재하면 4 값을 리턴한다.
 var rtn5 = strVal.indexOf("%");//rtn 값은 %가 존재하지 않으면 -1 존재하면 4 값을 리턴한다.
 var rtn6 = strVal.indexOf("^");//rtn 값은 ^가 존재하지 않으면 -1 존재하면 4 값을 리턴한다.
 var rtn7 = strVal.indexOf("&");//rtn 값은 &가 존재하지 않으면 -1 존재하면 4 값을 리턴한다.
 var rtn8 = strVal.indexOf("*");//rtn 값은 *가 존재하지 않으면 -1 존재하면 4 값을 리턴한다.
 var rtn9= strVal.indexOf("(");//rtn 값은 (가 존재하지 않으면 -1 존재하면 4 값을 리턴한다.
 var rtn10 = strVal.indexOf(")");//rtn 값은 )가 존재하지 않으면 -1 존재하면 4 값을 리턴한다.
 

var check =  rtn1 + rtn2 + rtn3 + rtn4 + rtn5 + rtn6 + rtn7 + rtn8 + rtn9 + rtn10;
  if(check  == -10)
 {
    alert("패스워드에 특수 문자를 넣어주세요.");

    return;
 }

알면 간단하게 해결 되는 TextArea에 대한 설정 방법중,

textArea에서 스크롤바를 최상단, 최하단으로 보내는 방법이다.

var maxVal= TextArea00.vscrollbar.max;
TextArea00.vscrollbar.pos = maxVal;

var minVal= TextArea00.vscrollbar.min;
TextArea00.vscrollbar.pos = minVal;
 

간단하게 설정이 가능하지만 모르면 모르는 기능이다.