오라클 tablespace 생성 삭제 조회 관련

########tablespace 조회####################################
select * from dba_tablespaces;

########tablespace 물리적인 위치 조회#########################

SELECT status,enabled, t.name ,d.name
FROM  V$DATAFILE d, V$TABLESPACE t
WHERE t.ts#=d.ts#;

########tablespace 생성####################################
CREATE TABLESPACE JD_SAMPLE
DATAFILE 'D:\dbdata\oracle\11g\sample\SAMPLE.DBF' SIZE 1G
AUTOEXTEND on NEXT 100M MAXSIZE UNLIMITED
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK on;

########tablespace 포맷####################################
COL TABLESPACE_NAME FORMAT [tablespace명]
* 실제 사용은 안해봤음

########tablespace 삭제####################################
drop tablespace [tablespace명]
including contents and datafiles
cascade constraints;

########tablespace 용량확인####################################
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;