행복한 스마일맨 ^^*

ANSI Query(SQL)

• ANSI(American National Standards Institute, 미국 국립 표준 협회)에서 제시한 SQL에 대한 보편적인 문법

※ https://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins

오라클 Join 예제.

테이블 : T_MEMBER

테이블 : T_DEPARTMENT

Inner Join
-- (교집합) 각 테이블에서 조인 조건에 일치되는 데이터만 가져온다.

(ANSI) SELECT m.*, d.* FROM T_MEMBER m INNER JOIN T_DEPARTMENT d ON m.DEPT_IDX = d.DEPT_IDX;
(Oralce) SELECT m.*, d.* FROM T_MEMBER m, T_DEPARTMENT d WHERE m.DEPT_IDX = d.DEPT_IDX;

Outer Join
-- 조인 조건에 일치하는 데이터 및 일치하지 않은 데이터를 모두 SELECT 한다.
-- 조인 조건에 일치하는 데이터가 없다면 NULL로 가져온다.
-- 주(main) 테이블이 어떤 테이블인지가 중요하다.

• Left Outer Join

(ANSI) SELECT m.*, d.* FROM T_MEMBER m LEFT OUTER JOIN T_DEPARTMENT d ON m.DEPT_IDX = d.DEPT_IDX ORDER BY m.MEM_ID;
(Oralce) SELECT m.*, d.* FROM T_MEMBER m, T_DEPARTMENT d WHERE m.DEPT_IDX = d.DEPT_IDX(+) ORDER BY m.MEM_ID;

• Right Outer Join

(ANSI) SELECT m.*, d.* FROM T_MEMBER m RIGHT OUTER JOIN T_DEPARTMENT d ON m.DEPT_IDX = d.DEPT_IDX ORDER BY m.MEM_ID;
(Oralce) SELECT m.*, d.* FROM T_MEMBER m, T_DEPARTMENT d WHERE m.DEPT_IDX(+) = d.DEPT_IDX ORDER BY m.MEM_ID;

• Full Outer Join

(ANSI) SELECT m.*, d.* FROM T_MEMBER m FULL OUTER JOIN T_DEPARTMENT d ON m.DEPT_IDX = d.DEPT_IDX ORDER BY m.MEM_ID;
(Oralce) X

Cross Join
-- 두 테이블 조인 시 가능한 모든 경우의 레코드를 SELECT 한다.
-- 경우의 수 : N * M

(ANSI) SELECT m.*, d.* FROM T_MEMBER m CROSS JOIN T_DEPARTMENT d ORDER BY m.MEM_ID, d.DEPT_IDX;
(Oralce) SELECT m.*, d.* FROM T_MEMBER m, T_DEPARTMENT d ORDER BY m.MEM_ID, d.DEPT_IDX;

Self Join
-- 테이블이 자기 자신을 마치 다른 테이블처럼 취급하여 조인한다.

(ANSI) SELECT m.*, d.* FROM T_MEMBER m JOIN T_MEMBER d ON m.MEM_ID = d.MEM_ID ORDER BY m.MEM_ID;
(Oralce) SELECT m.*, d.* FROM T_MEMBER m, T_MEMBER d WHERE m.MEM_ID = d.MEM_ID ORDER BY m.MEM_ID;

# 세션 레벨.(현재 세션 정보)
SELECT parameter, value FROM nls_session_parameters;
 - alter session set nls_XXX 로 변경 가능.

# 인스턴스 레벨.
SELECT PARAMETER, value FROM nls_instance_parameters;
 = SELECT name, VALUE FROM V$PARAMETER where name like '%nls%';

# DB 레벨.(설치시 정보 - 변경 불가)
SELECT parameter, value FROM nls_database_parameters;
= SELECT PARAMETER, VALUE FROM V$NLS_PARAMETERS;
= SELECT name, value$ FROM sys.props$ where name like '%NLS%';

set pagesize 30set linesize 300

column PARAMETER format a30
column VALUE format a30

column value$ format a30

#################
# 오라클 구동 방법 #
#################
 - 참고 사이트 : http://calmmass.tistory.com/246
                       http://blog.naver.com/syun?Redirect=Log&logNo=60008399058

(oracle 계정으로 접속)
$ cd $ORACLE_HOME/bin

# 오라클 시작.
$ ./dbstart

# 리스너 시작.
$ ./lsnrctl start

================================================================================================
- 오라클 시작의 또 다른 방법
- 2가지 방법
================================================================================================

------------------------------------------------------------------------------------------------
- 방법 1 : 한번에 구동 완료
------------------------------------------------------------------------------------------------
$ sqlplus '/as sysdba'
SQL> startup
  (한번에 구동 완료. instance -> mount -> open)

------------------------------------------------------------------------------------------------
- 방법 2 : 단계별 구동
------------------------------------------------------------------------------------------------
................................................................................................................................................
[ 1 단계 : instance start ]
................................................................................................................................................
# 인스턴스 상태를 확인 해본 결과 아직 데이터베이스를 기동하지 않았습니다.
SQL> select status from v$instance;
select status from v$instance
*
ERROR at line 1:
ORA-01034: ORACLE not available

# instance start
SQL> startup nomount;
ORACLE instance started.

# 파라미터 파일 읽기 시작
# 파라미터 파일 확인
SQL> show parameter processes

# 상태 확인
SQL> SELECT status FROM v$instance;
STATUS
------------------------
STARTED

................................................................................................................................................
[ 2 단계 : mount start ]
................................................................................................................................................
SQL> ALTER DATABASE mount;
Database altered.

SQL> SELECT status FROM v$instance;
STATUS
------------
MOUNTED

................................................................................................................................................
[ 3 단계 : open start ]
................................................................................................................................................
# 데이터베이스 open 시작
SQL> ALTER DATABASE open;
Database altered.

SQL> SELECT status FROM v$instance;
STATUS
------------
OPEN

jdbc에서 resultSet.getDate() 하면 new java.util.Date();
 - 시간, 분, 초 정보는 가지고 있지 않다.

ResultSet 의 메소드를 살펴 보면.

java.sql.ResultSet


public abstract Date getDate(String columnName) throws SQLException
 Get the value of a column in the current row as a java.sql.Date object.
 Returns: the column value; if the value is SQL NULL, the result is null


public abstract Time getTime(String columnName) throws SQLException
 Get the value of a column in the current row as a java.sql.Time object.
 Returns: the column value; if the value is SQL NULL, the result is null

getDate() 나 getTime() 은 java.util.Date 객체를 반환하는 것이 아니라
java.sql 패키지의 java.sql.Date와 java.sql.Time 객체를 반환하고 있습니다.


반면

public abstract Timestamp getTimestamp(String columnName) throws SQLException
 Get the value of a column in the current row as a java.sql.Timestamp object.
 Returns: the column value; if the value is SQL NULL, the result is null


getTimestamp() 는 "java.sql.Date, java.sql.Time, java.sql.Timestamp 는 java.util.Date를 상속받아서
모든 정보를 가지고 있다.



2009.10.21 00:00:00  <-- rs.getDate()
1970.01.01 01:15:38  <-- rs.getTime()
2009.10.21 01:15:38  <-- rs.getTimestamp()

SELECT GREATEST(4, CEIL(NUM_ROWS/
((ROUND(((1958-(INI_TRANS*23))*
((100-PCT_FREE)/100))/AVG_ROW_LEN)))) * 2048) 
TABLESIZE_KBYTES
FROM USER_TABLES
WHERE TABLE_NAME = UPPER('&1'); 

테이블이 사용중인 블록 크기를 계산해주는 SQL
/*--------------------------------------------------------------------------*/
/* TABLE이 사용하는 블럭 크기를 구하는 스크립트...
/* 사용법 : 1) DBA 권한으로 로그인한다.
/* 2) SQL> @TAB_BLOCK [TABLE명]
/* NOTICE : SUM(BLOCKS)는 사용하는 블럭의 갯수이며 사이즈는 
/* DB_BLOCK_SIZE를 곱하여 얻을 수 있다.
/*--------------------------------------------------------------------------*/
SELECT OWNER, TABLESPACE_NAME, SEGMENT_NAME, SUM(BLOCKS)
FROM DBA_EXTENTS
WHERE SEGMENT_NAME = UPPER('&1')
GROUP BY OWNER, TABLESPACE_NAME, SEGMENT_NAME
/

/* 테이블스페이스별 디스크 사용량 보기 */
SELECT SUBSTRB(A.TABLESPACE_NAME, 1,16) as TABLESPACE,
TO_CHAR(A.TOTAL, '999,999,999,990') as "총량(바이트)",
TO_CHAR(B.FREE, '999,999,999,990') as "남은량(바이트)",
TO_CHAR(A.BLOCKS, '9,999,990') as "총블럭",
TO_CHAR(C.BLOCKS, '9,999,990') as "사용블럭",
TO_CHAR(100*NVL(C.BLOCKS,0)/A.BLOCKS, '999.99') as "사용율%"
FROM (SELECT TABLESPACE_NAME,
SUM(BYTES) as TOTAL,
SUM(BLOCKS) as BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME
) A,
(SELECT TABLESPACE_NAME,
SUM(BYTES) as FREE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME
) B,
(SELECT TABLESPACE_NAME,
SUM(BLOCKS) as BLOCKS
FROM DBA_EXTENTS
GROUP BY TABLESPACE_NAME
) C
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+)
AND A.TABLESPACE_NAME = C.TABLESPACE_NAME(+)
ORDER BY A.TABLESPACE_NAME ;

SELECT SUBSTRB(TABLESPACE_NAME,1,20) AS "테이블스페이스",
SUBSTRB(FILE_NAME, 1, 26) AS "파일명",
TO_CHAR(BLOCKS,'999,999,990') as " 블럭수",
TO_CHAR(BYTES/1024/1024,'99,999,999') as " 크기(MB)"/*,
decode(STATUS, 'AVAILABLE', '사용', '중지') as "상태"*/
FROM SYS.DBA_DATA_FILES
WHERE TABLESPACE_NAME like 'REC_%'
ORDER BY TABLESPACE_NAME, FILE_NAME ;