Top 60 Oracle Blogs

Recent comments

DBFS and ORA-64007: invalid store specified

I made a post about creating and using Oracle’s DBFS. One thing I’ve done is just dropping the user which was the owner of a database filesystem (drop user cascade), with the intention to get rid of the database filesystem.

During the creation of a filesystem with the same name as I’ve used with the previous (dropped) user, I got this error message:

....much more other messages....
create stmt: begin dbms_dbfs_sfs.createFilesystem(store_name => 'FS_FS1',
tbl_name => 'T_FS1', tbl_tbs => 'dbfs', lob_tbs => 'dbfs', do_partition => true,
partition_key => 1, do_compress => false, compression => '', do_dedup => false,
do_encrypt => false); end;
ERROR: -64007 msg: ORA-64007: invalid store specified
ORA-06512: at
ORA-00001: unique constraint
(SYS.SYS_C003185) violated
ERROR at line 1:
ORA-64007: invalid store specified
ORA-06512: at "SYS.DBMS_DBFS_SFS_ADMIN", line 473
ORA-00001: unique constraint (SYS.SYS_C003185) violated
ORA-06512: at line 63

SQL> show errors;
No errors.
SQL> commit;

Commit complete.

SQL> drop procedure fsCreate;

Procedure dropped.

SQL> undefine ts_name
SQL> undefine fs_name
SQL> undefine fs_compress
SQL> undefine fs_dedup
SQL> undefine fs_encrypt
SQL> undefine fs_partition
SQL> exit;
Disconnected from Oracle Database 11g Enterprise Edition Release - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

This means the name specified as filesystem name (fs1 here). Is already present in the data dictionary.

How to solve this?

The best way is to drop the database filesystem with the script ‘dbfs_drop_filesystem’ in $ORACLE_HOME/rdbms/admin.

If the harm is already done:

1) Check if the filesystem you want to create not just exists. Do this by searching for a table with the name of the filesystem, prefixed with ‘T_’, so for ‘fs1′: T_FS1; as DBA:
SQL> select owner, table_name from dba_tables where table_name like 'T_FS1';

2) Get the table identifier from DBFS_SFS$_TAB:
SQL> select tabid from sys.dbfs_sfs$_tab where table_name = 'T_FS1';


3) Drop records belonging to filesystem FS1 in the data dictionary:
SQL> delete from sys.dbfs_sfs$_vol where tabid = 57;
SQL> delete from sys.dbfs_sfs$_tab where tabid = 57;
SQL> commit;

4) Now the ‘old’ information of filesystem ‘fs1′ is removed. Now it’s possible to create a filesystem with the name ‘fs1′ again.

Tagged: oracle database dbfs filesystem ora 64007 ora-64007