1、查看表空间使用情况的脚本
select a.tablespace_name,
curr_size_mb,
free_size_mb,
curr_size_mb-free_size_mb used_size_mb
from
(select tablespace_name,
sum(bytes/1024) free_size_mb
from dba_free_space
group by tablespace_name) a,
(select tablespace_name,
sum(bytes/1024) curr_size_mb
from dba_data_files
group by tablespace_name) b
where
a.tablespace_name=b.tablespace_name
/
2、查看各表空间大小
查看
SQL> select tablespace_name,file_id,file_name,ceil(bytes/1048567)||'M' MB from dba_data_files;
TABLESPACE_NAMEFILE_ID FILE_NAMEMB
--------------- ---------- --------------------------------------------- --------------------
USERS4 /u01/oracle/oradata/ora10g/users01.dbf26M
SYSAUX3 /u01/oracle/oradata/ora10g/sysaux01.dbf241M
UNDOTBS1 2 /u01/oracle/oradata/ora10g/undotbs01.dbf26M
SYSTEM1 /u01/oracle/oradata/ora10g/system01.dbf481M
3、创建表空间
create tablespace mytbs datafile '/u01/oracle/oradata/ora10g/mytbs01.dbf' size 10M;
4、创建用户时可以指定用户使用表空间
create user user6 defaulttablespacexyz identified by user6;
查看 用户的默认的表空间
SQL> select USERNAME,DEFAULT_TABLESPACE from dba_users ;
更改默认的表空间为 xxx
alter database default tablespace xxx;
5、查看表空间状态
select tablespace_name,status from dba_tablespaces;
6、查看数据文件状态
select tablespace_name,file_name,online_status from dba_data_files;
7、表空间扩容（文件自动增长）
alter database datafile '/oracle/mydba.dbf' autoextend on next 10M maxsize 4G;
alter database datafile '/oracle/mydba.dbf' autoextend on next 10M maxsize unlimited;//不限制最大容量
lter database datafile '/oracle/mydba.dbf' autoextend on; |
