| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Create database around existing tablespace
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_01C0014B.84F5CC50
Content-Type: text/plain;
charset="iso-8859-1"
If your DB is v8.1 (v8.0?) you can use the transportable tablespace feature. Oracle defines it as "The transportable tablespace feature enables you to move a set of tablespaces from one Oracle database to another."
You can start the learning process by following the link below.....
http://oradoc.photo.net/ora81/DOC/server.815/a67792/ch02.htm#38847
Brian P. Mac Lean
Senior Oracle Database Administrator
OCPv8/Oracle Master
HomeBid.Com
8700 N. Gainey Center Drive
Suite 150
Scottsdale, AZ 85258
Tel:480.609.4624 Cel:602.617.6075 Fax:480.609.4646 Net:brian.maclean_at_homebid.com
"The secret to creativity is knowing how to hide your sources." ---- Albert Einstein
-----Original Message-----
From: Philip West [mailto:P.West_at_g-icap.com]
Sent: Tuesday, August 08, 2000 7:09 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: Craete database around existing tablespace
Absolutely,
My answer was not complete without that. Of course you can use the create controlfile to clone the database and then prune that which you do not need.
Phil West
Unix Sys Admin and Oracle Financials DBA
+44 (0) 20 7623 5222 x5221
-----Original Message-----
Sent: 08 August 2000 14:22
To: Multiple recipients of list ORACLE-L
Hello Karen and Philip,
depending on your needs in can be useful, to keep an existing tablespace or more in a new database.
To do this you can clone the database and drop then the unneeded objects. Correct is, that you can't create a new database and then "append" a tablespace from an alreeady existing database and you can clone only within the same OS.
Stephan
-----Original Message-----
Sent: Tuesday, August 08, 2000 12:37 PM
To: Multiple recipients of list ORACLE-L
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-----
Sent: 08 August 2000 11:00
To: Multiple recipients of list ORACLE-L
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
-- Author: =?ISO-8859-1?Q?J=E4ck_Stephan?= INET: Jaeck.Stephan_at_REAL.DE Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing ListsReceived on Tue Aug 08 2000 - 10:15:38 CDT
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). **************************************************************************** *** 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. **************************************************************************** *** -- Author: Philip West INET: P.West_at_g-icap.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
------_=_NextPart_001_01C0014B.84F5CC50
Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"> <HTML> <HEAD> <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; = charset=3Diso-8859-1"> <META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version = 5.5.2448.0"> <TITLE>RE: Create database around existing tablespace</TITLE> </HEAD> <BODY> <P><FONT SIZE=3D2 FACE=3D"Arial">If your DB is v8.1 (v8.0?) you can use = the transportable tablespace feature. Oracle defines it as "The = transportable tablespace feature enables you to move a set of = tablespaces from one Oracle database to another." </FONT></P> <P><FONT SIZE=3D2 FACE=3D"Arial">You can start the learning process by = following the link below.....</FONT> </P> <P><FONT SIZE=3D2 FACE=3D"Arial"><A = HREF=3D"http://oradoc.photo.net/ora81/DOC/server.815/a67792/ch02.htm#388= 47" = TARGET=3D"_blank">http://oradoc.photo.net/ora81/DOC/server.815/a67792/ch= 02.htm#38847</A></FONT> </P> <BR> <P><B><FONT COLOR=3D"#0000FF" FACE=3D"Comic Sans MS">Brian P. Mac = Lean</FONT></B> <BR><FONT COLOR=3D"#808080" SIZE=3D1 FACE=3D"Comic Sans MS">Senior = Oracle Database Administrator</FONT> <BR><FONT COLOR=3D"#808080" SIZE=3D1 FACE=3D"Comic Sans = MS">OCPv8/Oracle Master</FONT> <BR><FONT COLOR=3D"#808080" SIZE=3D1 FACE=3D"Comic Sans = MS">HomeBid.Com</FONT> <BR><FONT COLOR=3D"#808080" SIZE=3D1 FACE=3D"Comic Sans MS">8700 N. = Gainey Center Drive</FONT> <BR><FONT COLOR=3D"#808080" SIZE=3D1 FACE=3D"Comic Sans MS">Suite = 150</FONT> <BR><FONT COLOR=3D"#808080" SIZE=3D1 FACE=3D"Comic Sans MS">Scottsdale, = AZ 85258</FONT> <BR><FONT COLOR=3D"#808080" SIZE=3D1 FACE=3D"Comic Sans = MS">Tel:480.609.4624</FONT> <BR><FONT COLOR=3D"#808080" SIZE=3D1 FACE=3D"Comic Sans = MS">Cel:602.617.6075</FONT> <BR><FONT COLOR=3D"#808080" SIZE=3D1 FACE=3D"Comic Sans = MS">Fax:480.609.4646</FONT> <BR><FONT COLOR=3D"#808080" SIZE=3D1 FACE=3D"Comic Sans = MS">Net:brian.maclean_at_homebid.com</FONT> </P> <P><FONT SIZE=3D2 FACE=3D"Arial">"The secret to creativity is = knowing how to hide your sources."</FONT><FONT SIZE=3D2 = FACE=3D"Arial"> ----</FONT><FONT SIZE=3D2 FACE=3D"Arial"> Albert = Einstein</FONT> </P> <P><FONT SIZE=3D2 FACE=3D"Arial">-----Original Message-----</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">From: Philip West [<A = HREF=3D"mailto:P.West_at_g-icap.com">mailto:P.West_at_g-icap.com</A>]</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">Sent: Tuesday, August 08, 2000 7:09 = AM</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">To: Multiple recipients of list = ORACLE-L</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">Subject: RE: Craete database around = existing tablespace</FONT> </P> <BR> <P><FONT SIZE=3D2 FACE=3D"Arial">Absolutely,</FONT> </P> <P><FONT SIZE=3D2 FACE=3D"Arial">My answer was not complete without = that. Of course you can use the create</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">controlfile to clone the database and = then prune that which you do not need.</FONT> </P> <P><FONT SIZE=3D2 FACE=3D"Arial">Phil West</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">Unix Sys Admin and Oracle Financials = DBA</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">+44 (0) 20 7623 5222 x5221</FONT> </P> <BR> <P><FONT SIZE=3D2 FACE=3D"Arial">-----Original Message-----</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">Sent: 08 August 2000 14:22</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">To: Multiple recipients of list = ORACLE-L</FONT> </P> <BR> <P><FONT SIZE=3D2 FACE=3D"Arial">Hello Karen and Philip,</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial"> </FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">depending on your needs in can be = useful, to keep an existing tablespace or</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">more in a new database.</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial"> </FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">To do this you can clone the database = and drop then the unneeded objects.</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">Correct is, that you can't create a = new database and then "append" a</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">tablespace from an alreeady existing = database and you can clone only within</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">the same OS.</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial"> </FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">Stephan</FONT> </P> <P><FONT SIZE=3D2 FACE=3D"Arial">-----Original Message-----</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">Sent: Tuesday, August 08, 2000 12:37 = PM</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">To: Multiple recipients of list = ORACLE-L</FONT> </P> <BR> <P><FONT SIZE=3D2 FACE=3D"Arial">YOU CANNOT DO THIS!</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial"> </FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">All you have done is to get the new = database to use the o/s file from the</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">old tablespace. Oracle has = reinitialised this datafile and it no longer</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">holds any data.</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial"> </FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">You cannot transfer data in this = way.</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial"> </FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">I apologise if this sounds rude but = you seem to lack an understanding of the</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">basic concepts of oracle = databases. Before moving forward I would suggest</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">that you read material on database = structures, database creation and backup</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">recovery. If you have a = friendly dba nearby get her/him to explain what you</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">have done and why it will not = work.</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial"> </FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">In this example you would need to = export the data from the original database</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">and import it into the new = database. You cannot export by tablespace so you</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">would need to export the individual = objects in the tablespace. If all</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">objects in the tablespace are owned = by a single oracle user and that user</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">does not have objects elsewhere then = you could export that user and import</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">it into the new database.</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial"> </FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial"> </FONT> </P> <P><FONT SIZE=3D2 FACE=3D"Arial">Phil West </FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">Unix Sys Admin and Oracle Financials = DBA </FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">+44 (0) 20 7623 5222 x5221 </FONT> </P> <P><FONT SIZE=3D2 FACE=3D"Arial">-----Original Message-----</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">Sent: 08 August 2000 11:00</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">To: Multiple recipients of list = ORACLE-L</FONT> </P> <BR> <BR> <P><FONT SIZE=3D2 FACE=3D"Arial">Good morning </FONT> </P> <P><FONT SIZE=3D2 FACE=3D"Arial">I have decided to move a tablespace = from database ORCL to it's own database.</FONT> </P> <P><FONT SIZE=3D2 FACE=3D"Arial">I have created the new database, but = getting the "old" tablespace into this</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">database is a bit of a problem. = </FONT> </P> <P><FONT SIZE=3D2 FACE=3D"Arial">The datafiles (4) have been copied to = the correct filesystem destination.</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">I</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial"> have used </FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">"CREATE TABLESPACE = "MINKEY" </FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">DATAFILE = '/merc1/IMAGE/mindkey01.dbf' REUSE;" - no problem. </FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">But there are 3 more datafiles that = are part of this tablespace. How to I</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">get them to be recognised by the = tablespace? </FONT> </P> <P><FONT SIZE=3D2 FACE=3D"Arial">Any advice would be greatly = appreciated </FONT> </P> <P><FONT SIZE=3D2 FACE=3D"Arial">Thanks </FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">Karen Turnbull </FONT> </P> <BR> <P><FONT SIZE=3D2 FACE=3D"Arial">-- </FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">Author: = =3D?ISO-8859-1?Q?J=3DE4ck_Stephan?=3D</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial"> INET: = Jaeck.Stephan_at_REAL.DE</FONT> </P> <P><FONT SIZE=3D2 FACE=3D"Arial">Fat City Network = Services -- (858) 538-5051 FAX: (858) = 538-5051</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">San Diego, = California -- Public Internet = access / Mailing Lists</FONT> <BR><FONT SIZE=3D2 = FACE=3D"Arial">---------------------------------------------------------=
-----------</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">To REMOVE yourself from this mailing = list, send an E-Mail message</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">to: ListGuru_at_fatcity.com (note EXACT = spelling of 'ListGuru') and in</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">the message BODY, include a line = containing: UNSUB ORACLE-L</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">(or the name of mailing list you want = to be removed from). You may</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">also send the HELP command for other = information (like subscribing).</FONT> </P> <BR> <P><FONT SIZE=3D2 = FACE=3D"Arial">*********************************************************= **********************</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">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. </FONT></P> <P><FONT SIZE=3D2 FACE=3D"Arial"> 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.</FONT></P> <P><FONT SIZE=3D2 FACE=3D"Arial">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.</FONT></P> <P><FONT SIZE=3D2 = FACE=3D"Arial">*********************************************************= **********************</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">-- </FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">Author: Philip West</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial"> INET: P.West_at_g-icap.com</FONT> </P> <P><FONT SIZE=3D2 FACE=3D"Arial">Fat City Network = Services -- (858) 538-5051 FAX: (858) = 538-5051</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">San Diego, = California -- Public Internet = access / Mailing Lists</FONT> <BR><FONT SIZE=3D2 = FACE=3D"Arial">---------------------------------------------------------=
-----------</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">To REMOVE yourself from this mailing = list, send an E-Mail message</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">to: ListGuru_at_fatcity.com (note EXACT =
 |
 |