1、查看表空间使用情况
SQL> SELECT /*+NO_MERGE(A) NO_MERGE(B)*/B.TABLESPACE_NAME 表空间名称, ROUND((B.BYTES/1024)/1024,2) 总空间大小MB, 2 NVL2(A.BYTES,ROUND((B.BYTES-NVL(A.BYTES,0))/1024/1024,2),B.BYTES) 已使用大小MB, 3 NVL2(A.BYTES,ROUND(NVL(A.BYTES,0)/1024/1024,2),0) 未使用大小MB, 4 NVL2(A.BYTES,TO_CHAR(ROUND(((B.BYTES-NVL(A.BYTES,0))/B.BYTES)*100,2),'990.0'),'100')||'%' 已使用率 5 FROM (SELECT TABLESPACE_NAME,SUM(BYTES) BYTES FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME)A, 6 (SELECT TABLESPACE_NAME,SUM(BYTES) BYTES FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) B 7 WHERE B.TABLESPACE_NAME=A.TABLESPACE_NAME(+); 表空间名称 总空间大小MB 已使用大小MB 未使用大小MB 已使用率 ------------------------------ ------------ ------------ ------------ -------- UNDOTBS1 165 13.19 151.81 8.0% SYSAUX 5510 374.56 5135.44 6.8% USERS 5 0.44 4.56 8.8% SYSTEM 5610 484.94 5125.06 8.6% RUSKY_DATA 2048 36.94 2011.06 1.8%
2、查看除了系统用户以外有没有其它用户使用系统表空间
SQL> select username, default_tablespace,temporary_tablespace from dba_users where (default_tablespace='SYSTEM' or temporary_tablespace='SYSTEM') and username not in ('SYSTEM','SYS'); USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE ------------------------------ ------------------------------ ------------------------------ OUTLN SYSTEM TEMP MGMT_VIEW SYSTEM TEMP
3、查看某用户所使用的表空间
SQL> SELECT USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE FROM DBA_USERS WHERE USERNAME='RUSKY'; USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE ------------------------------ ------------------------------ ------------------------------ RUSKY RUSKY_DATA RUSKY_TEMP
查看当前用户使用的表空间
SQL> SELECT USERNAME,DEFAULT_TABLESPACE FROM USER_USERS; USERNAME DEFAULT_TABLESPACE ------------------------------ ------------------------------ RUSKY RUSKY_DATA
查看数据文件的存储位置、大小等信息：select * from v$datafile; select * from dba_data_files;
4、查看system和sysaux表空间是否是自动扩展的
SQL> select tablespace_name,AUTOEXTENSIBLE,INCREMENT_BY from dba_data_files; TABLESPACE_NAME AUTOEXTENSIBLE INCREMENT_BY ------------------------------ -------------- ------------ USERS YES 160 SYSAUX YES 1280 UNDOTBS1 YES 640 SYSTEM YES 1280 RUSKY_DATA YES 65536 SYSTEM YES 64000 SYSAUX YES 64000
如果为自动扩展那么AUTOEXTENSIBLE字段的值应为YES，是否为NO；INCREMENT_BY 这个为每次自动扩展的空间大小。
5、采取的扩展表空间的做法
增加一个数据文件：
ALTER TABLESPACE SYSTEM ADD DATAFILE '/home/oracle/oradata/orcl/system02.dbf' size 5120M AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED;
ALTER TABLESPACE SYSAUX ADD DATAFILE '/home/oracle/oradata/orcl/sysaux02.dbf' size 5120M AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED;
或是重新调整数据文件的大小：
SQL> alter database datafile '/home/oracle/oradata/orcl/system01.dbf' resize 5120M autoextend on next 500M;
SQL> alter database datafile '/home/oracle/oradata/orcl/sysaux01.dbf' resize 5120M autoextend on next 500M;
参考资源：http://blog.chinaunix.net/uid-261392-id-2138978.html
===================================================
查看表空间及其对应的数据文件：
表空间信息可通过以下三张表及视图进行查询：V$DATAFILE,V$TABLESPACE,DBA_TABLESPACES 1、SQL> SELECT FILE#,TS#,STATUS,ENABLED,NAME FROM V$DATAFILE; ---查看表空间对应的数据文件 FILE# TS# STATUS ENABLED NAME ---------- ---------- ------- ---------- -------------------------------------------------------------------------------- 1 0 SYSTEM READ WRITE D:APPADMINISTRATORORADATAORCLSYSTEM01.DBF 2 1 ONLINE READ WRITE D:APPADMINISTRATORORADATAORCLSYSAUX01.DBF 3 2 ONLINE READ WRITE D:APPADMINISTRATORORADATAORCLUNDOTBS01.DBF 4 4 ONLINE READ WRITE D:APPADMINISTRATORORADATAORCLUSERS01.DBF 5 6 ONLINE READ WRITE D:APPADMINISTRATORORADATAORCLEXAMPLE01.DBF 6 7 ONLINE READ WRITE D:RMAN_CATALOGRMANTBS01.DBF 7 4 ONLINE READ WRITE D:APPADMINISTRATORORADATAORCLUSERS02.DBF 7 rows selected 2、SQL> SELECT * FROM V$TABLESPACE;--查看表空间基本信息 TS# NAME INCLUDED_IN_DATABASE_BACKUP BIGFILE FLASHBACK_ON ENCRYPT_IN_BACKUP ---------- ------------------------------ --------------------------- ------- ------------ ----------------- 0 SYSTEM YES NO YES 1 SYSAUX YES NO YES 2 UNDOTBS1 YES NO YES 4 USERS YES NO YES 3 TEMP NO NO YES 6 EXAMPLE YES NO YES 7 RMANTBS YES NO YES 7 rows selected 3、SQL> SELECT TABLESPACE_NAME,BLOCK_SIZE,STATUS,LOGGING,FORCE_LOGGING FROM DBA_TABLESPACES; --查看表空间详细信息 TABLESPACE_NAME BLOCK_SIZE STATUS LOGGING FORCE_LOGGING ------------------------------ ---------- --------- --------- ------------- SYSTEM 8192 ONLINE LOGGING NO SYSAUX 8192 ONLINE LOGGING NO UNDOTBS1 8192 ONLINE LOGGING NO TEMP 8192 ONLINE NOLOGGING NO USERS 8192 ONLINE LOGGING NO EXAMPLE 8192 ONLINE NOLOGGING NO RMANTBS 8192 ONLINE LOGGING NO 7 rows selected 4、查看表空间对应的数据文件 SQL> SELECT TB.TS#,TB.NAME,FILE#,DF.STATUS,DF.NAME FROM V$TABLESPACE TB, V$DATAFILE DF WHERE TB.TS#=DF.TS# ORDER BY TB.TS#; TS# NAME FILE# STATUS NAME ---------- ------------------------------ ---------- ------- -------------------------------------------------------------------------------- 0 SYSTEM 1 SYSTEM D:APPADMINISTRATORORADATAORCLSYSTEM01.DBF 1 SYSAUX 2 ONLINE D:APPADMINISTRATORORADATAORCLSYSAUX01.DBF 2 UNDOTBS1 3 ONLINE D:APPADMINISTRATORORADATAORCLUNDOTBS01.DBF 4 USERS 4 ONLINE D:APPADMINISTRATORORADATAORCLUSERS01.DBF 4 USERS 7 ONLINE D:APPADMINISTRATORORADATAORCLUSERS02.DBF 6 EXAMPLE 5 ONLINE D:APPADMINISTRATORORADATAORCLEXAMPLE01.DBF 7 RMANTBS 6 ONLINE D:RMAN_CATALOGRMANTBS01.DBF 7 rows selected
或者：
SQL> select ts.tablespace_name,df.file_name,df.file_id,df.status from dba_tablespaces ts, 2 (select tablespace_name,file_id,file_name,status from dba_data_files 3 union all 4 select tablespace_name,file_id,file_name,status from dba_temp_files) df 5 where ts.tablespace_name=df.tablespace_name; TABLESPACE_NAME FILE_NAME FILE_ID STATUS ------------------------------ -------------------------------------------------------------------------------- ---------- --------- USERS D:APPADMINISTRATORORADATAORCLUSERS01.DBF 4 AVAILABLE UNDOTBS1 D:APPADMINISTRATORORADATAORCLUNDOTBS01.DBF 3 AVAILABLE SYSAUX D:APPADMINISTRATORORADATAORCLSYSAUX01.DBF 2 AVAILABLE SYSTEM D:APPADMINISTRATORORADATAORCLSYSTEM01.DBF 1 AVAILABLE EXAMPLE D:APPADMINISTRATORORADATAORCLEXAMPLE01.DBF 5 AVAILABLE RMANTBS D:RMAN_CATALOGRMANTBS01.DBF 6 AVAILABLE USERS D:APPADMINISTRATORORADATAORCLUSERS02.DBF 7 AVAILABLE TEMP D:APPADMINISTRATORORADATAORCLTEMP01.DBF 1 ONLINE 8 rows selected