Oracle ctxsys 설치
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
1 CTXSYS 설치[편집]
1. SQL*Plus 에서 SYSDBA로 연결 한후 catctx.sql 호출.
SQL> show user;
SQL>@?/ctx/admin/catctx.sql CTXSYS SYSAUX TEMP LOCK
*Option 설명 :
- CTXSYS : ctxsys 사용자의 패스워드 지정
- SYSAUX: ctxsys 사용자의 default tablespace명 지정
- TEMP: ctxsys 사용자의 temporary tablespace명 지정
- LOCK|NOLOCK: ctxsys 사용자 계정을 잠글 것인지, 잠금 해제 할 것인지 지정만약 LOCK 으로 진행할경우 ctxsys 유저 사용전 alter user account unlock 해주어야 한다
--> PL/SQL 처리가 정상적으로 처리 되었음을 확인 .
2. CTXSYS 유저로 접속하여 ORACLE text 를 사용할 언어를 지정하는 defalut sql 실행 - 지원하는 언어별 sql은 /ctx/admin/defaults 디렉토리에 - 예를들어 영어 와 한국어를 ORACLE text 시 사용하기 위해서는 /ctx/admin/defaults 디렉토리에서 drdefuk.sql 와 drdefko.sql 사용
SQL>@?/ctx/admin/defaults/drdefko.sql--> 이단계에서 ORA955 에러 발생시 : Oracle text 설치전 ODM 을 설치하였을경우 발생하는 에러로 ,
ODM 설치되어 있는 상태에서는 ctx public sysnonym 생성은 실패하나 , public synonym 이 odm 의 객체를 가리키는
상태가 되므로 정상동작.
3. 모든 Oracle Text 객체가 CTXSYS 스키마에 정상 설치되었는지 확인
- context 기능이 instance 에 등록됬는지 여부와 Oracle text version 확인 .
SQL> Select comp_name, status, substr(version,1,10) as version
From dba_registry
Where comp_id = 'CONTEXT';
SQL> select substr(ctxsys.dri_version,1,10) VER_CODE from dual;- CTXSYS 유저 객체 확인
SQL> Select count(*)
From dba_objects Where owner='CTXSYS' ;
SQL> Select object_type , count(*)
From dba_objects Where owner='CTXSYS'
Group by object_type ;- CTXSYS유저 객체중 invalid 로 빠진 객체가 없어야 한다.
SQL> Select object_name, object_type, status
From dba_objects
Where owner='CTXSYS' and status != 'VALID'
Order by object_name ;2 권한 추가[편집]
1. 접속 권한
ALTER USER CTXAPP ACCOUNT UNLOCK;2. Roles for CTXSYS
GRANT CTXAPP TO CTXSYS WITH ADMIN OPTION;
GRANT RESOURCE TO CTXSYS;
ALTER USER CTXSYS DEFAULT ROLE ALL;3.System Privileges for CTXSYS
GRANT ALTER SESSION TO CTXSYS;
GRANT CREATE JOB TO CTXSYS;
GRANT CREATE PUBLIC SYNONYM TO CTXSYS;
GRANT CREATE SESSION TO CTXSYS;
GRANT CREATE SYNONYM TO CTXSYS;
GRANT CREATE VIEW TO CTXSYS;
GRANT DROP PUBLIC SYNONYM TO CTXSYS;
GRANT INHERIT ANY PRIVILEGES TO CTXSYS;
GRANT MANAGE SCHEDULER TO CTXSYS;
GRANT SET CONTAINER TO CTXSYS;
GRANT UNLIMITED TABLESPACE TO CTXSYS;4.Object Privileges for CTXSYS
GRANT SELECT ON SYS.ARGUMENT$ TO CTXSYS WITH GRANT OPTION;
GRANT SELECT ON SYS.CCOL$ TO CTXSYS WITH GRANT OPTION;
GRANT SELECT ON SYS.CDEF$ TO CTXSYS WITH GRANT OPTION;
GRANT SELECT ON SYS.COL$ TO CTXSYS WITH GRANT OPTION;
GRANT SELECT ON SYS.COLTYPE$ TO CTXSYS;
GRANT SELECT ON SYS.CON$ TO CTXSYS WITH GRANT OPTION;
GRANT SELECT ON SYS.DBA_COLL_TYPES TO CTXSYS;
GRANT SELECT ON SYS.DBA_CONSTRAINTS TO CTXSYS;
GRANT SELECT ON SYS.DBA_CONS_COLUMNS TO CTXSYS WITH GRANT OPTION;
GRANT SELECT ON SYS.DBA_DB_LINKS TO CTXSYS WITH GRANT OPTION;
GRANT SELECT ON SYS.DBA_INDEXES TO CTXSYS;
GRANT SELECT ON SYS.DBA_INDEXTYPES TO CTXSYS WITH GRANT OPTION;
GRANT SELECT ON SYS.DBA_JOBS TO CTXSYS;
GRANT SELECT ON SYS.DBA_JOBS_RUNNING TO CTXSYS;
GRANT SELECT ON SYS.DBA_OBJECTS TO CTXSYS WITH GRANT OPTION;
GRANT SELECT ON SYS.DBA_PART_KEY_COLUMNS TO CTXSYS;
GRANT SELECT ON SYS.DBA_PROCEDURES TO CTXSYS;
GRANT SELECT ON SYS.DBA_ROLES TO CTXSYS WITH GRANT OPTION;
GRANT SELECT ON SYS.DBA_ROLE_PRIVS TO CTXSYS WITH GRANT OPTION;
GRANT SELECT ON SYS.DBA_SEGMENTS TO CTXSYS;
GRANT SELECT ON SYS.DBA_SYNONYMS TO CTXSYS WITH GRANT OPTION;
GRANT SELECT ON SYS.DBA_SYS_PRIVS TO CTXSYS;
GRANT SELECT ON SYS.DBA_TABLES TO CTXSYS;
GRANT SELECT ON SYS.DBA_TAB_COLS TO CTXSYS;
GRANT SELECT ON SYS.DBA_TAB_COLUMNS TO CTXSYS;
GRANT SELECT ON SYS.DBA_TAB_PARTITIONS TO CTXSYS;
GRANT SELECT ON SYS.DBA_TAB_PRIVS TO CTXSYS WITH GRANT OPTION;
GRANT SELECT ON SYS.DBA_TRIGGERS TO CTXSYS;
GRANT SELECT ON SYS.DBA_TYPES TO CTXSYS WITH GRANT OPTION;
GRANT SELECT ON SYS.DBA_TYPE_ATTRS TO CTXSYS WITH GRANT OPTION;
GRANT SELECT ON SYS.DBA_USERS TO CTXSYS;
GRANT EXECUTE ON SYS.DBMS_LOCK TO CTXSYS;
GRANT EXECUTE ON SYS.DBMS_PIPE TO CTXSYS;
GRANT EXECUTE ON SYS.DBMS_PRIV_CAPTURE TO CTXSYS;
GRANT EXECUTE ON SYS.DBMS_REGISTRY TO CTXSYS;
GRANT SELECT ON SYS.GV_$DB_OBJECT_CACHE TO CTXSYS;
GRANT SELECT ON SYS.GV_$PARAMETER TO CTXSYS;
GRANT SELECT ON SYS.ICOL$ TO CTXSYS WITH GRANT OPTION;
GRANT SELECT ON SYS.IND$ TO CTXSYS WITH GRANT OPTION;
GRANT SELECT ON SYS.INDPART$ TO CTXSYS WITH GRANT OPTION;
GRANT SELECT ON SYS.LOB$ TO CTXSYS WITH GRANT OPTION;
GRANT SELECT ON SYS.LOBFRAG$ TO CTXSYS WITH GRANT OPTION;
GRANT SELECT ON SYS.OBJ$ TO CTXSYS WITH GRANT OPTION;
GRANT SELECT ON SYS.OPQTYPE$ TO CTXSYS;
GRANT SELECT ON SYS.PARTOBJ$ TO CTXSYS WITH GRANT OPTION;
GRANT SELECT ON SYS.SNAP$ TO CTXSYS WITH GRANT OPTION;
GRANT SELECT ON SYS.SYN$ TO CTXSYS WITH GRANT OPTION;
GRANT INHERIT PRIVILEGES ON USER SYS TO CTXSYS;
GRANT SELECT ON SYS.SYSAUTH$ TO CTXSYS WITH GRANT OPTION;
GRANT SELECT ON SYS.TAB$ TO CTXSYS WITH GRANT OPTION;
GRANT SELECT ON SYS.TABPART$ TO CTXSYS WITH GRANT OPTION;
GRANT SELECT ON SYS.TS$ TO CTXSYS WITH GRANT OPTION;
GRANT EXECUTE ON SYS.VALIDATE_CONTEXT TO CTXSYS;
GRANT SELECT ON SYS.VIEW$ TO CTXSYS WITH GRANT OPTION;
GRANT SELECT ON SYS.V_$DB_PIPES TO CTXSYS;
GRANT SELECT ON SYS.V_$PARAMETER TO CTXSYS WITH GRANT OPTION;
GRANT SELECT ON SYS.V_$RESOURCE TO CTXSYS WITH GRANT OPTION;
GRANT SELECT ON SYS.V_$SESSION TO CTXSYS WITH GRANT OPTION;
GRANT SELECT ON SYS.V_$THREAD TO CTXSYS WITH GRANT OPTION;
GRANT INHERIT PRIVILEGES ON USER XDB TO CTXSYS;
GRANT SELECT ON SYS."_BASE_USER" TO CTXSYS WITH GRANT OPTION;3 일반사용자에 롤 권한[편집]
GRANT CTXAPP TO ABT_DEV;
GRANT CTXAPP TO ABT_APP;
GRANT CTXAPP TO ABT;