/********************************************************************************************
-- Title : [ORA10g] Oracle 10g Tablespace 생성 및 권한 설정
-- Key word : 오라클 환경 설정
********************************************************************************************/
< 오라클 10g Tablespace 생성, 권한설정 >
1. TABLESPACE 생성
-- DATAFILE 경로 확인
SQL> SELECT NAME FROM V$DATAFILE;
NAME
--------------------------------------------------------------------
/home/oracle/oradata/INFRAORC/system01.dbf
/home/oracle/oradata/INFRAORC/undotbs01.dbf
/home/oracle/oradata/INFRAORC/sysaux01.dbf
/home/oracle/oradata/INFRAORC/users01.dbf
/home/oracle/oradata/INFRAORC/example01.dbf
-- TS_DATA tablespace 생성
SQL>
CREATE TABLESPACE TS_DATA DATAFILE '/home/oracle/oradata/INFRAORC/ts_data01.dbf' SIZE 10240M REUSE
AUTOEXTEND ON NEXT 1280K
MINIMUM EXTENT 128K
DEFAULT STORAGE ( INITIAL 128K NEXT 128K MINEXTENTS 1 MAXEXTENTS 4096 PCTINCREASE 0);
-- TS_INDEX tablespace 생성
SQL>
CREATE TABLESPACE TS_INDEX DATAFILE '/home/oracle/oradata/INFRAORC/ts_index01.dbf' SIZE 2048M REUSE
AUTOEXTEND ON NEXT 1280K
MINIMUM EXTENT 128K
DEFAULT STORAGE ( INITIAL 128K NEXT 128K MINEXTENTS 1 MAXEXTENTS 4096 PCTINCREASE 0);
-- TS_LOB tablespace 생성
SQL>
CREATE TABLESPACE TS_LOB DATAFILE '/home/oracle/oradata/INFRAORC/ts_lob01.dbf' SIZE 20480M REUSE
AUTOEXTEND ON NEXT 1280K
MINIMUM EXTENT 128K
DEFAULT STORAGE ( INITIAL 128K NEXT 128K MINEXTENTS 1 MAXEXTENTS 4096 PCTINCREASE 0);
-- 생성되었는지 확인해 봄.
SQL> SELECT NAME FROM V$DATAFILE;
NAME
--------------------------------------------------------------------
/home/oracle/oradata/INFRAORC/system01.dbf
/home/oracle/oradata/INFRAORC/undotbs01.dbf
/home/oracle/oradata/INFRAORC/sysaux01.dbf
/home/oracle/oradata/INFRAORC/users01.dbf
/home/oracle/oradata/INFRAORC/example01.dbf
/home/oracle/oradata/INFRAORC/ts_data01.dbf
/home/oracle/oradata/INFRAORC/ts_index01.dbf
/home/oracle/oradata/INFRAORC/ts_lob01.dbf
2. 사용자, 권한 생성
-- 사용자생성
-- priorartmaster
SQL> create user priorartmaster
identified by test
default tablespace users
temporary tablespace temp
quota unlimited on users;
-- priorartdev
SQL> create user priorartdev
identified by test
default tablespace users
temporary tablespace temp;
-- 사용자생성확인
SQL> select username, user_id, account_status, default_tablespace "DEFAULT_TS"
, temporary_tablespace "TEMP_TS", profile
from dba_users
where username in ('PRIORARTMASTER', 'PRIORARTDEV');
-- 롤생성
SQL> create role rol_dev;
-- 롤생성확인
SQL> select *
from dba_roles where role = 'ROL_DEV';
-- 롤에권한부여
SQL> grant select any table
, update any table
, delete any table
, insert any table
, select any sequence
, execute any procedure
, create any procedure
, alter any procedure
to rol_dev;
-- 롤에부여된권한확인
SQL> select * from role_sys_privs
where role = 'ROL_DEV';
-- 사용자에롤부여
SQL> grant connect, resource, dba to priorartmaster;
SQL> grant connect, resource
, rol_dev
to priorartdev;
-- 사용자롤확인
SQL> select * from dba_role_privs
where grantee in ('PRIORARTMASTER', 'PRIORARTDEV');
-- 커밋
SQL> commit;
--테스트
SQL> drop table priorartmaster.ttt; --있다면..
SQL> create table priorartmaster.ttt
( a integer not null primary key
, b integer not null
, c nchar(10) not null
, d nvarchar2(10) not null
);
SQL> insert into priorartmaster.ttt
select 1, 1, N'aaa', N'aaa' from dual union all
select 2, 2, N'bbb', N'bbb' from dual union all
select 3, 3, N'ccc', N'ccc' from dual union all
select 4, 4, N'ddd', N'ddd' from dual;
SQL> commit;
SQL> select * from priorartmaster.ttt;
-- IPDEV 사용자전환
SQL> conn priorartdev/dlsvmfk
SQL> insert into priorartmaster.ttt values(5,5,'eee','eee');
SQL> select * from priorartmaster.ttt;
SQL> update priorartmaster.ttt
set d = 'EE'
where a = 5;
SQL> select * from priorartmaster.ttt;
SQL> delete priorartmaster.ttt
where a = 5;
SQL> select * from priorartmaster.ttt;
SQL> commit;