2014年4月8号,一网友说他要删除一数据库中的表空间,删除不掉。下面是远程操作的具体信息。先说下环境,os 为linux,oracle 11gr2 ,表空间下的数据文件也一并删除。
SQL> show user
User is "SYS"
SQL>
SQL> drop user SHAREPLATFORMDB cascade;
drop user SHAREPLATFORMDB cascade
ORA-01940: cannot drop a user that is currently connected
SQL> drop tablespace SHAREPLATFORMDB including contents and datafiles ;
drop tablespace SHAREPLATFORMDB including contents and datafiles
ORA-00604: error occurred at recursive SQL level 1
ORA-38301: can not perform DDL/DML over objects in Recycle Bin
SQL>
SQL> show parameter recyclebin
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recyclebin string on
SQL> alter session set recyclebin=off
2 ;
Session altered
SQL> show recyclebin
SQL> show parameter recyclebin
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recyclebin string on
SQL> drop tablespace SHAREPLATFORMDB including contents and datafiles ;
drop tablespace SHAREPLATFORMDB including contents and datafiles
ORA-00604: error occurred at recursive SQL level 1
ORA-02429: cannot drop index used for enforcement of unique/primary key
SQL> select * from recyclebin;
OBJECT_NAME ORIGINAL_NAME OPERATION TYPE TS_NAME CREATETIME DROPTIME DROPSCN PARTITION_NAME CAN_UNDROP CAN_PURGE RELATED BASE_OBJECT PURGE_OBJECT SPACE
------------------------------ -------------------------------- --------- ------------------------- ------------------------------ ------------------- ------------------- ---------- -------------------------------- ---------- --------- ---------- ----------- ------------ ----------
BIN$y5Q74oRzRZiuOOKKMyfBUA==$0 RESEVALUATE DROP TABLE USERS 2014-04-01:10:36:41 2014-04-01:10:42:44 14221202 YES YES 85874 85874 85874 24
SQL> drop table "BIN$y5Q74oRzRZiuOOKKMyfBUA==$0";
Table dropped
SQL> select * from recyclebin;
OBJECT_NAME ORIGINAL_NAME OPERATION TYPE TS_NAME CREATETIME DROPTIME DROPSCN PARTITION_NAME CAN_UNDROP CAN_PURGE RELATED BASE_OBJECT PURGE_OBJECT SPACE
------------------------------ -------------------------------- --------- ------------------------- ------------------------------ ------------------- ------------------- ---------- -------------------------------- ---------- --------- ---------- ----------- ------------ ----------
SQL> purge tablespace SHAREPLATFORMDB;
Done
SQL> purge tablespace SHAREPLATFORMDB user SHAREPLATFORMDB;
Done
SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
USERS
TEMP
SHAREPLATFORMDB
SHAREGISDATADB
SDE
NSHAREAPPDB
NSHAREPLATFORMDB
10 rows selected
SQL> drop tablespace SHAREPLATFORMDB;
Tablespace dropped
SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
USERS
TEMP
SHAREGISDATADB
SDE
NSHAREAPPDB
NSHAREPLATFORMDB
9 rows selected
SQL> show parameter recyclebin
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recyclebin string on
SQL>