oracle 闪回区 删除,oracle闪回区存储空间耗尽解决方法（ORA-19815解决方法）

ORA-19815问题，是闪回区空间耗尽，解决的方法我使用增大闪回区的存储空间，来解决此问题

现在先查看一下v$recovery_file_dest视图，可以发现可以回收的空间为0，在查看闪回区的使用率

SQL> select file_type, percent_space_used as used,percent_space_reclaimable as reclaimable, number_of_files from v$flash_recovery_area_usage;

FILE_TYPE USED RECLAIMABLE number

CONTROLFILE 0 0 0

ONLINELOG 0 0 0

ARCHIVELOG 98.65 0 51

BACKUPPIECE 0 0 0

IMAGECOPY 0 0 0

FLASHBACKLOG 0 0 0

6 rows selected.

发现已经使用了98.65%了

下面为解决此问题的方法

登陆数据库

[oracle@master ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Jun 10 22:41:54 2012

r /> Copyright © 1982, 2005, Oracle. All rights reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

先查看当前的闪回区大小

SQL> show parameter db_recovery_file_dest

NAME TYPE VALUE

db_recovery_file_dest string /home/oracle/flash_recovery_ar

ea

db_recovery_file_dest_size big integer 2G

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 53

Next log sequence to archive 53

Current log sequence 55

可以看得闪回区的大小为2g，所以我把他扩展为10g

SQL> alter system set db_recovery_file_dest_size=10G scope=both;

System altered.

然后在查看闪回区的使用情况

SQL> select * from v$recovery_file_dest;

NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES

/home/oracle/flash_recovery_area 10737418240 2383963648 0 57

SQL> show parameter db_recovery_file_dest

NAME TYPE VALUE

db_recovery_file_dest string /home/oracle/flash_recovery_ar

ea

db_recovery_file_dest_size big integer 10G

在查看一下闪回区的使用率

SQL> select file_type, percent_space_used as used,percent_space_reclaimable as reclaimable, number_of_files as ‘number’ from v$flash_recovery_area_usage;

FILE_TYPE USED RECLAIMABLE number

CONTROLFILE 0 0 0

ONLINELOG 0 0 0

ARCHIVELOG 22.2 0 57

BACKUPPIECE 0 0 0

IMAGECOPY 0 0 0

FLASHBACKLOG 0 0 0

6 rows selected.

从之前的98.65%降到了22.2%