[Command]Tablespace 관련 Command

[Tablespace file 찾기]

# select file_name, bytes/1024/1024 MB from dba_data_files where tablespace_name='NTSS_IDX';

 

[Tablespace name 찾기]

# select tablespace_name from dba_tablespaces;

 

[Tablespace, size 확인]

# select tablespace_name,sum(bytes)/1024/1024 from dba_data_files group by tablespace_name;

 

[Tablespace size 변경]

# alter database datafile 'E:\ORA92\MARS\NTSSDATA01.DBF' resize 2000m;

 

[Tablespace 사용량 /남은용량 확인]

# select tablespace_name,sum(bytes)/1024/1024 from dba_data_files group by tablespace_name;

# select tablespace_name,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name;

 

[Tablespace 사용량 확인]

# SELECT u.tablespace_name "테이블스페이스명",

u.bytes / 1024000 "크기(MB)",

(u.bytes - sum(nvl(f.bytes,0))) / 1024000 "사용됨(MB)",

(sum(nvl(f.bytes,0))) / 1024000 "남음(MB)",

trunc((sum(nvl(f.bytes,0)) / u.bytes) * 100,2) "남은 %"

FROM DBA_FREE_SPACE f, DBA_DATA_FILES u

WHERE f.file_id(+) = u.file_id

GROUP BY u.tablespace_name, u.file_name, u.bytes

ORDER BY u.tablespace_name;

 

[Tablespace 이름 찾기]

# select tablespace_name from dba_tablespaces;

 

[Tablespace에 Datafile 추가]

# alter tablespace NTSS_IDX add datafile 'E:\ORA92\MARS\NTSSINDX02.DBF' size 2000m;

 

[Default Tablespace 확인]

# select default_tablespace from dba_users where username='POSTPROD';

 

[TEMP Tablespace 사용하는 놈 찾기]

# select b.username, b.sid, b.serial#, a.extents, a.blocks * to_number(rtrim(p.value)) as Space, tablespace, segtype

from v$sort_usage a, v$parameter p, v$session b

where p.name='db_block_size' and a.session_addr=b.saddr

order by 1,2;

 

[TEMP Datafile 확인]

# select * from dba_temp_files;

 

[TEMP Tablespace 사용량]

# select ss.tablespace_name, ROUND(sum((ss.used_blocks*ts.blocksize))/1024/1024/1024,2)GB

from gv$sort_segment ss, sys.ts$ ts

where ss.tablespace_name=ts.name

group by ss.tablespace_name;

TEMP Tablespace 용량 select tablespace_name, ROUND(sum(bytes)/1024/1024/1024,2)GB from dba_temp_files group by tablespace_name;

 

[TEMP Tablespace 생성]

# create temporary tablespace temp tempfile ['tempfile 경로'] size 2000m;

 

[TEMP Datafile 추가하기]

# alter tablespace temp add tempfile ['tempfile 경로'] size 2048M;

 

[사용자, tablespace, 용량 확인]

# select owner, tablespace_name, sum(bytes/1024/1024)

from dba_segments where owner not in ('SYS','SYSTEM')

group by owner, tablespace_name;

 

[일반 Tablespace 생성]

# create tablespace INO_DATA1 datafile 'E:\oracle\oradata2\INO_DATA1\ino_data1_1.dbf' size 2000m;