Diff for "Oracle" - BioHackersNet

Differences between revisions 3 and 11 (spanning 8 versions)

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

특징

Tablespace

사용 팁

사용자생성

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;  # 유저가 소유한 모든 테이블 보기

Oracle 시작과 종료

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

CharacterSet변경문제, 혹은 [http://database.sarang.net/?inc=read&aid=18388&criteria=oracle&subcrit=&id=&limit=20&keyword=character&page=3 관련질답]

# 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

CategoryProgram

-  ⇤ ← Revision 3 as of 2005-11-11 20:05:09 → 
  Size: 1783
  Editor: 203
  Comment:
+   ← Revision 11 as of 2007-09-19 14:18:09 → ⇥
  Size: 2033
  Editor: 59
  Comment:
-Deletions are marked like this.
+Additions are marked like this.
 Line 5:
+ * [http://www.oracleclub.com/profile/OracleFirst.jsp 오라클공부를 처음 시작하시는 분들께...]
-Line 6:
+Line 7:
-[Python] interface
 * DCOracle2 : http://www.zope.org/Members/matt/dco2
 * cx_oracle : http://www.computronix.com/utilities.shtml
+[Python]으로 연동하여 쓸 경우, [cx_Oracle] 사용.
-Line 10:
+Line 9:
-CharacterSet변경문제
+== 특징 ==
 1. WikiPedia:Tablespace

== 사용 팁 ==
사용자생성
-Line 12:
+Line 15:
-SQL> alter system enable restricted session;

System altered.

SQL> alter system set job_queue_processes = 0;

System altered.

SQL> alter system set aq_tm_processes = 0;

System altered.

SQL> alter database open;

Database altered.

SQL> alter database character set WE8ISO8859P1;

Database altered.

SQL> select VALUE$ from PROPS$ where NAME = 'NLS_CHARACTERSET';

VALUE$
--------------------------------------------------------------------------------
WE8ISO8859P1
+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;  -- 객체(테이블,뷰,시퀀스,프로시저)에 권한부여
-Line 39:
+Line 22:
-혹은 [http://database.sarang.net/?inc=read&aid=18388&criteria=oracle&subcrit=&id=&limit=20&keyword=character&page=3 관련질답]
+자주쓰이는 쿼리문
{{{
SQL>select table_name from user_tables;  # 유저가 소유한 모든 테이블 보기
}}}

Oracle 시작과 종료
{{{
# su - oracle
$ export ORACLE_SID=ORANLRI
$ sqlplus "/as sysdba"
> startup
> exit
}}}
-Line 42:
+Line 37:
-일반적인 접속절차 및 인코딩변경
+CharacterSet변경문제, 혹은 [http://database.sarang.net/?inc=read&aid=18388&criteria=oracle&subcrit=&id=&limit=20&keyword=character&page=3 관련질답]