误删example表空间的解决办法

在oracle9i用dbca自动建库时建了许多表空间，今天我一生气直接删除了example01.dbf，之后就不能正常启动了，使其offline后能数据库就启动了，但不能修复example这个表空间，后来就想尽办法把这个表空间给删了。

环境：
SQL> select file#,name,CHECKPOINT_CHANGE#,BYTES/1024/1024 MB from v$datafile;

FILE# NAME CHECKPOINT_CHANGE# MB
---------- ---------------------------------------- ------------------ ----------
1 /oracle/oradata/mydata/system01.dbf 527830 380
2 /oracle/oradata/mydata/undotbs01.dbf 527830 200
3 /oracle/oradata/mydata/cwmlite01.dbf 527830 20
4 /oracle/oradata/mydata/drsys01.dbf 527830 20
5 /oracle/oradata/mydata/example01.dbf 527830 149.375
6 /oracle/oradata/mydata/indx01.dbf 527830 25
7 /oracle/oradata/mydata/odm01.dbf 527830 20
8 /oracle/oradata/mydata/tools01.dbf 527830 10
9 /oracle/oradata/mydata/users01.dbf 527830 25
10 /oracle/oradata/mydata/xdb01.dbf 527830 45
11 /oracle/oradata/mydata/users02.dbf 527830 10

rm /oracle/oradata/mydata/example01.dbf --这个文件没有备份

操作步骤：
SQL> alter database datafile 5 offline;

SQL> alter database open;
Database altered.

SQL> select file_name,tablespace_name,status from dba_data_files;

FILE_NAME TABLESPACE_NAME STATUS
--------------------------------------------- ------------------------------ ---------
/oracle/oradata/mydata/system01.dbf SYSTEM AVAILABLE
/oracle/oradata/mydata/undotbs01.dbf UNDOTBS1 AVAILABLE
/oracle/oradata/mydata/cwmlite01.dbf CWMLITE AVAILABLE
/oracle/oradata/mydata/drsys01.dbf DRSYS AVAILABLE
/oracle/oradata/mydata/example01.dbf EXAMPLE AVAILABLE
/oracle/oradata/mydata/indx01.dbf INDX AVAILABLE
/oracle/oradata/mydata/odm01.dbf ODM AVAILABLE
/oracle/oradata/mydata/tools01.dbf TOOLS AVAILABLE
/oracle/oradata/mydata/users01.dbf USERS AVAILABLE
/oracle/oradata/mydata/xdb01.dbf XDB AVAILABLE
/oracle/oradata/mydata/users02.dbf USERS02 AVAILABLE

11 rows selected.
SQL> alter database create datafile '/oracle/oradata/mydata/example01.dbf'; 想重建一个，但失败
alter database create datafile '/oracle/oradata/mydata/example01.dbf'
*
ERROR at line 1:
ORA-01178: file 5 created before last CREATE CONTROLFILE, cannot recreate
ORA-01110: data file 5: '/oracle/oradata/mydata/example01.dbf'

SQL> drop tablespace example;
drop tablespace example
*
ERROR at line 1:
ORA-01549: tablespace not empty, use INCLUDING CONTENTS option

SQL> alter tablespace example drop datafile '/oracle/oradata/mydata/example01.dbf';删除也失败，有没有这个命令啊？

书上只有add
alter tablespace example drop datafile '/oracle/oradata/mydata/example01.dbf'
*
ERROR at line 1:
ORA-02142: missing or invalid ALTER TABLESPACE option

SQL> recover datafile 5; 恢复也失败

ORA-00283: recovery session canceled due to errors
ORA-01110: data file 5: '/oracle/oradata/mydata/example01.dbf'
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/oracle/oradata/mydata/example01.dbf'

看来现在只能删掉tablespace example啦，

SQL> drop tablespace example;
drop tablespace example
*
ERROR at line 1:
ORA-01549: tablespace not empty, use INCLUDING CONTENTS option

SQL> drop tablespace example INCLUDING CONTENTS;删也删不掉啊！！！
drop tablespace example INCLUDING CONTENTS
*
ERROR at line 1:
ORA-23515: materialized views and/or their indices exist in the tablespace

alter database create datafile '/oracle/oradata/mydata/example01.dbf' as '/oracle/oradata/mydata/example02.dbf';－

－转移也不行
ERROR at line 1:
ORA-01178: file 5 created before last CREATE CONTROLFILE, cannot recreate
ORA-01110: data file 5: '/oracle/oradata/mydata/example01.dbf'

后来从网上查的，得先删除物化视图，再删除表空间，怎么删除 物化视图？
SQL> select table_name, tablespace_name from dba_tables where tablespace_name='EXAMPLE' and table_name in (select

mview_name from dba_mviews);

TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
CAL_MONTH_SALES_MV EXAMPLE
FWEEK_PSCAT_SALES_MV EXAMPLE

SQL> drop table CAL_MONTH_SALES_MV ; －－这样会删除失败

SQL> drop view FWEEK_PSCAT_SALES_MV; －－这样也会删除失败
drop table FWEEK_PSCAT_SALES_MV
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> alter tablespace example offline;
alter tablespace example offline
*
ERROR at line 1:
ORA-01191: file 5 is already offline - cannot do a normal offline
ORA-01110: data file 5: '/oracle/oradata/mydata/example01.dbf'

SQL> alter tablespace example
2 add datafile '/oracle/oradata/mydata/example02.dbf' size 20M; －－重新建了一个
Database altered.

SQL> alter database datafile '/oracle/oradata/mydata/example01.dbf' offline drop; 又把example01.db给offline了

Database altered.

再看一下其它的信息：
SQL> select TABLESPACE_NAME,BLOCK_SIZE,STATUS,CONTENTS from dba_tablespaces;

TABLESPACE_NAME BLOCK_SIZE STATUS CONTENTS
------------------------------ ---------- --------- ---------
SYSTEM 8192 ONLINE PERMANENT
UNDOTBS1 8192 ONLINE UNDO
TEMP 8192 ONLINE TEMPORARY
CWMLITE 8192 ONLINE PERMANENT
DRSYS 8192 ONLINE PERMANENT
EXAMPLE 8192 ONLINE PERMANENT
INDX 8192 ONLINE PERMANENT
ODM 8192 ONLINE PERMANENT
TOOLS 8192 ONLINE PERMANENT
USERS 8192 ONLINE PERMANENT
XDB 8192 ONLINE PERMANENT
USERS02 8192 ONLINE PERMANENT

12 rows selected.

SQL> select FILE_NAME,FILE_ID,TABLESPACE_NAME,AUTOEXTENSIBLE from dba_data_files;

FILE_NAME FILE_ID TABLESPACE_NAME AUT
--------------------------------------------- ---------- ------------------------------ ---
/oracle/oradata/mydata/system01.dbf 1 SYSTEM YES
/oracle/oradata/mydata/undotbs01.dbf 2 UNDOTBS1 YES
/oracle/oradata/mydata/cwmlite01.dbf 3 CWMLITE YES
/oracle/oradata/mydata/drsys01.dbf 4 DRSYS YES
/oracle/oradata/mydata/example01.dbf 5 EXAMPLE
/oracle/oradata/mydata/indx01.dbf 6 INDX YES
/oracle/oradata/mydata/odm01.dbf 7 ODM YES
/oracle/oradata/mydata/tools01.dbf 8 TOOLS YES
/oracle/oradata/mydata/users01.dbf 9 USERS YES
/oracle/oradata/mydata/xdb01.dbf 10 XDB YES
/oracle/oradata/mydata/users02.dbf 11 USERS02 NO

11 rows selected.

后来又从网上查了查，还得先删除物化视图的用户
选择出来后一个个用户drop 掉。

SQL> select owner,TABLE_NAME from dba_tables where TABLESPACE_NAME='EXAMPLE';

OWNER TABLE_NAME
------------------------------ ------------------------------
HR REGIONS
HR LOCATIONS
HR DEPARTMENTS
HR JOBS
QS AQ$_MEM_MC
QS AQ$_AQ$_MEM_MC_S
QS SYS_IOT_OVER_31465
。。。

drop user HR cascade;
drop user QS cascade;
。。。

＃SQL> drop user in (select owner from dba_tables where TABLESPACE_NAME='EXAMPLE') cascade;--这样为什么删除不了呢

？

SQL> drop tablespace example including contents;
Database altered.

终于删掉了！
再检查一下吧：
SQL> select FILE_NAME,FILE_ID,TABLESPACE_NAME,AUTOEXTENSIBLE from dba_data_files;

FILE_NAME FILE_ID TABLESPACE_NAME AUT
-------------------------------------------------- ---------- ------------------------------ ---
/oracle/oradata/mydata/system01.dbf 1 SYSTEM YES
/oracle/oradata/mydata/undotbs01.dbf 2 UNDOTBS1 YES
/oracle/oradata/mydata/cwmlite01.dbf 3 CWMLITE YES
/oracle/oradata/mydata/drsys01.dbf 4 DRSYS YES
/oracle/oradata/mydata/indx01.dbf 6 INDX YES
/oracle/oradata/mydata/odm01.dbf 7 ODM YES
/oracle/oradata/mydata/tools01.dbf 8 TOOLS YES
/oracle/oradata/mydata/users01.dbf 9 USERS YES
/oracle/oradata/mydata/xdb01.dbf 10 XDB YES
/oracle/oradata/mydata/users02.dbf 11 USERS02 NO

11 rows selected.

总结 删除包含物化视图的表空间的方法
1。先删除物化视图的用户
drop user user_name cascade;
在删除表空间
drop tablespace tablespace_name INCLUDING CONTENTS ;或
drop tablespace tablespace_name INCLUDING CONTENTS and datafiles;

明天我还想把控制文件给删除了，当然这次得先cp一个哟！

======================================================

接下来在主库上运行：

RMAN> run {
backup format 'f:log_%U'
archivelog all delete all input;
}

查看：

select * from v$recovery_file_dest;

文件数从200，缩到2个；总文件量从3.1GB缩到17MB.

在备库上运行

select * from v$recovery_file_dest;

文件总量大小和文件数量不变。