Home » RDBMS Server » Server Utilities » Any way to import an external database without Oracle Utilties such as Import?
Any way to import an external database without Oracle Utilties such as Import? [message #147081] |
Tue, 15 November 2005 13:07 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
donlcs
Messages: 62 Registered: October 2005 Location: VA, USA
|
Member |
|
|
Hi,
I need to import an external database, namely, a sql server 2000 -based database. The Oracle 10g package (standard
or enterprise, don't recall, probably enterprise) does not include these utilities, is there any way to circumvent the problem? Are these sort of Oracle Utilities free to download (hence incorporation into the existing package)? And if not, then what?
TIA.
|
|
|
Re: Any way to import an external database without Oracle Utilties such as Import? [message #147124 is a reply to message #147081] |
Tue, 15 November 2005 19:31 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
I don't know of any way to "import" a non-oracle database. Such a utility would have to create tables, indexes, constraints. But then how would you handle things like AUTONUMs in SS, t-SQL store procs...?
If you want to create all of the empty objects manually and then just import the data, there is no easier way than the COPY command in SQL*Plus.
_____________
Ross Leishman
|
|
|
|
Re: Any way to import an external database without Oracle Utilties such as Import? [message #147237 is a reply to message #147126] |
Wed, 16 November 2005 09:44 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
donlcs
Messages: 62 Registered: October 2005 Location: VA, USA
|
Member |
|
|
Thanks, Ross Leishman and Mahesh Rajendran, for responding.
Mahesh Rajendran:
Good point about intention. Bear with me, it's a fairly long msg.
As I'm fairly new to Oracle, and my key interest at this point of time is 'building data warehouse in Oracle' (I have conceptual knowledge of dw).
1) Oracle native
At this point, I have two sample ORACLE databases, one, the default one, seems to run fine, but the second one, sample Oracle data warehouse database, has problem with the Enterprise Manager.
The strange thing is, Net Manager test indicates that connection is fine with this db, however, the Enterprise Manager complains that the Listener is down, err msg:
" ORA-12505: TNS:listener does not currently know of SID given in connect descriptor (DBD ERROR: OCIServerAttach)
". But the listerner was up (a Windows' service). I can connect to the dw db via SQL Plus tool as a normal user, scott.
And OK, so, I tried to start the listener from this interface, it asked me to login, I tried both sys and system with, I believe, correct password, it failed. And naturally, attempt to change sys's pwd from scott's account at SQL Plus did not work. So, it's sort of egg and chicken thing here for this instance.
But now, if my memory serves, probably this db/instance is generated when I installed OWB, and if its 'content' is essentially the same as the 'default' db/instance, which is already fine, then, I don't necessarily need to get to it at this moment but would be good to know some tricks for trouble-shooting.
2) Import db from SQL Server
So, alternatively, if I have another db, which I'm familar with, then, it might not be a bad idea to turn it into a dw db. Hence, the idea of import the classical Northwind db (in MS world). Now, one problem with the Oracle workbench, that is, the sql plugin, how to install all these classes?
Another (separate) question, how to learn/display all the user tables (except SYSTEM schemas) for a particular tablespace, e.g. EXAMPLE, ?
Many thanks.
|
|
|
Re: Any way to import an external database without Oracle Utilties such as Import? [message #147259 is a reply to message #147237] |
Wed, 16 November 2005 19:00 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
donlcs,
Oracle is very much unlike SQL Server in one important respect: you cannot adequately run an Oracle database without a skilled Database Administrator.
This is in no way a relection of your skills. I have been building and tuning OLTP and DW systems on Oracle for 16 years, and would not dream of trying to admister my own database. It's not that I couldn't get it running (I could). And it's not that I couldn't getting it running well (I reckon I could). But once I had done all that DBA work, there would be no time left to build the data warehouse. Oracle DBA work is hard, takes a lot of time, and skilled technicians are paid comensurately.
If, as you say, you have a 'conceptual knowlege of a DW', you are going to need all the time you can get working on the design and build, not worrying about the database.
If you know SQL Server, why don't you build and implement using that? Don't tell anyone I said this, but SS is a perfectly acceptable platform for most small-medium DW projects. What's more, the ETL tools come for free!
If you proceed down the Oracle track without a skilled DBA, I guarantee that you will regret it later.
_____________
Ross Leishman
|
|
|
|
Goto Forum:
Current Time: Fri Sep 06 07:40:27 CDT 2024
|