oracle sql1

su - oracle
sqlplus / as sysdba
shutdown immediate;
// 启动数据库
startup nomount;
alter database mount;
alter database open;

// 查询表空间文件
select file_name,tablespace_name,bytes/(1024*1024) from dba_data_files
where tablespace_name = 'TBS_DATA_SIG_USER_RPT1';

// 查询每一个表空间的大小和使用
select a.tablespace_name as "Tablespace",
       (a.totalspace - b.freespace) "Used(MB)",
       a.totalspace as "Totla(MB)",
       b.freespace as "Free(MB)",
       round(b.freespace / a.totalspace * 100) as "Pct. Free%"
  from (select tablespace_name, round(sum(bytes) / 1048576) TotalSpace
          from dba_data_files
         group by tablespace_name) a,
       (select tablespace_name, round(sum(bytes) / 1048576) FreeSpace
          from dba_free_space
         group by tablespace_name) b
where a.tablespace_name = b.tablespace_name;

// 重置表空间
Alter database datafile '/opt/oracle/oradata/tbs_data_public/tbs_data_sig_user_rpt1_01.dbf'  resize 500m;
Alter database datafile '/opt/oracle/oradata/tbs_data_public/tbs_data_sig_user_rpt2_01.dbf'  resize 500m;
Alter database datafile '/opt/oracle/oradata/tbs_data_public/tbs_data_sig_user_rpt3_01.dbf'  resize 500m;
Alter database datafile '/opt/oracle/oradata/tbs_data_public/tbs_data_sig_user_rpt5_01.dbf'  resize 500m;