1. Data dictionary ---> 데이타 딕셔너리는 데이터베이스 자원을 효율적으로 관리하기 위한 다양한 정보를 저장하는 시스템 테이블이다. 데이터 딕셔너리는 사용자가 테이블을 생성하거나 사용자를 변경하는 등의 작업을 할 때 데이터베이스 서버에 의해 자동으로 갱신되는 테이블로 사용자는 데이터 딕셔너리의 내용을 직접 수정하거나 삭제 할 수 없다. ---> 이러한 데이터 딕셔너리르 사용자가 조회해 보면 시스템이 직접 관리하는 테이블이기에 암호같은 기호만 보여 질 뿐 내용을 알 수 없다.
2. Data dictionary view 데이터 딕셔너리 원 테이블은 직접 조회시 의미있는 자료 조회가 불가능하기에 오라클은 사용자가 이해할 수 있는 데이터를 산출해 줄 수 있는 데이터 딕셔너리에 파생하 데이터 딕셔너리 뷰를 제공한다.
1) USER_데이타 딕셔너리 SHOW USER; DESC USER_TABLES; SELECT table_name FROM user_tables ORDER BY table_name DESC;
USER_TABLES(USER_INDEXES) : 해당 User가 소유한 테이블 정보 SELECT * FROM USER_TABLES;
2) ALL_데이타 딕셔너리 DESC all_tables; SELECT owner, table_name FROM all_tables;
ALL_TABLES(ALL_INDEXES) : dictionary에 등록된 모든 테이블을 User별로 SELECT * FROM ALL_TABLES WHERE OWNER='로그온아이디(대문자)'; SELECT * FROM ALL_TABLES
3) DBA_데이타 딕셔너리 CONN SYSTEM/MANAGER SELECT owner, table_name FROM dba_tables WHERE owner='SYSTEM';
4) ALL_PART_TABLES : 파티션 테이블 검색 SELECT * FROM ALL_PART_TABLES;
5) SELECT * FROM ALL_OBJECTS;
6) 파티션 인덱스 상태 체크 - SELECT i.table_name, i.index_name, ip.partition_name, i.status AS index_status, ip.status AS partition_status FROM user_indexes i JOIN user_ind_partitions ip ON (i.index_name=ip.index_name) ORDER BY i.table_name, i.index_name, ip.partition_name;
- 파티션 인덱스는 rebuild가 안된다. 다시 생성해주어야 한다. DROP하고 다시 CREATE해야 한다.
1. 해당 유저가 가진 테이블의 개수 SELECT count(*) FROM user_tables;
2. 해당 DB의 CHARACTERSET 확인 SELECT parameter, value FROM nls_database_parameters WHERE parameter='NLS_CHARACTERSET'; SELECT parameter, value FROM nls_database_parameters WHERE parameter like '%CHARACTER%';
한가지는 서버의 CHARACTER SET 문제, 다른 한가지는 클라이언트 피씨의 레지스트리 문제이다.
MSTR데모를 만들다 위와 같은 문제가 발생하여 다음과 같이 처리 하였다.
오라클은 10g R2버젼을 사용하였다.
먼저 CHARACTER SET을 확인한다.
select * from v$nls_parameters;
DW권한으로 DB에 접속한다.
[oracle@XXX]$sqlplus/nolog
SQL>conn /as sysdba SQL> SHUTDOWN IMMEDIATE; SQL> STARTUP MOUNT; SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION; SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0; SQL> ALTER DATABASE OPEN;
-->한글 CHARACTER SET은 KO16KSC5601을 사용한다.
SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE KO16KSC5601 ; SQL> SHUTDOWN IMMEDIATE; SQL> STARTUP;
-->CHARACTER SET을 확인한다.
SQL> SELECT * FROM sys.props$ where name='NLS_CHARACTERSET';
정상적으로 작업하였다면 변경되어 있을 것이다. 하지만 여기까지 작업을 한 이후 다시 한글 데이터를 조회하였으나 여전히 한글이 깨져있었다. 원인을 찾아보니 NLS_LANGUAGE와 NLS_TERRITORY가 각각 AMERICAN, AMERICA로 설정되어 있었기 때문이다.
따라서 다음과 같이 설정을 해주니 한글이 정상적으로 출력되었다.
DBA권한으로 다시 로그인한다.
conn /as sysdba
설정을 확인한다.
SELECT NAME, VALUE$ FROM PROPS$ WHERE NAME = 'NLS_LANGUAGE' OR NAME = 'NLS_TERRITORY OR NAME = 'NLS_CHARACTERSET';
다음과 같이 UPDATE 하였다.
UPDATE props$ SET VALUES$='KOREAN' WHERE NAME ='NLS_LANGUAGE'; UPDATE props$ SET VALUES$='KOREA' WHERE NAME ='NLS_TERRITORY';
commit;
다시 확인해보면 변경되어 있을 것이다.
SELECT NAME, VALUE$ FROM PROPS$ WHERE NAME = 'NLS_LANGUAGE' OR NAME = 'NLS_TERRITORY OR NAME = 'NLS_CHARACTERSET';
1. 테이블스페이스 크기 조절 ---> 테이블스페이스 생성시 용량이 부족할 경우 자동으로 용량이 증가하는 옵션(autoextend)이 존재한다. 하지만, 이 옵션을 사용하지 않고 테이블스페이스를 생성했는데, 부득이하게 용량을 조절해야 할 경우가 발생할 수 있다. 대부분의 경우 데이터의 양이 급격히 늘어나서 발생하지만, 간혹 용량을 줄이기 위해 사용할 때가 있으므로 자동증가 옵션만 믿고 크기 조절에 대해서 잊어보리면 안될 것이다.
alter database datafile '[데이터파일명]' resize [크기(예. 300M)];
2. Table/Index의 테이블스페이스 변경 ---> 사용자가 테이블을 생성하거나 인덱스 등을 생성할 때 별다른 옵션을 주지 않는다면 사용자에게 부여된 기본 테이블스페이스에 해당 객체를 생성하게 된다. 그러나, 상황에 따라 테이블이나 인덱스가 저장된 테이블스페이스를 다른 테이블스페이스로 변경할 수도 있다.
alter table [테이블명] move tablespace [테이블스페이스명]; alter index [인덱스명] rebuild tablespace [테이블스페이스명];
Tablespace란? DBMS에 저장되는 자료가 있는 물리적인 파일이다. 각 개체마다 테이블스페이스를 지정해줄 수 있고, 사용자에게 기본으로 사용할 테이블스페이스를 지정할 수도 있다. 또한 오라클에서는 작업을 위한 Temp 테이블스페이스도 존재하는데, 이 역시 별도로 사용자가 생성하고 지정할 수 있다. 테이블스페이스는 각각이 하나의 파일이기 때문에 생성시 물리적으로 저장될 공간을 지정할 수 있다. 이러한 특징 때문에 디스크 부하를 고려하여 여러 위치에 테이블스페이스를 생성하여 사용할 수 있다.
보통 오라클에서는 /usr/local/oracle/oradata/[SID]/ 디렉토리에 기본 테이블스페이스를 생성하며, Oracle Express Edition에서는 /usr/lib/oracle/xe/oradata/XE/ 디렉토리에 기본 테이블스페이스를 저장한다.
1. 테이블스페이스 생성 ---> 테이블스페이를 생성할 경우 여러 옵션이 사용될 수 있으나 테이블스페이스의 기본 용량을 자동으로 늘려주는 옵션(autoextend) 등은 기본으로 사용하는 것이 편하나, 대부분의 회사에서는 사용하지 않는다.
create tablespace [테이블스페이스명] datafile '[테이블스페이스경로(예. /usr/lib/oracle/xe/oradata/XE/)]/[테이블스페이스명].dbf' size [용량(예. 1M)] autoextend on extent management local autoallocate;
2. Temp 테이블스페이스 생성 ---> Temp 테이블스페이스의 최소 용량은 1 M 이상이다. 그러므로 처음 생성시 2M로 생성한 뒤 자동증가 옵션을 사용해야 한다.
1. 테이블스페이스에 대한 데이타 파일의 정보 조회 1) SELECT * FROM DBA_DATA_FILES;
2) SELECT SEGMENT_NAME, TABLESPACE_NAME, BYTES, BLOCKS, FILE_ID FROM DBA_EXTENTS WHERE OWNER = 'SCOTT';
2. 테이블스페이스에 대한 자동 메모리 확장 CREATE TABLESPACE "DATA_XOSL2_D_P" LOGGING DATAFILE 'D:\DATABASE\SLXO2P\DATAFILES\DATA_XOSL2_D_P01.ORA' SIZE 4M AUTOEXTEND ON NEXT 2M MAXSIZE 32M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; ---> 4M부터 시작해서 2M씩 증가해서 32M까지 자동 증가하게
3. 테이블스페이스 삭제하기 DROP TABLESPACE DATA_XOSL2_D_P; ---> 실제 데이타파일은 삭제 되지 않았다. 해당 파일을 지워주어야 한다.
만약 해당 데이타파일도 함께 삭제하고 싶으면 아래와 같이 입력해주면 된다. DROP TABLESPACE DATA_XOSL2_D_P INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
아래의 구문들은 본인이 참고하기 위한 것입니다. 만약 Test를 위한 것이라면 아래의 구문으로 Test하시기 바랍니다. 1. Test 테이블 스페이스 생성하기 CREATE TABLESPACE "DATA_TEST" LOGGING DATAFILE 'D:\DATABASE\SLXO2P\DATAFILES\DATA_TEST01.DBF' SIZE 4M, 'D:\DATABASE\SLXO2P\DATAFILES\DATA_TEST01.DBF' SIZE 4M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
오라클을 설치하면 해당 컴퓨터 어디에 Demobld.sql을 비롯하여 예제 테이블들이 위치할 것입니다. 그런데, 그 위치가 약간씩 상이한 것 같습니다. 그래서, 아래의 자료를 Upload하였습니다. 아래의 자료들은 제가 나중에 또 참고하게 될지 모르기 때문에 올린 자료입니다. 감사합니다. ^^
오라클 12c 이상의 버전에서 DB를 멀티테넌트 유형으로 생성한 경우에는 컨테이너 DB(CDB)와 플러거블DB(PDB)라는 구분이 있는데, CDB에 CONNECT하여 예제를 실행하면 'ORA-65096: 공통 사용자 또는 롤 이름이 부적합합니다.' 라는 에러가 발생한다. 이를 피하려면 CDB가 아닌 PDB에 접속하여 사용해야 한다.
create user scott identified by tiger; alter user scott DEFAULT TABLESPACE USERS; alter user scott QUOTA UNLIMITED ON USERS; GRANT RESOURCE, CONNECT TO scott;
DROP TABLE EMP; DROP TABLE DEPT; DROP TABLE BONUS; DROP TABLE SALGRADE; DROP TABLE DUMMY;
CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL, ENAME VARCHAR2(10), JOB VARCHAR2(9), MGR NUMBER(4), HIREDATE DATE, SAL NUMBER(7, 2), COMM NUMBER(7, 2), DEPTNO NUMBER(2));
INSERT INTO SALGRADE VALUES (1, 700, 1200); INSERT INTO SALGRADE VALUES (2, 1201, 1400); INSERT INTO SALGRADE VALUES (3, 1401, 2000); INSERT INTO SALGRADE VALUES (4, 2001, 3000); INSERT INTO SALGRADE VALUES (5, 3001, 9999);
CREATE TABLE DUMMY (DUMMY NUMBER);
INSERT INTO DUMMY VALUES (0);
COMMIT;
CREATE UNIQUE INDEX dept_pk ON dept(deptno); ALTER TABLE dept ADD CONSTRAINT dept_pk PRIMARY KEY(deptno); CREATE UNIQUE INDEX emp_pk ON emp(empno); ALTER TABLE emp ADD CONSTRAINT emp_pk PRIMARY KEY(empno); CREATE UNIQUE INDEX emp_ename_uk ON emp(ename); ALTER TABLE emp ADD CONSTRAINT emp_ename_uk UNIQUE(ename); CREATE UNIQUE INDEX bonus_pk ON bonus(ename); ALTER TABLE bonus ADD CONSTRAINT bonus_pk PRIMARY KEY(ename); CREATE UNIQUE INDEX salgrade_pk ON salgrade(grade); ALTER TABLE salgrade ADD CONSTRAINT salgrade_pk PRIMARY KEY(grade);
1. Oracle Server 오라클 서버는 information management에 종합적인 접근이 가능하도록 해주는 database 관리 시스템이다. 오라클 서버는 Oracle instance와 Oracle database로 구성된다.
2. Oracle Instance 오라클 인스턴스는 오라클 데이타 베이스에 접근하는 한 수단이다. 항상 오직 한 데이터 베이스에만 열려 있고 사용이 가능하다. 오라클 인스턴스는 메모리와 background process structure들로 구성된다.
3. Oracle Database 오라클 데이타베이스는 unit으로 다루어 지는 데이타들의 집합이다. 오라클 데이타베이스는 3가지 파일 타입으로 구성된다. 오라클 데이타베이스는 아래와 같이 3가지 타입을 가진다. 1) Data files 2) Control files 3) Redo Log files
4. Physical Structure Physical structure는 아래와 같이 3가지 파일 타입을 가진다. 1) Data files 데이타 파일은 database 내에서 실제적인 data를 가지고 있다. 2) Control files 컨트롤파일은 database integrity를 검증하고 유지하는데 필요로하는 정보를 가지고 있다. 예를 들면, Database name, Time stampe of database creation, names and locations of data files and online redo log files 등 기타 등등 여러가지를 가지고 있다. 3) Online redo log files 오라인 리두 로그 파일은 failure의 경우에 data의 복구가 가능하도록 database에 change가 가능한 record를 가지고 있다.
5. Memory Structure 오라클의 memory structure는 아래와 같은 두 가지 memory area로 구성된다. 1) System Global Area(SGA) It is allocated at instance start up. And it is a fundamental component of an Oracle instance. 2) Program Global Area(PGA) It is allocated when the server process is started.(It is equal to 'when a session is created)
Oracle 홈페이지에서 Oracle 10g를 다운로드 받아 설치하였다면 다음은 Database Configuration Assistant를 실행해야 한다.
Database 객체를 만들어야 한다.
그 다음에는 예제 소스를 실행해야 한다. 예제 소스의 실행 방법은 다음과 같다. @ C:\oracle\product\10.2.0\db_1\odp.net\samples\DataSet\RelationalData\setup\Demobld.sql
예제 테이블을 만들고 그것을 가지고 공부를 할 수 있다. 대부분 예제 테이블을 기준으로 교재들이 구성되었기 때문에 예제 테이블을 만들고 공부를 해야 한다. 위는 @ 에 Demobld.sql 경로를 추가해 준 것이다. 위의 '@ 경로'를 sqlplus에 접속하여 실행하면 예제 테이블이 만들어 진다.
처음 Database Configuration Assistant를 실행하고 sql*plus 로 접속하여 DML을 하면 잘 작동한다. 그러나 컴퓨터를 다시 부팅하면 실행이 안된다. 그것은 DB가 shutdown 된 상태이기 때문이다.
ORA-01034: ORACLE not available ORA-27101: shared memory realm deos not exist
DB를 startup 시켜 주어야 한다.
cmd 창에서 sqlplus /nolong 혹은 sqlplus "/as sysdba"로 접속을 한다. 그 뒤에 SQL> startup 한 뒤에 다시 원하는 user로 접속을 시도한다. SQL> connect scott/tiger
한 가지 문제점이 발생할 수도 있다. select * from emp; 를 했는데, 아무 값이 나오지 않을 수도 있다.
그것은 현재 윈도우의 날짜가 한글 형식으로 되어 있기 때문이다. 그래서 오라클 10g도 날짜가 한글형식으로 되어 있기 때문에, 만약,
1. NLS_DATE_FORMAT 변경 SQL> select sysdate from dual; SQL> alter session set nls_date_format ='DD-MON-YY'; SQL> select sysdate from dual;
2. 시스템 언어 변경 SQL> alter session set nls_language = 'AMERICAN'; SQL> select sysdate from dual;
3. 아예 입력 구문을 바꾸기 SQL> alter session set nls_date_format = 'DD-MON-YY'; SQL> INSERT INTO EMP VALUES (7369, 'SMITH', 'CLERK', 7902, '17-12월-80', 800, NULL, 20);
오늘 처음으로 혼자 공부를 해보았는데, 하루에 1시간 아니 30분씩이라도 꾸준히 공부해야 할 것 같다.
