SQL> select * from v$tablespace;
TS# NAME INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
0 SYSTEM YES NO YES
1 UNDO YES NO YES
2 SYSAUX YES NO YES
3 TEMP NO NO YES
4 TOOLS YES NO YES
5 TBS_USER_DAT YES YES YES
6 TBS_USER_IDX YES YES YES
6 rows selected.
SQL>
## 查看表空间文件名以及其大小：
SQL> select ts#, round(bytes/1024/1024),name from v$datafile;
TS# ROUND(BYTES/1024/1024) NAME
---------- ---------------------- ----------------------------------------------------------------------------
0 8192 /opt/oracle/oradata/SOC/system01.dbf
1 16384 /opt/oracle/oradata/SOC/rbs01.dbf
2 2048 /opt/oracle/oradata/SOC/sysaux01.dbf
4 1024 /opt/oracle/oradata/SOC/tools01.dbf
5 51200 /opt/oracle/oradata/SOC/udata01.dbf
6 25600 /opt/oracle/oradata/SOC/uindex01.dbf
6 rows selected.
SQL>
## 修改数据文件大小：
SQL> alter database datafile '/opt/oracle/oradata/SOC/uindex01.dbf' resize 25610 M;
Database altered.
## 再次查看
SQL> select ts#, round(bytes/1024/1024),name from v$datafile;
TS# ROUND(BYTES/1024/1024) NAME
---------- ---------------------- ----------------------------------------------------------------------------
1 16384 /opt/oracle/oradata/SOC/rbs01.dbf
2 2048 /opt/oracle/oradata/SOC/sysaux01.dbf
4 1024 /opt/oracle/oradata/SOC/tools01.dbf
5 51200 /opt/oracle/oradata/SOC/udata01.dbf
6 25610 /opt/oracle/oradata/SOC/uindex01.dbf
6 rows selected.
SQL>
## 检查表空间大小的SQL
SQL> SELECT V1.TABLESPACE_NAME TABLESPACENAME,
to_char(ROUND(NVL(V1.SPACE,0)))||'(M)' TOTALTABLESPACE,
to_char(ROUND(NVL(V2.SPACE,0)))||'(M)' FREETABLESPACE
FROM (SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024 SPACE FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) V1,
(SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024 SPACE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) V2
WHERE V1.TABLESPACE_NAME = V2.TABLESPACE_NAME( )
ORDER BY V1.TABLESPACE_NAME;
2 3 4 5 6 7
TABLESPACENAME TOTALTABLESPACE FREETABLESPACE
------------------------------ ------------------------------------------- -----------------------------------
SYSAUX 2048(M) 1753(M)
SYSTEM 8192(M) 7321(M)
TBS_USER_DAT 51200(M) 37238(M)
TBS_USER_IDX 25610(M) 21014(M)
TOOLS 1024(M) 1024(M)
UNDO 16384(M) 15467(M)
6 rows selected.
SQL>
作者 scrit