# DBMS
Database 관련 사항 정의
# Management Query
- Synonym 권한 등록 및 해제
```
GRANT SELECT, INSERT ON ZHR0010S TO ORAVOP;
revoke SELECT, INSERT on ZHR0010S from ORAVOP CASCADE CONSTRAINTS;
```
- 파일 경로 변경
```
UPDATE tb_customers_type_files set FILE_PATH = replace( FILE_PATH,'/weblogic/bea/domains/csswas/css/FILES/','/wasfiles/csswas/cssServer/FILES/');
```
- 테이블 , 컬럼 속성 구하기 SQL
```
1 SELECT A1.TABLE_COMMENTS TABLE_COMMENTS
2 , A1.TABLE_NAME TABLE_NAME
3 , A1.COLUMN_COMMENTS COLUMN_COMMENTS
4 , A1.COLUMN_NAME COLUMN_NAME
5 , (CASE
6 WHEN B1.CONSTRAINT_TYPE = 'P'
7 THEN 'Y'
8 END) PK_FLAG
9 , (CASE
10 WHEN B1.CONSTRAINT_TYPE = 'R'
11 THEN 'Y'
12 END) FK_FLAG
13 , A1.NULL_FLAG
14 , A1.DATA_TYPE
15 , A1.DATA_LENGTH
16 FROM (
17 SELECT B.COMMENTS TABLE_COMMENTS
18 , A.TABLE_NAME TABLE_NAME
19 , C.COMMENTS COLUMN_COMMENTS
20 , A.COLUMN_NAME COLUMN_NAME
21 , (CASE A.NULLABLE
22 WHEN 'Y'
23 THEN 'Y'
24 END) NULL_FLAG
25 , A.DATA_TYPE DATA_TYPE
26 , (CASE
27 WHEN A.DATA_TYPE IN ('CHAR', 'VARCHAR2')
28 THEN '(' || A.DATA_LENGTH || ')'
29 WHEN A.DATA_TYPE = 'NUMBER'
30 AND A.DATA_SCALE = 0
31 AND A.DATA_PRECISION IS NOT NULL
32 THEN '(' || A.DATA_PRECISION || ')'
33 WHEN A.DATA_TYPE = 'NUMBER'
34 AND A.DATA_SCALE <> 0
35 THEN '(' || A.DATA_PRECISION || ',' || A.DATA_SCALE
36 || ')'
37 END
38 ) DATA_LENGTH
39 FROM USER_TAB_COLUMNS A
40 , USER_TAB_COMMENTS B
41 , USER_COL_COMMENTS C
42 WHERE (A.TABLE_NAME = B.TABLE_NAME)
43 AND ( A.TABLE_NAME = C.TABLE_NAME
44 AND A.COLUMN_NAME = C.COLUMN_NAME
45 )
46 ) A1
47 , (
48 SELECT A.TABLE_NAME
49 , A.COLUMN_NAME
50 , B.CONSTRAINT_TYPE
51 FROM USER_CONS_COLUMNS A
52 , USER_CONSTRAINTS B
53 WHERE (A.CONSTRAINT_NAME = B.CONSTRAINT_NAME)
54 AND B.CONSTRAINT_TYPE IN ('P', 'R')
55 ) B1
56 WHERE (
57 A1.TABLE_NAME = B1.TABLE_NAME(+)
58 AND A1.COLUMN_NAME = B1.COLUMN_NAME(+)
59 )
60 ORDER BY
61 A1.TABLE_NAME
```
- 테이블 row 수 구하기
```
1 SELECT
2 TABLE_NAME,
3 TO_NUMBER(
4 EXTRACTVALUE(
5 XMLTYPE(
6 DBMS_XMLGEN.GETXML('SELECT COUNT(*) C FROM '||TABLE_NAME))
7 ,'/ROWSET/ROW/C')) COUNT
8 FROM
9 USER_TABLES
10 ORDER BY
11
```