oracle sql1

su-oracle

sqlplus/assysdba

shutdownimmediate;

//启动数据库

startupnomount;

alterdatabasemount;

alterdatabaseopen;

//查询表空间文件

selectfile_name,tablespace_name,bytes/(1024*1024)fromdba_data_files

wheretablespace_name='TBS_DATA_SIG_USER_RPT1';

//查询每一个表空间的大小和使用

selecta.tablespace_nameas"Tablespace",

(a.totalspace-b.freespace)"Used(MB)",

a.totalspaceas"Totla(MB)",

b.freespaceas"Free(MB)",

round(b.freespace/a.totalspace*100)as"Pct.Free%"

from(selecttablespace_name,round(sum(bytes)/1048576)TotalSpace

fromdba_data_files

groupbytablespace_name)a,

(selecttablespace_name,round(sum(bytes)/1048576)FreeSpace

fromdba_free_space

groupbytablespace_name)b

wherea.tablespace_name=b.tablespace_name;

//重置表空间

Alterdatabasedatafile'/opt/oracle/oradata/tbs_data_public/tbs_data_sig_user_rpt1_01.dbf'resize500m;

Alterdatabasedatafile'/opt/oracle/oradata/tbs_data_public/tbs_data_sig_user_rpt2_01.dbf'resize500m;

Alterdatabasedatafile'/opt/oracle/oradata/tbs_data_public/tbs_data_sig_user_rpt3_01.dbf'resize500m;

Alterdatabasedatafile'/opt/oracle/oradata/tbs_data_public/tbs_data_sig_user_rpt5_01.dbf'resize500m;