반응형

OS 변경설치를 위해 개인적으로 테스트하기 위해 작성한다.

 

1. 테스트 데이터 베이스 생성 및 테스트 계정 생성

먼저 테스트용 서버에 PostgreSQL 13을 설치, 신규 데이터베이스 생성과 신규 계정을 생성한다.

만약 순서가 잘못된다면 권한과 관련해서 조금 많이 꼬여버리니까 주의해야한다. 모든 내용은 psql 명령어로 수행했다. 

# psql 명령어를 이용하여 postgres 계정에 접속
[postgres@82559cbe0973 data]$ psql -h localhost -p 18081
psql (13.16)
Type "help" for help.
postgres=#

# 계정 및 비밀번호 생성
postgres=# create user [계정명] with password '[비밀번호]';
CREATE ROLE

# 데이터베이스 생성 후, 계정 권한 부여
postgres=# create database [데이터베이스명];
CREATE DATABASE

#해당 계정에 해당 데이터베이스의 모든 권한 부여
postgres=# grant all privileges on database [데이터베이스명] to [계정명];
GRANT

#생성 후 exit 로 psql 종료, 신규로 생성한 계정으로 접속
postgres=# exit
[postgres@82559cbe0973 data]$ psql -U [계정명] -d [데이터베이스명] -h localhost -p 18081
psql (13.16)
Type "help" for help.

kimfishs=>

 

2. 신규 테이블 생성 및 데이터 입력

여기서부터는 일반 SQL 쿼리와 크게 다르지 않다. 사실 위의 grant 명령어들도 SQL 이나 다를바 없긴 하지만 뭐...

# 테이블 생성, 해당 테이블에 접속할 계정으로 생성해야 한다
kimfishs=> create table [테이블명] ( [컬럼명] varchar(50) , ... [컬럼명n] varchar (50)) ;
CREATE TABLE

# 테이블에 데이터 입력
kimfishs=> insert into [테이블명] ( id , name ) values ( 'kimfish', '리고기');
INSERT 0 1

# 테이블 데이터 조회
kimfishs=> select * from [테이블명] ;
   id    |  name
---------+--------
 kimfish | 리고기
(1 row)

 

처음에 postgres로 테이블 생성하고 이후에 DBEaver로 신규생성계정으로 접속하니 권한이 없어 select도 못하는 상황이 존재했다. 이후에 권한문제임을 깨닫고 접속 자체를 신규계정으로 접속, create table 명령어를 수행하는 방식으로 재설정했다.

 

3. PostgreSQL 데이터 베이스 백업 및 복구

임시데이터들도 모두 갖춰졌겠다, 이젠 해당 데이터베이스 설정 및 파일들을 모두 tar 압축백업을 할 예정이다.

그리고 신규 서버에 OS 설치 후 이걸 고대로 갖다 놓았을 때 정상적으로 접속이 되는지 확인할 것이다.

 

아마도 PostgreSQL 버전이 같다면 큰 문제 없이 수행되리라 예상하고 있다.

 

일단 PostgreSQL 설정파일 및 데이터베이스 파일이 위치해있는 디렉토리로 이동, tar 압축을 수행한다.

# PostgreSQL의 기본 설정 디렉토리는 /var/lib/pgsql 에 위치해있다
[root@82559cbe0973 lib]# cd /var/lib
[root@82559cbe0973 lib]# ls
alternatives  dnf    misc   private  rpm-state  systemd
dbus          games  pgsql  portables       rpm      selinux    tpm
[root@82559cbe0973 lib]# tar -cvf pgsql_back.tar pgsql
[root@82559cbe0973 lib]# ls
alternatives  dnf    misc   pgsql_back.tar  private  rpm-state  systemd
dbus          games  pgsql  portables       rpm      selinux    tpm

생성된 tar파일을 고대로 다운로드 한 후, 신규 서버에 업로드한다.

 

https://kimfish.tistory.com/361

 

[PostgreSQL13] PostgreSQL 13 설치하기

Docker에 설치된 RockyLinux 8.10 버전에 PostgreSQL 13을 설치 후 데이터 이관 테스트를 진행하려 한다.Docker에서 PostgreSQL 13을 설치할 때에는 몇가지 추가로 설정해줘야 할 것들이 있어 정리할 겸 작성한

kimfish.co.kr

이 내용을 참조하여 신규 서버에 PostgreSQL을 설치한 후, tar 파일을 업로드 한 이후, 다음의 과정을 수행한다.

#자세히 보면 서버가 다르다
[root@93e37f9f73ea lib]# ls
alternatives  dbus  games  misc          private  rpm-state  systemd
dav           dnf   httpd  pgsql_back.tar  portables   rpm      selinux    tpm
[root@93e37f9f73ea lib]#
[root@93e37f9f73ea lib]# tar -xvf pgsql_back.tar
pgsql/
pgsql/.bash_profile
pgsql/.bash_history
pgsql/.psql_history
pgsql/13/
pgsql/13/data/

(중략)

[root@93e37f9f73ea lib]# ls
alternatives  dbus  games  misc   pgsql_back.tar  portables  rpm        selinux  tpm
dav           dnf   httpd  pgsql      private    rpm-state  systemd
[root@93e37f9f73ea lib]# cd pgsql/13/data
[root@93e37f9f73ea data]# ls
PG_VERSION        log           pg_ident.conf  pg_replslot   pg_stat_tmp  pg_wal                postmaster.opts
base              pg_commit_ts  pg_logical     pg_serial     pg_subtrans  pg_xact               postmaster.pid
current_logfiles  pg_dynshmem   pg_multixact   pg_snapshots  pg_tblspc    postgresql.auto.conf
global            pg_hba.conf   pg_notify      pg_stat       pg_twophase  postgresql.conf

# 이전 서버에서 접속포트를 18081로 설정했는데, 그대로 잘 압축해제 되었는지 확인
# 의도적으로 18081 에서 8084로 변경후 실행해본다.

[postgres@93e37f9f73ea data]$ vi postgresql.conf

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '*'          # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
port = 8084                             # (change requires restart)


# PostgreSQL 서버 실행
[postgres@93e37f9f73ea data]$ /usr/pgsql-13/bin/pg_ctl -D /var/lib/pgsql/13/data start
pg_ctl: another server might be running; trying to start server anyway
waiting for server to start....2024-08-21 07:36:04.797 UTC [6143] LOG:  redirecting log output to logging collector process
2024-08-21 07:36:04.797 UTC [6143] HINT:  Future log output will appear in directory "log".
 done
server started

 

뭐 그런데 결과가 당연하게도, 잘 구동 되었다.

 

문제는 기존 PostgreSQL 설정파일의 위치나 데이터베이스 파일, 테이블 스페이스 경로 등이 커스텀화 되지 않았는지 파악하는것이 가장 중요하다.

다음에는 직접 PostgreSQL 서버에 접속 후, 각종 테이블스페이스와 데이터베이스 설정 위치들을 쿼리를 통해 알아볼 예정이다.

 

반응형
블로그 이미지

김생선

세상의 모든것을 어장관리

,
반응형

얼마전에 docker linux에 PostgreSQL13 Server를 설치하였고 DBEaver를 통한 접속테스트를 수행했다.

이번에는 신규 계정 생성 및 TableSpace 생성, 기본적인 임시 테이블 서너개를 생성 후 백업 테스트를 위한 준비를 수행해볼 예정이다.

DB의 admin 계정으로 접속해서 직접 쿼리로 관리해도 되지만, 이번에는 새로운 방법(?)으로 해보려 했다.

 

아래의 순서는 다음과 같다.

데이터베이스 설정 - 테이블스페이스 설정 - 계정 생성 및 권한 설정

 

1. psql 접속 

docker linux에 접속 후, postgres 계정으로 변경한다.

이후, 설정에 따라 다르지만 아래의 명령어를 통해 psql에 접속한다.

# PostgreSQL 이 기본 포트로 동작하는 경우
[postgres@82559cbe0973 data]$ psql

#PostgreSQL 이 다른 포트로 동작하는 경우
[postgres@82559cbe0973 data]$ psql -h localhost -p [포트]

 

2. 데이터베이스 설정

이제 막 PostgreSQL을 설치했으므로 기본 데이터베이스 외에는 존재하지 않으므로, 데이터베이스 생성부터 수행한다.

다음과 같이 명령어를 입력한다.

create database [DB명];

\l 명령어는 생성된 database를 확인하는 명령어이다.

postgres=# create database kimfish_database;
CREATE DATABASE
postgres=# \l
                                 List of databases
       Name       |  Owner   | Encoding | Collate |  Ctype  |   Access privileges
------------------+----------+----------+---------+---------+-----------------------
 kimfish_database | postgres | UTF8     | C.UTF-8 | C.UTF-8 |
 postgres         | postgres | UTF8     | C.UTF-8 | C.UTF-8 |
 template0        | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
                  |          |          |         |         | postgres=CTc/postgres
 template1        | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
                  |          |          |         |         | postgres=CTc/postgres
(4 rows)

 

3. DB 계정 생성 및 권한 부여

#계정 생성

postgres=# create user [계정 아이디] with password '[비밀번호]';
CREATE ROLE

#계정에 데이터베이스 권한 부여
postgres=# grant connect on database [데이터베이스명] to [계정 아이디];
GRANT
postgres=# \l
                                 List of databases
       Name       |  Owner   | Encoding | Collate |  Ctype  |   Access privileges
------------------+----------+----------+---------+---------+-----------------------
 kimfish_database | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =Tc/postgres         +
                  |          |          |         |         | postgres=CTc/postgres+
                  |          |          |         |         | kimfish=c/postgres
 postgres         | postgres | UTF8     | C.UTF-8 | C.UTF-8 |
 template0        | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
                  |          |          |         |         | postgres=CTc/postgres
 template1        | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
                  |          |          |         |         | postgres=CTc/postgres
(4 rows)

 

여기까지 기본적인 PostgreSQL 설정이 모두 완료되었다.

DBEaver를 통해 접속했을 때 정상적으로 보여지는 것 또한 확인했다.

반응형
블로그 이미지

김생선

세상의 모든것을 어장관리

,
반응형

고객사에서 MariaDB를 설치하고 있다. 그러던 와중, 잘 구동되던 Tomcat을 재구동하게 되었는데 오류메시지 "Access denied for user '계정명'@'localhost'(using password:YES)" 라는 오류가 발생한다. 이전까지는 재구동하는데 문제가 없었고, 오류가 생길만한 작업은 약 서너가지 정도가 짚히는 상황이었다. 현재 WAS 설정으로는, server.xml 및 context.xml 내에서 JNDI를 해당 MariaDB로 설정해둔 상태였다.


먼저, MariaDB에 익숙하지 않은 나인지라 용어의 혼란이 조금 있을 것 같아 아래와 같은 내용으로 이해를 하고 있다.  MariaDB는 계정별로 두 개의 권한을 갖는 듯한 모습이었다. MariaDB의 계정 테이블을 쿼리해보면 Host라고 작성된 부분이 있는데, 여기에 localhost 및 % 정도로 권한(?)이 부여가 된다. localhost는 내부에서만 접속이 가능하고, %은 내/외부에서 접속이 모두 가능한 권한 정도로 이해하고 있다.


1. 원인파악

본론으로 돌아와서 검색해보니 뭐 당연하게도 해당 계정의 localhost 권한이 없다는 문구다. 그래서 MariaDB가 설치된 서버에 접속하여 아래의 명령어를 사용했다. 해당 명령어는 shell 에서 MariaDB에 접속하기 위한 명령어이다.


1
mysql -u 계정명 -p
cs

그런데 위의 tomcat과 마찬가지로 "Access denied for user '계정명'@'localhost'(using password:YES)" 오류가 발생한다. 그래서 MariaDB의 root계정으로 접속하려는데, root 권한 또한 마찬가지 오류가 발생한다. 


이래저래 구글링을 해보니, 다음과 같은 명령어를 찾게 되었다.

1
sudo /usr/bin/mysqladmin -u root password [비밀번호]
cs

해당 명령어는 서버의 root 권한으로 서버 내에 설치된 MariaDB의 root 비밀번호를 재설정하는 것이다. 이 이후에, mysql -u root -p 명령어로 접속하였고, localhost 계정을 다시 생성해주려 하였다.


1
MariaDB[(none)]> CREATE USER '계정명'@'localhost' IDENTIFIED BY '비밀번호';
cs

했더니, 아래와 같은 오류메시지가 출력된다.


1
ERROR 1396(HY000) : Operation CREATE USER failed for '계정명'@'localhost';
cs

뭔가 해서 봤더니 직접 생성이 불가능한 것 같다. 혹시나 싶어, 계정 테이블을 직접 쿼리해서 상태를 좀 보고자 했다.


1
2
MariaDB[(none)] > use sql; --Database를 변경하는 명령어
MariaDB[(mysql)] > SELECT * FROM user WHERE user='계정명';
cs

해당 명령어로 조회해보니 우리 DB에서 사용중인 계정 - Host(권한이라고 이해중)별 비밀번호가 각각 다른것이었다. A 계정의 % Host(권한)과 A 계정의 localhost Host(권한)과 비밀번호가 각기 다른 상황. 이러니 서버나 tomcat에서 접속할 때에는 Access Denied For User 에러가 발생했을 것이고, 개발자 PC의 DB툴로는 접속이 잘 되었겠지. 이제 원인이 파악된 상황이다.


1
2
3
4
5
6
7
8
9
10
+-------------------------------------------------------------------------------+
| Host      | User        | Password              | Select_priv  | Insert_priv  |
+-------------------------------------------------------------------------------+
| localhost | mariadb.sys |                       | N            | N            |
| localhost | root        | *E1C459C5 ~~~~~~~~~   | Y            | Y            |
| localhost | mysql       | invalid               | Y            | Y            |
| localhost |             |                       | N            | N            |
| %         | test        | *0B26A ~~~~~~~~~~~~~~~| N            | N            | 
| localhost | test        | *COB5F ~~~~~~~~~~~~~~~| N            | N            | 
+-------------------------------------------------------------------------------+

cs

* test라는 계정의 % Host 비밀번호는 암호화되어 *0B26A로 시작하는데, localhost는 *COB5F로 시작한다. 이러니 외부에서 접속하는 % Host는 기존 비밀번호로 접속이 되고, 같은 서버 내에 있는 tomcat은 기존 비밀번호로 접속이 안되겠지.



2. 문제해결


일단 원인은 알았다. test 계정의 % 비밀번호와 test 계정의 localhost 비밀번호가 다르기에 생긴 문제라는 것. 일단 localhost 비밀번호가 왜 저렇게 변경되었는지는 작업자들에게 물어보면 될 것이고, 접속이 되도록 수정을 해야겠다. 이럴땐 뭐다? test 계정의 localhost Host(권한)비밀번호를 기존에 사용중인 비밀번호로 Update 쳐주면 될 것이다.


1
MariaDB[(mysql)] > UPDATE user SET password=('비밀번호') WHERE user='계정명';
cs

했더니, 다음과 같은 에러가 발생한다.


1
ERROR 1348(HY000) : Column 'Password' is not updatable.
cs

않이; 뭐냐 대체 이건. 또다시 검색해보니 MariaDB 10.4 버전 이상은 user테이블을 직접관리하지 않고, 파일이나 뭐 그런식으로 관리하기 때문에 기존의 user테이블 update 방식은 사용할 수 없다고 한다. 그래서 또다시 뒤져보니 명령어로 직접세팅하는 방법이 있었다.


1
MariaDB[(mysql)] > set password for '계정명'@'localhost=password('비밀번호');
cs

했더니, Query OK, 0 rows affected (0.014sec) 메시지가 출력되었다. 그리고 다시 user 테이블을 확인해보았고, 해당 계정의 % Host와 localhost Host의 비밀번호가 동일한 것을 확인할 수 있었다.


1
2
3
4
5
6
7
8
9
10
+-------------------------------------------------------------------------------+
| Host      | User        | Password              | Select_priv  | Insert_priv  |
+-------------------------------------------------------------------------------+
| localhost | mariadb.sys |                       | N            | N            |
| localhost | root        | *E1C459C5 ~~~~~~~~~   | Y            | Y            |
| localhost | mysql       | invalid               | Y            | Y            |
| localhost |             |                       | N            | N            |
| %         | test        | *0B26A ~~~~~~~~~~~~~~~| N            | N            | 
| localhost | test        | *0B26A ~~~~~~~~~~~~~~~| N            | N            | 
+-------------------------------------------------------------------------------+

cs

이후, tomcat을 재구동하였더니 정상동작 하였다.



3. 후기


MariaDB를 직접적으로 설정하고 사용할 일이 없어 여러모로 삽질을 많이하게 되었다. 문제해결하면서 알게된 점을 아래에 간략하게 작성해보았다.

계정은 Host(권한)별로 password가 지정된다는 점이다. 위에서도 보여지듯, test 계정임에도 불구하고 로컬(같은 서버내)에서 접속가능한 비밀번호와 외부에서 접속 가능한 비밀번호가 각기 설정되어있다.


또한, 명령어의 password('비밀번호')는 자동으로(?) 인코딩되어 설정된다는 점이었다. 혹시나 싶어 구글링한 결과 그대로 작성해보았는데, 알아서 인코딩되어 저장되는 것을 보니 어딘가에 설정이 박혀있는게 아닐까 싶다. 뭐 복잡시럽게 sha256으로 인코딩해서 직접 박아넣는게 아니라 다행이다 싶다.


위의 상황이 발생하게 된 계기는, 하나의 서버를 여러부서가 공동으로 개발장비로 사용하는 중이었고, MariaDB를 우리팀에서 설치, 사용을 하고 있었다. 그러다가 타 부서에서 MariaDB를 사용 할 일이 생기게 되었고, 우리가 설치한 MariaDB에 접속이 안되니 멋대로 서버의 root 권한으로 test계정의 localhost 비밀번호를 바꿔버려 생긴 문제였다. 거기에, 우리팀 막내가 당일 MariaDB 설정과 관련하여 파일 권한이라거나 기타 설정들을 바꾸면서 MariaDB Service를 재구동하는 등 여러 변경점이 생기다보니 우리팀 내에서만 원인 찾기가 힘든 상황.


나의 경우에는 계정-host별 비밀번호가 다르게 적용되는 줄 몰랐던터라, 애꿎은 DB보안프로그램과 서버접속 보안프로그램 탓을 했었다. tomcat 재구동 작업을 하기 바로 전날 야간에 설치가 되었던터라, 이 부분이 문제인줄 알았던 것. 여러 작업적인 이슈가 겹치다보니 생긴 당연한 인재였다. 개발서버였기에 망정이지, 실 운영이 진행중인 운영서버였다면 초대형사고였을지도 모른다. 이러한 이유로, 무슨 작업이든 작업이 있을 경우에는 연관부서에 모두 noti를 해줘야한다는 것이다.

반응형
블로그 이미지

김생선

세상의 모든것을 어장관리

,
반응형

개발하다보면, 특정 컬럼의 네이밍을 조회하는 경우가 있다. 가령 A 라는 컬럼을 어떤 테이블에서 더 쓰는지. 뭐 이런 경우. Oracle은 all_tab_column인지 all_object를 쓰면 되긴 하는데 mysql은 처음이다. 하지만 간단하다.


1
SELECT * FROM information_schema.columns
cs



이렇게 해주면 된다. 개꿀.

반응형
블로그 이미지

김생선

세상의 모든것을 어장관리

,
반응형

DB 또는 시스템 작업을 하다보면 여러건에 대해 한번에 업데이트를 하는 경우가 많다. 뭐 그렇다치고 이번에는 MySQL 에서 서브쿼리를 이용하는 방법을 알아보자.


이번에 할 작업은 A_Table의 a_column의 데이터와 문자열을 합친 후, A_Table의 b_column에 업데이트를 해줄 일이 생겼다. 오라클과 다르게 한차례 더 가공을 해야 해서 손이 가는 편이지만 크게 어렵지는 않았다.


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
UPDATE 
    'A_Table' AS AliasA 
SET 
    AliasA.b_column = 
    (
    SELECT
        AliasC.a_column_text
    FROM
        (
        SELECT
            AliasB.a_column 
            , CONCAT('TEST_' , AliasB.a_column ) AS a_column_text //MySQL에서 문자열 합치기는 concat을 이용함
        FROM
            'A_Table' AS AliasB 
        ) AS AliasC
    WHERE 
        AliasC.a_column = AliasA.a_column 
    )  
WHERE 
    AliasA.b_column IS NULL;
cs


오라클 같은 경우에는 SET 구문에서 서브쿼리를 바로 날리면 되지만, MySQL의 경우에는 SELECT 구문에 서브쿼리를 한 번 더 감싸줘야 한다.

이로써 A_Table의 b_column에 'TEST_'문자열이 합쳐진 a_column값이 update 되게 된다.

반응형
블로그 이미지

김생선

세상의 모든것을 어장관리

,
반응형

엑셀로 데이터를 밀어넣다 보면 데이터가 간혹 잘못들어가는 경우가 있다. 의도치 않은 tab 문자열이라거나 공백문자열이라거나.

한두건이면 그냥 update query를 실행하면 되지만 여러건일 경우에는 답이 없다.


1
2
3
UPDATE
    table_a
SET column_name = REPLACE( column_name , ' ''')
cs


table_a 라는 테이블에서 column_name 컬럼의 데이터가 공백 문자열이 있을 경우, set 구문에 replace로 해당 컬럼을 치환한 후 update 해주면 된다.

아주 간단하고 심플함.

반응형
블로그 이미지

김생선

세상의 모든것을 어장관리

,
반응형

[Oracle 11g Enterprise] / [Mybatis 3.2.7] 기준


varchar2 데이터타입은 최대 사이즈를 4,000byte 까지 줄 수 있다. 그런데 4,000byte 이상의 데이터를 저장할 일이 있다. base64로 인코딩된 이미지라거나, xml 파싱된 영수증내용이라거나 아주 긴 블로그의 글이라거나. 그럴 때 oracle 에서 사용하는 데이터타입은 clob 이다.


CLOB은 XML 형태로 데이터를 지정하게 되는데, DB 툴에서는 select * from table로 간단하게 데이터 확인이 가능하지만, mybatis를 사용하게 되면 일반적인 select 쿼리로는 활용이 불가능하다. 그럴 때 다음과 같이 mybatis의 select 구절에 resultmap을 설정하면 된다.


1
2
3
4
5
6
7
8
9
10
11
12
<!-- resultMap 지정 -->
<resultMap id="QID_CLOB" type="hashMap" >
    <result property="ETC" column="etc" jdbcType="CLOB" javaType="java.lang.String"/>
</resultMap>
 
<!-- 조회쿼리 -->
<select id="QID_SELECT_INFO" parameterType="hashMap" resultMap="QID_CLOB" >
SELECT
    etc
FROM
    table
</select>
cs


사용법은 심플하다.

select 쿼리의 resultMap 파라미터는 별도로 선언할 resultMap의 ID를 지정해준다.

별도로 선언할 resultMap에서는 id를 맞춘 후, clob으로 가져올 컬럼명(column)을 지정하고, jdbcType을 clob으로 지정, 이를 어떠한 column name으로 내보낼 것인지 지정(property)해주면 된다.



1
2
3
4
5
6
SELECT
    a.etc AS a_etc
    b.etc AS b_etc
FROM
    table_1 a
    LEFT OUTER JOIN table_2 b ON a.no = b.no
cs

그런데 여기서 한가지 궁금한 점이 생기게 된다. 

각기 다른 테이블을 조인 후 가져올 때, 각 테이블의 같은 이름 clob은 어떻게 가져올까 하는 점이다.

테스트를 잠시 해보았는데 생각보다 매우 간단했다.


처음에는 같은 컬럼명이니까 위의 resultMap을 그냥 가져다 쓰면 ETC 컬럼을 ETC Property로 출력해주겠거니, 했지만 전혀 그렇지 않았다.(왜인지 Java의 Service 처럼 동작할거라 생각;)

그래서 두번째 방법을 사용해보았다.



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<!-- resultMap 지정 -->
<resultMap id="QID_CLOB" type="hashMap" >
    <result property="A_ETC" column="a_etc" jdbcType="CLOB" javaType="java.lang.String"/>
    <result property="B_ETC" column="b_etc" jdbcType="CLOB" javaType="java.lang.String"/>
</resultMap>
 
<!-- 조회쿼리 -->
<select id="QID_SELECT_INFO" parameterType="hashMap" resultMap="QID_CLOB" >
SELECT
    a.etc AS a_etc
    b.etc AS b_etc
FROM
    table_1 a
    LEFT OUTER JOIN table_2 b ON a.no = b.no
</select>
cs


그랬더니 각기 다른 테이블에서 동일한 네이밍의 clob 컬럼을 정상적으로 가져올 수 있었다.

간단한 테스트 결과, resultMap의 동작구조는 SELECT가 우선적으로 실행된 결과를 가져온 뒤, 이를 resultMap에서 처리하는 구조로 여겨진다. 결과적으로 resultMap은 Alias로 잡힌 컬럼명을 인식한다는 말이다.

반응형
블로그 이미지

김생선

세상의 모든것을 어장관리

,
반응형

[Oracle 11g Enterprise] 기준


개발을 하다보면 산출물을 작성해야하고, 이 산출물에는 테이블 생성날짜 등도 필요한 경우가 있다. 몰아서 테이블들을 생성해두고 나중에 작성하려다보면 뭐 임의의 날짜로 작성해도 무방하나 혹여나 하는 1g의 불안감 때문에 확인하는 경우가 있다.


1
2
3
4
5
6
7
8
9
10
11
12
SELECT
    owner
    , object_name
    , object_type
    , created
    , timestamp
FROM 
    all_objects
WHERE
    owner = 'USER_NAME'
    AND object_name ='TABLE_NAME'
 
cs


뭐 대충 이정도. 검색을 하다보면 all_objects 테이블이 아니라 user_objects라느니 뭐라느니 하는데 아마 버전마다 다른게 아닌가 싶을 정도.

여튼, 위의 컬럼은 다음과 같은 구성을 가지고 있다.


 owner

개체 소유계정. sys 계정이라면 해당 DB 내 모든 계정을 조회 가능하다.

 object_name

(간단히 말해) 개체명

개체명이라고 표현하는 이유는 테이블만이 보이는 것이 아니기 때문임.

자세한 부분은 object_type 참조

object_type

개체 타입. table인지 index인지 sequence 인지를 표현함. 

created 

생성날짜 

timestamp 

최근 access 날짜.

생성한 이후에 데이터를 insert 혹은 delete 할 때 마다 해당 날짜가 갱신된다.


뭐 대충 이정도. owner 에는 SYS 혹은 EXFSYS, SYSTEM 등 수많은 시스템용 owner가 있기에, 가장 기본적인 필터를 owner로 지정해두고 select 하는 것이 정신건강에 이로울 듯.

반응형
블로그 이미지

김생선

세상의 모든것을 어장관리

,
반응형

[Oracle 11g Enterprise 기준]


테이블의 데이터를 다른 테이블로 그대로 옮겨야 할 때가 있다.

Excel Export / Inport를 통한 방법이나 기타 여러 방법들이 있긴 한데 아무래도 귀찮은 방법이기도 하고. 그래서 Select Insert Query를 주로 사용한다.


1
2
INSERT INTO A_TABLE a
SELECT * FROM B_TABLE b WHERE b.no = '1'
cs


가장 기본적으로 위와 같은 구성으로 시작할 수 있다. A_TABLE에 B_TABLE의 데이터 중, no가 1인 항목만을 넣는 쿼리이다.


만약, 특정 컬럼에 대해서만 select insert를 한다면? 다음과 같다.


1
2
3
4
5
6
7
8
INSERT INTO A_TABLE a
    ( no , title , content , insert_date , insert_user )
SELECT 
    no , title , content , insert_date , insert_user
FROM
    B_TABLE
WHERE
    no = '1'
cs


위와 별개로, 다른 user에 할당된 테이블에 넣기 위해서는? 

회사에서는 하나의 DB에 user 단위로 운용/개발 DB가 분리되어있다. 당연하게도 dba 권한으로 아래의 쿼리를 이용해주면 된다.


1
2
3
4
5
6
7
8
INSERT INTO user_1.A_TABLE a
    ( no , title , content , insert_date , insert_user )
SELECT 
    no , title , content , insert_date , insert_user
FROM
    user_2.B_TABLE
WHERE
    no = '1'
cs


user_1은 user_2에 접근할 수 없기에 dba 권한으로 둘 다 접속이 가능한 상태에서 select / insert를 수행해주면 된다.


반응형
블로그 이미지

김생선

세상의 모든것을 어장관리

,
반응형

지난글, 2013/06/26 - [어장 프로그래밍/어장 DBA] - [MSSQL] MAX 함수 NULL 일 때 치환 에서는 MSSQL을 기준으로 글을 작성하였다.

현재 개발중인 플젝의 DB는 informix로 되어있고, 위와 같은 방식으로 max null 값을 구하려 했더니 informix 에서는 isnull 함수가 사용불가능하더라.


검색 해 보니 NVL 이란 함수가 있었고, 아래와 같은 방식으로 사용하였다.



SELECT NVL(MAX(testColumn), '0')+1 tempName FROM testTableName


testColumn의 MAX 값을 구하고, 이 값이 null 일 경우에는 0으로 치환한 후, 그 값에 +1을 하여 tempName 이라는 임시 컬럼명으로 Select 한다는 내용이다.(내가 써놓고 뭔가 어정쩡한 설명이란 생각이 든다.)


아무튼, 테스트를 해 보니 testColumn 값이 null 이 아니더라도 정상적으로 쿼리가 작동하기 때문에 만족하고 사용중. 뭐 이렇게 하면 된다.


반응형
블로그 이미지

김생선

세상의 모든것을 어장관리

,