查询表空间文件默认位置:
Select * FROM DBA_DATA_FILES; 或者 select name from v$datafile;
查询表空间使用情况
SELECT a.tablespace_name "表空间名称", total / (1024 * 1024) "表空间大小(M)", free / (1024 * 1024) "表空间剩余大小(M)", (total - free) / (1024 * 1024 ) "表空间使用大小(M)", total / (1024 * 1024 ) "表空间大小(M)", free / (1024 * 1024 ) "表空间剩余大小(M)", (total - free) / (1024 * 1024 ) "表空间使用大小(M)", round((total - free) / total, 4) * 100 "使用率 %" FROM (SELECT tablespace_name, SUM(bytes) free FROM dba_free_space GROUP BY tablespace_name) a, (SELECT tablespace_name, SUM(bytes) total FROM dba_data_files GROUP BY tablespace_name) b WHERE a.tablespace_name = b.tablespace_name; 或者查询临时表空间+表空间: SELECT d.tablespace_name "Name", d.status "Status", TO_CHAR( NVL( a.BYTES / 1024 / 1024, 0 ), '99,999,990.90' ) "Size (M)", TO_CHAR( NVL( a.BYTES - NVL( f.BYTES, 0 ), 0 ) / 1024 / 1024, '99999999.99' ) USE, TO_CHAR( NVL( ( a.BYTES - NVL( f.BYTES, 0 ) ) / a.BYTES * 100, 0 ), '990.00' ) "Used %" FROM SYS.dba_tablespaces d, ( SELECT tablespace_name, SUM( BYTES ) BYTES FROM dba_data_files GROUP BY tablespace_name ) a, ( SELECT tablespace_name, SUM( BYTES ) BYTES FROM dba_free_space GROUP BY tablespace_name ) f WHERE d.tablespace_name = a.tablespace_name ( + ) AND d.tablespace_name = f.tablespace_name ( + ) AND NOT ( d.extent_management LIKE 'LOCAL' AND d.CONTENTS LIKE 'TEMPORARY' ) UNION ALL SELECT d.tablespace_name "Name", d.status "Status", TO_CHAR( NVL( a.BYTES / 1024 / 1024, 0 ), '99,999,990.90' ) "Size (M)", TO_CHAR( NVL( t.BYTES, 0 ) / 1024 / 1024, '99999999.99' ) USE, TO_CHAR( NVL( t.BYTES / a.BYTES * 100, 0 ), '990.00' ) "Used %" FROM SYS.dba_tablespaces d, ( SELECT tablespace_name, SUM( BYTES ) BYTES FROM dba_temp_files GROUP BY tablespace_name ) a, ( SELECT tablespace_name, SUM( bytes_cached ) BYTES FROM v$temp_extent_pool GROUP BY tablespace_name ) t WHERE d.tablespace_name = a.tablespace_name ( + ) AND d.tablespace_name = t.tablespace_name ( + ) AND d.extent_management LIKE 'LOCAL' AND d.CONTENTS LIKE 'TEMPORARY';
创建表空间:
create tablespace 表间名 datafile '数据文件名' size 表空间大小; 例子: create tablespace user_data datafile '/app/oracle/oradata/orcl/user_data.dbf' size 100M autoextend on next 50m maxsize 32767m extent management local;
因为oracle表空间有限制,通常增加到32G就不再增加了所以如果库超过32G通常需要增加表空间的数据文件,达到增加表空间大小的目的
增加表空间数据文件:
alter tablespace 表空间名字 add datafile '数据文件路径' size 100m autoextend on next 50m maxsize 32767m;
//此处注意,数据文件名字不能与已经存在的名字相同。可以任意指定位置,前提是该位置实际的空间充足。
alter tablespace user_data add datafile '/app/oracle/oradata/orcl/user_data1.dbf' size 100m autoextend on next 50m maxsize 32767m; 查看表空间是否自增: SELECT FILE_NAME,TABLESPACE_NAME,AUTOEXTENSIBLE FROM dba_data_files;
创建用户指定表空间:
create user 用户名 identified by test01 default tablespace 表空间名;
删除表空间
drop tablespace 表空间名字 including contents and datafiles cascade constraint;
查看当前库默认临时表空间
SELECT * FROM database_properties WHERE property_name = 'DEFAULT_TEMP_TABLESPACE';
查询临时表空间使用情况
SELECT tablespace_name, file_name, user_bytes / bytes, bytes / 1024 / 1024 "file_size(M)", autoextensible FROM dba_temp_files t WHERE t.TABLESPACE_NAME = 'TEMP';
临时表空间扩容
ALTER database tempfile '/volumes01/admin/orcl/dpdump/cmstbs_temp.dbf' resize 5G;
临时表空间增加数据文件
ALTERT ABLESPACE CMSTBS_TEMP ADD TEMPFILE '/volumes01/admin/orcl/dpdump/cmstbs_temp20210402.dbf' SIZE 5G AUTOEXTEND ON NEXT 128M;