Oracle XE 사용하기

Database 2013.11.21 17:09 Posted by 파란크리스마스
출처 : [ORACLE] 11g XE 버전 설치 후에 샘플 사용자/DB 설정하기

Oracle 서버 실행

oradim -startup -sid xe

Oracle 서버 종료

oradim -shutdown -sid xe

접속확인

tnsping xe

sqlplus 접속하기

sqlplus system/pass@xe

Demo Database(scott) 만들기

CREATE USER scott IDENTIFIED BY tiger DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;
@C:\oraclexe\app\oracle\product\11.2.0\server\rdbms\admin\scott.sql

오라클 scott 계정 활성화 하기

alter user scott identified by tiger account unlock;

Oracle Character set 변경

Database 2013.07.22 16:23 Posted by 파란크리스마스

출처 : Oracle Character set 변경
오라클 캐릭터셋 확인및 변경

캐릭터 셋 설정 확인

select name, value$ from props$ where name in ('NLS_LANGUAGE', 'NLS_TERRITORY', 'NLS_CHARACTERSET');

SQL> select name, value$ from props$ where name in ('NLS_LANGUAGE', 'NLS_TERRITORY', 'NLS_CHARACTERSET');

NAME                              VALUE$
--------------------------------- ---------------------------------
NLS_LANGUAGE                      AMERICAN
NLS_TERRITORY                     AMERICA
NLS_CHARACTERSET                  AL32UTF8

KO16MSWIN949 으로 변경

SQL> UPDATE SYS.PROPS$ SET VALUE$='KOREAN' WHERE NAME='NLS_LANGUAGE';

1 row updated.

SQL> UPDATE SYS.PROPS$ SET VALUE$='KOREA' WHERE NAME='NLS_TERRITORY';

1 row updated.

SQL> UPDATE SYS.PROPS$ SET VALUE$='KO16MSWIN949' WHERE NAME='NLS_CHARACTERSET';

1 row updated.

SQL> commit;

Commit complete.

오라클 다시 시작

C:\>oradim -shutdown -sid xe

C:\>oradim -startup -sid xe

클라이언트의 캐릭터셋 변경 방법

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\오라클홈이름\NLS_LANG의 값을 KOREAN_KOREA.KO16MSWIN949로 변경

오라클 클라이언트 수동설치

Database 2012.12.07 12:49 Posted by 파란크리스마스
set oracle_home=C:\oracleclient\instantclient_11_2
set TNS_ADMIN=%oracle_home%
set path=%oracle_home%;%path%
set NLS_LANG=KOREAN_KOREA.KO16MSWIN949
"C:\Program Files\Quest Software\Toad for Oracle 11.6\toad.exe"

 

오라클 계정 추가, 수정, 삭제, 8080포트 번경

Database 2011.10.21 23:43 Posted by 파란크리스마스

출처 : http://thlife.net/557
http://jonelove.tistory.com/entry/Oracle-XE-%EC%9B%B9-%ED%8F%AC%ED%8A%B8-8080-%EB%B3%80%EA%B2%BD%ED%95%98%EA%B8%B0

테이블 스페이스 생성

CREATE TABLESPACE user1_data
DATAFILE 'C:\oraclexe\app\oracle\oradata\XE\USER1_DATA' SIZE 100M AUTOEXTEND ON NEXT 20M MAXSIZE 1024M;

오라클 계정 추가, 수정, 삭제

계정 추가

create user user01 identified by user0110
default tablespace user1_data temporary tablespace temp;

권한 부여

grant connect, resource to user01;
grant dba to user01;

계정 삭제

drop user user01 cascade;

암호 변경

alter user user01 identified by user0111;

관리자용 8080 포트 변경

현재 포트 확인

SELECT dbms_xdb.getHttpPort() FROM DUAL;

포트 변경

EXEC DBMS_XDB.SETHTTPPORT(9090);

백업

exp user01/user01@ubitech owner=user01 statistics=none file=user01.dmp

복원

출처 : http://www.rudaks.co.kr/bbs/view.jsp?seq=1965&bbs=db&PageNo=1

D:\>imp user01/user01 full=y fromuser=user01 touser=user01 file=user01.dmp

Export file created by EXPORT:V11.02.00 via conventional path
import done in KO16MSWIN949 character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)

D:\>imp user01/user01 fromuser=user01 touser=user01 file=user01.dmp




1. 다운로드(mysql-5.5.12-win32.zip)
http://dev.mysql.com/downloads/mysql/5.5.html

2. 압축풀기
압축폴더 : C:\mysql\mysql-5.5.12-win32

3. mysql.ini 파일 작성
경로 : C:\mysql\mysql-5.5.12-win32\mysql.ini
----------------------------------------------------
[mysqld]
port=2233
basedir=C:\mysql\mysql-5.5.12-win32
datadir=C:\mysql\mysql-5.5.12-win32\data
character-set-server=utf8
collation-server=utf8_general_ci

[mysql]
default-character-set=utf8

[client]
default-character-set=utf8
----------------------------------------------------

4. MySQL 서버 NT 서비스에 등록
- 명령 프롬프트 관리자 권한으로 실행

---------------------------------------------------- 
C:\Windows\system32>cd C:\mysql\mysql-5.5.12-win32

C:\mysql\mysql-5.5.12-win32>bin\mysqld.exe --install "MySQL 5.5.12" --defaults-file=C:\mysql\mysql-5.5.12-win32\mysql.ini
Service successfully installed.
----------------------------------------------------



5. 서비스 시작하기

----------------------------------------------------
C:\mysql\mysql-5.5.12-win32>net start "MySQL 5.5.12"
MySQL 5.5.12 서비스를 시작합니다..
MySQL 5.5.12 서비스가 잘 시작되었습니다.
----------------------------------------------------

6. 데이터베이스 접속

----------------------------------------------------
mysql -h localhost -u root -P 2233
----------------------------------------------------

7. 데이터베이스 생성

----------------------------------------------------
mysql> create database db_test;
Query OK, 1 row affected (0.00 sec)
----------------------------------------------------

8. 사용자 추가

----------------------------------------------------
mysql> use mysql;
Database changed
mysql> create user 'test'@'%' identified by 'test00';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on *.* to 'test'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql>
----------------------------------------------------

JDBC 접속 테스트

----------------------------------------------------
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class JDBCConnectTest {

 public static void main(String[] args) throws Exception {
  Connection conn = null;
  
  String jdbc_driver = "org.gjt.mm.mysql.Driver";
  String jdbc_url = "jdbc:mysql://192.168.0.62:2233/db_test";
  
  try {
   Class.forName(jdbc_driver);
   conn = DriverManager.getConnection(jdbc_url, "test", "test00");
   System.out.println(conn);   
   conn.close();
  } catch (SQLException e) {
   e.printStackTrace();
  }
 }
}
----------------------------------------------------

NT 서비스 지우기
- 명령 프롬프트 관리자 권한으로 실행

----------------------------------------------------
C:\mysql\mysql-5.5.12-win32>bin\mysqld.exe --remove "MySQL 5.5.12"
Service successfully removed.
----------------------------------------------------

Oracle - foreign key 생성

Database 2010.05.20 11:13 Posted by 파란크리스마스

CREATE TABLE ERD_EMP (
  EMP_ID         NUMBER            NOT NULL,
  EMP_NAME       VARCHAR2(256)         NULL,
  EMP_ADDRESS    VARCHAR2(256)         NULL,
  EMP_TEL        VARCHAR2(256)         NULL,
  DEPT_ID        NUMBER                NULL,
  CONSTRAINT PK_ERD_EMP PRIMARY KEY(EMP_ID)
);

CREATE TABLE ERD_DEPT (
  DEPT_ID      NUMBER            NOT NULL,
  DEPT_NAME    VARCHAR2(256)         NULL,
  CONSTRAINT PK_ERD_DEPT PRIMARY KEY(DEPT_ID)
);

ALTER TABLE ERD_EMP ADD
(
    CONSTRAINT FK_DEPTNO
    FOREIGN KEY ( DEPT_ID )
        REFERENCES ERD_DEPT ( DEPT_ID )
);


 

Oracle Tip - 계정에 디버깅 권한 부여하기

Database 2010.04.20 00:22 Posted by 파란크리스마스


- 오류 메시지
ORA-01031: insufficient privileges
ORA-06512: at "SYS.PBSDE", line 78
ORA-06512: at "SYS.DBMS_DEBUG", line 224
ORA-06512: at line 2

debug connect session
debug any procedure

- 권한 부여
GRANT DEBUG CONNECT SESSION TO "SCOTT";
GRANT DEBUG ANY PROCEDURE TO "SCOTT";

[Oracle] ORA-28000: the account is locked 해결방법

Database 2010.03.23 00:00 Posted by 파란크리스마스
- 출처

http://hyunayarn.tistory.com/9

- 오류 내용

- Lock 유저 확인 질의문

SELECT USERNAME,
        ACCOUNT_STATUS,
        TO_CHAR(LOCK_DATE,'YYYY.MM.DD HH24:MI') LOCK_DATE          
  FROM DBA_USERS
 WHERE USERNAME = 'SCOTT'

- 질의결과

USERNAME                       ACCOUNT_STATUS                   LOCK_DATE      
------------------------------ -------------------------------- ----------------
SCOTT                          EXPIRED & LOCKED                 2010.03.22 23:45
1 row selected.

- Lock 해제

ALTER USER scott ACCOUNT UNLOCK;

- 확인1

SELECT USERNAME, ACCOUNT_STATUS
  FROM DBA_USERS
 WHERE USERNAME = 'SCOTT';

USERNAME                       ACCOUNT_STATUS                 
------------------------------ --------------------------------
SCOTT                          EXPIRED                        
1 row selected.

- 암호 설정

ALTER USER scott IDENTIFIED BY tiger;

- 확인2

SELECT USERNAME, ACCOUNT_STATUS
  FROM DBA_USERS
 WHERE USERNAME = 'SCOTT';

USERNAME                       ACCOUNT_STATUS                 
------------------------------ --------------------------------
SCOTT                          OPEN                           
1 row selected.

MySQL - SQLGate로 SSH 터널링을 통해 연결하기

Database 2010.02.17 12:10 Posted by 파란크리스마스
SQLGate2010 For MySQL을 통해서 SSH 터널링을 통해 MySQL 연결하기

1. [SSH 터널링] 체크를 선택하여 SSH 텝을 활성화 시킨다.


2. SSH 접속 정보를 입력한다.


3. [SSH 연결 테스트] 버튼을 선택하여 SSH 연결이 접근이 되는지 확인한다. (옵션)

4. 처음 SSH 서버에 접근시 확인창에서 [OK] 버튼을 선택한다.


5. SSH 연결을 확인 한다.


6. [로그인] 텝으로 이동한다.


7. 로그인 정보를 입력한다.


8. [연결 테스트] 버튼을 선택하여 연결이 잘 되는지 테스트 해본다. (옵션)

9. [연결하기] 버튼을 선택하여 데이터베이스엔 연결한다.

출처 http://ask.nate.com/qna/view.html?n=4636134

MySQL 4.1.21 설치후 접속 오류 해결

해당오류
- ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
- ERROR 2003 <HY000>:Can't connect to Mysql server on 'localhost'<10061>
- mysqladmin: connect to server at 'localhost' failed
- error: 'Can't connect to MySQL server on 'localhost' (10061)'

Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 4.1.21-community-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use mysql;
Database changed
mysql> update user set password=password('sqldba') where user='root';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 2  Changed: 0  Warnings: 0

mysql> delete from user where user='';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql>

TAG 28000, MySQL

MySQL - 사용자, 그룹, 데이터베이스, 테이블 관리

Database 2009.08.24 01:08 Posted by 파란크리스마스

출처 : http://www.gpgstudy.com/gpgiki/MySQL%EC%97%90%EC%84%9C%20%EC%82%AC%EC%9A%A9%EC%9E%90%EC%99%80%20%EB%8D%B0%EC%9D%B4%ED%84%B0%EB%B2%A0%EC%9D%B4%EC%8A%A4%20%EB%A7%8C%EB%93%A4%EA%B8%B0

0. 커맨드 모드 접근

C:\mysql-5.1.37-win32\bin>mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.37-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

1. 데이터베이스 관리

1-1. 데이터베이스 생성

mysql> create database db_study;
Query OK, 1 row affected (0.00 sec)

1-2. 데이터베이스 목록 조회

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db_study           |
| mysql              |
| test               |
+--------------------+

1-3. 데이터베이스 삭제

mysql> drop database db_study;
Query OK, 0 rows affected (0.09 sec)

2. 사용자 관리

2-1. 사용자 추가


mysql> INSERT INTO user (Host,User,Password,
    -> Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv,
    -> Reload_priv,Shutdown_priv,Process_priv,File_priv,Grant_priv,References_priv,
    -> Index_priv,Alter_priv,Show_db_priv,Super_priv,Create_tmp_table_priv,
    -> Lock_tables_priv,Execute_priv,Repl_slave_priv,Repl_client_priv,Create_view_priv
    -> ,Show_view_priv,Create_routine_priv,Alter_routine_priv,Create_user_priv,
    -> Event_priv,Trigger_priv)
    -> values ('localhost', 'scott', PASSWORD('tiger'),
    ->  'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y',
    ->  'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y');

mysql> INSERT INTO user (Host,User,Password,
    -> Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv,
    -> Reload_priv,Shutdown_priv,Process_priv,File_priv,Grant_priv,References_priv,
    -> Index_priv,Alter_priv,Show_db_priv,Super_priv,Create_tmp_table_priv,
    -> Lock_tables_priv,Execute_priv,Repl_slave_priv,Repl_client_priv,Create_view_priv
    -> ,Show_view_priv,Create_routine_priv,Alter_routine_priv,Create_user_priv,
    -> Event_priv,Trigger_priv)
    -> values ('%', 'scott', PASSWORD('tiger'),
    ->  'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y',
    ->  'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y');
Query OK, 1 row affected, 3 warnings (0.00 sec)


2-2. 사용자 조회

mysql> select host, user, password from user;
+-----------+-------+-------------------------------------------+
| host      | user  | password                                  |
+-----------+-------+-------------------------------------------+
| localhost | root  |                                           |
| 127.0.0.1 | root  |                                           |
| localhost |       |                                           |
| %         | scott | *F2F68D0BB27A773C1D944270E5FAFED515A3FA40 |
| localhost | scott | *F2F68D0BB27A773C1D944270E5FAFED515A3FA40 |
+-----------+-------+-------------------------------------------+

4 rows in set (0.00 sec)

3. 권한부여

3-1. 해당 DB의 사용자에 대한 권한 부여

mysql> INSERT INTO db (Host, Db, User, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv)
    -> VALUES('%', 'db_study', 'scott', 'Y', 'Y', 'Y', 'Y','Y', 'Y');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO db (Host, Db, User, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv)
    -> VALUES('localhost', 'db_study', 'scott', 'Y', 'Y', 'Y', 'Y','Y', 'Y');
Query OK, 1 row affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)


3-2. 조회

mysql> select host, db, user from db;
+-----------+----------+-------+
| host      | db       | user  |
+-----------+----------+-------+
| %         | db_study | scott |
| %         | test     |       |
| %         | test\_%  |       |
| localhost | db_study | scott |
+-----------+----------+-------+

3 rows in set (0.00 sec)

4. 테이블 관리

4-1. 테이블 생성



-end


TAG MySQL

MySQL 수동설치

Database 2009.08.23 16:08 Posted by 파란크리스마스

1. MySQL 다운받기 

경로 : http://dev.mysql.com/downloads/mysql/5.1.html

2. 압축풀기

7z.exe x mysql-noinstall-5.1.37-win32.zip -oC:\

3. C:\Windows\mysql.ini 작성

[mysqld]
# set basedir to your installation path
basedir=C:\mysql-5.0.37-win32
# set datadir to the location of your data directory
datadir=C:\mysql-5.0.37-win32\data

4. 윈도우 서비스에 등록 (관리자 권한으로 실행)


C:\mysql-5.1.37-win32\bin>mysqld.exe --install MySQL --defaults-file=%WINDIR%\mysql.ini
Service successfully installed.

5. 버전확인

C:\mysql-5.1.37-win32\bin>mysqladmin version
mysqladmin  Ver 8.42 Distrib 5.1.37, for Win32 on ia32
Copyright 2000-2008 MySQL AB, 2008 Sun Microsystems, Inc.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version          5.1.37-community
Protocol version        10
Connection              localhost via TCP/IP
TCP port                3306
Uptime:                 52 sec

Threads: 1  Questions: 1  Slow queries: 0  Opens: 15  Flush tables: 1  Open tabl
es: 8  Queries per second avg: 0.19

6. 데이터베이스 목록 조회

C:\mysql-5.1.37-win32\bin>mysqlshow
+--------------------+
|     Databases      |
+--------------------+
| information_schema |
| test               |
+--------------------+

TAG MySQL