1.未使用的表空间大小 select tablespace_name,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name;
2.查看所有表空间大小 select tablespace_name, sum(bytes)/1024/1024 from dba_data_files group by tablespace_name; 3.查询所有的表空间 select tablespace_name from dba_tablespaces; 4.在oracle数据库中，临时表空间主要用于用户在使用order by 、group by语句进行排序和汇总时所需的临时工作空间。要查询数据库中临时表空间的名称，大小及数据文件，可以查询数据字典dba_tablespaces及dba_data_files。命令如下： select a.tablespace_name space_name, b.bytes bytes, b.file_name filename from dba_tablespaces a, dba_data_files b where a.tablespace_name=b.tablespace_name and a.contents='TEMPORARY'; 5.查看Oracle数据库中表空间信息的命令方法： select a.a1 space_name, c.c2 type, c.c3 quM, b.b2/1024/1024 space_size, (b.b2-a.a2)/1024/1024 used, substr((b.b2-a.a2)/b.b2*100,1,5) rating from (select tablespace_name a1,sum(nvl(bytes,0)) a2 from dba_free_space group by tablespace_name) a, (select tablespace_name b1,sum(bytes) b2 from dba_data_files group by tablespace_name) b, (select tablespace_name c1,contents c2,extent_management c3 from dba_tablespaces) c where a.a1=b.b1 and c.c1=b.b1; 6.查看Oracle数据库中表空间使用情况的命令方法：SELECT upper(f.tablespace_name) "Tablespacename", d.Tot_grootte_Mb "TablespaceSize", d.Tot_grootte_Mb - f.total_bytes "Used", to_char(round((d.Tot_grootte_Mb - f.total_bytes) / d.Tot_grootte_Mb * 100,2),'990.99') "Rate", f.total_bytes "Avail", f.max_bytes "MaxBlock" FROM (SELECT tablespace_name, round(SUM(bytes)/(1024*1024),2) total_bytes, round(MAX(bytes)/(1024*1024),2) max_bytes FROM sys.dba_free_space GROUP BY tablespace_name) f, (SELECT dd.tablespace_name,round(SUM(dd.bytes)/(1024*1024),2) Tot_grootte_Mb FROM sys.dba_data_files dd GROUP BY dd.tablespace_name) d WHERE d.tablespace_name = f.tablespace_name ORDER BY 4 DESC;
-------------------------------- 1.查看临时表空间使用情况： SELECT d.status "Status", d.tablespace_name "Name", d.contents "Type", d.extent_management "Extent Management", TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)", NVL(t.bytes, 0)/1024/1024 ||'/'||NVL(a.bytes/1024/1024, 0) "Used (M)", 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'; 从oracle 9i开始，可以创建Temporary tablespace类表空间，即“临时“表空间，这类表空间使用临时文件。临时文件的信息被存储在数据字典V$tempfile中。命令如下： select file#, status, name from v$tempfile; 2.查询oracle系统用户的默认表空间和临时表空间 select default_tablespace,temporary_tablespace from dba_users; --------------------------------------------------- 3.分享: 如何查找正在使用临时表空间的session/sqltext --------------------------------------------------- select a.username, a.sid, a.serial#, a.process, b.tablespace, b.segtype, b.userblocks, d.current_users, d.total_blocks, d.used_blocks, d.free_blocks, d.max_used_blocks, d.max_sort_blocks, e.physizemb, e.phyblocks, round(d.max_used_blocks/e.phyblocks*100,2) max_used_ratio, round(d.used_blocks/e.phyblocks*100,2) current_used_ratio, round(b.userblocks/e.phyblocks*100,2) user_ratio, c.sql_text from v$session a, (select session_addr, tablespace,segtype, sum(blocks) userblocks from v$tempseg_usage group by session_addr, tablespace,segtype) b, v$sqltext c, v$sort_segment d, (select tablespace_name, round(sum(bytes)/1024/1024,2) physizemb, sum(blocks) phyblocks from dba_temp_files group by tablespace_name) e where a.saddr = b.session_addr and a.sql_address = c.address and a.sql_hash_value = c.hash_value and c.piece = 0 and b.tablespace = d.tablespace_name and b.tablespace = e.tablespace_name; 4.查询用户表空间的表 select * from user_tables;
获取创建表空间的语句： select dbms_metadata.get_ddl('TABLESPACE','MLOG_NORM_SPACE') from dual;
--4确认磁盘空间足够，增加一个数据文件 alter tablespace MLOG_NORM_SPACE add datafile '/Oracle/oms/oradata/mlog/Mlog_Norm_data001.dbf' size 10M autoextend on maxsize 20G
--5验证已经增加的数据文件 select file_name,file_id,tablespace_name from dba_data_files where tablespace_name='MLOG_NORM_SPACE'
--6如果删除表空间数据文件，如下： alter tablespace MLOG_NORM_SPACE drop datafile '/oracle/oms/oradata/mlog/Mlog_Norm_data001.dbf'
