DB스토리

TABLESPACE의 종류 (SYSTEM, UNDO, TEMPORARY ,DATA 등 4가지)

현재버전

select * from v$version

전체 테이블 스페이스 (9i 이상)

select * from dba_data_files

or

select * from dba_tablespaces

임시 테이블 스페이스 (9i 이상)

select * from dba_temp_files

현재 유저의 default tablespace 확인

select * from user_users

유저의 default tablespace 변경

alter user 유저명 default tablespace 테이블스페이스명        

or

alter user 유저명 default tablespace quota 1m[사이즈지정] ON 테이블스페이스명

테이블의 tablespace 변경

alter table 테이블명 move tablespace 테이블스페이스명

테이블 스페이스 수정이나 삭제시 offline 설정

alter tablespace 테이블 스페이스명 offline

테이블 스페이스 수정이나 삭제시 online 설정

alter tablespace 테이블 스페이스명 online

테이블 스페이스의 물리적인 파일의 이름 또는 위치 변경

alter tabelspace rename A to B

테이블 스페이스 공간관리

1. alter database datafile 'c:\경로\insa1.dbf' resize 10M;

2. alter tablespace [tablespace명] add datafile 'c:\경로\insa2.dbf' size 10M;

3. alter tablespace [tablespace명] add datafile 'c:\경로\insa2.dbf' size 10M

    autoextend on next 10M maxsize 10M;

1번과 2번은 공간이 가득찰 때마다 실행

3번은 자동관리

테이블 스페이스 삭제

drop tablespace 테이블 스페이스명 include contents 

          -> include contents는 테이블스페이스 내의 객체(테이블, 인덱스등) 다 지움

or

drop tablespace 테이블 스페이스명

          including contents -> 테이블스페이스의 모든 세그먼트를 삭제( 데이터가 있는 테이블스페이스는 삭제할수 없다)

          cascade constraints;  -> 삭제된 테이블스페이스 내의 테이블의 기본키와 유일키를 참조하는 다른 테이블스페이스의

                                               테이블로부터 참조무결성 제약 조건을 삭제합니다.

or

drop tablespace test_tbs including contents and datafiles   ->물리적 파일까지 삭제

테이블 스페이스 생성

CREATE TABLESPACE 테이블 스페이스명
DATAFILE 'C:\ORACLE\DATA\NEOSS_FM01.DBF'
SIZE 100M
AUTOEXTEND ON NEXT 10M /* 데이터 파일 용량 초과시 자동증가 설정(ON/OFF : AUTOEXTEND OFF), 초과시 다음 자동 증가 용량(OFF시 생략) */
MAXSIZE 1000M /* 데이터 파일 최대 크기(OFF시 생략) */
EXTENT MANAGEMENT LOCAL 
UNIFORM SIZE 1M
-- STORAGE 절을 구성할 필요 없다.(INITIAL, NEXT, PCTINCREASE, MINEXTENTS, 
-- MAXEXTENTS 를 자동관리)
-- 최초 생성시에는 INITIAL, NEXT, MINEXTENTS 에 대하여 효력이 있다.
-- Autoallocate방식과 Uniform 방식중 EXTENT MANAGEMENT 가 명시되지 않으면 Autoallocate로 설정

DEFAULT STORAGE(
INITIAL 10K  /* 테이블 스페이스의 맨 첫번째 Extents의 크기 */
NEXT 10K  /* 다음 Extents의 크기 */
MINEXTENTS 2  /* 생성할 Extents의 최소 값 */
MAXEXTENTS 50  /* 생성할 Extents의 최대 값 */
PCTINCREASE 50)  /* Extents의 증가율, Default값은 50 입니다 */

SEGMENT SPACE MANAGEMENT AUTO; 
-- PCTUSED, FREE LIST, FREE LIST GROUP 설정을 자동으로 한다.

ONLINE;   /* ONLINE : 사용가능하게 활성화 | OFFLINE : 사용할수없도록 비활성화 */

or

create tablespace 테이블 스페이스명

datafile '/oracle/infodata/infodata.dbf'

size 200m

default storage(

     initial 80k

     next 80k

     minnextents 1

     maxnextents 121

     pctincrease 80

) online;

create tablespace : 오라클 데이터베이스내에서 생성되고 처리될 테이블들의 레코들들이 실제로 존재할 영역을 디스크 상에

                            물리적으로 생성시키는 명령어이다.
tablespace_name : 생성될 테이블 스페이스의 이름이다.
datafile : 데이터베이스내에서 사용되는 레코드들이 실제로 디스크상에 파일로 존재하게 되는데, 이때의 파일의 위치와 이름을

              지정하는 곳이다.
data_file : 데이터베이스내에서 사용되는 레코드들이 실제로 디스크상에 파일로 존재하게 되는데, 이때의 파일의 위치와 이름을

               지정하는 곳이다.
data_full_file_name : 레코드들이 실제로 존재할 디렉토리(절대패스사용)와 파일의 이름이다.
size : 테이블 스페이스내의 레코드들을 저장할 디스크상의 파일의 최대 코기를 지정해 줄 수 있다.
datafilesize : 레코드들을 저장할 파일의 크기를 k(킬로바이트), M(메가바이트)의 단위를 사용하여 나타낼 수 있다.
initial : 테이블 생성시 해당 테이블에 할당되어 있는 영역의 크기를 지정해 줄 수 있다.
datafilesize_min : 테이블생성시 사용할 수 있는 공간의 크기로, 예를 들어 10m로 지정되면 생성된 임의의 테이블에 입력되는

                          데이터들을 10m의 영역에 저장한다는 의미이다.
next : 처음에 저장될 데이터의 영역인 initial만큼을 다 쓰고 더 이상의 공간이 없을 때, 사용할 수 있는 영역을 할당 시켜 준다.
datafilesize_max : 추가로 테이블에 데이터가 입력될 때, 사용할 수 있는 여역의 크기이다. 예를 들어 5M를 할당하여 두면,

                           임의의 테이블이 사용한 영역이 10M (위의 initial영역의 크기이다)를 넘을 경우, 주가로 5M만큼의 영역을 더

                           사용할 수 있게 된다. 따라서 총 사용공간은 15M가 된다.
minextents minuum : next 영역으로 할당할 수 있는 최소의 갯수를지정해 줄 수 있다.
maxextents maxnum : next 영역으로 할당할 수 있는 최대의 갯수를 지정해 줄 수 있다.
picincrease num : next를 지정하여 추가로 사용할 영역을 확장하고자 할 때, 늘어날 영역의 크기를 '%'로 나타낸 값이다. pct는

                           '%'를 의미한다. 예를 들어 picincrease 5라고 지정해 두면, next로 추가로 작업할 영역을 늘여 줄때, 처음에는

                           next롤 설정된 영역만을 확장시켜 주나, 두 번째부터는 next영역의 크기에서 5%만큼 더 크게 확장시켜 주게

                          되는 것이다.
online/offline : 테이블 스페이스 생성시 online이나 offline 중 택일하여 쓸 수 있으며, 생략하면 online을 의미한다.
                      online으로 설정하여 테이블 스페이스를 생성하면, 테이블스페이스를 생성함과 동시에 데이터베이스 사용자들이

                      사용가능하다는 것을 의미하며, 일반적으로 online으로 설정하여 사용한다.

테이블 스페이스 변경

alter tablespace tax2110
  default storage(
     initial        1024k
     next           2048k
     minextents     1
     maxextents     5
    )online ;
  pctincrease 기본이 50%이다

TEMP 테이블 스페이스 만들기
   ① locally managed tablespace uniform size 만 생성가능
     autoallocate ,extent management dictionary option 을 사용하면 ora-25319 error 가 발생
   ② rename 이 불가능
------------------------------------------------------------------
CREATE TEMPORARY TABLESPACE TS_TEMP
TEMPFILE 'C:\ORACLE\TEMPDATA\NEOSS_TEMP01.DBF'  
-->DATAFILE이 아니라 TEMPFILE 이다..
SIZE 10M
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 512K

TEMP 테이블 스페이스를 default 테이블 스페이스로 변경

alter database default temporary tablespace TS_TEMP

UNDO 테이블 스페이스 만들기
  ① UNDO_MANAGEMENT 와 UNDO_TABLESPACE, UNDO_RETENTION PARAMETER를 제공
  ② 지역적으로 관리되는 익스텐트만 사용가능
  ③ 시스템에 의해 관리되는 익스텐트 할당만 사용가능하다.
   (UNIFORMSIZE를 할당 할 수 없고 AUTOALLOCATE만 가능)
------------------------------------------------------------------
CREATE UNDO TABLESPACE TS_UNDO
DATAFILE 'C:\ORACLE\TEMPDATA\NEOSS_UNDO01.DBF'
SIZE 1M
AUTOEXTEND ON 
NEXT 1M 
MAXSIZE 1024M

primary key 생성

CREATE TABLE TABLE_NAME

COL_NAME1 CHAR(4)

CONSTRAINT PK_TABLE_NAME PRIMARY KEY(COL_NAME1)

USING index TABLESPACE INDEX_TABLESPACE_NAME)

TABLESPACE TABLESPACE_NAME STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0)

or

ALTER TABLE INSA_TB ADD CONSTRAINT INSA_TB_PK
PRIMARY KEY (SABUN)
USING INDEX TABLESPACE INSAIDX

SECONDARY KEY 생성

CREATE INDEX INSA_TB_IX01 ON INSA_TB
( IPSA_YMD, SABUN )
STORAGE ( INITIAL 4 M
NEXT 4 M
PCTINCREASE 1
MINEXTENTS 1
MAXEXTENTS 1024 ) TABLESPACE INSAIDX

or

create unique index 인덱스명 on 테이블명 (컬럼명)

secondary key 삭제

drop index 인덱스명

index key 변경

ALTER INDEX index_name REBUILD PARTITION partition_name
      [ TABLESPACE tablespace_name]
      [ PARALLEL para_num ]
      [ LOGGING or NOLOGGING ]

index_name : 변경 하고자 하는 인텍스 명

partition_name : 변경 하고자 하는 PARTITION INDEX명
tablespace_name : 생성시킬 테이블스페이스명을 지정한다.
                  생략시 기존에 생성되어 있는 테이블 스페이스에 재생성된다.
※ 기존의 테이블 스페이스와 다른 테이블스페이스를 지정하면 해당 인덱스가 이동 하는

   결과를 얻을 수 있다. (테이블스페이스 변경)
para_num : PARALLEL 처리(병렬처리)를 하고자 하는 경우에 사용한다.
           처리할 데이터가 많은 경우 CPU가 지정한 para_num 수치 만큼 프로세스를 분리하여
           처리한다.
LOGGING or NOLOGGING : LOG처리를 할 것인지를 결정한다. 대량의 자료를 처리하는 경우
           NOLOGGING으로 처리하면 처리속도를 올릴 수 있다.

현재 유저의 모든 index key 보기

select table_name, index_name from user_indexes

사용자 추가

create user 유저명 identified by 비밀번호 default tablespace 테이블 스페이스명

QUOTA unlimited ON prjdosu   // prjdosu 테이블스페이스의 내용을 모두 쓸수 있다.
temporary tablespace 임시 테이블 스페이스명

사용자 권한 부여

grant resource, connect  to 유저명

// connect : 오라클에 접속할 수 있는 세션 생성 및 테이블을 생성하거나 쿼리 실행과 같은 가장 일반적인 권한들로 이루어져 있음

                   CONNECT권한이 없으면 User를 생성하고도 오라클에 접속할 수 없다
// resource : Store Procedure나 Triger와 같은 PL/SQL을 사용할 수 있는 권한들로 이루어져 있음

grant select on scott.emp to 유저명 // emp 테이블을 select 할 수 있는 권한 부여

// dba : 120가지 시스템 권한을 가진 롤, 데이터베이스의 모든 작업 권한 부여

// create session : 사용자에게 데이터베이스 접속을 가능하게 해주는 시스템 권한 부여

// SELECT ANY TABLE : view 생성시 insufficient privileges 에러 나올 때

// select any dictionary : view 생성시 insufficient privileges 에러 나올 때

// create role : 오라클 데이터베이스 역할을 생성할 수 있는 권한 부여

// create view : 뷰 생성 권한

// alter user : 생성한 사용자의 정의를 변경할 수 있는 권한

// drop user : 생성한 사용자를 삭제시키는 권한

// with admin option : 권한을 부여 받은 사용자도 부여 받은 권한을 다른 사용자 또는 역할로 부여할 수 있게 되며, 만약 사용자가

                                with admin option과 같이 역할을 부여 받는다면 부여된 역할은 그 사용자에 의해 변경 또는 삭제 될 수 있다.

사용자 object (table) 권한 부여

grant select, insert on 테이블명 to 유저명

// cascade constraints : refrerences 객체 권한에서 사용된 참조 무결성 제한을 같이 삭제 할 수 있습니다

// with grant option : 객체 권한을 부여한 사용자의 객체 권한을 철회하면, 권한을 부여받은 사용자가 부여한 객체 권한 또한

                               같이 철회되는 종속철회가 발생합니다.

사용자 권한 철회

system 계정으로 로그인 후

revoke resource, connect from 유저명

전체 테이블

select * from all_tables

테이블 생성시 pk fk check unique 설정

constraint pk명 primary key (컬럼명)

constraint fk명 foreign key (컬럼명) references 상대테이블명(상대컬럼명) on delete cascade

constraint ck명 check(컬럼명 in ('구분1','구분2')) //ex) 남자, 여자 값이 정해져 있을 때

테이블 생성 후 pk fk 변경

alter table 테이블명 add constraint pk_테이블명 primary key(컬럼1, 컬럼2, 컬럼3, ...)

alter table 테이블명 add constraint fk명 foreign key (컬럼명) references 상대테이블명(상대컬럼명) [ on delete cascade]

primary key 삭제

ALTER TABLE 테이블명 DROP primary key ;

constraint 수정

ALTER TABLE yourtablename ADD [CONSTRAINT symbol] 
FOREIGN KEY [id] (index_col_name, ...) 
REFERENCES tbl_name (index_col_name, ...) 
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]

foreign key 삭제

alter table 테이블명 drop constraint constraint명;

외부 파일 import!

@경로/파일명

테이블 구조보기
describe 테이블명

화면 정리
clear scr

스풀(sqlplus 화면 파일로 저장하기)
spool 경로/파일명
저장할 sql 명령어

spool off

A유저의 테이블을 B유저에게 권한 부여

grant select on A유저명.테이블명 to B유저명  // A유저의 특정 테이블의 select 권한을 B유저에게 부여

데이터베이스 전체 사전

select * from dictionary;

테이블 컬럼 타입 변경

ALTER TABLE 테이블명 MODIFY (컬럼명 타입);

테이블 컬럼 추가

alter table 테이블명 add (컬럼명 타입);

테이블 컬럼 삭제

alter table 테이블명 drop column 컬럼명;

테이블 컬럼 이름 변경

alter table 테이블명 rename column 기존컬럼명 to 변경할컬럼명;

테이블명 변경

rename 기존테이블명 to 변경할테이블명;

테이블 삭제

drop table 테이블명 [cascade constraints];