QUERY 연습

   

   

QUERY 연습

   

   

1

사용자(honggildong) 계정에 대한 잠금/잠금해제에 관련된 DCL문을 작성

사용자 계정 잠금	ALTER USER honggildong ACCOUNT LOCK;
사용자 계정 잠금해제	ALTER USER honggildong ACCOUNT UNLOCK;

   

   

'test1234'라는 비밀번호를 부여한 사용자(honggildong) 계정 생성

CREATE USER "honggildong" IDENTIFIED BY "test1234";

   

   

생성한 "honggildong" 사용자에 대하여 connect role과 resource role에 정의된 권한을 부여한다.

GRANT connect, resource TO "honggildong";

   

   

   

2

설치된 DBMS에 대하여 아래에 제시된 내용의 보고서를 작성하시오

- 아 래 -

1) DBMS IP ADDRESS 2) DBMS PORT 번호 3) DBMS 테이블스페이스 목록 4) 계정 정보(3개 이상)

   

DBMS 명	DBMS IP ADDRESS	DBMS 서비스명	DBMS  PORT번호	테이블스페이스 구분	테이블스페이스 TBSP 명	계정명	계정 PWD
oracle	xxx.xxx.xxx.xxx	http	1521	system	system	sys	pwd1234
oracle	xxx.xxx.xxx.xxx	http	1521	system	sysaux	system	pwd1472
oracle	xxx.xxx.xxx.xxx	em	1158	default	test_tb	honggd	qwe1598
oracle	xxx.xxx.xxx.xxx	em	1158	temp	temp_testtb	hanadul	asd2479

   

   

3

1) 테이블스페이스의 용량을 확인하는 쿼리문을 작성하시오

SELECT * FROM dba_data_files; 또는 SELECT 테이블스페이스관련정보컬럼리스트.... FROM dba_data_files;

   

   

2) 테이블스페이스를 생성하는 쿼리문 작성하시오

CREATE TABLESPACE udata1 DATAFILE '/u02/app/oracle/oradata/orcl/udata1a.dbf' SIZE 11256K AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED UNIFORM SIZE 1M ;

AUTOEXTEND 으로 테이블 스페이스 자동 설정(생성한 데이터파일이 다 채워졌을 경우 자동으로 데이터 파일을 확장하는 방법)

   

   

Extent

• Extent는 Segment에 할당된 연속적인 블록으로 Database Storage 할당의 논리적인 단위 입니다.
• 각 Segment는 Data를 넣기 위해 적어도 하나 이상의 Extent가 생성되어야 합니다.

   

UNIFORM

• 테이블스페이스가 동일한 크기의 익스텐트로 구성되도록 지정합니다. 크기는 기본적으로 바이트 단위로 지정
• 익스텐트 크기를 KB 또는 MB 단위로 지정하기 위해서는 K 또는 M 을 사용하여 지정 합니다
• 이 옵션을 사용하게 되면, DEFAULT Storage 절을 사용할 수 없습니다.

   

   

4

다음의 조건에 만족하는 쿼리문을 작성하기.

가. 사용자 계정을 만들기.

(단 테이블스페이스는 이미 만들어져 있다고 가정)

계정명	계정명: sampleuser 패스워드: sample12#$
default 테이블스페이스	디폴트 테이블스페이스명: test_tbs temporary 테이블스페이스명: imsi_test_tbs
테이블스페이스 사용량	50MB (System 및 sysaux의 테이블스페이스는 사용할 수 없다)

   

   

CREATE USER sampleuser IDENTIFIED BY "sample12#$" DEFAULT TABLESPACE test_tbs TEMPORARY TABLESPACE imsi_test_tbs QUOTA 0 ON system QUOTA 0 ON sysaux QUOTA 50MB ON test_tbs;

   

   

나. 사용자 권한 주기

가에서 생성한 사용자에 대하여 connect, resource, dba권한 부여

GRANT connect, resource, dba TO "sampleuser";

   

   

다. 사용자 권한 회수

나항에 부여된 권한 중 dba권한 회수

REVOKE dba FROM sampleuser;

   

   

라. 사용자 정보 보기

데이터 딕셔너리를 이용한 사용자 정보 보기 쿼리문 작성

   

SELECT * FROM all_users;

   

   

5

1.아래의 조건에 맞는 테이블을 설계하고 데이터를 조작하여 테스트하는 쿼리문을 작성하시오

   

인사관리에 필요한 테이블(employees)을 생성하기

컬럼	조건
사원번호 empno	PK
사원명 empname	Not Null
성별 gender	m 또는 w의 값만 허용
부서코드 deptno	부서테이블과 관계 설정
메일주소 email	중복불허
휴대폰번호 phone	중복불허
학력 school	코드(학력테이블)
주소 address	-

   

인사 기본테이블(employees) 구성된 테이블 생성

CREATE TABLE employees( empno NUMBER(4) PRIMARYKEY, empname VARCHAR2(10) NOT NULL, gender VARCHAR2(1) CONSTRAINT emp_gender_CK CHECK( gender IN('M', 'F') ), gender VARCHAR2(10) constraint check_gender check( gender IN('남성','여성') ), deptno VARCHAR2(10) NOT NULL FOREIGN KEY REFERENCES departments(deptno), email VARCHAR2(10) UNIQUE, phone NUMBER(4) UNIQUE, school VARCHAR2(10), address VARCHAR2(10) );

   

   

부서테이블(departments) 생성하기

컬럼	조건
부서코드 (deptno)	PK
부서명 (deptname)	not Null
지역 (region)	-
부서 전화번호 (depttel)	-

   

CREATE TABLE departments( deptno VARCHAR2(10) PRIMARYKEY, deptname VARCHAR2(10)NOT NULL, region VARCHAR2(10) depttel VARCHAR2(10) );

   

   

   

2. 두 테이블간의 관계도를 도식화하여 나타내시오

   

   

   

   

3. 인사기본테이블의 성별컬럼을 기준으로 B 트리 인덱스를 생성하려고 할 때 문제점을 설명하시오

사원정보 테이블에서 성별 컬럼의 데이터는 m과 w의 값만을 가질 수 있으며, 카디널리티가 낮은 컬럼을 대상으로 할 때는 bitmap 인덱스를 생성하는 것이 더 효율적이다.

   

   

   

4. 사원번호와 사원명, 부서명, 메일주소, 휴대폰번호의 컬럼을 갖는 View(empl_view)를 생성하시오

CREATE OR REPLACE VIEW empl_view AS SELECT emp.사원명, dept.부서명, emp.메일주소, emp.휴대폰번호 FROM employees emp, department dept;