TEST
查看表空间所含数据文件信息
8192 ONLINE LOCAL
SQL> select file_name,tablespace_name from dba_data_files; FILE_NAME
TABLESPACE_NAME
-------------------------------------------------- --------------- ----------------------------
/u01/oracle/oradata/ora10/users01.dbf /u01/oracle/oradata/ora10/sysaux01.dbf /u01/oracle/oradata/ora10/undotbs01.dbf /u01/oracle/oradata/ora10/system01.dbf /u01/oracle/oradata/example01.dbf /u01/oracle/oradata/ora10/test01.dbf /u02/oracle/oradata/test02.dbf
注意，这里不包含临时表空间的信息，需要另外查询
SQL> select file_name,tablespace_name from dba_temp_files; FILE_NAME
TABLESPACE_NAME
-------------------------------------------------- --------------- --------------------------
/u01/oracle/oradata/ora10/temp01.dbf 查询表空间的空间大小信息
SQL> select tablespace_name,sum(bytes)/1024/1024
2 from dba_data_files group by
tablespace_name; TABLESPACE_NAME SUM(BYTES)/1024/1024 --------------- ----------
------------------------------------ SYSAUX UNDOTBS1 USERS TEST SYSTEM
EXAMPLE
查询表空间的空闲空间信息
2 from dba_free_space group by
tablespace_name;
TABLESPACE_NAME SUM(BYTES)/1024/1024 -------------------------
--------------------------- UNDOTBS1 SYSAUX USERS TEST
USERS SYSAUX UNDOTBS1 SYSTEM EXAMPLE TEST TEST
TEMP
----
240
30 80 5 480 100
SQL> select tablespace_name,sum(bytes)/1024/1024
-----
15.25
4.625 77 4.875
SYSTEM EXAMPLE
2.625 31.75
② 更改表空间 设置表空
间脱机/联机
SQL>alter tablespace test offline; SQL>alter tablespace test online;
对表空间中的数据文件进行脱机和联机 SQL>alter database datafile
‘/u01/oracle/a.dbf ’offline; SQL>alter database datafile ‘/u01/oracle/a.dbf’online; 设置表空间为只读和可读写
SQL>alter tablespace test read only; SQL>alter tablespace test read write;
扩展表空间
SQL>alter tablespace test add datafile
‘/u01/oracle/oradata/ora10/test02.dbf ’
size 50m; 以上通过增加数据文件个数以扩充表空间 SQL>alter database datafile
‘/u01/oracle/oradata/ora10/test02.dbf ’re
size 50m;
以上通过扩充数据文件大小以扩充表空间 SQL>alter database datafile
‘/u01/oracle/oradata/ora10/system01.dbf ’ autoextend on
next 100m maxsize
unlimited;
通过设置数据文件的自动扩展参数以自动扩充表空间大小
