'개발/RDBMS' 카테고리의 글 목록

postgresql jsonb array(jsonb list) 제어

개발/RDBMS 2023. 2. 7. 13:06

postgresql jsonb array타입(jsonb list)에 대해 json 추가/수정/삭제

Insert

INSERT INTO table_name (column1, column2, column_jsonb_list)
VALUES ('value1', 'value2', '["jsonb_value1", "jsonb_value2", ... ]'::jsonb[])

Update

UPDATE table_name
SET column_name = array_to_json(
  array_agg(
    CASE
      WHEN element->>'id' = 'uuid_value' THEN
        jsonb_set(element, '{deactive_dt}', to_jsonb(current_date))
      ELSE element
    END
  )
)
FROM (
  SELECT jsonb_array_elements(column_name) AS element
  FROM table_name
) sub;

Delete

SELECT jsonb_set('{"data": [1, 2, 3, 4, 5]}'::jsonb, '{data}', '[]');

저작자표시 비영리 변경금지

'개발 > RDBMS' 카테고리의 다른 글

[Mysql 계정( 생성, 삭제, 권한) (0)	2018.08.02
Oracle, Mysql ROW_NUMBER() OVER(...) (0)	2018.08.02
ORACLE(오라클), TIBERO(티베로) 언어설정 확인 (0)	2018.08.02
[MySQL] Lock wait timeout exceeded; try restarting transaction (0)	2018.08.02
오라클/티베로 프로시저/함수 확인/삭제/재컴파일 (0)	2018.08.02

민오라방구

개발 , 여행, 일상 아직 배울게 많은 개발자

,

[Mysql 계정( 생성, 삭제, 권한)

개발/RDBMS 2018. 8. 2. 16:41

계정 생성 - 버전별 방법이 용이함

- 1.

mysql > create user 계정@% identified by '비밀번호';

- 2.

mysql > USE mysql; // mysql database 선택
mysql > INSERT INTO user (Host, User, Password) VALUES ('localhost', '계정', password('비밀번호'));
mysql > INSERT INTO user (Host, User, Password) VALUES ('%', '계정', password('비밀번호'));
mysql > FLUSH privileges;

권한 설정

mysql > GRANT ALL PRIVILEGES ON *.* TO 계정@'%' IDENTIFIED BY 'password'; -- 모든 테이블의 권한

권한 확인

mysql > SHOW GRANTS FOR 계정@localhost; -- 계정과 호스트를 붙인다

mysql > SHOW GRANTS FOR 계정@'%';

mysql > SHOW GRANTS FOR 계정@'192.168.1.1';

권한 삭제

mysql > revoke all on DB.테이블명 from 계정;

계정 삭제 - 버전별 방법이 용이함

- 1.

mysql > drop user '계정'@localhost;

- 2.

mysql > select * from user;
mysql > delete from user where user = '계정';

저작자표시 비영리 변경금지

'개발 > RDBMS' 카테고리의 다른 글

postgresql jsonb array(jsonb list) 제어 (0)	2023.02.07
Oracle, Mysql ROW_NUMBER() OVER(...) (0)	2018.08.02
ORACLE(오라클), TIBERO(티베로) 언어설정 확인 (0)	2018.08.02
[MySQL] Lock wait timeout exceeded; try restarting transaction (0)	2018.08.02
오라클/티베로 프로시저/함수 확인/삭제/재컴파일 (0)	2018.08.02

민오라방구

개발 , 여행, 일상 아직 배울게 많은 개발자

,

Oracle, Mysql ROW_NUMBER() OVER(...)

개발/RDBMS 2018. 8. 2. 16:23

[Oracle]

SELECT empno, ename, job, sal,

ROW_NUMBER() OVER(PARTITION BY job ORDER BY sal) AS rnum

FROM scott.emp;

[MySQL]

SELECT empno, ename, job, sal, rnum

FROM (

SELECT a.*,

(CASE @vjob WHEN a.job THEN @rownum:=@rownum+1 ELSE @rownum:=1 END) rnum,

(@vjob:=a.job) vjob

FROM emp a, (SELECT @vjob:='', @rownum:=0 FROM DUAL) b

ORDER BY a.job, a.sal

) c;

저작자표시 비영리 변경금지

'개발 > RDBMS' 카테고리의 다른 글

postgresql jsonb array(jsonb list) 제어 (0)	2023.02.07
[Mysql 계정( 생성, 삭제, 권한) (0)	2018.08.02
ORACLE(오라클), TIBERO(티베로) 언어설정 확인 (0)	2018.08.02
[MySQL] Lock wait timeout exceeded; try restarting transaction (0)	2018.08.02
오라클/티베로 프로시저/함수 확인/삭제/재컴파일 (0)	2018.08.02

민오라방구

개발 , 여행, 일상 아직 배울게 많은 개발자

,

ORACLE(오라클), TIBERO(티베로) 언어설정 확인

개발/RDBMS 2018. 8. 2. 16:20

오라클

select * from v$nls_parameters ;

티베로

select * from database_properties;

저작자표시 비영리 변경금지

'개발 > RDBMS' 카테고리의 다른 글

[Mysql 계정( 생성, 삭제, 권한) (0)	2018.08.02
Oracle, Mysql ROW_NUMBER() OVER(...) (0)	2018.08.02
[MySQL] Lock wait timeout exceeded; try restarting transaction (0)	2018.08.02
오라클/티베로 프로시저/함수 확인/삭제/재컴파일 (0)	2018.08.02
ORA-00600: 내부 오류 코드, 인수 : [koklismem1: r_length is 0] (0)	2018.08.02

민오라방구

개발 , 여행, 일상 아직 배울게 많은 개발자

,

[MySQL] Lock wait timeout exceeded; try restarting transaction

개발/RDBMS 2018. 8. 2. 16:14

DB에서 SQL실행시에 에러 발생

Lock wait timeout exceeded; try restarting transaction

특정 테이블이나 레코드에 락이 걸려있다.

해결방법

프로세스들을 확인하고 해당 프로세스에 대해 종료한다.

프로세스 조회

- show processlist;

프로세스 종료
- kill id;

테이블 락 해제

- unlock tables;

MySQL 재시작

- /etc/init.d/mysqld restart

- service mysqld restart

저작자표시 비영리 변경금지

'개발 > RDBMS' 카테고리의 다른 글

[Mysql 계정( 생성, 삭제, 권한) (0)	2018.08.02
Oracle, Mysql ROW_NUMBER() OVER(...) (0)	2018.08.02
ORACLE(오라클), TIBERO(티베로) 언어설정 확인 (0)	2018.08.02
오라클/티베로 프로시저/함수 확인/삭제/재컴파일 (0)	2018.08.02
ORA-00600: 내부 오류 코드, 인수 : [koklismem1: r_length is 0] (0)	2018.08.02

민오라방구

개발 , 여행, 일상 아직 배울게 많은 개발자

,

오라클/티베로 프로시저/함수 확인/삭제/재컴파일

개발/RDBMS 2018. 8. 2. 16:09

프로시저 확인

select * from user_objects where object_type = 'PROCEDURE';

프로시저/함수 삭제

DROP PROCEDURE 프로시저명;

DROP FUNCTION 함수명;

INVALID OBJECT 확인

select * from dba_objects where STATUS = 'INVALID';

재컴파일을 위한 스크립트 실행

@/bridgetec/trdata.sql

오브젝트 타입 종류

INDEX

PROCEDURE

TABLE

VIEW

FUNCTION

DATABASE LINK

SYNONYM

LOB

SEQUENCE

저작자표시 비영리 변경금지

'개발 > RDBMS' 카테고리의 다른 글

[Mysql 계정( 생성, 삭제, 권한) (0)	2018.08.02
Oracle, Mysql ROW_NUMBER() OVER(...) (0)	2018.08.02
ORACLE(오라클), TIBERO(티베로) 언어설정 확인 (0)	2018.08.02
[MySQL] Lock wait timeout exceeded; try restarting transaction (0)	2018.08.02
ORA-00600: 내부 오류 코드, 인수 : [koklismem1: r_length is 0] (0)	2018.08.02

민오라방구

개발 , 여행, 일상 아직 배울게 많은 개발자

,

ORA-00600: 내부 오류 코드, 인수 : [koklismem1: r_length is 0]

개발/RDBMS 2018. 8. 2. 15:30

2018-03-20 16:47:28,258 ERROR [com.bridgetec.sa.dao.SaDispatchSaDAO] [lsm] update EXCEPTION :org.springframework.jdbc.UncategorizedSQLException: SqlMapClient operation; uncategorized SQLException for SQL []; SQL state [60000]; error code [600];

--- The error occurred in sqlmap/sa/CA4020S600_ORA.xml.

--- The error occurred while applying a parameter map.

--- Check the CA4020S600.rInsertRecogAnswer-InlineParameterMap.

--- Check the statement (update failed).

--- Cause: java.sql.SQLException: ORA-00600: 내부 오류 코드, 인수: [koklismem1: r_length is 0], [], [], [], [], [], [], [], [], [], [], []

; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException:

--- The error occurred in sqlmap/sa/CA4020S600_ORA.xml.

--- The error occurred while applying a parameter map.

--- Check the CA4020S600.rInsertRecogAnswer-InlineParameterMap.

--- Check the statement (update failed).

--- Cause: java.sql.SQLException: ORA-00600: 내부 오류 코드, 인수: [koklismem1: r_length is 0], [], [], [], [], [], [], [], [], [], [], []

2018-03-20 16:47:28,269 ERROR [org.springframework.transaction.interceptor.TransactionInterceptor] Application exception overridden by rollback exception

org.springframework.jdbc.UncategorizedSQLException: SqlMapClient operation; uncategorized SQLException for SQL []; SQL state [60000]; error code [600];

--- The error occurred in sqlmap/sa/CA4020S600_ORA.xml.

--- The error occurred while applying a parameter map.

--- Check the CA4020S600.rInsertRecogAnswer-InlineParameterMap.

--- Check the statement (update failed).

--- Cause: java.sql.SQLException: ORA-00600: 내부 오류 코드, 인수: [koklismem1: r_length is 0], [], [], [], [], [], [], [], [], [], [], []

; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException:

--- The error occurred in sqlmap/sa/CA4020S600_ORA.xml.

--- The error occurred while applying a parameter map.

--- Check the CA4020S600.rInsertRecogAnswer-InlineParameterMap.

--- Check the statement (update failed).

--- Cause: java.sql.SQLException: ORA-00600: 내부 오류 코드, 인수: [koklismem1: r_length is 0], [], [], [], [], [], [], [], [], [], [], []

at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83)

at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)

at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:203)

at org.springframework.orm.ibatis.SqlMapClientTemplate.update(SqlMapClientTemplate.java:378)

at egovframework.rte.psl.dataaccess.EgovAbstractDAO.update(EgovAbstractDAO.java:101)

at com.bridgetec.sa.dao.SaDispatchSaDAO.update(SaDispatchSaDAO.java:61)

at com.bridgetec.sa.dao.SaDispatchSaDAO.excute(SaDispatchSaDAO.java:180)

at com.bridgetec.sa.service.SaDispatchServiceImpl.daoExcute(SaDispatchServiceImpl.java:134)

at com.bridgetec.sa.service.SaDispatchServiceImpl.excute(SaDispatchServiceImpl.java:95)

at com.bridgetec.sa.service.SaDispatchServiceImpl$$FastClassByCGLIB$$14581e26.invoke(<generated>)

at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:191)

at org.springframework.aop.framework.Cglib2AopProxy$CglibMethodInvocation.invokeJoinpoint(Cglib2AopProxy.java:688)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)

at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)

at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:89)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)

at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:621)

at com.bridgetec.sa.service.SaDispatchServiceImpl$$EnhancerByCGLIB$$210a4753.excute(<generated>)

at com.bridgetec.sa.controller.SaDispatchController.invloke(SaDispatchController.java:120)

at sun.reflect.GeneratedMethodAccessor39.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:606)

at org.springframework.web.bind.annotation.support.HandlerMethodInvoker.invokeHandlerMethod(HandlerMethodInvoker.java:176)

at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.invokeHandlerMethod(AnnotationMethodHandlerAdapter.java:426)

at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.handle(AnnotationMethodHandlerAdapter.java:414)

at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:790)

at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:719)

at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:644)

at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:560)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:650)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:731)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)

at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)

at egovframework.rte.ptl.mvc.filter.HTMLTagFilter.doFilter(HTMLTagFilter.java:51)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)

at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)

at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)

at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:219)

at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:110)

at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:506)

at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:169)

at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)

at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:962)

at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)

at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:445)

at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1115)

at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:637)

at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:316)

at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)

at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)

at java.lang.Thread.run(Thread.java:745)

Caused by: com.ibatis.common.jdbc.exception.NestedSQLException:

--- The error occurred in sqlmap/sa/CA4020S600_ORA.xml.

--- The error occurred while applying a parameter map.

--- Check the CA4020S600.rInsertRecogAnswer-InlineParameterMap.

--- Check the statement (update failed).

--- Cause: java.sql.SQLException: ORA-00600: 내부 오류 코드, 인수: [koklismem1: r_length is 0], [], [], [], [], [], [], [], [], [], [], []

at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeUpdate(MappedStatement.java:107)

at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.update(SqlMapExecutorDelegate.java:457)

at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.update(SqlMapSessionImpl.java:90)

at org.springframework.orm.ibatis.SqlMapClientTemplate$9.doInSqlMapClient(SqlMapClientTemplate.java:380)

at org.springframework.orm.ibatis.SqlMapClientTemplate$9.doInSqlMapClient(SqlMapClientTemplate.java:1)

at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:200)

... 56 more

Caused by: java.sql.SQLException: ORA-00600: 내부 오류 코드, 인수: [koklismem1: r_length is 0], [], [], [], [], [], [], [], [], [], [], []

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)

at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)

at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)

at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:955)

at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1169)

at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3285)

at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3390)

at org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:197)

at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeUpdate(SqlExecutor.java:80)

at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.sqlExecuteUpdate(MappedStatement.java:216)

at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeUpdate(MappedStatement.java:94)

... 61 more