반응형

oracle foreign key 생성/삭제

create table bbs (
 no number not null,
 id varchar2(20) not null,
 bbspasswd varchar2(20) not null,
 title varchar2(100) not null,
 content varchar2(1000) not null,
 regdate date not null,
 readcount number not null,
);

생성
SQL> ALTER TABLE 테이블명 ADD CONSTRAINT fk_bbs_id FOREIGN KEY(id) REFERENCES 참조되는 테이블명(id);

삭제
SQL> ALTER TABLE 테이블명 DROP CONSTRAINT fk_bbs_id;
반응형
반응형

Tibero 테이블 조회, 컬럼 조회, 테이블 정보 조회

//-- 테이블 명 보기
select *
from ALL_TAB_COMMENTS
where TABLE_NAME = ' 테이블 이름 ' ;

//-- 컬럼 명 보기
select *
from ALL_COL_COMMENTS
where TABLE_NAME = ' 테이블 이름 ' ;

//-- 테이블 정보 보기
select *
from ALL_TAB_COLUMNS
where TABLE_NAME = ' 테이블 이름 ' ;



반응형
반응형

Tibero Function 확인 쿼리

--FUNCTION TEXT검색

--FUNCTION TEXT검색
SELECT *
FROM   USER_SOURCE
WHERE  1=1
AND TYPE = 'FUNCTION'
AND    TEXT LIKE  '%REG%'  
ORDER BY NAME
반응형
반응형

Tibero DB rock 걸렸을때~ KILL SESSION

> tbsql sys/tibero
>
> SELECT A.SID, A.SERIAL#  , 'ALTER SYSTEM KILL SESSION '''||A.SID||','||A.SERIAL#||''';'  
    FROM V$SESSION A,V$LOCK B, DBA_OBJECTS C WHERE A.SID = B.SESS_ID AND B.ID1 = C.OBJECT_ID 
     AND B.TYPE = 'TM' AND C.OBJECT_NAME = 'TB_CO_GENO';
>
> alter system kill session '65,1663701';
반응형
반응형

티베로(TIBERO) 시퀀스 생성, 삭제, 변경, 목록 조회

티베로는 오라클처럼 자동증가를 시퀀스로~ 

시퀀스(Sequence)는 순차적으로 부여할 수 있는 고유 값을 생성하는 스키마 객체이다. 시퀀스를 통해 생성된 값은 주로 기본 키(primary key) 또는 유일 키(Unique Key)에 값을 채워 넣는다.

CURRVAL : 시퀀스의 현재 값을 반환한다.
NEXTVAL : 시퀀스의 현재 값을 증가시키고, 증가된 그 값을 반환한다.

-- 시퀀스 생성 
CREATE SEQUENCE SEQ_TEST 
INCREMENT BY 1 
MINVALUE 1 
MAXVALUE 10; 

-- NEXTVAL 의사 컬럼을 통해 시퀀스 처음 접근 
-- 1 출력 
SELECT SEQ_TEXT.NEXTVAL FROM DUAL;
 
 -- 삭제
 DROP SEQUENCE sequence_name;
 
 -- 변경
 ALTER SEQUENCE sequence_name 
 INCREMENT BY 10 
 MAXVALUE 99999999999 
 NOCYCLE CACHE 200;


-- 소유한 시퀀스 조회 
SELECT * 
FROM ALL_SEQUENCES b 
WHERE sequence_owner = '소유주아이디';

Tibero SQL 참조 안내서 

https://technet.tmaxsoft.com/upload/download/online/tibero/pver-20150504-000001/sql-reference/index.html

 

Tibero SQL 참조 안내서

Tibero SQL 참조 안내서  Tibero 6 저작권 © 2019 TmaxData Co., Ltd. All Rights Reserved. 기술서비스센터 Tel : +82-1544-8629 E-Mail : info@tmax.co.kr Restricted Rights Legend All TmaxData Software (Tibero®) and documents are protected by copyright laws and international con

technet.tmaxsoft.com

 

반응형
반응형

 

root bin]# tbsql sys/tibero  

* sys : ID
* tibero : PW

 

tbsql id/pw

@'C:\Users\___\Desktop\sql\sql_file.sql'

반응형
반응형

*** Tibero 접속 
1.  SSH 접속
>  su – root

2. 유저 root에서 Tibero 로 변경
>  su - tibero

3.디비 호출
> export TB_SID=디비명

4. tbsql 아이디/비밀번호
> tbsql 아이디
  pw...

//----------------------------------

 

반응형
반응형

Tibero(티베로) 함수 Function

4. 함수
4.1. 개요
4.1.1. 단일 로우 함수
4.1.2. 집단 함수
4.1.3. 분석 함수
4.2. 함수 목록
4.2.1. ABS
4.2.2. ACOS
4.2.3. ADD_MONTHS
4.2.4. AGGR_CONCAT
4.2.5. APPENDCHILDXML
4.2.6. ASCII
4.2.7. ASCIISTR
4.2.8. ASIN
4.2.9. ATAN
4.2.10. ATAN2
4.2.11. AVG
4.2.12. BITAND
4.2.13. CAST
4.2.14. CEIL
4.2.15. CHARTOROWID
4.2.16. CHR
4.2.17. COALESCE
4.2.18. COMPOSE
4.2.19. CONCAT
4.2.20. CONVERT
4.2.21. CORR
4.2.22. COS
4.2.23. COSH
4.2.24. COUNT
4.2.25. COVAR_POP
4.2.26. COVAR_SAMP
4.2.27. CUME_DIST
4.2.28. CURRENT_DATE
4.2.29. CURRENT_TIME
4.2.30. CURRENT_TIMESTAMP
4.2.31. DBTIMEZONE
4.2.32. DECODE
4.2.33. DECOMPOSE
4.2.34. DELETEXML
4.2.35. DENSE_RANK
4.2.36. DUMP
4.2.37. EMPTY_BLOB
4.2.38. EMPTY_CLOB
4.2.39. EXISTSNODE
4.2.40. EXP
4.2.41. EXTRACT
4.2.42. EXTRACT(XML)
4.2.43. EXTRACTVALUE
4.2.44. FIRST
4.2.45. FIRST_VALUE
4.2.46. FLOOR
4.2.47. FROM_TZ
4.2.48. GREATEST
4.2.49. GROUPING
4.2.50. GROUPING_ID
4.2.51. GROUP_ID
4.2.52. HEXTORAW
4.2.53. INET_ATON
4.2.54. INET_NTOA
4.2.55. INITCAP
4.2.56. INSERTCHILDXML
4.2.57. INSERTCHILDXMLAFTER
4.2.58. INSERTCHILDXMLBEFORE
4.2.59. INSERTXMLAFTER
4.2.60. INSERTXMLBEFORE
4.2.61. INSTR
4.2.62. ISFRAGMENT
4.2.63. KURT
4.2.64. LAG
4.2.65. LAST_DAY
4.2.66. LAST
4.2.67. LAST_VALUE
4.2.68. LEAD
4.2.69. LEAST
4.2.70. LENGTH
4.2.71. LN
4.2.72. LNNVL
4.2.73. LOCALTIMESTAMP
4.2.74. LOG
4.2.75. LOWER
4.2.76. LPAD
4.2.77. LTRIM
4.2.78. MAX
4.2.79. MEDIAN
4.2.80. MIN
4.2.81. MOD
4.2.82. MONTHS_BETWEEN
4.2.83. NEW_TIME
4.2.84. NEXT_DAY
4.2.85. NLSSORT
4.2.86. NLS_INITCAP
4.2.87. NLS_LOWER
4.2.88. NLS_UPPER
4.2.89. NTILE
4.2.90. NULLIF
4.2.91. NUMTODSINTERVAL
4.2.92. NUMTOYMINTERVAL
4.2.93. NVL
4.2.94. NVL2
4.2.95. ORA_HASH
4.2.96. OVERLAPS
4.2.97. PERCENT_RANK
4.2.98. PERCENTILE_CONT
4.2.99. PERCENTILE_DISC
4.2.100. POWER
4.2.101. RANK
4.2.102. RATIO_TO_REPORT
4.2.103. RAWTOHEX
4.2.104. REGEXP_COUNT
4.2.105. REGEXP_INSTR
4.2.106. REGEXP_REPLACE
4.2.107. REGEXP_SUBSTR
4.2.108. REGR_AVGX
4.2.109. REGR_AVGY
4.2.110. REGR_COUNT
4.2.111. REGR_INTERCEPT
4.2.112. REGR_R2
4.2.113. REGR_SLOPE
4.2.114. REGR_SXX
4.2.115. REGR_SXY
4.2.116. REGR_SYY
4.2.117. REMAINDER
4.2.118. REPLACE
4.2.119. REVERSE
4.2.120. ROUND(number)
4.2.121. ROUND(date)
4.2.122. ROWIDTOCHAR
4.2.123. ROW_NUMBER
4.2.124. RPAD
4.2.125. RTRIM
4.2.126. SESSIONTIMEZONE
4.2.127. SIGN
4.2.128. SIN
4.2.129. SINH
4.2.130. SKEW
4.2.131. SQRT
4.2.132. STDDEV
4.2.133. STDDEV_POP
4.2.134. STDDEV_SAMP
4.2.135. SUBSTR
4.2.136. SUM
4.2.137. SYS_CONNECT_BY_PATH
4.2.138. SYS_CONTEXT
4.2.139. SYS_EXTRACT_UTC
4.2.140. SYS_GUID
4.2.141. SYSDATE
4.2.142. SYSTIME
4.2.143. SYSTIMESTAMP
4.2.144. TAN
4.2.145. TANH
4.2.146. TO_CHAR(character)
4.2.147. TO_CHAR(datetime)
4.2.148. TO_CHAR(number)
4.2.149. TO_CLOB
4.2.150. TO_DATE
4.2.151. TO_DSINTERVAL
4.2.152. TO_LOB
4.2.153. TO_MULTI_BYTE
4.2.154. TO_NCHAR
4.2.155. TO_NUMBER
4.2.156. TO_SINGLE_BYTE
4.2.157. TO_TIME
4.2.158. TO_TIMESTAMP
4.2.159. TO_TIMESTAMP_TZ
4.2.160. TO_YMINTERVAL
4.2.161. TRANSLATE
4.2.162. TRIM
4.2.163. TRUNC(number)
4.2.164. TRUNC(date)
4.2.165. TZ_OFFSET
4.2.166. TZ_SHIFT
4.2.167. UID
4.2.168. UNISTR
4.2.169. UPDATEXML
4.2.170. UPPER
4.2.171. USER
4.2.172. USERENV
4.2.173. VAR_POP
4.2.174. VAR_SAMP
4.2.175. VARIANCE
4.2.176. VSIZE
4.2.177. XMLAGG
4.2.178. XMLCAST
4.2.179. XMLCOMMENT
4.2.180. XMLCONCAT
4.2.181. XMLELEMENT
4.2.182. XMLEXISTS
4.2.183. XMLFOREST
4.2.184. XMLPARSE
4.2.185. XMLPI
4.2.186. XMLQUERY
4.2.187. XMLSERIALIZE
4.2.188. XMLTABLE
4.2.189. XMLTRANSFORM

반응형

+ Recent posts