user_tables의 구조(참고용)

SQL> desc user_tables;
이름 널? 유형
—————————————– ——– ——————–
TABLE_NAME NOT NULL VARCHAR2(30)
TABLESPACE_NAME VARCHAR2(30)
CLUSTER_NAME VARCHAR2(30)
IOT_NAME VARCHAR2(30)
PCT_FREE NUMBER
PCT_USED NUMBER
INI_TRANS NUMBER
MAX_TRANS NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NUMBER
MAX_EXTENTS NUMBER
PCT_INCREASE NUMBER
FREELISTS NUMBER
FREELIST_GROUPS NUMBER
LOGGING VARCHAR2(3)
BACKED_UP VARCHAR2(1)
NUM_ROWS NUMBER
BLOCKS NUMBER
EMPTY_BLOCKS NUMBER
AVG_SPACE NUMBER
CHAIN_CNT NUMBER
AVG_ROW_LEN NUMBER
AVG_SPACE_FREELIST_BLOCKS NUMBER
NUM_FREELIST_BLOCKS NUMBER
DEGREE VARCHAR2(20)
INSTANCES VARCHAR2(20)
CACHE VARCHAR2(10)
TABLE_LOCK VARCHAR2(8)
SAMPLE_SIZE NUMBER
LAST_ANALYZED DATE
PARTITIONED VARCHAR2(3)
IOT_TYPE VARCHAR2(12)
TEMPORARY VARCHAR2(1)
SECONDARY VARCHAR2(1)
NESTED VARCHAR2(3)
BUFFER_POOL VARCHAR2(7)
ROW_MOVEMENT VARCHAR2(8)
GLOBAL_STATS VARCHAR2(3)
USER_STATS VARCHAR2(3)
DURATION VARCHAR2(15)
SKIP_CORRUPT VARCHAR2(8)
MONITORING VARCHAR2(3)
CLUSTER_OWNER VARCHAR2(30)
DEPENDENCIES VARCHAR2(8)
COMPRESSION VARCHAR2(8)
DROPPED VARCHAR2(3)

오라클 플래시백 쿼리

delete 쿼리문 입력하다가, 전화받고, where절에 조건 반밖에 안 준 상태로 상큼하게 엔터를 쳐버리는 행동을 하고 바로 해야 할 일;;;

— 삭제된 데이터 복구(오라클 버전 Release 9.2.0.1.0 실행)
SQL>INSERT INTO emp
(SELECT * FROM emp AS OF TIMESTAMP TO_TIMESTAMP(‘2006-01-21 17:16:51’, ‘YYYY-MM-DD HH24:MI:SS’))

— 복구된 데이터 확인
SQL>SELECT * FROM emp;
14 개의 행이 선택되었습니다.

남영성선생님 감사합니다 으흑 T_T

상세설명은 http://www.oracleclub.com/oracle/lecture/LectureInclude.jsp?lectureID=1876&lectureType=BACKUPTIP 에서.

오라클 시작 및 종료

* 오라클 시작

oracle 계정으로 로그인한 뒤 SqlPlus를 실행

$ sqlplus ‘/as sysdba’

sqlplus 에서
> startup
> exit

sqlplus 종료 후
$ lsnrctl start [엔터]

* 오라클 종료

$ lsnrctl stop

$ sqlplus ‘/as sysdba’

> shutdown immediate
> exit

(물론 이 작업 역시 oracle 계정)

[오라클] 합격자에게 학번 부여하기

입시를 치르고 학번을 부여하기 위해 다음과 같은 테이블을 살펴본다.

<<접수테이블>>
수험번호, 이름, 지망학과(3자리, FK), 합격자(합격자 1, 예비 2, 불합격 3)

<<학과코드>>
학과코드(4자리, PK), 학과이름, SORTKEY

<<입시용코드>>
학과코드_입시용(3자리), 학과코드(4자리, FK)

접수테이블에는 CHAR(7)의 학번이라는 필드가 있다.
접수자 중 합격자만 골라, 학과순으로 먼저 소트하고, 다음으로 이름순으로 소트하여 학번을 부여한다. 학번은 앞에 2자리는 06과 같이 년도를 나타내고, 뒤의 5자리는 소트한 순서대로 00001부터 입력한다.

해명은 아직 훈련중….. 이 문제를 해결하라고 하시는데, 사실 까짓거 변수 받아서 돌리면 간단하겠지만. PHP나 뭐나 쓰지 말고 그냥 SQL로만 하자는 것이 오늘의 관건. 대충은 알지만 sql에 아직 익숙하지 못하니 땅 파는 수 밖에;;

일단은 다음과 같이 쿼리하여 임시로 학번을 매겨보았다.

SELECT 접수테이블.수험번호, 접수테이블.이름, 학과코드.학과이름, 학과코드.SORTKEY,
(’06’|| lpad(rank() over (ORDER BY 학과코드.SORTKEY, 접수테이블.이름),5,’0′)) as rank, 접수테이블.학번
FROM 학과코드, 입시용코드, 접수테이블
WHERE (접수테이블.지망학과=입시용코드.학과코드_입시용)
AND (입시용코드.학과코드=학과코드.학과코드)
and (접수테이블.합격자=’1′)
ORDER BY 학과코드.SORTKEY, 접수테이블.이름;

여기서 rank에 들어간 가학번을 실제로 접수테이블에 입력하기 위해, 일단 위의 내용을 뷰로 만들고 다음과 같이 돌려보았다.

create view view_test as
SELECT 접수테이블.수험번호, 접수테이블.이름, 학과코드.학과이름, 학과코드.SORTKEY,
(’06’|| lpad(rank() over (ORDER BY 학과코드.SORTKEY, 접수테이블.이름),5,’0′)) as rank, 접수테이블.학번
FROM 학과코드, 입시용코드, 접수테이블
WHERE (접수테이블.지망학과=입시용코드.학과코드_입시용)
AND (입시용코드.학과코드=학과코드.학과코드)
and (접수테이블.합격자=’1′)
ORDER BY 학과코드.SORTKEY, 접수테이블.이름;

declare
cursor c is select * from view_test;
begin
for rec in c loop
dbms_output.put_line(rec.rank || ‘ ‘ || rec.수험번호);
update 접수테이블 set 학번=rec.rank where 수험번호=rec.수험번호;
end loop;
commit;
end;
/

제대로 입력되었다. 아예 뷰 대신 select문을 그 자리에 집어넣어 한번에 처리할 수도 있지만, 아직 정신없어서 두번에 나누었다.

또다른 방법으로 아예 다음과 같이 할 수 있다고도 한다.

SELECT ‘update sisjups set 학번=”’||
(’06’|| lpad(rank() over (ORDER BY 학과코드.SORTKEY, 접수테이블.이름),5,’0′))||
”’ where 수험번호=”’||접수테이블.수험번호||”’;’
FROM 학과코드, 입시용코드, 접수테이블
WHERE (접수테이블.지망학과=입시용코드.학과코드_입시용)
AND (입시용코드.학과코드=학과코드.학과코드)
and (접수테이블.합격자=’1′)
ORDER BY 학과코드.SORTKEY, 접수테이블.이름
/

이렇게 실행하면 각 수험번호에 대해 학번을 부여하는 update문이 펼쳐진다. 이 내용을 덤프하여 실행해도 같은 결과를 얻을 수 있다.

오라클에서 오늘 날짜를 가져올 때

SELECT SYSDATE FROM DUAL

DUAL은 VARCHAR2(1)인 DUMMY 필드 하나만 가진 더미 테이블이다.

따로 테이블이 필요없는 문장에서 사용하기 위해 그냥 있는 거다. 날짜나 숫자 계산할 때 특별한 테이블에서 계산하는 거 아닐때 쓴다.

테이블 이름이 더미가 아니라 듀얼이다. 잘못 기억한게 인이 박혀서;; 젠장.

ORA-12540 TNS:내부한계제한

오라클에서는 프로파일을 통해 사용자의 리소스를

제한 할 수 있죠. 사용자별로 접속가능한 세션의 수나,

idle time의 최대치등을 설정할 수 있는데,

그냥 다음을 참고 하세요..

create profile tempprofile limit
sessions_per_user max_value
cpu_per_session max_value
cpu_per_call max_value
connect_time max_value
idle_time max_value
logical_reads_per_session max_value
logical_reads_per_call max_value
composite_limit max_value
private_sga max_value

max_value :== { integer | unlimited | default }

cpu_per_session은 1/100초 단위로 측정한 CPU사용 시간
sessions_per_user 각 사용자마다 허용된 동시 세션 수
connect_time 분 단위로, 경과된 접속시간
idle_time 분단위로, 비활동시간.
logical_reads_per_session 데이터 블록 수
private_sga 바이트단위로 SGA내의 전용 공간 (MTS만)

프로파일을 만들고 사용자에게 적용시키면 됩니다.

create user tempuser identified by tempuser
default tablespace data01
temporary tablespace temp
quota unlimited on data01
profile tempprofile;

이미 존재한다면

alter user tempuser profile tempprofile;