Re: I can attach in SQL Server, but can I in Oracle?

Hi Ron Fluegge, thanks for writing this:
> First, let me apologize for the probably "simple and basic" question, but I
> have searched both Google and Deja without success.
>
> I have a commercial application that I am finishing in VS.NET that connects
> with both SQL Server and Oracle databases -- the client gets to choose one
> of the following to store the data in: Access, SQL Server 7 or 2000, or
> Oracle 8i or 9i. The development has gone well and I can read/write to any
> of the 5 options successfully during development.
>
> I have installed the developer versions of SQL Server 7 and 2000 and Oracle
> 8i and 9i.
>
> The application runs just fine. I have been creating the "master" databases
> in SQL Server and then "uploading" them into Oracle using the MS DTS app.
> Then I switch the "pointer" in my application so that it reads the data from
> the Oracle DBs and it also works just fine.
>
> However, now I'm getting ready to create the distribution CDs and I realized
> that I have a problem...<G>.
>
> With the SQL Server databases, I can distribute the two required database
> files and then either run SP_ATTACH_DB in SQL 7 or interactively in SQL 2000
> in order to "attach" the databases to the Server. Works great.
>
> I have tried to understand how to do the same thing in Oracle. I suspect
> that since I have the developer versions of the Oracle software that I don't
> have all of the tools to do what I need to do. When I attempt to import or
> export the tablespaces it says something about "the wizard can only be
> launched when the application is connected to the Oracle Management Server
> (OMS)."
>
> As a result, I'm at a loss to understand what it is I need to do. I've gone
> through as much online as I can find and I have several Oracle 8i and 9i
> books, but this leaves me mystified.
>
> Essentially what I need to do is the following:
>
> (1) Create copies of the tablespace (database in SQL Server terminology)
> required for the application (not only the schema, but also one "table"
> contains about 5,000 records that need to be a part of the installation so
> it appears to be more than a SQL script to create the tables)
>
> (2) Copy the files created in (1) to the distribution CD
>
> (3) Clients then copy the files created in (1) to their own Oracle server
> and then "attach" these files to the database instance so that the
> application can then access the tables.
>
> Please understand that I may not be using the correct terminology for the
> Oracle databases and tables -- I apologize.
>
> Any assistance would be greatly appreciated.
>
> Thanks.
>
> Ron Fluegge
>

If I understand your requirement correctly (btw, thanks for being so thorough in your description ... sadly, a rare occurance in this NG), you need to read up on an Oracle feature known as "transportable tablespaces" (which you can find by searching for this phrase on Google).

Since I don't know what the SQL Server stored procedure does, I don't know if this is the same thing. But basically, transportable tablespaces allow you to unhook a tablespace from one database and plug it into another.

-- 
[:%s/Karsten Farrell/Oracle DBA/g]