linux上用rm命令误将oracle表空间文件删除

[oracle@db1 ~]$ sqlplus / as sysdba

1.查看数据文件：

SQL> select name from v$datafile;

2.查看数据文件状态
SQL> select file#,status,name from v$datafile where name like ‘%SMS%’;

3.确认操作系统上SMS数据文件已经不存在，u01文件夹都没有
ll /oracle/

4.此时查看数据文件状态，状态没有异常
select file#,status,name from v$datafile where name like ‘%SMS%’;

5.手工将误删除的文件offline drop，成功
SQL> alter database datafile ‘/oracle/u01/app/oracle/oradata/orcl/SMS.dbf’ offline drop;

Database altered.

6.此时，该文件的状态为“RECOVER”
SQL> select file#,status,name from v$datafile where name like ‘%SMS%’;

7.演示删除表空间，成功。
SQL> alter database datafile ‘/oracle/u01/app/oracle/oradata/orcl/SMS.dbf’ offline drop;

8.最后确认，删除得很彻底。

SQL>select file#,status,name from v$datafile where name like ‘%SMS%’;

SQL> select name from v$datafile;

---

备注：
先停库 shutdown immediate 然后备份文件 tar -zcvf database.tar.gz /sgb/orcl/base_data01.dbf /sgb/orcl/base_idx01.dbf /sgc/orcl/base_data01.dbf 把excel里这些都列进来

---

start mount完操作是可以的
show parameter user_dump_dest

---

SELECT a.tablespace_name “表空间名”,
total “表空间大小”,
free “表空间剩余大小”,
(total - free) “表空间使用大小”,
total / (1024 * 1024 * 1024) “表空间大小(G)”,
free / (1024 * 1024 * 1024) “表空间剩余大小(G)”,
(total - free) / (1024 * 1024 * 1024) “表空间使用大小(G)”,
round((total - free) / total, 4) * 100 “使用率 %”
FROM (SELECT tablespace_name, SUM(bytes) free
FROM dba_free_space
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) total
FROM dba_data_files
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name

select file#,status,name from v$datafile where name like ‘%SMS%’;
alter tablespace BASE_DATA add datafile ‘/xxx/orcl/base_data02.dbf’ size 30G;
alter tablespace BASE_DATA add datafile ‘/xxx/orcl/base_data03.dbf’ size 30G;

run{
set newname for datafile 9 to ‘/sgg/orcl/base_data31.dbf’ ;
restore datafile 9;
switch datafile all;
recover datafile 9;
}