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
"SYS.DBMS_DBFS_SFS_ADMIN", line 473
ORA-00001: unique constraint
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;
SQL> drop procedure fsCreate;
SQL> undefine ts_name
SQL> undefine fs_name
SQL> undefine fs_compress
SQL> undefine fs_dedup
SQL> undefine fs_encrypt
SQL> undefine fs_partition
Disconnected from Oracle Database 11g Enterprise Edition Release 220.127.116.11.0 - 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;
4) Now the ‘old’ information of filesystem ‘fs1′ is removed. Now it’s possible to create a filesystem with the name ‘fs1′ again.