2007년 1월 29일

|

1.테이블생성
1)생성권한이 존재해야 함
select * from user_sys_privs;
select * from role_sys_privs;

2)여유공간이 있어야 함.
select tablespace_name, sum(bytes)
from user_free_space
group by tablespace_name;

conn /as sysdba
select tablespace_name, file_name
from dba_data_files;

** select table_name from dictionary
    where table_name like '%PRIV%';

3)새로운 테이블스페이스를 생성해서 그곳에 테이블을 생성

conn /as sysdba
create tablespace newtbs
datafile 'C:\ORACLEXE\ORADATA\XE\newtbs01.dbf' size 500k;
=>물리적인 파일이 생성됨을 확인

select * from dba_tablespaces
where tablespace_name='NEWTBS';
=>테이블 스페이스의 기본 속성 확인

select * from dba_data_files
where tablespace_name='NEWTBS';
=> TS를 구성하는 파일속성 확인

select * from user_free_space
where tablespace_name='NEWTBS';
=> 여유공간 확인

**새로운테이블을 생성
이름 : employee (scott계정 소유)
컬럼 :
empno number(2) primary key,
ename varchar2(20) unique,
sal number(10) 600~9000사이의 값만 허용,
job varchar2(30) not null,
deptno number(2) foreign key로서 dept테이블의 deptno컬럼의 값을 참조할수 있도록 설정


create table scott.employees
(empno number(2) constraint employees_empno_pk primary key,
ename varchar2(20) constraint employees_ename_uk unique,
sal number(10) constraint employees_sal_ck check(sal between 600 and 9000),
job varchar2(30) constraint employees_job_nn not null,
deptno number(2) constraint employees_deptno_fk references scott.dept(deptno));

select * from dba_tables
where table_name='EMPLOYEES' and owner='SCOTT';

select * from dba_users
where username ='SCOTT';

drop table scott.employees;

create table scott.employees
(empno number(2) constraint employees_empno_pk primary key,
ename varchar2(20) constraint employees_ename_uk unique,
sal number(10) constraint employees_sal_ck check(sal between 600 and 9000),
job varchar2(30) constraint employees_job_nn not null,
deptno number(2) constraint employees_deptno_fk references scott.dept(deptno))
tablespace newtbs;


insert into scott.employees(empno,ename,sal,job,deptno)
values(1,'A',500,'ANALYST',10); => sal의 제약조건에 따라 입력안됨

insert into scott.employees(empno,ename,sal,job,deptno)
values(1,'A',600,'ANALYST',60); => 외래키가 없어서 60이 에러

insert into scott.employees(empno,ename,sal,job,deptno)
values(1,'A',600,'ANALYST',10);


///////////////////////////////////////////////////////////
테이블 속성 비활성화
///////////////////////////////////////////////////////////

alter table scott.employees
disable primary key;

alter table scott.employees
disable unique(ename);

alter table scott.employees
disable constraint employees_sal_ck;

alter table scott.employees
disable constraint employees_job_nn;

alter table scott.employees
disable constraint employees_deptno_fk;

///////////////////////////////////////////////////////////
데이터 입력 =>테이블스페이스 크기 때문에 에러가 발생한다
///////////////////////////////////////////////////////////
insert into scott.employees(empno,ename,sal,job,deptno)
values(1,'A',600,'ANALYST',10);

insert into scott.employees
select * from scott.employees;

** 테이블스페이스의 공간을 확장
1)기존의 파일사이즈를 증가
alter database datafile 'C:\oraclexe\oradata\XE\newtbs01.dbf' resize 1M;

2)새로운 파일을 추가
alter tablespace newtbs add datafile 'C:\oraclexe\oradata\XE\newtbs02.dbf' size 1M autoextend on;


**생성된 테이블의 정보 확인
1.생성된 테이블들의 이름을 확인
conn scott/tiger
select * from tab;
select table_name from user_tables;
select object_name from user_objects
where object_type='TABLE';

2. employees테이블의 구조 확인(컬럼명, 데이터타입)
3. employees테이블에 설정된 제약들 확인
select
a.table_name, b.column_name, a.constraint_name,a.constraint_type,a.search_condition, a.status
from user_constraints a, user_cons_columns b
where a.constraint_name=b.constraint_name and
a.table_name='EMPLOYEES';

save cons //쿼리 저장

ed cons // 쿼리 수정

@cons // 쿼리 실행

**CTAS를 이용한 테이블 생성
:기존의 테이블의 구조 및 데이터를 이용하여 새로운 테이블을 생성, 일종의 데이터 복사
create table 새로운테이블이름
as select~ ;

예) emp테이블에서 10번 부서원들의 사번, 이름, 업무, 급여, 입사일자를 포함하는 새로운 emp_10이라는 테이블을 생성하시오.
create table emp_10
as select empno,ename, job,sal,hiredate
     from emp
     where deptno=10;
select * from tab;
desc emp_10
select * from emp_10;


문제1)emp 테이블에서 부서별로 부서번호, 인원수, 평균급여, 급여의 합, 최소급여, 최대급여를 포함하는 emp_deptno테이블을 생성하여라
create table emp_deptno
as select deptno, count(*) dept_people_num , avg(sal) sal_avg, sum(sal) sal_sum, min(sal) sal_min , max(sal) sal_max
     from emp
     group by deptno;

**컬럼, 테이블명 naming rule
1. 영어, 숫자, 한글,_,#,$ 가능
2. 예약어 불가능(v$reserved_words)
3. 시작은 영어, 한글로만 시작
4. 1~30자이내


문제2) emp 테이블에서 사원번호, 이름, 업무, 입사일자, 부서번호만 포함하는 emp_temp테이블을 생성하는데 자료는 포함하지 않고 구조만 생성하여라.
create table emp_temp
as select empno, ename, job, hiredate, deptno
     from emp
     where empno is null;

** 테이블의 구조 수정
1)테이블 이름 수정
 RENAME a TO b;
2)컬럼 이름 수정
 ALTER TABLE 테이블명 RENAME a to b;
3)컬럼의 데이터 타입 및 최대길이 변경
 ALTER TABLE 테이블명
 MODIFY 컬럼명 datatype(최대길이);
4)컬럼의 추가 삭제
 ALTER TABLE 테이블명
 ADD 컬럼명 datatype(최대길이);

 ALTER TABLE 테이블명
 DROP COLUMN 컬럼명; (즉시 삭제) 또는

 ALTER TABLE 테이블명
 SET UNUSED COLUMN 컬럼명; ==>이후
 ALTER TABLE 테이블명
 DROP UNUSED COLUMNS;
5)제약조건의 추가 및 삭제
 ALTER TABLE 테이블명
 ADD CONSTRAINT 제약명 제약유형(컬럼명);

 ALTER TABLE 테이블명
 DROP PRIMARY KEY/UNIQUE(컬럼명)/CONSTRAINT 제약명              CASCADE;   ==>cascade는 pk제약을 삭제하려고 하는데 pk제약을 참조하는 fk제약이 만약이 존재한다면 이 옵션을 사용함으로서 pk제약이란 fk제약이 함께 삭제

6)제약의 비활성화 및 활성화  ==>다량의 BATCH업무의 성능향상을 위해 비활성화
ALTER TABLE 테이블명
DISABLE PRIMARY KEY/UNIQUE(컬럼명)/CONSTRAINT 제약명 CASCADE;

ALTER TABLE 테이블명
enable PRIMARY KEY/UNIQUE(컬럼명)/CONSTRAINT 제약명;

** 테이블 삭제
1)DROP TABLE 테이블명 CASCADE CONSTRAINTS;

- DEPT테이블을 삭제
DROP TABLE dept CASCADE CONSTRAINTS;

 - oracle 10g에서는 drop table을 시키면 테이블을 recyclebin으로 일단 저장후 이후에 삭제시킴
show recyclebin

flashback table dept to before drop;

2)TRUNCATE TABLE 테이블명;
 truncate table employees;

 select * from employees;
 desc employees

** 결론)
drop table은 완전삭제,truncate table은 모든 데이터를 완전삭제,구조는 존재함, 저장공간은 해제됨.


**테이블에 데이터를 입력, 수정, 삭제 작업, DML(Date Manipulation Language) ==> DML작업은 트랜잭션을 구성

1)입력
insert into 테이블명(컬럼1, 컬럼2...)
values(값1, 값2,...)

1.emp 테이블에 새로운 사원의 정보를 입력하시오.
(사번 : 8000, 이름: 홍길동, 업무 : MANAGER, 입사일: 현재날짜 및 시간, 급여 : 3000, 부서코드 : 10, 기타 : null)
insert into emp(empno,ename, job, mgr, hiredate, sal,comm, deptno)
values(8000, '홍길동', 'MANAGER',null, sysdate, 3000, null, 10);

2.emp 테이블에 새로운 사원의 정보를 입력하시오
(사번 : 8001, 이름: 김길동, 업무 : CLERK, 입사일 : 2001년도 8월 1일 10시, 급여: 2000, 부서코드: 20, 기타 : null)
insert into emp(empno,ename, job, mgr, hiredate, sal,comm, deptno)
values(8001, '김길동', 'CLERK',null, to_date('2001/08/01/10','rrrr/mm/dd/hh24'), 2000, null, 20);

select empno, ename, to_char(hiredate,'rrrr/mm/dd:hh24:mi:ss')
from emp;

2)수정
update 테이블명
set 컬럼명1 = 값1, 컬럼명2=값...
(where 조건절);

3)삭제
delete (from) 테이블명
(where 조건절);


And