오라클 테이블 스페이스 및 계정생성,권한 만들기

 

테이블 스페이스 및 계정생성 권한 만들기

 

CREATE TABLESPACE PGMailer DATAFILE 
  '/data/oracle/oradata/orcl/XXXTEST.dbf' SIZE 500M AUTOEXTEND OFF
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

SELECT * FROM DBA_DATA_FILES order by file_name  
SELECT * FROM DBA_FREE_SPACE

 

[ 2 ]
CREATE USER pgmailer
IDENTIFIED BY 비밀번호  
DEFAULT TABLESPACE PGMailer
QUOTA unlimited ON PGMailer
TEMPORARY TABLESPACE temp 

select * from sys.user$ where type# =1 order by name
select * from sys.dba_role_privs where grantee = 'PGMAILER'
select * from sys.dba_tab_privs

 

[ 3 ]
GRANT  CREATE SESSION, CREATE TABLE, CREATE SEQUENCE, CREATE VIEW TO pgmailer

 

 

삭제

 

alter tablespace BS_EDU offline;
drop tablespace BS_EDU including contents and datafiles;
CREATE TABLESPACE BS_EDU DATAFILE 
  '/data/oracle/oradata/orcl/BS_EDU01.dbf' SIZE 500M AUTOEXTEND OFF
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
alter tablespace BS_EDU online;

==> SYSTEM계정으로 테이블스페이스 위치와 파일명 확인
SELECT * FROM DBA_DATA_FILES
SELECT * FROM DBA_FREE_SPACE