Oracle - BioHackersNet

가장 널리 알려진 상업용 RelationalDatabase Management System

특징

Tablespace

설치

On Ubuntu

원격 접속방법

참고 : Oracle 접속문제

Oracle client 프로그램을 설치한다.
$ORACLE_HOME/network/admin 디렉토리에 tnsnames.ora 파일을 생성한다.

tnsnames.ora 예제

SidExample =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ipaddress )(PORT = 1521))
    (CONNECT_DATA =
      (SID = SidExample)
    )
  )

sqlplus를 이용한 접속

sqlplus id/password@sid

사용 팁

사용자생성

SQL>CONN SYSTEM/MANAGER       -- DBA Role이 있는 유저로 접속합니다.
SQL>CREATE USER yong IDENTIFIED BY 1111;    -- USER를 다시 생성합니다.
 사용자가 생성되었습니다.
SQL>GRANT CREATE USER, ALTER USER, DROP USER TO yong WITH  ADMIN  OPTION;  -- 권한부여
SQL>GRANT SELECT, INSERT ON some_object TO yong WITH  GRANT  OPTION;  -- 객체(테이블,뷰,시퀀스,프로시저)에 권한부여

자주쓰이는 쿼리문

SQL>select table_name from user_tables;  # 유저가 소유한 모든 테이블 보기
SQL>select sequence_name from user_sequences; # 유저가 소유한 모든 시퀀스 보기

Oracle 시작과 종료

# su - oracle
$ export ORACLE_SID=ORANLRI
$ sqlplus "/as sysdba"
> startup
> shutdown immediate
> exit

백업과 복구

exp id/password@sid file=my.dump  #해당 사용자의 모든 테이블
exp id/password@sid file=my.dump tables=kmaregs,lifekind   #모든 테이블
imp id/password@sid file=my.dump

CharacterSet변경문제, 혹은 관련질답

# su - oracle
$ export ORACLE_SID=sid
$ echo $ORACLE_SID
sid
$ sqlplus /nolog
> connect / as sysdba
> shutdown
> startup mount exclusive
> alter database enable restricted session;
> alter database set job_queue_processes = 0;
> alter database open;
> alter database character set internal_use utf8;
> alter database national character set internal_use utf8;
> select * from nls_database_parameters where parameter like '%CHARACTERSET%';

> update sys.props$ set value$='UTF-8' where name='NLS_CHARACTERSET'; 
> update sys.props$ set value$='UTF-8' where name='NLS_NCHAR_CHARACTERSET';
> update sys.props$ set value$='UTF-8' where name='NLS_LANGUAGE';

> commit;
> shutdown immediate
> startup
> exit

테이블스페이스 용량확인

# su - oracle
$ sqlplus "as sysdba"
> SELECT u.tablespace_name "TMS_DAT01",
u.bytes / 1048576 "크기(MB)",
(u.bytes - sum(nvl(f.bytes,0))) / 1048576 "사용됨(MB)",
(sum(nvl(f.bytes,0))) / 1048576 "남음(MB)",
trunc((sum(nvl(f.bytes,0)) / u.bytes) * 100,2) "남은 %"
FROM DBA_FREE_SPACE f, DBA_DATA_FILES u
WHERE f.file_id(+) = u.file_id
GROUP BY u.tablespace_name, u.file_name, u.bytes
ORDER BY u.tablespace_name;

DBA권한 주거나 뺐기

grant sysdba to user;
revoke sysdba from user;

테이블 수 체크 후, 파일에 쓰기

SET VERIFY OFF
SET LINESIZE 200
SET PAGESIZE 500
SET FEEDBACK OFF
COL SPOOL_NAME FORMAT A30
COL SPOOL_NAME NEW_VALUE S
COL NAME1 NEW_VALUE YYYYMM
COL NAME2 NEW_VALUE THREAD
SET TERMOUT OFF

SELECT host_name||'_count_tab_'||to_char( sysdate, 'yyyymmdd')||'.txt' spool_name, t
o_char(sysdate, 'yyyymm') name1, thread# name2 FROM v$instance;

SET TERMOUT ON
SPOOL &S

select count(segment_type) "COUNT_TABLE", TABLESPACE_NAME from dba_segments
where segment_type = 'TABLE'
group by tablespace_name;

SELECT u.tablespace_name "TMS_DAT01",
u.bytes / 1048576 "Size(MB)",
(u.bytes - sum(nvl(f.bytes,0))) / 1048576 "Used(MB)",
(sum(nvl(f.bytes,0))) / 1048576 "Free(MB)",
trunc((sum(nvl(f.bytes,0)) / u.bytes) * 100,2) "Free %"
FROM DBA_FREE_SPACE f, DBA_DATA_FILES u
WHERE f.file_id(+) = u.file_id
GROUP BY u.tablespace_name, u.file_name, u.bytes
ORDER BY u.tablespace_name;


SPOOL OFF

테이블스페이스가 꽉 찼을 때

ORA-01653: THE_TABLE_NAME 테이블을 4(으)로 PF_DAT 테이블스페이스에서 확장할 수 없습니다

해결 방법 3가지

TableSpace를 resize를 통해서 늘여준다
TableSpace에 Datafile을 추가한다.
TableSpace의 AutoExtend를 off일 경우 on을 변경한다.

select file_name, tablespace_name, bytes, autoextensible from dba_data_files where tablespace_name='MY_TABLESPACE'; #filename 확인 후,

#1
alter database datafile 'c:\oracle\oradata\ora92\test_02.dbf' resize 5M;

#2
alter tablespace app_data add datafile '/disk1/app04.dbf' size 200M autoextend on next 10M maxsize 500M;
 
#3
alter database datafile 'c:\oracle\oradata\ora92\test_02.dbf' autoextend on next 1M

유닉스환경에서의 환경설정

SQL> define _editor=vim
SQL> ed  --> to the vim editor
SQL> /   --> latest sql query

테이블 변경

SQL> create table test (
  2  name varchar(10)
  3  );
SQL> alter table test add(osy number(10));
SQL> alter table test rename column osy to osy79;
SQL> alter table test modify(osy79 varchar(10));
SQL> alter table test drop(osy79);

CategoryProgram