Oracle Tablespace
가. 기존 용량 파악
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 | SQL> set linesize 180 pages 200 SQL> col tablespace_name for a30 SQL> SELECT D.TABLESPACE_NAME 2 , ROUND(D.KBYTES/1024) "Allocated(M)" 3 , ROUND(D.MAXKBYTES/1024) "MaxAllocated(M)" 4 , ( CASE WHEN D.MAXKBYTES > 0 5 THEN ROUND((D.KBYTES - F.KBYTES)/D.MAXKBYTES*100,1) 6 ELSE ROUND((D.KBYTES - F.KBYTES)/D.KBYTES*100,1) 7 END ) "Max_Used(%)" 8 , ( CASE WHEN D.MAXKBYTES > 0 AND D.MAXKBYTES > D.KBYTES 9 THEN ROUND(D.MAXKBYTES/1024 - D.KBYTES/1024 + F.KBYTES/1024) 10 ELSE ROUND(F.KBYTES/1024) 11 END ) "Free(M)" 12 , ( CASE WHEN D.MAXKBYTES > 0 AND D.MAXKBYTES > D.KBYTES 13 THEN ROUND(D.MAXKBYTES/1024 - D.KBYTES/1024 + F.MAX_FREE_KBYTES/1024) 14 ELSE ROUND(F.MAX_FREE_KBYTES/1024) 15 END ) "Max_Free(M)" 16 , S.MAX_EXT_KBYTES/1024 "Max_Seg(M)" 17 FROM ( SELECT SUM(BYTES)/1024 KBYTES, TABLESPACE_NAME, SUM(MAXBYTES)/1024 MAXKBYTES 18 FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME ) D, 19 ( SELECT SUM(BYTES)/1024 KBYTES, TABLESPACE_NAME, MAX(BYTES)/1024 MAX_FREE_KBYTES 20 FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME ) F, 21 ( SELECT TABLESPACE_NAME, MAX(NEXT_EXTENT)/1024 MAX_EXT_KBYTES 22 FROM DBA_SEGMENTS GROUP BY TABLESPACE_NAME ) S 23 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) 24 AND D.TABLESPACE_NAME = S.TABLESPACE_NAME(+) 25 ORDER BY 4 DESC ; TABLESPACE_NAME Allocated(M) MaxAllocated(M) Max_Used(%) Free(M) Max_Free(M) Max_Seg(M) ------------------------------ ------------ --------------- ----------- ---------- ----------- ---------- EXAMPLE 120 210 35.4 136 136 1 USERS 4000 32768 7.9 30172 28776 1 SYSAUX 1200 32768 3.5 31627 31625 5 SYSTEM 1090 32768 3.1 31742 31702 1 UNDOTBS1 3525 32768 .3 32679 30458 .0625 TOOLS 1000 2000 .1 1999 1999 1 TS_TEST11_D 1000 10000 0 9999 9999 7 rows selected. | cs |
나. 신규 Tablespace 추가
1 2 3 4 5 6 7 8 | SQL> create tablespace TS_TGDOM_D datafile '/u01/data/temp3/TS_TGDOM_D_01.dbf' size 10m 2 autoextend on next 100m maxsize 200m 3 extent management local autoallocate 4 segment space management auto ; Tablespace created. SQL> | cs |
다. 신규 Datafile 추가
1 2 3 4 | SQL> alter tablespace TS_TGDOM_D add datafile '/u01/data/temp3/TS_TGDOM_D_02.dbf' size 10m 2 autoextend on next 100m maxsize 200m ; Tablespace altered. | cs |