오라클 팁

오라클 쿼리 팁

◆ 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  값 범위와 일치
= 같음
<> 같지 않음
< 보다 작음
> 보다 큼
<= 보다 작거나 같음
>= 보다 크거나 같음
+ 더하기
- 빼기
/ 나누기
* 곱하기