发现磁盘空间写的有点满,闲着无事,看哪个表空间写的数据比较多,进入到oracle的datafile目录
[[email protected] fstest]# ll -h
?荤.?.5.2G —乱码,语言的问题,先不管
-rw-r----- 1 oracle dba 6.8M 5?.14 14:57 control01.ctl
-rw-r----- 1 oracle dba 6.8M 5?.14 14:57 control02.ctl
-rw-r----- 1 oracle dba 6.8M 5?.14 14:57 control03.ctl
-rw-r----- 1 oracle dba 51M 5?.14 14:54 redo01.log
-rw-r----- 1 oracle dba 51M 5?.14 14:57 redo02.log
-rw-r----- 1 oracle dba 51M 5?.13 12:09 redo03.log
-rw-r----- 1 oracle dba 551M 5?.14 11:55 sysaux01.dbf
-rw-r----- 1 oracle dba 1.1G 5?.14 14:41 system01.dbf
-rw-r----- 1 oracle dba 165M 5?. 1 22:00 temp01.dbf
-rw-r----- 1 oracle dba 3.3G 5?.14 14:55 undotbs01.dbf
-rw-r----- 1 oracle dba 5.1M 5?.14 11:51 users01.dbf
sqlplus进入
SQL> set wrap off -- 查询返回的纪录每行超过默认宽度时,可选择换行(on )或不换行(off),默认为换行;
SQL> select * from v$datafile;
truncating (as requested) before column CHECKPOINT_CHANGE#
rows will be truncated
FILE# CREATION_CHANGE# CREATION_ TS# RFILE# STATUS ENABLED C
---------- ---------------- --------- ---------- ---------- ------- ---------- -
1 9 30-JUN-05 0 1 SYSTEM READ WRITE 1
2 444079 30-JUN-05 1 2 ONLINE READ WRITE 1
3 6609 30-JUN-05 2 3 ONLINE READ WRITE 1
4 10566 30-JUN-05 4 4 ONLINE READ WRITE 1
5 8967911909 20-MAY-10 6 5 ONLINE READ WRITE 1
6 8967911945 20-MAY-10 7 6 ONLINE READ WRITE 1
7 8967912008 20-MAY-10 8 7 ONLINE READ WRITE 1
8 8967912057 20-MAY-10 9 8 ONLINE READ WRITE 1
9 8967912153 20-MAY-10 10 9 ONLINE READ WRITE 1
10 8967912204 20-MAY-10 11 10 ONLINE READ WRITE 1
11 8967912267 20-MAY-10 12 11 ONLINE READ WRITE 1
FILE# CREATION_CHANGE# CREATION_ TS# RFILE# STATUS ENABLED C
---------- ---------------- --------- ---------- ---------- ------- ---------- -
12 8967912554 20-MAY-10 13 12 ONLINE READ WRITE 1
13 8967912620 20-MAY-10 14 13 ONLINE READ WRITE 1
14 8967912670 20-MAY-10 15 14 ONLINE READ WRITE 1
15 8967912709 20-MAY-10 16 15 ONLINE READ WRITE 1
16 8967912747 20-MAY-10 17 16 ONLINE READ WRITE 1
17 8967912779 20-MAY-10 18 17 ONLINE READ WRITE 1
18 8967912820 20-MAY-10 19 18 ONLINE READ WRITE 1
19 8967912850 20-MAY-10 20 19 ONLINE READ WRITE 1
20 8967912884 20-MAY-10 21 20 ONLINE READ WRITE 1
21 8967916961 20-MAY-10 22 21 ONLINE READ WRITE 1
22 8967917014 20-MAY-10 23 22 ONLINE READ WRITE 1
FILE# CREATION_CHANGE# CREATION_ TS# RFILE# STATUS ENABLED C
---------- ---------------- --------- ---------- ---------- ------- ---------- -
23 8967917060 20-MAY-10 24 23 ONLINE READ WRITE 1
24 8967917108 20-MAY-10 25 24 ONLINE READ WRITE 1
25 8967917146 20-MAY-10 26 25 ONLINE READ WRITE 1
26 8967917176 20-MAY-10 27 26 ONLINE READ WRITE 1
27 8967917226 20-MAY-10 28 27 ONLINE READ WRITE 1
28 8967917255 20-MAY-10 29 28 ONLINE READ WRITE 1
29 9074125138 17-APR-13 22 29 ONLINE READ WRITE 1
30 9074154915 17-APR-13 23 30 ONLINE READ WRITE 1
30 rows selected.
SQL> select * from v$tablespace;
TS# NAME INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
0 SYSTEM YES NO YES
1 UNDOTBS1 YES NO YES
2 SYSAUX YES NO YES
4 USERS YES NO YES
3 TEMP NO NO YES
6 HS_SYSTEM_DATA YES NO YES
7 HS_SYSTEM_IDX YES NO YES
8 HS_USER_DATA YES NO YES
9 HS_USER_IDX YES NO YES
10 HS_FUND_DATA YES NO YES
11 HS_FUND_IDX YES NO YES
TS# NAME INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
12 HS_SECU_DATA YES NO YES
13 HS_SECU_IDX YES NO YES
14 HS_SECUSZ_DATA YES NO YES
15 HS_SECUSZ_IDX YES NO YES
16 HS_OPFUND_DATA YES NO YES
17 HS_OPFUND_IDX YES NO YES
18 HS_BOND_DATA YES NO YES
19 HS_BOND_IDX YES NO YES
20 HS_TRUST_DATA YES NO YES
21 HS_TRUST_IDX YES NO YES
22 HS_HIS_DATA YES NO YES
TS# NAME INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
23 HS_HIS_IDX YES NO YES
24 HS_CRDT_DATA YES NO YES
25 HS_CRDT_IDX YES NO YES
26 HS_FUTURES_DATA YES NO YES
27 HS_FUTURES_IDX YES NO YES
28 HS_FIL_DATA YES NO YES
29 HS_FIL_IDX YES NO YES
29 rows selected.
SQL> select t1.name,t2.name from v$tablespace t1,v$datafile t2 where t1.TS#=t2.TS#;
NAME NAME
------------------------------ -------------------------------------------------
SYSTEM /oracle/product/10.2.0/oradata/fstest/system01.db
UNDOTBS1 /oracle/product/10.2.0/oradata/fstest/undotbs01.d
SYSAUX /oracle/product/10.2.0/oradata/fstest/sysaux01.db
USERS /oracle/product/10.2.0/oradata/fstest/users01.dbf
HS_SYSTEM_DATA /home/oracle/oradata/systemdat.dbf
HS_SYSTEM_IDX /home/oracle/oradata/systemidx.dbf
HS_USER_DATA /home/oracle/oradata/userdat.dbf
HS_USER_IDX /home/oracle/oradata/useridx.dbf
HS_FUND_DATA /home/oracle/oradata/funddat.dbf
HS_FUND_IDX /home/oracle/oradata/fundidx.dbf
HS_SECU_DATA /home/oracle/oradata/secudat.dbf
NAME NAME
------------------------------ -------------------------------------------------
HS_SECU_IDX /home/oracle/oradata/secuidx.dbf
HS_SECUSZ_DATA /home/oracle/oradata/secuszdat.dbf
HS_SECUSZ_IDX /home/oracle/oradata/secuszidx.dbf
HS_OPFUND_DATA /home/oracle/oradata/opfunddat.dbf
HS_OPFUND_IDX /home/oracle/oradata/opfundidx.dbf
HS_BOND_DATA /home/oracle/oradata/bonddat.dbf
HS_BOND_IDX /home/oracle/oradata/bondidx.dbf
HS_TRUST_DATA /home/oracle/oradata/trustdat.dbf
HS_TRUST_IDX /home/oracle/oradata/trustidx.dbf
HS_HIS_DATA /home/oracle/oradata/hisdat.dbf
HS_HIS_DATA /home/oracle/oradata/hisdat_1.dbf
NAME NAME
------------------------------ -------------------------------------------------
HS_HIS_IDX /home/oracle/oradata/fstest/hisidx.dbf
HS_HIS_IDX /home/oracle/oradata/hisidx.dbf
HS_CRDT_DATA /home/oracle/oradata/crdtdat.dbf
HS_CRDT_IDX /home/oracle/oradata/crdtidx.dbf
HS_FUTURES_DATA /home/oracle/oradata/futuresdat.dbf
HS_FUTURES_IDX /home/oracle/oradata/futuresidx.dbf
HS_FIL_DATA /home/oracle/oradata/fildat.dbf
HS_FIL_IDX /home/oracle/oradata/filidx.dbf
30 rows selected.
环境的数据文件在/home/oracle/oradata目录下,/oracle/product/10.2.0/oradata/fstest下是数据库system的数据文件
UNDOTBS1 表空间占用过大,达到3.3G,先到这里,后面再研究这个UNDO这个东东的问题