XE 21에서 계정만들기

SYSTEM 계정으로

 

ALTER SESSION SET "_ORACLE_SCRIPT"=TRUE ;

CREATE USER user01 IDENTIFIED BY 1234;

alter user user01 default tablespace users quota unlimited on users;

grant create session to user01;  -- 접속권한

grant create table to user01;

grant create sequence to user01;

GRANT CREATE PROCEDURE TO user01;

 

 

참고)

SELECT * FROM ALL_users; // 모든 계정에 대한 정보 확인

SELECT * FROM DBA_users; // 모든 계정에 대한 정보 확인

SELECT username FROM ALL_users; // 모든 계정에 대한 이름 확인

SELECT username FROM DBA_users; // 모든 계정에 대한 이름 확인

SELECT username, account_status, lock_date FROM DBA_users; // 모든 계정에 대한 이름, 계정 잠금 상태 확인

Alter USER [user_name] ACCOUNT UNLOCK. //잠금 계정 풀기

 

 

user01계정으로...

 

create table guestbook  
(
id number(10) primary key,
title varchar2(1000) not null,
contents clob, 
writer varchar2(40),
wdate date
);

 

alter table guestbook add url varchar(400);  -- url 필드 추가

-- DBMS마다 자동증가 오라클은 시퀀스라는 별도의 객체를 만든다
-- MySQL - auto increment   MSSQL - 일련번호 
create sequence Board_SEQ;

select BOARD_SEQ.nextval from dual;  -- 오라클은 무조건 from  절 뒤에 테이블이 와야한다 
-- 더미테이블:가짜테이블 dual 

insert into guestbook  (id, title, contents, writer, wdate) 
values( board_seq.nextval, '호텔리어', '글로벌 명품 호텔', '선주', sysdate);

insert into guestbook  (id, title, contents, writer, wdate) 
values( board_seq.nextval, '여행자', '여행 마니아', '선정', sysdate);

insert into guestbook  (id, title, contents, writer, wdate) 
values( board_seq.nextval, '부동산실장', '누구나부자될수있다', '대한량', sysdate);

 

insert into guestbook  (id, title, contents, writer, wdate) values( board_seq.nextval, '딸기title', '딸기가 좋아', '딸기contents', sysdate);
insert into guestbook  (id, title, contents, writer, wdate) values( board_seq.nextval, '바나나title', '딸기가 좋아', '바나나contents', sysdate);
insert into guestbook  (id, title, contents, writer, wdate) values( board_seq.nextval, '망고title', '딸기가 좋아', '망고contents', sysdate);
insert into guestbook  (id, title, contents, writer, wdate) values( board_seq.nextval, '사과title', '딸기가 좋아', '사과contents', sysdate);
insert into guestbook  (id, title, contents, writer, wdate) values( board_seq.nextval, '유채title', '딸기가 좋아', '유채contents', sysdate);
insert into guestbook  (id, title, contents, writer, wdate) values( board_seq.nextval, '커피title', '딸기가 좋아', '커피contents', sysdate);
insert into guestbook  (id, title, contents, writer, wdate) values( board_seq.nextval, '삼겹살title', '딸기가 좋아', '삼겹살contents', sysdate);
insert into guestbook  (id, title, contents, writer, wdate) values( board_seq.nextval, '팥빙수title', '딸기가 좋아', '팥빙수contents', sysdate);
insert into guestbook  (id, title, contents, writer, wdate) values( board_seq.nextval, '양장피title', '딸기가 좋아', '양장피contents', sysdate);
insert into guestbook  (id, title, contents, writer, wdate) values( board_seq.nextval, '군만두title', '딸기가 좋아', '군만두contents', sysdate);
insert into guestbook  (id, title, contents, writer, wdate) values( board_seq.nextval, '마키야또title', '딸기가 좋아', '마키야또contents', sysdate);
insert into guestbook  (id, title, contents, writer, wdate) values( board_seq.nextval, '짤뽕title', '딸기가 좋아', '짤뽕contents', sysdate);
insert into guestbook  (id, title, contents, writer, wdate) values( board_seq.nextval, '서영이title', '딸기가 좋아', '서영이contents', sysdate);
insert into guestbook  (id, title, contents, writer, wdate) values( board_seq.nextval, '선생님title', '딸기가 좋아', '선생님contents', sysdate);
insert into guestbook  (id, title, contents, writer, wdate) values( board_seq.nextval, '스프링title', '딸기가 좋아', '스프링contents', sysdate);
insert into guestbook  (id, title, contents, writer, wdate) values( board_seq.nextval, '써머title', '딸기가 좋아', '써머contents', sysdate);
insert into guestbook  (id, title, contents, writer, wdate) values( board_seq.nextval, '어텀title', '딸기가 좋아', '어텀contents', sysdate);
insert into guestbook  (id, title, contents, writer, wdate) values( board_seq.nextval, '윈터title', '딸기가 좋아', '윈터contents', sysdate);

select * from guestbook  ;

commit;

create table address(
    seq number(10) primary key,
    name varchar2(40) not null,
    phone varchar2(40) not null,
    email varchar2(40),
    address varchar2(200),
    wdate date
);

create sequence address_seq;

select address_seq.nextval from dual;

insert INTO address(seq, name, phone, email, address, wdate) values(address_seq.nextval, '허기져','010-1111-2222','fake@gmail.com','서울 강서구',sysdate);

 

insert into address(seq, name, phone, email, address, wdate) values(address_seq.nextval, '짜장면','010-2222-3333','swan@gmail.com','서울 마포구',sysdate);

 

select * from address;

 

commit;

 

create table member (

    member_id number(10) primary key,

    user_id varchar2(40),

    password varchar2(40),

    username varchar2(40),

    phone varchar2(40),

    email varchar2(40),

    wdate date);

 

insert into member(member_id, user_id, password, username, phone, email, wdate) values (1, 'test', '1234','홍길동', '010-0000-0001', 'hong@dddd.com', sysdate);

 

commit;