Kamsi's Blog

암복호화를 하기 위한 pgcrypto 확장 기능 적용

CREATE EXTENSION pgcrypto;

암호화 하기

SELECT
   encode(
      encrypt(
         convert_to('암호화 하기 위한 문자열', 'utf8'),
         '암호화Key',
         'aes'
      ),
      'hex'
);

복호화 하기

SELECT
   convert_from(
      decrypt(
         decode('d359bf2938e5ef1cb6dca49dce459654b10e82e4cb3a62c63ef7511df01b6...',
         'hex'
      ),
      '암호화Key',
      'aes'
   ),
   'utf8'
);

테이블 목록 조회

SELECT RELNAME AS TABLE_NAME
  FROM PG_STAT_USER_TABLES

컬럼목록 조회

SELECT *
  FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_CATALOG = '데이터베이스명'
   AND TABLE_NAME    = '테이블명'
 ORDER BY ORDINAL_POSITION;

기본키 조회

SELECT CC.COLUMN_NAME AS COLUMN_NAME
  FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS       TC
      ,INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CC
 WHERE TC.TABLE_CATALOG   = '데이터베이스명'
   AND TC.TABLE_NAME      = '테이블명'
   AND TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
   AND TC.TABLE_CATALOG   = CC.TABLE_CATALOG
   AND TC.TABLE_SCHEMA    = CC.TABLE_SCHEMA
   AND TC.TABLE_NAME      = CC.TABLE_NAME
   AND TC.CONSTRAINT_NAME = CC.CONSTRAINT_NAME

테이블 Comment 조회

SELECT PS.RELNAME    AS TABLE_NAME
      ,PD.DESCRIPTION AS TABLE_COMMENT
  FROM PG_STAT_USER_TABLES PS
      ,PG_DESCRIPTION      PD
 WHERE PS.RELNAME  = '테이블명'
   AND PS.RELID   = PD.OBJOID
   AND PD.OBJSUBID  = 0

컬럼 Comment 조회

SELECT PS.RELNAME    AS TABLE_NAME      ,PA.ATTNAME     AS COLUMN_NAME      ,PD.DESCRIPTION AS COLUMN_COMMENT  FROM PG_STAT_ALL_TABLES PS      ,PG_DESCRIPTION     PD      ,PG_ATTRIBUTE       PA WHERE PS.SCHEMANAME = (SELECT SCHEMANAME                            FROM PG_STAT_USER_TABLES                           WHERE RELNAME = '테이블명')   AND PS.RELNAME  = '테이블명'   AND PS.RELID   = PD.OBJOID   AND PD.OBJSUBID <> 0   AND PD.OBJOID    = PA.ATTRELID   AND PD.OBJSUBID  = PA.ATTNUM ORDER BY PS.RELNAME, PD.OBJSUBID

출처 : https://jmap.tistory.com/549

ORACLE의 MERGE INTO와 같은 역할을 하는 구문이 PostgreSQL에서는 다음과 같다.

INSERT INTO example (

USERID, NAME, EMAIL

) VLAUES (

'kamsi76', '홍길동', 'kamsi76@webmaster.com'

)

ON CONFLICT (USERID) DO

UPDATE 

SET 

NAME = '임꺽정', 

EMAIL = 'kamsi76@webmaster.com';

위와 같은 방식으로 하면 된다.

또한 INSERT 할 경우 KEY값이 중복 되었을 때 아무것도 하지 않고자 할 경우

INSERT INTO example (

USERID, NAME, EMAIL

) VLAUES (

'kamsi76', '홍길동', 'kamsi76@webmaster.com'

) ON CONFLICT (USERID) DO NOTHING;

위와 같이 하면 됨..

1.Tmaxsoft 홈페이지에서 파일과 데모라이선스를 신청한다.

  다운로드 경로 : https://technet.tmaxsoft.com/ko/front/download/findDownloadList.do?cmProductCode=0301

2. 사전 패키지 설치 확인 및 설치

* 아래의 해당 버전 이상의 패키지가 설치되어 있어야 한다.

[Packages]
gcc-3.4.6.-11
gcc-c++-3.4.6.-11
libgcc-3.4.6.-11
libstdc++-3.4.6-11
libstdc++-devel-3.4.6-11
compat-libstdc++-33-3.2.3-47.3
libaio-0.3.105-2
libaio-devel-0.3.105-2

3. 환경설정 관련 사항

- 설정파일  /etc/sysctl.conf  제일 하단에 다음 값을 추가한다.

kernel.shmmni = 4096
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.sem = 10000 32000 10000 10000

fs.file-max = 6815744

net.ipv4.ip_local_port_range = 1024 65500

- shell Limit 파라미터 설정을 위해 /etc/security/limits.conf 파일 제일 하단에 다음 값을 추가한다.

tibero           soft    nproc           2047
tibero           hard    nproc           16384
tibero           soft    nofile          1024
tibero           hard    nofile          65536

-  /etc/sysyemd/logind.conf 파일의 RemoveIPC=no 의 주석을 풀어 준다.

4. Tibero를 설치한다.

Tmaxsoft에 다운받은 파일과 라이센스 파일을 서버로 옮긴다.

- 설치하고자 하는 위치에 다운로드 파일 압축을 해제한다.

# tar -zxvf tibero6-bin-FS07_CS_1902-linux64-166256-opt.tar.gz

- 환경변수 설정

export TB_HOME=/home/tibero/Tibero/tibero6
export TB_SID=tibero
export LD_LIBRARY_PATH=$TB_HOME/lib:$TB_HOME/client/lib
export PATH=$PATH:$TB_HOME/bin:$TB_HOME/client/bin

5. $TB_HOME/config/gen_tip.sh 파일 실행

6. Tibero 서버를 NOMOUNT 모드로 시작

# tbboot nomount

Change core dump dir to /home/tibero6/tibero6/bin/prof.
Listener port = 8629

Tibero6

TmaxData Corporation Copyright (c) 2008-. All rights reserved.
Tibero instance started up (NOMOUNT mode).

7. tbsql 을 통해 데이터베이스 접속

최초 sys 계정의 비밀번호는 tibero로 설정되어 있다.

# tbsql sys/tibero

tbSQL 6

TmaxData Corporation Copyright (c) 2008-. All rights reserved.

Connected to Tibero.

SQL>

8. 데이터베이스 생성.

원할 시 다른 명칭으로 생성해도 된다. 다음 예제는 tibero로 생성했다.

SQL> create database "tibero" 
  user sys identified by tibero 
  maxinstances 8 
  maxdatafiles 100 
  character set MSWIN949 
  national character set UTF16 
  logfile 
    group 1 'log001.log' size 100M, 
    group 2 'log002.log' size 100M, 
    group 3 'log003.log' size 100M 
  maxloggroups 255 
  maxlogmembers 8 
  noarchivelog 
    datafile 'system001.dtf' size 100M autoextend on next 100M maxsize unlimited 
    default temporary tablespace TEMP 
      tempfile 'temp001.dtf' size 100M autoextend on next 100M maxsize unlimited 
      extent management local autoallocate 
    undo tablespace UNDO 
      datafile 'undo001.dtf' size 100M autoextend on next 100M maxsize unlimited 
      extent management local autoallocate;
Database created.

SQL> quit
Disconnected.

9. 데이터베이스 생성 후 tbboot 명령어로 NORMAL 모드로 재기동한다.

# tbboot
Change core dump dir to /home/tibero6/tibero6/bin/prof.
Listener port = 25010

Tibero 6  

TmaxData Corporation Copyright (c) 2008-. All rights reserved.
Tibero instance started up (NORMAL mode).

10. $TB_HOME/scripts 디렉토리의 system.sh 를 실행한다. 

해당 파일은 현재 디렉토리에 있는 SQL파일을 수행하여 role, system user, view, package 등을 생성한다. 실행 시 sys 및 syscat 계정에 대한 비밀번호를 물어 보는데 sys는 tibero, syscat는 syscat로 기본 설정되어 있다.

# ./system.sh 
Enter SYS password:

Enter SYSCAT password:

Creating the role DBA...
create default system users & roles?(Y/N):

Creating system users & roles...
Creating virtual tables(1)...
Creating virtual tables(2)...
Granting public access to _VT_DUAL...
Creating the system generated sequences...
Creating internal dynamic performance views...
Creating outline table...
Creating system package specifications:
    Running /home/tibero/Tibero/tibero6/scripts/pkg/pkg_standard.sql...
    Running /home/tibero/Tibero/tibero6/scripts/pkg/pkg_dbms_output.sql...
    Running /home/tibero/Tibero/tibero6/scripts/pkg/pkg_dbms_lob.sql...
    Running /home/tibero/Tibero/tibero6/scripts/pkg/pkg_dbms_utility.sql...
    ......중간 생략......

Creating spatial meta tables and views ...
Creating internal system jobs...
Creating internal system notice queue ...
Done.
For details, check /home/tibero6/tibero6/instance/tibero/log/system_init.log.

11. Tibero 정상 동작 여부 확인

# ps -ef | grep tbsvr
tibero   19981     1  0 21:12 pts/2    00:00:00 tbsvr         ... 
tibero   19983 19981  0 21:12 pts/2    00:00:00 tbsvr_TBMP    ...
tibero   19984 19981  0 21:12 pts/2    00:00:00 tbsvr_WP000   ...
tibero   19985 19981  3 21:12 pts/2    00:00:00 tbsvr_WP001   ...
tibero   19986 19981  1 21:12 pts/2    00:00:12 tbsvr_WP002   ...
tibero   19987 19981  2 21:12 pts/2    00:00:12 tbsvr_PEP000   ...
tibero   19988 19981  0 21:12 pts/2    00:00:00 tbsvr_AGNT    ...
tibero   19989 19981  1 21:12 pts/2    00:00:00 tbsvr_DBWR    ...
tibero   19999 19981  0 21:12 pts/2    00:00:00 tbsvr_RECO    ...  

참조 : 따뜻한 커피한잔: [CentOS 7] Tibero6 설치 방법

Tablespace 용량 확인

select   substr(a.tablespace_name,1,30) tablespace,

         round(sum(a.total1)/1024/1024,1) "TotalMB",

         round(sum(a.total1)/1024/1024,1)-round(sum(a.sum1)/1024/1024,1) "UsedMB",

         round(sum(a.sum1)/1024/1024,1) "FreeMB",

         round((round(sum(a.total1)/1024/1024,1)-round(sum(a.sum1)/1024/1024,1))/round(sum(a.total1)/1024/1024,1)*100,2) "Used%"

from

         (select   tablespace_name,0 total1,sum(bytes) sum1,max(bytes) MAXB,count(bytes) cnt

          from     dba_free_space

          group by tablespace_name

          union

          select   tablespace_name,sum(bytes) total1,0,0,0

          from     dba_data_files

          group by tablespace_name) a

group by a.tablespace_name

order by tablespace;

Tablespace 용량 늘리기

alter DATABASE datafile '<경로>/<파일명>.dbf' resize 1024M;

SELECT *

FROM 

    DEPT_INFO

WHERE  

    CONNECT_BY_ISLEAF  = 1

    AND CONNECT_BY_ISCYCLE = 0

START WITH PARNTS_DEPT_CD = '00000000'

CONNECT BY NOCYCLE PRIOR DEPT_CD = PARNTS_DEPT_CD;

UPDATE TABLE1 T1 

SET (NAME, DESC) = ( SELECT T2.NAME, T2.DESC FROM TABLE2 T2 WHERE T1.ID = T2.ID )

WHERE EXISTS ( SELECT 1 FROM TABLE2 T2 WHERE T1.ID = T2.ID );

foreign Key 걸려 있는 테이블 비우기 처리 하기

SET FOREIGN_KEY_CHECKS = 0; 

TRUNCATE table $table_name; 

SET FOREIGN_KEY_CHECKS = 1;

1. 테이블 목록조회

SELECT *

FROM all_all_tables;

SELECT *

FROM dba_tables;

SELECT *

FROM ALL_OBJECTS

WHERE OBJECT_TYPE = 'TABLE'

-- 접속한 계정의 테이블 목록보기

SELECT *

FROM tabs;

SELECT *

FROM USER_OBJECTS 

WHERE OBJECT_TYPE = 'TABLE';

SELECT *

FROM USER_TABLES;

-- 테이블 코멘트 보기

SELECT *

FROM ALL_TAB_COMMENTS

WHERE TABLE_NAME = '테이블명'

SELECT *

FROM USER_TAB_COMMENTS;

2. 컬럼 목록조회

SELECT *

FROM COLS;

WHERE TABLE_NAME = '테이블명';

SELECT *

FROM ALL_TAB_COLUMNS

WHERE TABLE_NAME = '테이블명';

SELECT *

FROM USER_TAB_COLUMNS;

-- 컬럼 코멘트 보기

SELECT *

FROM USER_COL_COMMENTS;

원인 : DB에 접속하는 커넥션 프로세서 수의 오버

> sqlplus "/as sysdba"

> ALTER system SET processes=1000 scope=spfile;

> shutdown immediate;

> startup

사용자 삭제

drop user 아이디 CASCADE;

사용자 생성

create user 아이디 identified by 비밀번호;

권한주기

grant connect,resource, DBA to 아이디;

grant CREATE DATABASE LINK, CREATE PUBLIC DATABASE LINK, CREATE MATERIALIZED VIEW, CREATE PROCEDURE, CREATE PUBLIC SYNONYM, CREATE ROLE, CREATE SEQUENCE, CREATE SYNONYM, CREATE TABLE, CREATE TRIGGER, CREATE TYPE, CREATE VIEW to 아이디;

CREATE TABLE [dbo].[tblcommon](
    [SEQ] [int] IDENTITY(1,1) NOT NULL,
    [GUBUN] [varchar](30) NOT NULL,
    [TITLE] [varchar](150) NOT NULL,
    [DEPTH] [int] NOT NULL,
    [PARENT_SEQ] [int] NULL,
    [SORT] [int] NOT NULL,
    CONSTRAINT [PK_tblcommon] 
    PRIMARY KEY CLUSTERED ([SEQ] ASC) 
    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

WITH DirectReports([PARENT_SEQ], [SEQ], [GUBUN], [TITLE], [DEPTH], [SORT], [LVL]) AS
(
    SELECT [PARENT_SEQ], [SEQ], [GUBUN], [TITLE], [DEPTH], [SORT], 0 as [LVL]
    FROM tblcommon
    WHERE [PARENT_SEQ] IS NULL
    UNION ALL
    SELECT a.[PARENT_SEQ], a.[SEQ], a.[GUBUN], a.[TITLE], a.[DEPTH], a.[SORT], [LVL] + 1
    FROM tblcommon AS a INNER JOIN DirectReports AS d ON a.[PARENT_SEQ] = d.[SEQ]
)
SELECT ISNULL([PARENT_SEQ], [SEQ]) AS [PARENT_SEQ], [SEQ], [GUBUN], [TITLE], [DEPTH], [SORT], [LVL]
FROM DirectReports
WHERE [GUBUN] = 'DEVICE_MAKER'
ORDER BY [PARENT_SEQ], [DEPTH]

select
    machine "hostname",
    username "username",
    osuser "OS User",
    program "Program",
    sid||','||serial# "ID",
    last_call_et "Last Activity",
    status
from v$session
where
    username is not null
order by status desc, last_call_et desc;

select
    b.machine "hostname",
    b.username "username",
    b.osuser "OS User",
    b.program "Program",
    a.tablespace_name "TableSpace",
    row_wait_file# "File Number",
    row_wait_block# "Block Number",
    c.owner,
    c.segment_name,
    c.segment_type
from
    dba_data_files a,
    v$session b,
    dba_extents c
where
    b.row_wait_file# = a.file_id
    and c.file_id = row_wait_file#
    and row_wait_block# between c.block_id and c.block_id + c.blocks -1
    and row_wait_file# <> 0
    and type = 'USER'

MySql

<insert id="insertContent" parameterClass="content">

    <![CDATA[    

INSERT INTO CONTENT (        

CREATED_DATE, TITLE, CONTENT, CONTENT_TYPE    

) VALUES (

        now(), #title#, #content#, #contentType#   

)    

]]>    

<selectKey keyProperty="seqId" resultClass="int">        

SELECT LAST_INSERT_ID()    

</selectKey>

</insert>

MSSQL

<insert id="createProjectBasicInfo" parameterClass="prjIdx">

    <selectKey keyProperty="prj_info_seq" resultClass="int">

       INSERT INTO PRJ_INFO (             ...         ) VALUES (              ...         )        

SELECT SCOPE_IDENTITY()

    </selectKey>

</insert>

Oracle

<insert id="insert_message" parameterClass="java.util.HashMap">    

<selectKey keyProperty="message_id" resultClass="Integer">

        SELECT message_id_seq.nextval FROM DUAL

</selectKey>

INSERT INTO guestbook_message (

message_id, guest_name, password, message

) VALUES(

#message_id#, #gName#, #pw#, #ms#

) 

</insert>