Skip to Main Content
Forums

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

ORA-01543 and ORA-00959 for the same tablespace

622284Feb 8 2008 — edited Feb 8 2008
Thu Jan 24 15:04:58 2008
CREATE TABLESPACE "bac_prof_caesar"
LOGGING
DATAFILE
'/oracle/data/bac/bac_BAC_PROFILE_CAESAR_01.dbf' SIZE 1000M REUSE
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO

Thu Jan 24 15:05:07 2008
Completed: CREATE TABLESPACE "bac_prof_caesar"
LOGGING
DATAFILE
'/oracle/data/bac/bac_BAC_PROFILE_CAESAR_01.dbf' SIZE 1000M REUSE
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO

Thu Jan 24 15:09:26 2008
CREATE TABLESPACE "BAC_PROFILE_CAESAR"
LOGGING
DATAFILE
'/oracle/data/bac/bac_BAC_PROFILE_CAESAR_01.dbf' SIZE 1000M REUSE
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
Thu Jan 24 15:09:26 2008
ORA-1537 signalled during: CREATE TABLESPACE "BAC_PROFILE_CAESAR"
LOGGING
DATAFILE
'/oracle/data/bac/bac_BAC_PROFILE_CAESAR_01.dbf' SIZE 1000M REUSE
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO...

Thu Jan 24 15:10:13 2008
CREATE TABLESPACE "BAC_PROFILE_CAESAR"
LOGGING
DATAFILE
'/oracle/data/bac/bac_BAC_PROFILE_CAESAR_02.dbf' SIZE 1000M REUSE
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO

....
Thu Jan 24 16:05:16 2008
Errors in file /oracle/log/bac/bdump/bac_ckpt_24549.trc:
ORA-01110: data file 9: '/oracle/data/bac/bac_BAC_PROFILE_CAESAR_01.dbf'
ORA-01116: error in opening database file 9
ORA-01110: data file 9: '/oracle/data/bac/bac_BAC_PROFILE_CAESAR_01.dbf'
ORA-27041: unable to open file
SVR4 Error: 2: No such file or directory


after specifying the file "01" for the wrong tablespace, the file seems to be missing and the tablespace cannot be dropped or recreated


SQL> ! ls -l /oracle/data/bac/bac_BAC_PROFILE_CAESAR_01.dbf
/oracle/data/bac/bac_BAC_PROFILE_CAESAR_01.dbf: No such file or directory


SQL> drop tablespace bac_prof_caesar including contents;
drop tablespace bac_prof_caesar including contents
*
ERROR at line 1:
ORA-00959: tablespace 'BAC_PROF_CAESAR' does not exist


SQL> drop tablespace BAC_PROF_CAESAR including contents;
drop tablespace BAC_PROF_CAESAR including contents
*
ERROR at line 1:
ORA-00959: tablespace 'BAC_PROF_CAESAR' does not exist


SQL> select TABLESPACE_NAME, FILE_ID from dba_data_files where FILE_NAME='/oracle/data/bac/bac_BAC_PROFILE_CAESAR_01.dbf';

TABLESPACE_NAME FILE_ID
------------------------------ ----------
bac_prof_caesar 9



SQL> alter tablespace bac_prof_caesar add datafile '/oracle/data/bac/bac_BAC_PROFILE_CAESAR_01.dbf' SIZE 1000M reuse;
alter tablespace bac_prof_caesar add datafile '/oracle/data/bac/bac_BAC_PROFILE_CAESAR_01.dbf' SIZE 1000M reuse
*
ERROR at line 1:
ORA-00959: tablespace 'BAC_PROF_CAESAR' does not exist


SQL> CREATE TABLESPACE "bac_prof_caesar" LOGGING DATAFILE '/oracle/data/bac/bac_BAC_PROFILE_CAESAR_01.dbf' SIZE 1000M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE "bac_prof_caesar" LOGGING DATAFILE '/oracle/data/bac/bac_BAC_PROFILE_CAESAR_01.dbf' SIZE 1000M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
*
ERROR at line 1:
ORA-01543: tablespace 'bac_prof_caesar' already exists



How do I go about this??

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Mar 7 2008
Added on Feb 8 2008
#general-database-discussions
5 comments
8,832 views