Re: Created a tablespace in another database file

From: Peter van Rijn <p.vanrijnREMOVE_at_THISzhew.nl>
Date: Tue, 10 Dec 2002 09:37:27 +0100
Message-ID: <uvba62os4goeed@corp.supernews.com>

Just tried the following:

[1] created tablespace peter and datafile peter.dbf
[2] connected to second instance/database
[3] tried to create tablespace peter and use same datafile
[4] ERRORS:

ORA-01119: error in creating database file '/oradata/ght/peter.dbf' ORA-27086: skgfglk: unable to lock file - already in use HP-UX Error: 13: Permission denied
Additional information: 8

[5] shutdown first instance and tried step [3] again
[6] tablespace succesfully created
[7] started first instance again
[8] ERROR:

Database mounted.
ORA-01157: cannot identify/lock data file 23 - see DBWR trace file ORA-01110: data file 23: '/oradata/ght/peter.dbf'

Ergo: you can reuse a datafile that is being used by a second database, IF that database is down. If you did this the first database cannot be started again.

Solution:

in first database:

alter database datafile 'xxxxx' offline drop; alter database open;
drop tablespace yyyy;
create tablespace yyyy datafile 'the_right_path_to_datafile' size ...

in secodn database:

drop tablespace yyyy;

regards,
Peter

"Bert Bear" <bertbear_at_NOSPAMbertbear.net> schreef in bericht news:I7hJ9.2424$Hg5.1304243337_at_newssvr11.news.prodigy.com...
> Bret,
>
> Can you be more specific on the problem. Do you mean they share the same
> datafile (not tablespace)?
>
> Without knowing the specifics (including going back over the alert logs
for
> both databases, which will give many of the steps leading to your
> situation), my gut says:
>
> 1) Use the drop command to drop the tablespaces using the datafile in
both
> databases.
> 2) use the drop command to drop the datafile in both database.
>
> Again without knowing the specifics, I can say this will work.
>
> Bertram Moshier
> Oracle Certified Professional 8i and 9i DBA
>
> http://www.bmoshier.net/bertram
>
>
>
> "Bretg" <bretg80_at_yahoo.com> wrote in message
> news:74acbad3.0212092248.64af4efd_at_posting.google.com...
> > Somehow Oracle 8.1.7 on linux allowed me to create a tablespace in
> > another database. These two databases use the same index tablespace
> > and I have no idea how I got Oracle to do this, but now that it is
> > done, I can't get figure out how to fix it. I have tried to edit the
> > control files using the alter database backup controlfile to trace
> > command. However, when I remove the entry that points to the other
> > database, then I get the infamous ORA-0600 error.
> >
> > I imagine I'm going to have to restore from tape unless anyone has any
> > suggestions.
> >
> > Bret
>
>
Received on Tue Dec 10 2002 - 02:37:27 CST