혼자공부하기

[ 전체 tablespace 확인 ]

select * from dba_data_files ; 

select * from dba_tablespaces ;

[ Temp tablespace 확인 ]

select * from dba_temp_files ;

[ 현재 유저의 default tablespace 확인 ]

select * from user_users ;

==> Defaut Tablespace로 설정된 부분을 확인

[ 유저의 default tablespace 변경 ]

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

[ 테이블의 tablespace 변경 ]

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

[ tablespace 수정이나 삭제시 online/offline 설정 ]
alter tablespace [테이블 스페이스명] online

alter tablespace [테이블 스페이스명] offline

[ tablespace의 물리적인 파일의 이름 또는 위치변경 ]

alter tablespace rename A to B

[ tablespace 공간관리 ]

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

  ==> 공간이 가득차면 실행

alter tablespace [테이블 스페이스명] add datafile 'c:\경로\test2.dbf' size 10M;

  ==> 공간이 가득차면 실행

alter tablespace [테이블 스페이스명] add datafile 'c:\경로\test2.dbf' size 10M

       autoextend on next 10M maxsize 10M;

  ==> 10M씩 자동증가

[ tablespace 삭제 ]

drop tablespace [테이블 스페이스명] include contents;

  ==> 테이블스페이스 내의 객체(테이블,인덱스등)를 다 지운다.

drop tablespace [테이블 스페이스명] including contents;

  ==> 테이블스페이스의 모든 세그먼트를 삭제한다.

  ==> 단, 데이타가 있는 테이블스페이스는 삭제할수 없다.

drop tablespace [테이블 스페이스명] cascade constraints;

  ==> 삭제된 테이블스페이스 내의 테이블의 기본키와 유일키를 참조하는

        다른 테이블스페이스의 테이블로부터 참조무결성 제약 조건을 삭제한다.        

drop tablespace [테이블 스페이스명] including contents and datafiles;

  ==> 물리적파일까지 삭제한다.

[ tablespace 생성1 ]

create tablespace [테이블 스페이스명]

datafile 'c:\경로\test.dbf'

size 10M ==> 여기까지 작성하면 기본적인 사항에 대해서만 생성된다.

autoextend on next 10M ==> (옵션)데이타 파일 용량초과시 자동증가설정

maxsize 100M ==> (옵션)데이타파일 최대크기지정

extent management local ==> (옵션)

uniform size 1M ==> (옵션)

[ tablespace 생성2 ]

create tablespace [테이블 스페이스명]

datafile 'c:\경로\test.dbf'

size 10M

default storage(

   initial 80k  ==> 테이블 스페이스의 맨 첫번째 extents의 크기

   next 80k  ==> 다음 extents의 크기

   minnextents 1  ==> 생성할 extents의 최소값

   maxnextents 121  ==> 생성할 extents의 최대값

   pctincrease 80  ==> extents의 증가율,(Default값은 50%)

) online ;

[ temp tablespace 생성 ]

create temporary tablespace [테이블 스페이스명]

tempfile 'c:\경로\test_temp.dbf'

size 10M

extent management local

uniform size 512k

  ==> localy managed tablespace nuiform size만 생성가능하다.

       (주의)autoallocate, extent management dicionary option을 사용하면 ora-25319 error 발생한다.

  ==> rename 이 불가능하다.

[ temp tablespace를 default tablespace로 변경 ]

alter database default temporary tablespace [테이블 스페이스명]

[ undo tablespace 생성 ]

create undo tablespace [테이블 스페이스명] ==> 이부분만 다름.

datafile 'c:\경로\test_undo.dbf'

size 10M

autoextend on next 10M

maxsize 100M

  ==> undo_management와 undo_tablespace, undo_retention parameter를 제공

  ==> 지역적으로 관리되는 익스텐트만 사용가능

  ==> 시스템에 의해 관리되는 익스텐트 할당만 사용가능하다.

        (uniformsize를 할당 할 수 없고 autoallocate만 가능)

[출처]

http://yagi815.tistory.com/394

1. 테이블 스페이스 생성

 create tablespace [tablespace_name] datafile '경로' size 500m;

ex) CREATE TABLESPACE noiltmp DATAFILE 'C:\ORACLE\noil1.dbf' SIZE  500M ;

2. user 생성

 create user [유저명] identified by [비밀번호];

ex) CREATE noil identified by 1234;

3. 권한 주기

 권한을 줘야지 sqldeveloper에서 접속 및 테이블 생성 가능하다.

GRANT CREATE SESSION TO 유저명      

GRANT CREATE DATABASE LINK TO 유저명

GRANT CREATE MATERIALIZED VIEW TO 유저명

GRANT CREATE PROCEDURE TO 유저명

GRANT CREATE PUBLIC SYNONYM TO 유저명

GRANT CREATE ROLE TO 유저명

GRANT CREATE SEQUENCE TO 유저명

GRANT CREATE SYNONYM TO 유저명

GRANT CREATE TABLE TO 유저명         

GRANT DROP ANY TABLE TO 유저명      

GRANT CREATE TRIGGER TO 유저명 

GRANT CREATE TYPE TO 유저명 

GRANT CREATE VIEW TO 유저명

오라클에서 제공하는 표준 함수 외에도 사용자가 정의하여 사용하는 함수

사용자 정의 함수 조회

SELECT *

FROM all_objects

WHERE object_name like '%함수명%';

사용자 정의 함수 내용 조회

SELECT *

FROM all_source

WHERE name = '함수명';

COMMENT ON COLUMN 테이블명.컬럼명 IS '내용';

SELECT DBMS_METADATA.GET_DDL('TABLE','테이블명','권한자') 

FROM DUAL;

세그먼트 빈 공간 관리 방식에는 FLM(Free List Management)와 ASSM(Automatic Segment Space Management) 두 종류가 있다. 설정 방식은 테이블스페이스 생성시 SEGMENT SPACE MANAGEMENT절로 설정한다.

FLM은 테이블에 PCTUSED 파라미터를 설정하고 블록의 사용률이 PCTUSED 보다 아래로 내려가면 데이터를 입력할 수 있게 된다. PCTFREE보다 낮게 설정을 해야한다.

SEGMENT SPACE MANAGEMENT 절에 'MANUAL'로 지정.

오라클 9i~10g R1 까지 기본 관리 방식이다.

ASSM은 테이블스페이스가 데이터를 입력할 수 있는지를 오라클이 자동으로 판단한다. PCTUSED를 설정하더라도 무시한다. SEGMENT SPACE MANAGEMENT 절에 'AUTO'로 지정.

오라클 10g R2 부터 기본 관리 방식이다.

SELECT tablespace_name,segment_space_management

FROM DBA_TABLESPACES;

오라클을 구성하는 파일은 데이터 파일, REDO 로그 파일, 컨트롤 파일이 있다.

초기화 파라미터 정보는 컨트롤 파일에 있다.

초기화 파라미터는 오라클에 관한 특성을 결정하는 파라미터이다.

인스턴스 레벨에서 초기화 파라미터를 변경하면, 인스턴스 전체에 적용된다.

ALTER SYSTEM SET <파라미터명> = <새로운 설정값>

SCOPE = {MEMORY | SPFILE | BOTH}

MEMORY : 데이터베이스에 변경한 설정이 즉시 반영, 하지만 재기동하면 설정 초기화

SPFILE : 설정한 값이 서버 파라미터 파일 설정에만 변경되므로 재기동후 적용. (기동 중일 때 설정 변경할 수 없는 파라미터 경우 사용)

BOTH : 기본값. 변경 즉시 적용되며, 서버파라미터에도 반영되므로 재기동한 이후에도 설정값 유효

SELECT *

FROM V$PARAMETER;

세션 레벨에서 초기화 파라미터를 변경하면, 해당 명령어를 수행한 세션에만 적용된다.

ALTER SESSION SET <파라미터명> = <새로운 설정값>

이 명령어는 초기화 파라미터를 특정 SQL 처리시에만 임시로 변경하고 싶을 때 편리하게 사용

SELECT *

FROM V$SYSTEM_PARAMETER;

테이블 스페이스는 한 개 이상의 데이터 파일을 그룹으로 묶은 후 이름을 붙인 논리적인 공간

*영구(Permanent) 테이블 스페이스

  - 테이블이나 인덱스 등을 저장하기 위한 데이터 보존용 테이블 스페이스

* UNDO 테이블 스페이스

  - UNDO 정보만을 저장하기 위한 특별한 테이블 스페이스, 테이블이나 인덱스 등의 오브젝트를 저장하는 것은 불가능하다.

* 임시 테이블 스페이스

  - SQL 처리할 때 사용하는 임시 작업용 특별한 테이블 스페이스, 테이블이나 인덱스 등의 오브젝트를 저장하는 것은 불가능하다.

오라클 내부 정보를 확인할 수 있는 특수한 뷰인데 DBA_, USER_, ALL_로 시작한다.

* 테이블 스페이스 확인

SELECT * FROM ALL_TABLESPACES;

* 테이블 스페이스명과 데이터 파일의 디렉토리 확인

SELECT t.tablespace_name, f.file_name

FROM DBA_TABLESPACE t, DBA_DATA_FILES f

WHERE t.tablespace_name = f.tablespace_name;

* 임시 테이블 스페이스명과 임시 파일의 디렉토리 확인

SELECT t.tablespace_name, f.file_name

FROM DBA_TABLESPACE t, DBA_TEMP_FILES f

WHERE t.tablespace_name = f.tablespace_name;