728x90 반응형 데이터베이스118 오라클 각종 메타데이터 조회 /* 1. 해당 테이블의 기본키 정보 */ select a.table_name, b.column_name, b.position, a.status from all_constraints a, all_cons_columns b where 1=1 and a.owner = 'SMARTFARM' and a.table_name = 'TB_USER_INFO' and a.table_name = b.table_name and a.constraint_type = 'P' and a.constraint_name = b.constraint_name and a.owner = b.owner order by b.position asc ; /* 2. 해당 사용자의 테이블 리스트 및 레코드 개수 */ select table_name, n.. 2021. 9. 3. 컬럼정보조회 SELECT TABLE_NAME FROM ALL_TABLES WHERE TABLE_NAME LIKE 'TB_USER_INFO%' AND OWNER = 'SCOTT' SELECT T.TABLE_NAME AS TBL_NM , T.COLUMN_NAME AS COLS_ID , C.COMMENTS AS COLS_NM , C.COMMENTS AS NOTE , CASE WHEN T.DATA_TYPE='TIMESTAMP(6)' THEN 'TIMESTAMP' ELSE T.DATA_TYPE END AS DATA_TP_NM , CASE WHEN T.DATA_TYPE='VARCHAR' THEN T.DATA_LENGTH WHEN T.DATA_TYPE='NUMBER' THEN T.DATA_PRECISION END AS DATA_.. 2021. 9. 3. DATAPUMP 백업 DATAPUMP 기능을 사용하기 위해서는 첫째 DIRECTORY가 설정되어 있어야한다. * 필요 권한 EXP_FULL_DATABASE IMP_FULL_DATABASE Directory 조회 SELECT * FROM DBA_DIRECTORIES; Directory 생성 및 권한 SQL> CREATE DIRECTORY DUMP AS '/oracle/dumpfile'; Directory created. SQL> GRANT READ, WRITE ON DIRECTORY DUMP TO SYSTEM; Grant succeeded. SQL> GRANT CREATE ANY DIRECTORY TO SYSTEM; Grant succeeded. SQL> SELECT * FROM DBA_DIRECTORIES; GRANT REA.. 2021. 9. 3. [티베로] MS949에서 UTF-8로 DB 변경 MS949(euc-kr)에서 한글의 경우는 2byte를 사용하고 UTF-8로 변경시 한글은 3byte를 사용함.(ASCII 경우(영문,숫자)는 기존대로 1byte만 사용함) ** DBMS 변경시 관련 스크립트 작성 참조(작업전 백업은 필수!!) ORACLE, Tibero만 해당됨 1. 기존 DB에서(MS949) UTF-8 캐릭터셋 사이즈를 미리 조정한다. 1-1. 스크립트 생성 SELECT 'ATLER TABLE '|| table_name ||' MODIFY('|| LOWER(column_name) ||' VARCHAR2('|| LEAST(CEIL(data_length*1.5/10) * 10, 4000) ||'));' AS sqltext FROM USER_TAB_COLUMNS WHERE data_type.. 2021. 8. 17. [PostgreSQL] 시간 데이터타입 쿼리/함수 ● POSTGRESQL에서의 시간 타입은 타 DBMS와 다른 부분이 있어 시간/날짜 데이터타입에 대한 쿼리 및 함수를 따로 정리하여 포스팅합니다. -- 날짜 시간 타입으로 캐스팅 select '20190320'::date, '2019-01-02'::date select date '2019-03-18' select to_date('01022019','MMDDYYYY'), to_date('2018-12-25', 'YYYY-MM-DD') select to_timestamp('2019-01-01 20:12:12', 'YYYY-MM-DD HH:MI:SS') -- 현재시간 날짜 구하기 select now(), current_date, current_time -- 시간 더하고 빼기 select '2019-01-01'.. 2021. 8. 7. [PostgreSQL] 시퀀스 생성 및 사용 ▷생성 CREATE SEQUENCE [seq_name] ▷nextval nextval('seq_name') ▷currval currval('seq_name') ▷값 초기화 setval('seq_name', seq_val, [true/false]) ▷삭제 DROP SEQUENCE [seq_name] 사용 예제) CREATE SEQUENCE mine_seq; SELECT nextval('mine_seq'); SELECT currval('mine_seq'); DROP SEQUENCE mine_seq; 시퀀스를 생성해놓고 바로 확인을 하기 위해 currval()을 사용하여 확인하게 되면 다음과 같은 에러가 발생하는데 ERROR: 오류: "mine_seq" 시퀀스의 currval 값이 현재 세션에 지정되어 있지 .. 2021. 8. 7. 베큠 사용 postgresql 쓰레기 데이터 정리 간단 설명 베큠(Vacuum)은 PostgreSQL의 쓰레기 데이터를 정리하여 쾌적하게 청소하라는 명령인데, 쉽게 "디스크 조각 모음"이라고 생각하면된다. 베큠을 해야되는 이유 데이터는 물리적으로 디스크에 저장되고 읽어서 보여주는데, 데이터를 갱신(UPDATE) 혹은 삭제(DELETE) 시에 디스크에 있던 기존 정보를 갱신하거나 삭제하지 않습니다. 기존 정보는 변경되었다는 표시를 남기고 새롭게 디스크에 갱신(UPDATE)된 정보를 기록합니다. 삭제(DELETE) 했어도 디스크 용량은 줄어들지 않으며 갱신(UPDATE) 시에는 새로운 행이 추가되기 때문에 디스크 용량이 증가하게되는거죠. 이런 개념은 MVCC(다중 버전 동시성 제어) 구현에 따른 튜플(Tuple) 개념때문에 그렇습니다. 다시 쉽게 말하면 트.. 2021. 7. 28. [PostgreSQL] da# erd 생성 초기 데이터 설정 순서 1. STD_AREA 테이블 : 표준분류 체계관리 등록 SELECT * FROM STD_AREA WHERE STD_AREA_ID = '499135df-e8a3-45e2-b311-5f388506cd34'; 2. STD_DOM_GRP : 도메인 그룹등록 SELECT * FROM STD_DOM_GRP sdg WHERE STD_AREA_ID = '499135df-e8a3-45e2-b311-5f388506cd34'; 3. STD_DOM : 도메인 등록 SELECT * FROM STD_DOM sd WHERE STD_AREA_ID = '499135df-e8a3-45e2-b311-5f388506cd34'; 4. STD_DIC 테이블 : 표준단어 등록 SELECT * FROM STD_DIC sd .. 2021. 7. 27. [PostgresSQL] postgresql Block Size postgresql block size 확인 기본은 8192 bytes다 SELECT current_setting('block_size'); PostgreSQL에서 (테이블, 스키마, 테이블 스페이스) 등의 DB 용량 조회하기 위한 SQL문이다. [데이터베이스 총 용량 확인] select datname, pg_size_pretty(pg_database_size(datname)) from pg_database; [테이블 스페이스 총 용량 확인] select spcname, pg_size_pretty(pg_tablespace_size(spcname)) from pg_tablespace; [DB의 전체 스키마 조회] select nspname from pg_catalog.pg_namespace pn; [DB의.. 2021. 7. 27. [PostgreSQL] 원격접속시 오류 발생할때 처리 pg_hba.conf 파일열어서 원격접속 허용아이피 추가 개발 테스트용에는 전체 아이피 접속 허용 아래와 같이 추가 후 재기동 host all all 0.0.0.0/0 md5 2021. 7. 26. [PostgresSQL] postgresql 백업 및 복원 /* 패스워드를 파일로 등록해 놔야 한다 */ C:\Users\cbw\AppData\Roaming\postgresql\pgpass.conf 파일 내용 localhost:5432:*:postgres:postgres /* db전체백업 */ pg_dump.exe --file "D:\\temp\\invako.backup" --host "localhost" --port "5432" --username "postgres" --no-password --verbose --format=c --section=pre-data --section=data --section=post-data --column-inserts --encoding "UTF8" "invakodb" /* db전체복원 */ pg_restore.exe --h.. 2021. 7. 21. [PostgresSQL] 테이블의 컬럼정보확인 NFORMATION_SCHEMA.COLUMNS 을 이용하여 특정 테이블의 컬럼 목록과 특정 컬럼 존재 여부를 확인해보자. 우선 PostgreSQL의 구조를 보면 알겠지만 데이터베이스 내에 Schemas 안에 여러 그룹(?) 이 존재하며 각각 테이블 목록이 존재한다. SELECT * FROM INFORMATION_SCHEMA.COLUMNS 라는 구문을 사용하면 모든 SCHEMA 안에 있는 컬럼 정보들을 가져오는데 그 컬럼 정보들은 table_catalog, table_schema, table_name, column_name, ... 말그대로 테이블 정보를 전부 가져온다. 이 구문을 이용하여 where 조건을 걸어주어 특정 테이블 정보와, 특정 테이블의 컬럼 존재 여부를 확인할 수 있다. ▷예제1) 특정 테.. 2021. 7. 17. 조건에 따른 값 보여주기 조건에 따라 다른값을 보여주는 CASE WHEN ~ END 구문에 대해 알아보자. CASE WHEN 구문을 간단하게 설명하자면 아래와 같다. CASE WHEN condition_1 THEN result_1 WHEN condition_2 THEN result_2 [WHEN...] [ELSE else_result] END 여기서 ELSE 부분 역시 생략이 가능하지만 위에서 만족하는 조건이 없으면 NULL을 반환하니 상황에 따라 뭐라도 넣어주는것이 좋을 수 있다. CASE WHEN 구문은 PostgreSQL 에서만 존재하는건 아니라서 오라클 등등에서 사용할 수 있으니 이참에 잘 알아두자. ▶예제1) 일반 구문 형식 SELECT CASE WHEN score IS NOT NULL THEN 'D' ELSE 'F' .. 2021. 7. 17. [PostgreSQL] 다른테이블 복사하여 새로운 테이블 생성 ▷예제1) 다른 테이블의 구조만 복사하기 CREATE TABLE mine (LIKE othertable); ▷예제2) 다른 테이블의 구조와 데이터 복사하기 CREATE TABLE it AS SELECT * FROM othertable ▷예제3) 다른 테이블의 구조와 테이블의 모든 정보 복사하기(ft. index) create table trecord (like othertable including all); -- 추가로 데이터도 넣어주기 insert into trecord (select * from othertable) 2021. 7. 17. [PostgreSQL] 데이터 있으면 update 없으면 insert PostgreSQL 에서 사용하는 upsert구문에 대해 알아보자. (오라클에서는 merge into / mysql에서는 on duplicate on key update 를 사용한다.) 구문은 다음과 같다. INSERT INTO [TABLE] (COLUMN1, COLUMN2, ...) VALUES (VALUE1, VALUE2, ...) ON CONFLICT [column_name / ON CONSTRAINT constraint_name/ WHERE predicate] [DO NOTHING] [DO UPDATE SET column1 = value1, ...] 기본구문은 이런식으로 이루어져 있고 하나씩 설명하자면 ON CONFLICT column_name : 특정 컬럼명을 기준으로 체크를하며 컬럼을 여러개 .. 2021. 7. 17. [PostgreSQL] 시퀀스 생성 및 사용 ▷생성 CREATE SEQUENCE [seq_name] ▷nextval nextval('seq_name') ▷currval currval('seq_name') ▷값 초기화 setval('seq_name', seq_val, [true/false]) ▷삭제 DROP SEQUENCE [seq_name] 사용 예제) CREATE SEQUENCE mine_seq; SELECT nextval('mine_seq'); SELECT currval('mine_seq'); DROP SEQUENCE mine_seq; 시퀀스를 생성해놓고 바로 확인을 하기 위해 currval()을 사용하여 확인하게 되면 다음과 같은 에러가 발생하는데 ERROR: 오류: "mine_seq" 시퀀스의 currval 값이 현재 세션에 지정되어 있지 .. 2021. 7. 17. [PostgreSQL] Schema 관리(생성/추가/변경/삭제) 1. postgresql schema 구조 - schema는 object들의 논리적인 집합을 의미한다. - TABLE, VIEW, SEQUENCE, SYSNONYM, DOMAIN, FUNCTION 등의 OBJECT들로 구성된다. - SCHEMA를 사용하는 이유는 논리적집합체를 만들어 관리 편의성을 높이고 여러 사용자의 간섭없이 접속할 수 있게 한다. 2. 스키마 조회 - \dn 명령어로 조회 가능 3. 스키마 생성 (1) CREATE SCHEMA [schema_name] AUTHORIZATION [user_name] [ schema_element [ . . . ] ]; - 이름을 입력하지 않을 경우 USER의 이름을 schema 이름으로 사용한다. (pg_ 로 시작하는 이름은 스키마 이름으로 불가능) (.. 2021. 7. 16. [PostgreSQL] SQL 파일 실행 postgres 계정에서 실행합니다. [root@localhost ~]$ su - postgres 비밀번호 입력 : [postgres@localhost ~]$ [postgres@localhost ~]$ psql -U postgres -d postgres -a -f /var/lib/pgsql/11/database.sql psql -U [database_user] -d [database_name] -a -f [sql file 절대경로] * postgres 계정으로 실행하면 관리자 권한이 없어 파일이 검색되지 않거나 찾지 못하는 경우가 많으니 postgres가 소유권을 가진 폴더로 sql 파일을 이동시켜 놓은 후 pwd로 정확한 현재 경로 확인해서 psql 명령어 실행하는게 간편하고 빠릅니다. 출처: http.. 2021. 7. 16. [PostgreSQL] 문자열 함수 (자르기, 붙히기, 치환 등) 문자열 함수 btrim : 문자열의 시작과 끝 양쪽에 지정된 문자들을 제거 char_length : 문자열의 길이를 반환 character_length : 지정된 문자열의 길이를 반환 || : 2개의 문자열을 합하여 반환 initcap : 문자열 중 첫번째 문자는 대문자로 변환하고 나머지는 소문자로 변환 length : 문자열의 길이를 반환 lower : 지정된 문자열을 모두 소문자로 변환 lpad : 지정된 문자열을 원하는 길이로 맞추는데, 이때 부족한 문자를 왼쪽에 채움 ltrim : 문자열의 왼쪽에서 지정된 모든 문자를 제거 position : 문자열에서 지정된 문자열이 존재하는 인덱스값을 반환 repeat : 문자열을 원하는 만큼 반복해서 반환 replace : 기존의 문자열을 구성하는 부분 문자.. 2021. 7. 16. [PostgreSQL] 사용하지 않는 인덱스 조회 및 성능 개선 인덱스(Index) 혹은 색인은 테이블 설계시 많이 고민하는 부분이고 성능 향상에 많은 도움을 주고 있지만 프로그램의 설계 변경 혹은 기능 개선등 여러 상황에 따라 사용하지 않는 인덱스가 발생하게 됩니다. 여러 상황 때문에 현재는 사용하지 않는 인덱스를 찾아보기 위한 간략한 정리 내용입니다. 인덱스 구조 이해를 위한 예시 인덱스는 지정한 컬럼에 대한 매핑 정보를 가지고 있습니다. 사용하지 않는 인덱스를 찾아봐야되는 이유 보통 쿼리 튜닝을 위해 운영중인 데이터베이스에서 EXPLAIN 명령어를 많이 사용하고 필요시 인덱스 추가는 생기지만 삭제는 거의 없을거라 생각됩니다. 만약 그렇다면 극단적으로 생각했을때 오래된 프로그램 일 수록 추가된 인덱스는 많지만 삭제된 인덱스가 없다는건 불필요한 인덱스도 많을수 있다.. 2021. 7. 16. 이전 1 2 3 4 5 6 다음 728x90 반응형
