테이블스페이스 (조회,생성,삭제)

select * from dba_tablespaces;
select * from user_tablespaces;
select * from dba_data_files;

select * from user_users;

select owner, table_name, tablespace_name from dba_tables;
select owner, table_name, tablespace_name from dba_tables where owner={소유자/사용자}

select owner, table_name, tablespace_name from dba_tables where owner={소유자/사용자} and table_name={테이블명}

select file_name,tablespace_name,autoextensible from dba_data_files;

SELECT     
    A.TABLESPACE_NAME,
    ROUND(A.BYTES_ALLOC / 1024 / 1024, 2) CURRENT_SIZE,
    ROUND(NVL(B.BYTES_FREE, 0) / 1024 / 1024, 2) FREE_SIZE,
    ROUND((A.BYTES_ALLOC - NVL(B.BYTES_FREE, 0)) / 1024 / 1024, 2) USED_SIZE,
    ROUND((NVL(B.BYTES_FREE, 0) / A.BYTES_ALLOC) * 100,2) FREE_RATE,
    100 - ROUND((NVL(B.BYTES_FREE, 0) / A.BYTES_ALLOC) * 100,2) USED_RATE,
    ROUND(MAXBYTES/1048576,2) MAX_SIZE
FROM ( SELECT 
        F.TABLESPACE_NAME,
        SUM(F.BYTES) BYTES_ALLOC,
        SUM(DECODE(F.AUTOEXTENSIBLE, 'YES',F.MAXBYTES,'NO', F.BYTES)) MAXBYTES
        FROM DBA_DATA_FILES F
        GROUP BY TABLESPACE_NAME) A,
    ( SELECT 
        F.TABLESPACE_NAME,SUM(F.BYTES)  BYTES_FREE FROM DBA_FREE_SPACE F GROUP BY TABLESPACE_NAME) B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME (+)
UNION
SELECT     
    TABLESPACE_NAME,
    ROUND(SUM(BYTES_USED + BYTES_FREE) / 1048576, 2),
    ROUND(SUM(BYTES_FREE) / 1048576,2),
    ROUND(SUM(BYTES_USED) / 1048576,2),
    ROUND((SUM(BYTES_FREE) / SUM(BYTES_USED + BYTES_FREE)) * 100,2) FREE_RATE,
    100 - ROUND((SUM(BYTES_FREE) / SUM(BYTES_USED + BYTES_FREE)) * 100,2) USED_RATE,
    ROUND(MAX(BYTES_USED + BYTES_FREE) / 1048576, 2)
FROM SYS.V_$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME
ORDER BY 1;

CREATE TABLESPACE ${MY-SPACE명} DATAFILE '+XX/mydir/datafile/mydb.dbf' size 1048576000 autoextend on next 100M;

# 파일사이즈 자동증가
alter database datafile '/data/oradata/mydb/mydb.dbf' autoextend on;

# create user 
create user ${myuser} identified by "${password}" default tablespace "${space name}" temporary tablespace "TEMP";
grant unlimited tablespace to ${myuser};
grant connect,resource to ${myuser};

grant connect,resource,create synonym to ${myuser};  -- 동의어 생성권한추가 시
grant select on ${tbl_user} to ${myuser};      -- myuser 에게 tbl_user 테이블 select권한 부여
revoke select on ${tbl_user} from ${myuser};  -- myuser 으로부터 tbl_user 테이블의 select권한 박탈