oracle查看表空间占用情况，导入导出dmp文件

1、查看表空间占用情况
with 
  a as (select tablespace_name,sum(bytes) totalbytes from dba_data_files group by tablespace_name),
  b as (select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_name)
select 
  a.tablespace_name,round(a.totalbytes/power(1024,3),2) "合计(GB)",
  round((a.totalbytes-b.bytes)/power(1024,3),2) "已使用(GB)",
  round(b.bytes/power(1024,3),2) "剩余(GB)",round(((a.totalbytes-b.bytes)/a.totalbytes)*100,2) "已使用(%)" 
from a,b
where a.tablespace_name=b.tablespace_name 

2、查看使用的表空间
select distinct tablespace_name from user_tables;
select distinct tablespace_name from user_indexes;

3、查看空间占用超过800M的表
WITH X AS (
Select owner,
segment_name TABLE_NAME,
round(sum(bytes)   /(1024*1024*1024),2) SPACE_USED
From dba_Extents
where segment_type='TABLE' 
group by owner,segment_name
having round(sum(bytes)   /(1024*1024*1024),2)>0.08
)
SELECT a.owner,A.TABLE_NAME TABLE_NAME,A.NUM_ROWS NUM_ROWS,X.SPACE_USED  "SPACEUSED(G)"
FROM dba_TABLES A ,x
where A.TABLE_NAME=X.TABLE_NAME and a.owner=x.owner
order by a.owner,X.SPACE_USED;

4、查看哪些索引占用空间比较大
WITH X AS (
Select segment_name TABLE_NAME,
round(sum(bytes)/(1024*1024*1024),2) SPACE_USED
From User_Extents
where segment_type='INDEX' 
group by segment_name
)
SELECT A.TABLE_NAME TABLE_NAME,A.INDEX_NAME,A.NUM_ROWS NUM_ROWS,X.SPACE_USED  "SPACEUSED(G)"
FROM USER_INDEXES A 
LEFT JOIN X ON A.INDEX_NAME=X.TABLE_NAME
order by X.SPACE_USED;

5、导出dmp文件
--在OS命令符下执行

--每个用户各执行以下两条指令，注意把文件名区分开

--1、先只导出结构而不带数据（速度应当非常快）
exp WXGL_YTHYW/wxgl_ythyw statistics=none rows=n file=1.dmp log=1.log

imp WXGL_YTHYW/wxgl_ythyw statistics=none rows=n file=1.dmp log=1.log

--再导出数据
exp WXGL_YTHYW/wxgl_ythyw  statistics=none  rows=y file=2.dmp log=2.log 

imp WXGL_YTHYW/wxgl_ythyw statistics=none rows=n file=1.dmp log=1.log