创建表空间和临时表空间:
create tablespace test
logging
datafile 'D:/tablespace/test.dbf'
size 50m
autoextend on
next 1m maxsize 20480m
extent management local;
create temporary tablespace test_temp
tempfile 'D:/tablespace/test_temp.dbf'
size 50m
autoextend on
next 1m maxsize 2048m
extent management local;
创建用户:
create user test identified by test
default tablespace test
temporary tablespace test_temp;
为用户赋权限
grant all privileges to test;(所有权限)
grant resource,connect,dba to test;
查看表空间使用情况
SELECT tbs 表空间名,
sum(totalM) 总共大小M,
sum(usedM) 已使用空间M,
sum(remainedM) 剩余空间M,
sum(usedM)/sum(totalM)*100 已使用百分比,
sum(remainedM)/sum(totalM)*100 剩余百分比
FROM(
SELECT b.file_id ID,
b.tablespace_name tbs,
b.file_name name,
b.bytes/1024/1024 totalM,
(b.bytes-sum(nvl(a.bytes,0)))/1024/1024 usedM,
sum(nvl(a.bytes,0)/1024/1024) remainedM,
sum(nvl(a.bytes,0)/(b.bytes)*100),
(100 - (sum(nvl(a.bytes,0))/(b.bytes)*100))
FROM dba_free_space a,dba_data_files b
WHERE a.file_id = b.file_id
GROUP BY b.tablespace_name,b.file_name,b.file_id,b.bytes
ORDER BY b.tablespace_name
)
GROUP BY tbs
修改表空间文件大小
alter database datafile 'D:\APP\ADMINISTRATOR\ORADATA\ORACLE\test.DBF' resize 5000M;
select file_name from dba_data_files where tablespace_name = 'test_data';
修改表空间大小不限制,自动增长
alter database datafile 'F:\APP\ORACLE\ORADATA\ORCL\test_DATA.DBF' autoextend on maxsize unlimited;
当表空间达到32G时,为表空间新增一个文件,这样表空间就增大到32G+新文件大小了
ALTER TABLESPACE aaa
ADD DATAFILE 'E:\APP\ORACLE11GR2\ORADATA\ORCL\test_DATA02.DBF'
SIZE 32767M;