------------------------------ ----- -----
SYS TRUE TRUE
desc dba_free_space; 查剩余情况
a.BYTES bytes_used,
b.BYTES bytes_free,
b.largest,
round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) percent_used
from
(
select TABLESPACE_NAME,
sum(BYTES) BYTES
from dba_data_files
group by TABLESPACE_NAME
)
a,
(
select TABLESPACE_NAME,
sum(BYTES) BYTES ,
max(BYTES) largest
from dba_free_space
group by TABLESPACE_NAME
)
b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME
order by ((a.BYTES-b.BYTES)/a.BYTES) desc;
-------------- ----- ---------- ---------- ---------- ------------
BOPSINDXTS 537919488 125829120 59768832 76.61
APPDATA1M 1.0743E+10 3233808384 629145600 69.9
PERFSTAT 157286400 48431104 48168960 69.21
MEMBER_TS 1075838976 356515840 226492416 66.86
BOPSDATATS 4833935360 2252341248 2034237440 53.41
PRODUCTTS 3.8656E+10 2.1326E+10 2.1305E+10 44.83
APP_DATA1K 4568645632 2569011200 2083520512 43.77
USERS 537919488 358612992 239075328 33.33
APPINDX1K 6445596672 4364173312 3602907136 32.29
APPINDX1M 6177161216 4265607168 3745513472 30.95
SYSTEM 1074790400 838008832 837746688 22.03
MCSHADOWTS 1074790400 1070596096 797966336 .39
UNDOTBS1 8593080320 8566145024 4143972352 .31
TOOLS 1680867328 1677721600 1073741824 .19
APPINDX4M 4833935360 4831838208 4294967296 .04
15 rows selected.
--------------------------------------------- ------------------------------
/home/oracle/oradata/moree/APPDATA1M01.dbf APPDATA1M
/home/oracle/oradata/moree/APPDATA1M02.dbf APPDATA1M
/home/oracle/oradata/moree/APPDATA1M04.dbf APPDATA1M
/home/oracle/oradata/moree/APPDATA1M05.dbf APPDATA1M
/home/oracle/oradata/moree/APPDATA1M03.dbf APPDATA1M
/home/oracle/oradata/moree/APPINDX1K01.dbf APPINDX1K
/home/oracle/oradata/moree/APPINDX1K02.dbf APPINDX1K
/home/oracle/oradata/moree/APPINDX1K03.dbf APPINDX1K
/home/oracle/oradata/moree/APPINDX1M01.dbf APPINDX1M
/home/oracle/oradata/moree/APPINDX1M03.dbf APPINDX1M
/home/oracle/oradata/moree/APPINDX1M02.dbf APPINDX1M
/home/oracle/oradata/moree/APPINDX4M01.dbf APPINDX4M
/home/oracle/oradata/moree/APPINDX4M02.dbf APPINDX4M
/home/oracle/oradata/moree/APP_DATA1K01.dbf APP_DATA1K
/home/oracle/oradata/moree/APP_DATA1K02.dbf APP_DATA1K
/home/oracle/oradata/moree/APP_DATA1K05.dbf APP_DATA1K
/home/oracle/oradata/moree/APP_DATA1K04.dbf APP_DATA1K
/home/oracle/oradata/moree/APP_DATA1K03.dbf APP_DATA1K
/home/oracle/oradata/moree/BOPSDATATS01.dbf BOPSDATATS
/home/oracle/oradata/moree/bopsdatats02.dbf BOPSDATATS
/home/oracle/oradata/moree/BOPSDATATS02.dbf BOPSDATATS
/home/oracle/oradata/moree/BOPSDATATS03.dbf BOPSDATATS
/home/oracle/oradata/moree/BOPSINDXTS01.dbf BOPSINDXTS
/home/oracle/oradata/moree/MCSHADOWTS01.dbf MCSHADOWTS
/home/oracle/oradata/moree/MEMBER_TS01.dbf MEMBER_TS
/home/oracle/oradata/moree/MEMBER_TS02.dbf MEMBER_TS
/home/oracle/oradata/moree/perfstat.dbf PERFSTAT
/home/oracle/oradata/moree/PRODUCTTS01.dbf PRODUCTTS
/home/oracle/oradata/moree/PRODUCTTS06.dbf PRODUCTTS
/home/oracle/oradata/moree/PRODUCTTS05.dbf PRODUCTTS
/home/oracle/oradata/moree/PRODUCTTS02.dbf PRODUCTTS
/home/oracle/oradata/moree/PRODUCTTS03.dbf PRODUCTTS
/home/oracle/oradata/moree/PRODUCTTS04.dbf PRODUCTTS
/home/oracle/oradata/moree/system01.dbf SYSTEM
/home/oracle/oradata/moree/tools01.dbf TOOLS
/home/oracle/oradata/moree/tools02.dbf TOOLS
/home/oracle/oradata/moree/tools03.dbf TOOLS
/home/oracle/oradata/moree/undotbs01.dbf UNDOTBS1
/home/oracle/oradata/moree/undotbs02.dbf UNDOTBS1
/home/oracle/oradata/moree/undotbs03.dbf UNDOTBS1
/home/oracle/oradata/moree/users01.dbf USERS
41 rows selected.
-rw-r----- 1 oracle oinstall 513M Jan 3 09:30 /home/oracle/oradata/moree/PRODUCTTS02.dbf
-rw-r----- 1 oracle oinstall 513M Jan 3 09:30 /home/oracle/oradata/moree/PRODUCTTS03.dbf
-rw-r----- 1 oracle oinstall 513M Jan 3 09:30 /home/oracle/oradata/moree/PRODUCTTS04.dbf
-rw-r----- 1 oracle oinstall 4.1G Jan 3 09:30 /home/oracle/oradata/moree/PRODUCTTS05.dbf
-rw-r----- 1 oracle oinstall 31G Jan 3 09:30 /home/oracle/oradata/moree/PRODUCTTS06.dbf
d.username as "用户",
b.file_id as "文件ID号",
b.tablespace_name as "表空间名",
b.file_name as "表空间位置",
b.bytes/1024/1024||'M'"字节数",
(b.bytes-sum(nvl(a.bytes,0)))/1024/1024||'M' as "已使用",
sum(nvl(a.bytes,0))/1024/1024||'M' as "剩余空间",
100 - sum(nvl(a.bytes,0))/(b.bytes)*100 as "占用百分比" ,
c.tablespace_name as "临时表空间名",
c.file_name as "临时表空间位置"
from dba_free_space a,dba_data_files b ,dba_temp_files c,dba_users d
where a.file_id=b.file_id and d.temporary_tablespace = c.tablespace_name and b.tablespace_name = d.default_tablespace
group by d.username, b.tablespace_name,b.file_name,b.file_id,b.bytes ,c.tablespace_name ,c.file_name
order by d.username ;
create tablespace perfstat datafile '/home/oracle/oradata/moree/perfstat.dbf' size 100M autoextend on next 50M maxsize 1000M;
TABLESPACE 临时表空间名
方式一:
方式二:
drop tablespace users including contents;
drop tablespace users including contents and datafiles;
col file_name format a20;
select file_name, tablespace_name, bytes, autoextensible from dba_data_files;