oracle 表空间

---

• Oracle 的物理文件最大只允许 4M(4194304) 个数据块;
• 每个数据块大小：select value from v$parameter where name='db_block_size' ; 一般都是8kB
• 如果每个数据块大小是 8kB ，那么一个表空间文件最大大小就是 8kB * 4M = 32G；
• 在即将达到32G时会报错：ORA-01653: unable to extend table XXX by 128/1024/XXX in tablespace XXX

新建表空间

create tablespace "ts_test" datafile '/ora_data/ts_test.dbf' 
	size 52428800 
	autoextend on 
	next 8388608 
	maxsize umlimited 
	logging online permanent blocksize 8192
	extent management local autoallocate default 
	mocompress segment space management auto;

修改用户默认表空间

alter user user_test default tablespace ts_test;

增加表空间文件

alter tablespace "ts_test" datafile '/ora_data/ts_test_1.dbf' 
	size 10G
	autoextend on 
	next 8388608 
	maxsize umlimited;

表空间使用情况查看

SELECT a.tablespace_name "表空间名", 
	total/1073741824 "表空间大小(G)", 
	free/1073741824 "表空间剩余大小(G)", 
	(total - free)/1073741824 "表空间使用大小(G)", 
	round((total - free) / total, 4) * 100 "使用率 %" 
FROM (SELECT tablespace_name, SUM(bytes) free 
		FROM dba_free_space 
		GROUP BY tablespace_name) a, 
	(SELECT tablespace_name, SUM(bytes) total 
		FROM dba_data_files 
		GROUP BY tablespace_name) b 
WHERE a.tablespace_name = b.tablespace_name ;