Ⅰ. Tablespace
오라클 데이터베이스 사용자인 HR의 default tablespace 무엇인지 조회해 보기
SYS
select *
from dba_users
where username = 'HR';
-- HR 의 default tablespace는 USERS 이다.
오라클 데이터베이스 사용자 계정 생성하기
SYS
create user testuser01 identified by eclass;
-- 사용자 계정명은 testuser01이고, 비밀번호는 eclass인 사용자 계정을 생성한다.
testuser02 계정 새로 만들기
SYS
create user testuser02 identified by eclass
default tablespace users;
grant create session to testuser02;
grant create table to testuser02;
-- 사용자 계정명은 testuser02이고, 비밀번호는 eclass인 사용자 계정을 생성한다.
-- 이때, testuser02의 default tablespace는 users이다.
-- testuser02에게 오라클서버에 접속이 가능하도록 권한을 부여해 준다.
-- testuser02에게 테이블을 생성할 수 있는 권한을 부여해 준다.
TESTUSER02
show user;
▷ 결과
USER이(가) "TESTUSER02"입니다.
-- 'testuser02에서작업한것'이라는 이름으로 sql 파일을 저장한다.
TESTUSER02
create table testCart
(userid varchar2(10)
,productCode varchar2(20)
,cnt number(2)
);
오류 보고 -
ORA-01950: no privileges on tablespace 'USERS'
01950. 00000 - "no privileges on tablespace '%s'"
*Cause: User does not have privileges to allocate an extent in the
specified tablespace.
*Action: Grant the user the appropriate system privileges or grant the user
space resource on the tablespace.
-- 어떠한 오라클 사용자가 테이블을 만들 때 tablespace를 명시해야 하지만 명시하지 않으면 해당 사용자의 기본 tablespace(default space)에 들어온다.
SYS
select *
from dba_users;
SYS
alter user testuser02
quota 48m on users;
TESTUSER02
create table testCart
(userid varchar2(10)
,productCode varchar2(20)
,cnt number(2)
);
insert into testCart(userid, productCode, cnt)
values('hongkd', 'swk', 20);
commit;
select *
from testCart;
-- 자신의 계정(testuser01)의 정보를 조회
TESTUSER01
select *
from user_users;
-- 자신의 계정(testuser02)의 정보를 조회
TESTUSER02
select *
from user_users;
-- testuser01에 생성된 모든 테이블에 대한 정보를 조회
TESTUSER01
select *
from user_tables;
-- testuser02에 생성된 모든 테이블에 대한 정보를 조회
TESTUSER02
select *
from user_tables;
SYS
select *
from dba_users;
-- hr에서 생성한 테이블은 user에 저장되어진다.
-- 자신의 계정(hr)의 정보를 조회
HR
select *
from user_users;
Tablespace 생성하기
SYS
create tablespace tbs_mytesta
datafile 'c:\myoradata\tbs_mytesta_01.dbf' size 10m
extent management local
segment space management auto;
-- TBS_MYTESTA 테이블스페이스는 파일크기가 10mb인 테이블스페이스로서 extent와 segment의 관리는 오라클서버가 알아서 자동적으로 관리하고 처리해 주는 테이블스페이스로 만든다.
SYS
create tablespace tbs_mytestb
datafile 'c:\myoradata\tbs_mytestb_01.dbf' size 10m
autoextend on next 2m maxsize 100m
extent management local
segment space management auto;
-- autoextend on next 2m maxsize 100m extent management local
-- TBS_MYTESTB 테이블스페이스는 파일크기가 10mb인 테이블스페이스로서 만약에 10mb를 다 사용하면 그 다음번에는 2mb가 자동 증가되어 사용되어진다. 즉, 12mb가 된다. 그리고 12mb를 다 사용하면 그 다음번에는 오라클서버가 알아서 자동적으로 그 크기를 관리하고 처리해 주는데 최대 크기는 100mb인 테이블스페이스로 만든다.
SYS
select *
from dba_data_files;
SYS
create tablespace tbs_mytestc
datafile 'c:\myoradata\tbs_mytestc_01.dbf' size 10m
autoextend on
extent management local
segment space management auto;
-- 최대 크기 설정되지 않음
-- TBS_MYTESTC 테이블스페이스는 파일크기가 10mb인 테이블스페이스로서 10mb를 다 사용하면 그 다음번에는 오라클서버가 알아서 자동적으로 그 크기를 관리하고 처리해 주는데 최대 크기도 오라클 서버가 알아서 잡아주는 테이블스페이스로 만든다.
SYS
select *
from dba_data_files;
SYS
create tablespace tbs_mytestd
datafile 'c:\myoradata\tbs_mytestd_01.dbf' size 10m
autoextend on maxsize 100m
extent management local
segment space management auto;
-- TBS_MYTESTD 테이블스페이스는 파일크기가 10mb인 테이블스페이스로서 10mb를 다 사용하면 그 다음번에는 오라클서버가 알아서 자동적으로 그 크기를 관리하고 처리해 주는데 최대 크기는 100mb로 하는 테이블스페이스로 만든다.
SYS
create tablespace tbs_myteste
datafile
'c:\myoradata\tbs_myteste_01.dbf' size 10m autoextend on maxsize 100m
,'c:\myoradata\tbs_myteste_02.dbf' size 10m autoextend on maxsize 100m
,'c:\myoradata\tbs_myteste_03.dbf' size 10m autoextend on maxsize 100m
extent management local
segment space management auto;
-- TBS_MYTESTE 테이블스페이스는 초기 파일크기가 30mb인 테이블스페이스로서 30mb를 다 사용하면 그 다음번에는 오라클서버가 알아서 자동적으로 그 크기를 관리하고 처리해 주는데 최대 크기는 300mb로 하는 테이블스페이스로 만든다.
SYS
create tablespace tbs_mytestf
datafile
'c:\myoradata\tbs_mytestf_01.dbf' size 10m autoextend on
,'c:\myoradata\tbs_mytestf_02.dbf' size 10m autoextend on
,'c:\myoradata\tbs_mytestf_03.dbf' size 10m autoextend on
extent management local
segment space management auto;
-- TBS_MYTESTF 테이블스페이스는 초기 파일크기가 30mb인 테이블스페이스로서 30mb를 다 사용하면 그 다음번에는 오라클서버가 알아서 자동적으로 그 크기를 관리하고 처리해 주는데 최대 크기도 오라클 서버가 알아서 해 주는 테이블스페이스로 만든다.
SYS
create tablespace tbs_mytestg
datafile
'c:\myoradata\tbs_mytestg_01.dbf' size 10m autoextend on
,'c:\myoradata\tbs_mytestg_02.dbf' size 10m autoextend on
,'c:\myoradata\tbs_mytestg_03.dbf' size 10m autoextend on
extent management local
uniform size 64k
segment space management auto;
-- 증가할 때 동일하게 64k만큼 증가한다.
-- TBS_MYTESTG 테이블스페이스는 초기 파일크기가 30mb인 테이블스페이스로서 30mb를 다 사용하면 그 다음번에는 오라클서버가 알아서 자동적으로 크기를 증가시키는데 증가되는 크기는 항상 동일하게 64kb만큼 증가를 한다.
-- 최대 크기도 오라클 서버가 알아서 해 주는 테이블스페이스로 만든다.
Ⅱ. Tablespace 삭제하기
1. 삭제하려는 테이블스페이스에 내용물(테이블, 인덱스)이 없을 경우
SYS
drop tablespace tbs_mytestg;
-- 그런데 탐색기에 가보면 아직까지 dbf 파일이 남아있으므로 운영체제에서 dbf 파일을 삭제한다.
2. 삭제하려는 테이블스페이스에 내용물(테이블, 인덱스)이 있을 경우
SYS
drop tablespace tbs_mytesta;
오류 보고 -
ORA-01549: tablespace not empty, use INCLUDING CONTENTS option
01549. 00000 - "tablespace not empty, use INCLUDING CONTENTS option"
SYS
drop tablespace tbs_mytesta including contents and datafiles;
select *
from dba_data_files;
-- 탐색기에서도 삭제된다.
SYS
drop tablespace tbs_mytestb including contents and datafiles;
drop tablespace tbs_mytestc including contents and datafiles;
drop tablespace tbs_mytestd including contents and datafiles;
drop tablespace tbs_myteste including contents and datafiles;
drop tablespace tbs_mytestf including contents and datafiles;
select *
from dba_data_files;
-- 초기 상태로 되돌아간다.
Ⅲ. 시스템권한(System Privilege)
오라클 사용자 자신(지금은 testuser01)이 관리자로부터 부여받은 시스템권한(System Privilege)의 종류에 대해서 조회해 본다.
TESTUSER01
select *
from user_sys_privs;
오라클 사용자 자신(지금은 testuser01)이 사용할 수 있는 할당량(quota)이 얼마인지 조회해 본다.
TESTUSER01
select *
from user_ts_quota;
1 block = 8kb
8 block = 64kb => 65536 bytes
dropped yes => 삭제되었다.
TESTUSER02
select *
from user_sys_privs;
select *
from user_ts_quotas;
TESTUSER02
create table testCart2
(userid varchar2(10)
,productCode varchar2(20)
,cnt number(2)
);
select *
from user_ts_quotas;
-- 공간을 확보 후 data 입력한다.
SYS
create tablespace tbs_a
datafile 'c:\myoradata\tbs_a_01.dbf' size 48m
extent management local
segment space management auto;
create tablespace tbs_b
datafile 'c:\myoradata\tbs_b_01.dbf' size 128m
extent management local
segment space management auto;
-- alter user 오라클데이터베이스사용자명 quota unlimited on 특정테이블스페이스명;
-- 오라클데이터베이스사용자명에게 특정테이블스페이스명을 무한대(특정테이블스페이스의 전체크기)만큼 할당량을 부여하는 것이다.
SYS
alter user testuser01 quota unlimited on tbs_a;
TESTUSER01
create table testmember03
(userid varchar2(10)
,passwd varchar2(20)
,name Nvarchar2(20)
,address Nvarchar2(300)
) tablespace tbs_a;
create table testmember04
(userid varchar2(10)
,passwd varchar2(20)
,name Nvarchar2(20)
,address Nvarchar2(300)
) tablespace tbs_a;
TESTUSER02
create table testCart3
(userid varchar2(10)
,productCode varchar2(20)
,cnt number(2)
) tablespace tbs_a;
오류 보고 -
ORA-01950: no privileges on tablespace 'TBS_A'
01950. 00000 - "no privileges on tablespace '%s'"
TESTUSER02
create table testCart4
(userid varchar2(10)
,productCode varchar2(20)
,cnt number(2)
) tablespace tbs_b;
ORA-01950: no privileges on tablespace 'TBS_B'
01950. 00000 - "no privileges on tablespace '%s'"
TESTUSER02
create table testCart5
(userid varchar2(10)
,productCode varchar2(20)
,cnt number(2)
) tablespace tbs_c;
오류 보고 -
ORA-00959: tablespace 'TBS_C' does not exist
00959. 00000 - "tablespace '%s' does not exist"
SYS
grant unlimited tablespace to testuser02;
TESTUSER02
create table testCart3
(userid varchar2(10)
,productCode varchar2(20)
,cnt number(2)
) tablespace tbs_a;
create table testCart4
(userid varchar2(10)
,productCode varchar2(20)
,cnt number(2)
) tablespace tbs_b;
▷ 결과
Table TESTCART3이(가) 생성되었습니다.
Table TESTCART4이(가) 생성되었습니다.
-- 할당량이 없는데 생성됨
TESTUSER02
select *
from user_sys_privs;
SYS
create tablespace tbs_c
datafile 'c:\myoradata\tbs_c_01.dbf' size 128m
extent management local
segment space management auto;
TESTUSER02
create table testCart5
(userid varchar2(10)
,productCode varchar2(20)
,cnt number(2)
) tablespace tbs_c;
▷ 결과
Table TESTCART5이(가) 생성되었습니다.
-- 오라클데이터베이스사용자명 은 모든 테이블 스페이스에 대해 무한대로 사용(할당량)이 가능하다. (앞으로 생성되어질 테이블 스페이스도 모두 포함)
Ⅳ. ROLE
-- ROLE 이란? 오라클데이터베이스사용자에게 허가해 주는 권한들의 묶음(집합)이다.
SYS
create role developer_role;
-- 생성되어진 ROLE에게 권한 부여하기
SYS
grant create session, create table, unlimited tablespace to developer_role;
SYS
create user testuser03 identified by eclass
default tablespace users;
grant developer_role to testuser03;
TESTUSER03
show user;
USER이(가) "TESTUSER03"입니다.
-- TESTUSER03에 주어진 시스템 권한 조회
TESTUSER03
select *
from user_sys_privs;
TESTUSER03
create table testOrders_01
(orderno number(5)
,productName Nvarchar2(50)
);
오류 보고 -
ORA-01950: no privileges on tablespace 'USERS'
01950. 00000 - "no privileges on tablespace '%s'"
TESTUSER03
select *
from user_users;
-- 오라클 사용자 자신(지금은 testuser03)에게 부여되어진 ROLE의 종류를 조회
TESTUSER03
select *
from user_role_privs;
-- DEVELOPER_ROLE 에게 부여되어진 시스템권한(System Privilege)이 무엇이 있는지 조회하는 것이다.
TESTUSER03
select *
from role_sys_privs
where role = 'DEVELOPER_ROLE';
TESTUSER03
create table testOrders_02
(orderno number(5)
,productName Nvarchar2(50)
) tablespace system;오류 보고 -
ORA-01950: no privileges on tablespace 'SYSTEM'
01950. 00000 - "no privileges on tablespace '%s'"
TESTUSER03
create table testOrders_03
(orderno number(5)
,productName Nvarchar2(50)
) tablespace tbs_a;오류 보고 -
ORA-01950: no privileges on tablespace 'TBS_A'
01950. 00000 - "no privileges on tablespace '%s'"
-- ROLE에 부여되어진 시스템권한(System Privilege) 회수하기
SYS
revoke unlimited tablespace from developer_role;
TESTUSER03
select *
from user_sys_privs;
-- 아까 생성되지 않은 테이블 생성하면 생성되는 것을 볼 수 있다
SYS
revoke unlimited tablespace from testuser03;
TESTUSER03
create table testOrders_04
(orderno number(5)
,productName Nvarchar2(50)
) tablespace tbs_b;
오류 보고 -
ORA-01950: no privileges on tablespace 'TBS_B'
01950. 00000 - "no privileges on tablespace '%s'"
오라클 사용자 자신(지금은 hr)이 관리자로부터 부여받은 시스템권한(System Privilege)의 종류에 대해서 조회해 본다.
HR
select *
from user_sys_privs;
-- 오라클 사용자 자신(지금은 hr)에게 부여되어진 ROLE의 종류를 조회
HR
select *
from user_role_privs;
-- RESOURCE 롤 에게 부여되어진 시스템권한(System Privilege)이 무엇이 있는지 조회
HR
select *
from role_sys_privs
where role = 'RESOURCE';
-- CONNECT 롤 에게 부여되어진 시스템권한(System Privilege)이 무엇이 있는지 조회
HR
select *
from role_sys_privs
where role = 'CONNECT';
-- 생성되어진 ROLE(롤)의 종류 조회
SYS
select *
from dba_roles;
-- DBA 롤에게 부여된 시스템권한(System Privilege) 조회
SYS
select *
from role_sys_privs
where role = 'DBA';
Ⅴ. 테이블 삭제 / 복원
-- 테이블을 5개 생성한다.
HR
create table tbl_member1
(userid varchar2(20)
,passwd varchar2(20)
,name varchar2(20)
) tablespace tbs_a;
insert into tbl_member1(userid, passwd, name) values('leess', '1234', '이순신');
commit;
create table tbl_member2
(userid varchar2(20)
,passwd varchar2(20)
,name varchar2(20)
) tablespace tbs_a;
insert into tbl_member2(userid, passwd, name) values('hong', '1234', '홍길동');
commit;
create table tbl_member3
(userid varchar2(20)
,passwd varchar2(20)
,name varchar2(20)
) tablespace tbs_a;
insert into tbl_member3(userid, passwd, name) values('eom', '1234', '엄정화');
commit;
create table tbl_member4
(userid varchar2(20)
,passwd varchar2(20)
,name varchar2(20)
) tablespace tbs_b;
insert into tbl_member4(userid, passwd, name) values('cha', '1234', '차은우');
commit;
create table tbl_member5
(userid varchar2(20)
,passwd varchar2(20)
,name varchar2(20)
) tablespace tbs_b;
insert into tbl_member5(userid, passwd, name) values('sulgi', '1234', '슬기');
commit;
select *
from tab;
1. 테이블 삭제하기(휴지통에 버리기) / 복원가능
HR
drop table tbl_member1;
drop table tbl_member2;
select *
from tab;
-- 테이블이 완전히 삭제되지 않고 휴지통 안으로 들어가 지정하지 않은 이름으로 표시된다.
2. 휴지통에 있는 테이블 조회하기
HR
select *
from user_recyclebin;
HR
select *
from "BIN$vFxMbjtZQyeOVrxywknBHw==$0";쌍따옴표 꼭 해 주어야 함
3. 휴지통에 있던 테이블을 복원하기
HR
flashback table tbl_member1 to before drop;
select *
from user_recyclebin;
-- 테이블 조회해 보면 TBL_MEMBER1 테이블이 다시 복원된 것을 확인할 수 있다.
HR
select *
from tab;
4. 테이블 영구히 삭제하기
HR
drop table tbl_member4 purge;
HR
select *
from tab;
-- TBL_MEMBER4가 완전히 삭제된 것을 확인할 수 있다.
5. 휴지통에서 특정 테이블 삭제하기
HR
drop table tbl_member3;
drop table tbl_member5;
select *
from use_recyclebin;
-- TBL_MEMBER3, TBL_MEMBER5를 휴지통에 버린다.
HR
purge table "BIN$kfXBBMfsTAyqJ2sDX52zYQ==$0";
select *
from user_recyclebin;
-- 휴지통에서 특정 테이블(TBL_MEMBER3) 삭제
6. 휴지통 모두 버리기
HR
purge recyclebin;
select *
from user_recyclebin;
HR
select *
from tab;
7. 휴지통에서 특정 테이블 스페이스에 있는 모든 테이블 삭제하기
HR
create table tbl_member1
(userid varchar2(20)
,passwd varchar2(20)
,name varchar2(20)
) tablespace tbs_a;
insert into tbl_member1(userid, passwd, name) values('leess', '1234', '이순신');
commit;
create table tbl_member2
(userid varchar2(20)
,passwd varchar2(20)
,name varchar2(20)
) tablespace tbs_a;
insert into tbl_member2(userid, passwd, name) values('hong', '1234', '홍길동');
commit;
create table tbl_member3
(userid varchar2(20)
,passwd varchar2(20)
,name varchar2(20)
) tablespace tbs_a;
insert into tbl_member3(userid, passwd, name) values('eom', '1234', '엄정화');
commit;
create table tbl_member4
(userid varchar2(20)
,passwd varchar2(20)
,name varchar2(20)
) tablespace tbs_b;
insert into tbl_member4(userid, passwd, name) values('cha', '1234', '차은우');
commit;
create table tbl_member5
(userid varchar2(20)
,passwd varchar2(20)
,name varchar2(20)
) tablespace tbs_b;
insert into tbl_member5(userid, passwd, name) values('sulgi', '1234', '슬기');
commit;
drop table tbl_member1;
drop table tbl_member2;
drop table tbl_member3;
drop table tbl_member4;
drop table tbl_member5;
select *
from user_recyclebin;
-- 테이블 5개(테이블스페이스 TBS_A를 사용하는 테이블 3개, TBS_B를 사용하는 테이블 2개)를 생성하여 모두 휴지통에 버린다.
HR
purge tablespace tbs_a;
select *
from user_recyclebin;
-- 테이블스페이스 TBS_A를 사용하는 테이블을 모두 영구히 삭제한다.
-- 오라클 데이터베이스 사용자에게 부여해 준 시스템권한(System Privilege)가 무엇인지 조회
SYS
select *
from dba_sys_privs
where grantee = 'HR';
-- 오라클 데이터베이스 사용자에게 부여해 준 ROLE(롤)이 무엇인지 조회
SYS
select *
from dba_role_privs
where grantee = 'HR';
-- 오라클 데이터베이스 사용자인 HR의 default tablespace 무엇인지 조회
SYS
select *
from dba_users
where username = 'HR';
-- 오라클 데이터베이스 사용자인 HR의 할당량(quota)이 어떻게 되어져 있는지 조회
SYS
select *
from dba_ts_quotas
where username= 'HR';
-- myorauser 계정 새로 만들기
SYS
create user myorauser identified by eclass
default tablespace users;
grant CREATE VIEW
,UNLIMITED TABLESPACE
,CREATE DATABASE LINK
,CREATE SEQUENCE
,CREATE SESSION
,ALTER SESSION
,CREATE SYNONYM
to myorauser;
grant RESOURCE, CONNECT to myorauser;
alter user myorauser quota unlimited on users;
MYORAUSER
show user;
-- USER이(가) "MYORAUSER"입니다.
-- 'myorauser에서작업한것'이라는 이름의 sql 파일로 저장
