노마드오브

* 오라클 SYSTEM 계정에서 새로운 계정 생성 CREATE USER jspid IDENTIFIED BY jsppass; * 생성된 오라클 계정을 활성화 ALTER USER jspid ACCOUNT UNLOCK; * 생성된 오라클 계정에게 DBA라는 역할(롤)을 부여 GRANT DBA TO jspid; CREATE TABLE member ( id varchar2(12) primary key, passwd varchar2(12) not null, name varchar2(12) not null, reg_date date not null ); ALTER TABLE member ADD age NUMBER; ALTER TABLE member ADD gender VARCHAR2(5); ALTER TABLE me..

-- 테이블 생성 시에 제약 조건 이름 지정하여 제약 조건 지정하기create table new_emp1(no number(4) constraint emp1_no_pk primary key,name varchar2(20) constraint emp1_name_nn not null,jumin varchar2(13) constraint emp1_jumin_nn not null constraint emp1_jumin_uk unique,loc_code number(1) constraint emp1_area_ck check(loc_code < 5),deptno varchar2(6) constraint emp1_deptno_fk references dept2(dcode)); -- 테이블 생성 시에 제약 조건 이름 ..

-- SEQUENCE(시퀀스) 생성 create sequence jno_seqincrement by 1start with 100maxvalue 110minvalue 90cyclecache 2; -- SEQUENCE 생성 및 각종 옵션 테스트create table s_order(ord_no number(4),ord_name varchar2(10),p_name varchar2(20),p_qty number); insert into s_order values (jno_seq.NEXTVAL, 'James', 'apple', 5);insert into s_order values (jno_seq.NEXTVAL, 'Ford', 'berry', 3); select * from s_order; select jno_seq..

-- DDL 명령과 딕셔너리 -- CREATEcreate table new_table(no number(3),name varchar2(10),birth date); create table tt02(no number(3,1) default 0,name varchar2(10) default 'NO Name',hiredate date default sysdate); insert into tt02 (no) values (1); -- 한글 이름 테이블 생성create table 한글테이블(컬럼1 number, 컬럼2 varchar2(10), 컬럼3 date); -- Global Tempory Table(임시테이블) 생성하기create global temporary table temp01(no number,name..

-- INITCAPselect ename, INITCAP(ename) "INITCAP"from emp where deptno = 10; select name, INITCAP(name) "INITCAP"from professorwhere deptno = 101; -- LOWER, UPPERselect ename, LOWER(ename) "LOWER", UPPER(ename) "UPPER"from emp where deptno = 10; -- LENGTH, LENGTHBselect ename, LENGTH(ename) "LENGTH", LENGTHB(ename) "LENGTHB"from empwhere deptno = 20; select '서진수' "NAME", LENGTH('서진수') "LENGTH", L..

1. 오라클 설치2. sqlplus 설치3. sqldeveloper 설치4. java jdk 설치4. font 설치 * 오라클 시작 명령어 : sudo service oracle-xe start

터미널 창 열기sqlplus 입력 엔터sys as sysdba 엔터비밀번호 입력 엔터 alter user hr account unlock;alter user hr identified by 1234;

select * from t_reg; select * from t_regwhere regexp_like(text, '[a-z]'); select * from t_regwhere regexp_like(text, '[A-Z]'); select * from t_regwhere regexp_like(text, '[a-zA-Z]'); select * from t_regwhere regexp_like(text, '[a-z] '); select * from t_regwhere regexp_like(text, '[a-z] [0-9]'); select * from t_regwhere regexp_like(text, '[[:space:]]'); select * from t_regwhere regexp_like(text, ..

-- 1. 단순뷰 -- View를 생성하기 위해서는 create view 권한이 필요-- sysdba로 접속해서-- grant create view to scott; 를 해줘야한다. -- view 생성create or replace view v_emp1asselect empno, ename, hiredatefrom emp; -- view에 index 생성 불가create index idx_v_emp_enameon v_emp1(ename); -- 테이블 생성create table o_table (a number, b number); -- view view1 생성create view view1asselect a, bfrom o_table; -- insertinsert into view1 values (1, ..

-- resource_limit = truealter system set resource_limit = true; -- password profile 생성create profile prof_passwd limitfailed_login_attempts 5password_lock_time 30password_life_time 30password_reuse_time 30; -- resource profile 생성create profile prof_resource limitcpu_per_session 1500connect_time 480idle_time 15; --------------------- -- scott가 적용 받고 있는 Profile 확인select username, profilefrom dba_u..