Reuse datafile when creating FBA tablespace to avoid ORA-01119/ORA-27038 #8
Description
When no flashback archive is available the following statements will be generated:
-- to solve "No flashback archive available" run the following statements as SYS:
CREATE TABLESPACE fba DATAFILE '/u01/app/oracle/oradata/ocdb/opdb1/fba01.dbf' SIZE 10M AUTOEXTEND ON NEXT 1M;
CREATE FLASHBACK ARCHIVE fba TABLESPACE fba RETENTION 1 YEAR;
GRANT FLASHBACK ARCHIVE ON fba TO OGDEMO;
-- quotas for flashback archive tablespaces required to ensure the background process does not fail with ORA-01950 when creating archive tables
-- alternatively to the UNLIMITED TABLESPACE privilege you may set quotes on tablespaces defined in DBA_FLASHBACK_ARCHIVE_TS
GRANT UNLIMITED TABLESPACE TO OGDEMO;
If the datafile already exists (for whatever reason) the following errors will be thrown:
CREATE TABLESPACE fba DATAFILE '/u01/app/oracle/oradata/ocdb/opdb1/fba01.dbf' SIZE 10M AUTOEXTEND ON NEXT 1M
Error report -
SQL Error: ORA-01119: error in creating database file '/u01/app/oracle/oradata/ocdb/opdb1/fba01.dbf'
ORA-27038: created file already exists
To solve the problem, the existing datafile should be reused. E.g. generate a CREATE TABLESPACE statement as the following:
CREATE TABLESPACE fba DATAFILE '/u01/app/oracle/oradata/ocdb/opdb1/fba01.dbf' SIZE 10M REUSE AUTOEXTEND ON NEXT 1M;