RE: Craete database around existing tablespace

From: Philip West <P.West_at_g-icap.com>
Date: Tue, 8 Aug 2000 10:31:11 +0100
Message-Id: <10583.114045@fatcity.com>

This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible.

------_=_NextPart_001_01C0011D.C689BCAE
Content-Type: text/plain;

charset="iso-8859-1"

YOU CANNOT DO THIS! All you have done is to get the new database to use the o/s file from the old tablespace. Oracle has reinitialised this datafile and it no longer holds any data.

You cannot transfer data in this way.

I apologise if this sounds rude but you seem to lack an understanding of the basic concepts of oracle databases. Before moving forward I would suggest that you read material on database structures, database creation and backup recovery. If you have a friendly dba nearby get her/him to explain what you have done and why it will not work.

In this example you would need to export the data from the original database and import it into the new database. You cannot export by tablespace so you would need to export the individual objects in the tablespace. If all objects in the tablespace are owned by a single oracle user and that user does not have objects elsewhere then you could export that user and import it into the new database.

Phil West
Unix Sys Admin and Oracle Financials DBA +44 (0) 20 7623 5222 x5221

-----Original Message-----
From: Turnbull, Karen (BSA) [mailto:KTurnbull_at_Billiton.co.za] Sent: 08 August 2000 11:00
To: Multiple recipients of list ORACLE-L Subject: Craete database around existing tablespace

Good morning

I have decided to move a tablespace from database ORCL to it's own database.

I have created the new database, but getting the "old" tablespace into this database is a bit of a problem.

The datafiles (4) have been copied to the correct filesystem destination.

I have used
"CREATE TABLESPACE "MINKEY"
DATAFILE '/merc1/IMAGE/mindkey01.dbf' REUSE;" - no problem. But there are 3 more datafiles that are part of this tablespace. How to I get them to be recognised by the tablespace?

Any advice would be greatly appreciated

Thanks
Karen Turnbull
This message and any attachments may be confidential and may also be privileged or otherwise protected from disclosure. They are sent for the attention of the named addressee(s) only. If you are not the named addressee(s) please notify the sender immediately and destroy this message. In this case, you should not copy or distribute this message or attachments, use it for any purpose or disclose its contents to any other person. Opinions, conclusions and other information in this message that do not relate to the official business of my firm/company shall be understood as neither given nor endorsed by it.

The information in this Internet e-mail is confidential and may be legally privileged. It is intended solely for the addressee. Access to this Internet e-mail by anyone else is unauthorised and any disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it, is prohibited and may be unlawful. When addressed to our clients any opinions or advice contained in this Internet e-mail are subject to the terms and conditions expressed in any applicable documentation or market practices governing the relationship between Garban Intercapital plc and its clients.

Any views expressed in this message are those of the individual sender except where they are stated to be the views of Garban Intercapital plc.

------_=_NextPart_001_01C0011D.C689BCAE
Content-Type: text/html;

charset="iso-8859-1"

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<TITLE>Craete database around existing tablespace</TITLE>

<META content="MSHTML 5.00.2314.1000" name=GENERATOR></HEAD>
<BODY>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN class=734292209-08082000>YOU
CANNOT DO THIS!</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=734292209-08082000></SPAN></FONT> </DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN class=734292209-08082000>All
you have done is to get the new database to use the o/s file from the old tablespace.  Oracle has reinitialised this datafile and it no longer holds any data.</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=734292209-08082000></SPAN></FONT> </DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN class=734292209-08082000>You
cannot transfer data in this way.</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=734292209-08082000></SPAN></FONT> </DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN class=734292209-08082000>I
apologise if this sounds rude but you seem to lack an understanding of the basic concepts of oracle databases.  Before moving forward I would suggest that you read material on database structures, database creation and backup recovery.  If you have a friendly dba nearby get her/him to explain what you have done and why it will not work.</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=734292209-08082000></SPAN></FONT> </DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN class=734292209-08082000>In
this example you would need to export the data from the original database and import it into the new database.  You cannot export by tablespace so you would need to export the individual objects in the tablespace.  If all objects in the tablespace are owned by a single oracle user and that user does not have objects elsewhere then you could export that user and import it into the new database.</SPAN></FONT></DIV>
<DIV> </DIV>
<DIV> </DIV>
<P><FONT face=Arial>Phil West</FONT> <BR><FONT face="Arial Narrow" size=1>Unix
Sys Admin and Oracle Financials DBA</FONT> <BR><FONT face="Arial Narrow" size=1>+44 (0) 20 7623 5222 x5221</FONT> </P>
<BLOCKQUOTE style="MARGIN-RIGHT: 0px">

  <DIV align=left class=OutlookMessageHeader dir=ltr><FONT face=Tahoma   size=2>-----Original Message-----<BR><B>From:</B> Turnbull, Karen (BSA)   [mailto:KTurnbull_at_Billiton.co.za]<BR><B>Sent:</B> 08 August 2000   11:00<BR><B>To:</B> Multiple recipients of list ORACLE-L<BR><B>Subject:</B>   Craete database around existing tablespace<BR><BR></DIV></FONT>   <P><FONT face=Arial size=2>Good morning</FONT> </P>   <P><FONT face=Arial size=2>I have decided to move a tablespace from database   ORCL to it's own database.</FONT> <BR><FONT face=Arial size=2>I have created   the new database, but getting the "old" tablespace into this database is a bit   of a problem.</FONT> </P>
  <P><FONT face=Arial size=2>The datafiles (4) have been copied to the correct   filesystem destination.</FONT> </P>
  <P><FONT face=Arial size=2>I have used </FONT><BR><FONT face=Arial   size=2>"</FONT><FONT face=Arial size=2>CREATE TABLESPACE "MINKEY"   </FONT><BR><FONT face=Arial size=2>DATAFILE  '/merc1/IMAGE/mindkey01.dbf'   REUSE;</FONT><FONT face=Arial size=2>"  - no problem.</FONT> <BR><FONT   face=Arial size=2>But there are 3 more datafiles that are part of this   tablespace.  How to I get them to be recognised by the tablespace?</FONT>

  </P>
  <P><FONT face=Arial size=2>Any advice would be greatly appreciated</FONT> </P>
  <P><FONT face=Arial size=2>Thanks</FONT> <BR><FONT face=Arial size=2>Karen

Turnbull</FONT> <BR><FONT face=Arial size=2>This message and any attachments may be confidential and may also be privileged or otherwise protected from disclosure. They are sent for the attention of the named addressee(s) only. If you are not the named addressee(s) please notify the sender immediately and destroy this message. In this case, you should not copy or distribute this message or attachments, use it for any purpose or disclose its contents to any other person. Opinions, conclusions and other information in this message that do not relate to the official business of my firm/company shall be understood as neither given nor endorsed by it.</FONT></P></BLOCKQUOTE><CODE><FONT SIZE=3><BR>
<BR>

*******************************************************************************<BR>

The information in this Internet e-mail is confidential and may be legally privileged. It is intended solely for the addressee. Access to this Internet e-mail by anyone else is unauthorised and any disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it, is prohibited and may be unlawful. <BR> When addressed to our clients any opinions or advice contained in this Internet e-mail are subject to the terms and conditions expressed in any applicable documentation or market practices governing the relationship between Garban Intercapital plc and its clients.<BR>
<BR>

Any views expressed in this message are those of the individual sender except where they are stated to be the views of Garban Intercapital plc.<BR> Received on Tue Aug 08 2000 - 04:31:11 CDT