오라클 쿼리 팁
◆ DB에 있는 모든 Table 이름보기
select table_name from user_tables
◆ Table의 Primary Key 찾기
select * from user_ind_columns where table_name =
''CodeTable''
◆ 인수전달
select * from user_ind_columns where table_name = ''&1''
===>; save key
SQL> start key CodeTable
◆ 서로 연결되는 Key를 찾는 방법
select constraint_name, constraint_type,
r_constraint_name
from user_constraints where table_name = ''TABLE_NAME
◆ TABLE 구조 보기
DESC TABLE_NAME
◆ Constraint 확인
select table_name, constraint_name, constraint_type
from user_constraints
where table_name in (''DEPARTMENT'',''EMPLOYEE'');
◆ 테이블 COPY 하기
create table emp_41
as
select id, last_name,
userid, start_date from s_emp
where dept_id = 41;
===> where절에 엉뚱한
조건을 주면 emp_41이란 이름으로 테이블이 만들어진다.
◆ 선택한 Row만큼만 보여주기
select * from tmp_table
where rownum <= 100
---> 이렇게 하면 데이터가 10000건이 있더라도, 1~100건만 보여주게 된다.
◆ 오라클의 모든 유저 보기
select * from all_users
◆ Parameter정보 보기 (한/영코드값, 버젼정보등을 볼수있다.)
select * from
nls_database_parameters
---> 이때, NLS_CHARACTERSET의 Value가 KO16KSC5601 이면
한글...
US7ASCII 이면 영문이다. ---> regedit에서 편집하면 간단히 해결.
◆ Space 있는 값을 Null로 비교
RTRIM(a.ymd_myun) IS NULL
◆ Desc명령으로 Table구조 보는 효과와 같은 방법
SELECT column_name, data_type,
data_length, nullable FROM cols
WHERE table_name = ''YTB_CARCOM''
---> 반드시 테이블명은 대문자 이어야 한다.
◆ Function Script 보는 방법.
select text from user_source where name =
''FUNCTION_NAME''
◆ 요일 찾는 방법.
select TO_CHAR(sysdate,''D'') from dual
오라클: 관리 명령 모음....
좀 예전에 정리한 것이라서리...쩝.
7.x때... ㅋㅋ
지금도 사용할 수
있습니다. 툴만 사용하시던 분들에게도 도움이 되었으면 합니다.
사실은 우리와이프가 정리한 것...쩝(ㅠㅠ)
/********************************************/
/******* table space (cityinsa )create ******/
/********************************************/
ORACLE#] sqldba
lmode=y
SQLDBA> connect internal
SQLDBA> drop
tablespace cityinsa;
SQLDBA> create tablespace cityinsa
datafile
'usr2/oracle/dbs/cityinsa.dbf' size 150M
default storage(
initial 1M
next 1M
pctincrease 0
);
/********************************************/
/**** temp table(
cityts ) space create *****/
/********************************************/
SQLDBA> drop
tablespace cityts;
SQLDBA> create tablespace cityts datafile
'usr2/oracle/dbs/cityts.dbf' size 100M
default storage(
initial 1M
next 1M
pctincrease 0
);
/********************************************/
/****** user
(cityinsa/insa) create ******/
/********************************************/
SQLDBA> drop
user cityinsa;
SQLDBA> create user cityinsa identified by insa
default tablespace
cityinsa temporary tablespace cityts;
/***** grant 시키는 방법 ****************/
SQLDBA>
grant connect, resource to cityinsa;
/********************************************/
/************* ctl
file ********************/
/********************************************/
load data
infile 'fgibon.dat'
into table fgibon
(gb_jumin_no position( 01:13) char,
gb_kikwan position( 14:23)
char,
gb_name position(24:31) char,
gb_num
position(32:35) integer external)
/********************************************/
/******* oracle
error message display *******/
/********************************************/
[인사#] oerr ora
sqlca.sqlcode
/********************************************/
/****** oracle data
'load' command *******/
/********************************************/
sqlload
userid=cityinsa/insa control=$1.ctl log=$1.log
/********************************************/
/******** db export ************************/
/********************************************/
[인사#] exp
cityinsa/insa
[인사#] exp system/manager owner=cityinsa
file=cityinsa.dmp
log=cityinsa.log
여기서 log와 full는 없어도 됨
index=y를 넣어주면 인덱스 받아줌(default로 'y').
/********************************************/
/********** db import **********************/
/********************************************/
[인사#] imp
cityinsa/insa
[인사#] imp system/manager file=cityinsa.dmp full=y
/********************************************/
/* table space에 내용이 있을경우 강제로 drop*/
/* table space
name: cityinsa */
/********************************************/
SQLDBA> drop tablespace cityinsa including contents;
/********************************************/
/***** user drop 시키는 방법 *****/
/***** db name:
cityinsa *****/
/********************************************/
SQLDBA> drop user cityinsa ;
/******************************************************/
/**** db의 dbf file을 그냥 rm 명령으로 지웠을 경우 ****/
/******************************************************/
SQLDBA> startup mount;
SQLDBA> alter database datafile
'/users/INSA/dbs/cityinsa.dbf'
offline drop;
SQLDBA> alter database open;
SQLDBA> drop tablespace
cityinsa including contents;
/********************************************/
/***** database
tablespace 정보 보기 ********/
/********************************************/
Sqlplus> select
* from v$datafile;
select * from sys.dba_data_files;
/********************************************/
/*****
.dbf file name change 방법 ********/
/********************************************/
1. 데이타베이스 backup을
시행하고 데이타베이스의 모든 작업을
중단하고 작업을 실시한다.
2. SQLDBA> alter tablespace cityinsa offline;
3. os copy 명령을 사용하여 새로운 directory에 file을 copy한다.
[인사#] cp /users/INSA/dbs/cityinsa.dbf /usr2/INSA/dbs/cityinsa.dbf
4. SQLDBA> alter tablespace cityinsa rename datafile
'/users/INSA/dbs/cityinsa.dbf' to
'/usr/INSA/dbs/cityinsa.dbf';
5. SQLDBA> alter tablespace cityinsa online;
6. os rm 명령을 사용하여 예전 file을 삭제한다.
< 위의 작업을 하기위해서는 old file과 new file이 존재해야한다. 또한 데이타 화일의 이
름이
변경 되었으므로 SQLDBA> alter database backup controlfile to 'filename' >
/********************************************/
/*****
rollback segment space 보는법 ***/
/********************************************/
SQLDBA> select
* from dba_free_space
where tablespace_name='RBS';
/********************************************/
/*****
system segment space 보는법 ***/
/********************************************/
SQLDBA> select
* from dba_free_space
where tablespace_name='SYSTEM';
SQLDBA> select segment_name, initial_extent, next_extent,
max_extents
from
dba_rollback_segs;
/********************************************/
/*****
rollback segment (RBS) 부족할 경우 ***/
/********************************************/
SQLDBA>alter
tablespace rbs add datafile
'/ORACLE/oracle/dbs/rbs_1.dbf' size 10M;
참고: rbs는 rollback tablespace 이름이고
rbs_1.dbf는
새로 늘려주는 tablespace이름.
/********************************************/
/*****
database tablespace가 recover일때 ***/
/********************************************/
SQLDBA>alter
database recover automatic tablespace cityinsa;
SQLDBA>alter
tablespace cityinsa online;
/********************************************/
/*****
database link 만들기 ***/
/********************************************/
ECRM> create
database link ebiz_db_ecrm.ebiz_dev
connect to ecrm
identified by akwldrk0
using 'ebiz_db'
참고: test ecrm 에서 메인 ebiz_db의 ecrm 유저의 db를
사용하고자 함.
[SELECT INTO 와 같은 구문]
create table bar [UNRECOVERABLE] as select * from
원본테이블
---------------------------------------------------------------
DESC
테이블명
---------------------------------------------------------------
[동일한
테이블에 있는 내용을 동일한 컬럼에 입력할 경우]
UPDATE TABLE_NAME SET REGDATE =
APPDATE
---------------------------------------------------------------
[쿼리문]
alert
table supply modify (name varchar(10) null)
alert table supply add (name
varchar(10)
null)
---------------------------------------------------------------
[INSERT SELECT]
insert into wavy_clubMember (memberid, clubid, membername,
email, company, grade)
select R.memberid, R.clubID, R.memberName, M.email,
M.company, 'M'
from wavy_clubRecommend R, wavy_member M
where
R.memberID = M.mid and R.CLUBID =
16
---------------------------------------------------------------
[시퀀스 사용]
create sequence aa_seq
start with 1
increment by
1
minvalue 1
nocycle
order;
insert into tableName (seq) values
(aa_seq.nextval)
---------------------------------------------------------------
[날짜 처리]
select regDate from member where substr(to_char(regDate),0,10) =
substr(to_char( add_months(sysdate, -24) ), 0, 10)
select to_char(sysdate, 'yyyymmdd') from dual
add_months(str, -24) 는 str에서 24개월을 뺀 것과
같다.
---------------------------------------------------------------
[일반 함수]
substr(str, 0, 10) 은 left(str, 10)과 다른 의미
select nvl(max(item_no), 0) from club_item where club_id='aaaa';
연산자 설명
AND 두 조건이 모두 충족되어야 합니다.
OR 최소한 한 가지 조건이 충족되어야 합니다.
NOT 다음에 오는 조건 제외
LIKE 패턴과 일치
IN 값 목록과 일치
BETWEEN 값 범위와 일치
= 같음
<> 같지 않음
< 보다 작음
> 보다 큼
<= 보다 작거나 같음
>= 보다 크거나 같음
+ 더하기
- 빼기
/ 나누기
* 곱하기