oracle 关闭回收站

--11g 以后关闭回收站 system 级别需要加 DEFERRED 参数；---session 级别不需要

select name,isses_modifiable,issys_modifiable from v$parameter where name='recyclebin'

SQL> show parameter recyclebin

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

recyclebin                           string      on

SQL> alter system set recyclebin=off; alter system set recyclebin=off                              

* ERROR at line 1: ORA-02096: specified initialization parameter is not modifiable with this option

SQL> alter system set recyclebin=off DEFERRED;

System altered.

SQL> show parameter recyclebin

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

recyclebin                           string      on

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

[oracle@dwdb1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Dec 19 14:11:18 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show parameter recyclebin

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

recyclebin                           string      OFF

 

删除回收站所有垃圾信息：     PURGE recyclebin;  purge dba_RECYCLEBIN;

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------         

2 PDB$SEED                       READ ONLY  NO         

3 DSDB                           READ WRITE NO         

4 DWDB                        READ WRITE NO

SQL> alter session set container=DSDB;

Session altered.

SQL> purge dba_RECYCLEBIN;

DBA Recyclebin purged.

SQL> show parameter recyclebin

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

recyclebin                           string      OFF

SQL> alter session set container=DWDB;

Session altered.

SQL> SELECT  count(1) FROM dba_RECYCLEBIN;

  COUNT(1) ----------          0

SQL> show parameter recyclebin

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

recyclebin                           string      OFF