经典的ORACLE 11/12/19闪回操作

1、闪回表

SQL> show parameter recycle

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_recycle                  string
db_recycle_cache_size                big integer 0
recyclebin                           string      on

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/PROD4/PROD4/system01.dbf
/u01/app/oracle/oradata/PROD4/PROD4/sysaux01.dbf
/u01/app/oracle/oradata/PROD4/PROD4/undotbs01.dbf
/u01/app/oracle/oradata/PROD4/PROD4/users01.dbf
/u01/app/oracle/oradata/PROD4/PROD4/example01.dbf

10 rows selected.

SQL> create tablespace test datafile '/u01/app/oracle/oradata/PROD4/PROD4/test.dbf' size 10m;

Tablespace created.

SQL> create user sf identified by oracle default tablespace test;

User created.

SQL> grant dba to sf;

Grant succeeded.

SQL> conn sf/oracle
Connected.
SQL> create table TEST1 as select * from dba_objects;

create table TEST10 as select * from dba_objects;

Table created.

SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name='TEST1';

SUM(BYTES)/1024/1024
--------------------
                   9

SQL> show recyclebin
SQL> desc dba_free_space;     
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TABLESPACE_NAME                                    VARCHAR2(30)
 FILE_ID                                            NUMBER
 BLOCK_ID                                           NUMBER
 BYTES                                              NUMBER
 BLOCKS                                             NUMBER
 RELATIVE_FNO                                       NUMBER

SQL> select TABLESPACE_NAME,bytes/1024/1024 from dba_free_space where tablespace_name='TEST'; --查看TEST表空间剩余量
     select TABLESPACE_NAME,sum(bytes)/1024/1024 from dba_free_space group by TABLESPACE_NAME; --查看所有表空间的剩余量
no rows selected

SQL> DROP TABLE TEST1;

Table dropped.

select TABLESPACE_NAME,SUM(BYTES)/1024/1024 FROM dba_free_space WHERE TABLESPACE_NAME='TEST' GROUP BY TABLESPACE_NAME；
TABLESPACE_NAME                SUM(BYTES)/1024/1024
------------------------------ --------------------
TEST                                              9

SQL> alter database datafile 9 autoextend on;

SQL>  select TABLESPACE_NAME,SUM(BYTES)/1024/1024 FROM dba_free_space WHERE TABLESPACE_NAME='TEST' GROUP BY TABLESPACE_NAME;

TABLESPACE_NAME                SUM(BYTES)/1024/1024
------------------------------ --------------------
TEST                                              9

SQL> create table test2 as select * from dba_objects;

Table created.

SQL> show paramter recyclebin

SQL>  select TABLESPACE_NAME,SUM(BYTES)/1024/1024 FROM dba_free_space WHERE TABLESPACE_NAME='TEST' GROUP BY TABLESPACE_NAME;

no rows selected

SQL> drop table test2;

Table dropped.

SQL> show parameter recyclebin
show  recyclebin

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST2            BIN$JAkhk08TM