◆ 오라클 서비스 구조
1. iSQLPlus : Default Port Number 5560
2. TNSListener : Default Port Number 1152
3. DBConsole : Default Port Number 1158
- orcl와 1:1 매치되므로 orcl이 증가할 때마다 DBConsole도 증가한다.
- 두번째 DBConsole부터 Port Number가 5500 부터 시작한다. (1158,5500,5501,5502,...)
4. orcl : 실제적인 오라클 서버이다. orcl 외에도 여러개의 오라클 서버를 추가적으로 설치 가능.
- DBConsole과 1:1 매치된다.
* 외부에서 접속 시 연결구조
1. user의 접속
- sqlplus id/password@ip:portnumber/net_service_name
2. TNSListener 연결 (Oracle 에서 외부와의 통신을 위해 존재하는 패키지)
- TNSListener 는 현재 오라클 서버가 정상적으로 구동 중인지 확인한다.
- 오라클 서버가 정상 동작 중일 경우 Server Process와 외부 user를 연결시켜준 후 TNSListener의 연결은 끊어진다.
■ Oracle 테이블스페이스란
테이블스페이스(Tablespace)는 데이터베이스 오브젝트 내 실제 데이터를 저장하는 공간이다. 이것은 데이터베이스의 물리적인 부분이며, 세그먼트로 관리되는 모든 DBMS에 대해 저장소(세그먼트)를 할당한다.
데이터베이스 세그먼트는 데이터베이스 오브젝트 중의 하나이며, 테이블이나 인덱스와 같이 물리적 공간을 점유한다. 테이블스페이스는 한번 생성되면, 데이터베이스 세그먼트 생성시 이름으로 참조된다.
테이블스페이스는 단지 데이터베이스 저장소 위치를 지정할 뿐이며, 논리적 데이터베이스 구조나 스키마를 지정하지 않는다. 예를 들면, 동일한 스키마내의 다른 오브젝트는 서로 다른 테이블스페이스에 놓일 수 있다. 마찬가지로, 하나의 테이블스페이스는 여러 세그먼트들을 서비스 할 수 있다.
- 테이블스페이스 생성 예:
CREATE TABLESPACE index1 DATAFILE '/mnt/sda1/index1.dbf' SIZE 100M;
- 영구(Permnet) 테이블스페이스는 사용자 데이터베이스 오브젝트를 저장하는 주요한 테이블스페이스이다.
- 취소(Undo) 테이블스페이스는 트랜잭션이 롤백 된 이후 undo commit 트랜잭션에 사용될 데이터를 저장하기 위해 사용된다.
- 임시(Temporary) 테이블스페이스는 짧은 기간 동안 임시로 사용될 데이터를 저장하는 데 사용된다. DBMS가 데이터베이스 정렬하거나, 사용자의 전역 변수 임시 저장용으로 사용될 수 있다.
■ Oracle 기본 계정 정보
sys : 관리자 계정 (모든 권한을 가짐)
system : 유지보수용 계정으로 sys와 같으나 DB 생성권한만 없음.
scott : 비밀번호는 tiger로 테스트 계정
관리자 특권(previleges)
sysdba, sysoper, sysasm
■ Oracle 접속방법 (원격)
- 서버셋팅
<설치디렉토리>\product\11.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora
아래 부분 추가.
TEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.40)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test)
)
)
- 클라이언트 셋팅
<설치디렉토리>\product\11.2.0\client_1\NETWORK\ADMIN\tnsnames.ora
만일 파일이 없으면 생성해주고, 서버의 tnsnames.ora 파일을 복사해 오거나 아래를 추가해 준다.
TEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.40)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test)
)
)
■ 클라이언트에서 sqlplus로 접속
sqlplus <사용자명>/<비밀번호>@<접속식별자> [ as { sysdba | sysoper | sysasm } ]
※ as...은 관리자 특권을 지정한다. sysdba가 가장 막강하며, 일반 사용자는 불필요.
- 테스트계정 접속 (scott)
sqlplus scott/tiger@orcl
- sys 계정 접속
sqlplus sys/"비밀번호"@orcl as sysdba
- test 계정 접속
sqlplus test/"비밀번호"@test
■ 오라클 사용자 확인방법
sql> SELECT username FROM DBA_USERS;
■ 사용자 암호변경(변경가능한사람 : 사용자(본인), sys, system)
sql> ALTER USER scott IDENTIFIED BY "java401$!";
■ scott 사용자로 로그인
sql> CONN scott/"java401$!"
■ sys 계정 로그인
sql> CONN sys/"java401$!"as sysdba
■ scott의 락 해제
sql> ALTER USER scott ACCOUNT UNLOCK; => sys나 system으로만 해제 가능하다.
■ scott 사용자로 로그인
sql> CONN scott/"java401$!"
sql> SELECT * FROM tabs;
■ 사용자 추가(sys 계정을 접속한 상태에서)
sql> CREATE USER 사용자명 IDENTIFIED BY "암호";
■ 사용자추가 확인
sql> SELECT username FROM DBA_USERS;
sql> CONN sky/"java401$!"
권한 없어 접속 불가
sys 계정으로 conn => 권한없이 접속하면 접속이 풀리므로 다시 sys로 conn
sql> CONN sys/"java401$!" as sysdba;
■ conn(접속) 및 테이블스페이스를 사용 할 수 있는 권한 부여
sql> GRANT CONNECT, RESOURCE TO 사용자명;
- 모든권한:
GRANT CONNECT, DBA, RESOURCE TO <사용자명>;
특정테이블에 대한 모든권한:
GRANT ALL ON <테이블명> TO <사용자명> WITH GRANT OPTION;
다시 접속
sql> CONN sky/"java401$!"
■ 권한 확인하기
- 사용자에게 부여된 시스템 권한 확인
SELECT * FROM DBA_SYS_PRIVS
WHERE GRANTEE = '사용자명' ;
- 사용자에게 부여된 롤 확인(시스템 권한이 롤에 포함됨)
SELECT * FROM DBA_ROLE_PRIVS
WHERE GRANTEE = '사용자명' ;
- 사용자에게 부여된 롤에 부여된 시스템 권한 확인
SELECT * FROM DBA_SYS_PRIVS
WHERE GRANTEE = '롤명' ;
- 타 사용자에게 부여한 객체(테이블등) 권한 확인
SELECT * FROM DBA_TAB_PRIVS
WHERE OWNER = '테이블소유자명' ;
또는,
SELECT * FROM DBA_TAB_PRIVS
WHERE GRANTEE = '권한부여자명' ;
■ 사용자 목록 보기
SELECT * FROM ALL_USERS;
■ 사용자 상태 보기
SELECT USERNAME,ACCOUNT_STATUS FROM DBA_USERS;
■ 사용자 상태 변경 (사용자가 락 걸려 있을 경우)
ALTER USER scott ACCOUNT UNLOCK;
■ 상태 보기
SELECT * FROM TAB -> 테이블 목록 보기.
SELECT * FROM CAT -> TAB과 같습니다.
SELECT * FROM USER_TABLES -> TAB과 같은데 테이블의 정보도 나옵니다.
SELECT * FROM DBA_TABLES -> DBA권한이 있는 유저가 모든 테이블과 그 정보를 반환합니다.
■ 테이블 구조 보기
DESC TEST;
■ SQL 파일 실행하기
SQL> start d:\0_tmp\create-tables.sql
또는
SQL> @d:\0_tmp\create-tables.sql
■ Oracle 시작/종료
sqlplus를 sysdba로 접속하여.
startup;
shutdown [normal, transactional, immediate, abort];
(보통 immediate를 사용)
■ Oracle 리스너 시작
- UNIX
oracle 계정으로 접속하여,
lsnrctl start
- Windows
서비스에서
OracleOraDb11g_XXXXXTNSListener 시작