Home » SQL & PL/SQL » SQL & PL/SQL » Guidelines (Oracle EE 10g, windows)
Guidelines [message #318024] Mon, 05 May 2008 06:37 Go to next message
founderbamboo
Messages: 2
Registered: May 2008
Junior Member
Hi everbody!

In SQL Server i do this:
1) export some tables from database1
2) create a new database 'database2'
3) i import the exported tables in database2
4) i run a query like:

select * from database1.dbo.table
except
select * from database2.dbo.table

(this to check if there are some things that don't match, this is a fictive example, i do know that when i follow these steps, there will be no differences between the 2 tables)

Now my question is, how do i do that in oracle?
Do i need to create a new database or can i do the same thing with a different SCHEMA or what?
Re: Guidelines [message #318032 is a reply to message #318024] Mon, 05 May 2008 06:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The same way but using Oracle tools (exp/imp) and SQL syntax.

Regards
Michel
Re: Guidelines [message #318033 is a reply to message #318024] Mon, 05 May 2008 06:53 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Basically, it is the same job, just change MS SQL Server terminology to Oracle's one. In another words, it isn't a "database" but a "schema".

1. export some tables from schema1
2. create a new USER (using the CREATE USER command)
3. import exported tables into a new user's schema
4. grant privileges (SELECT might be enough) in order to be able to view data stored in another user's schema
5. write your queries and compare records
Re: Guidelines [message #318047 is a reply to message #318033] Mon, 05 May 2008 07:45 Go to previous messageGo to next message
founderbamboo
Messages: 2
Registered: May 2008
Junior Member
Littlefoot wrote on Mon, 05 May 2008 06:53
Basically, it is the same job, just change MS SQL Server terminology to Oracle's one. In another words, it isn't a "database" but a "schema".

1. export some tables from schema1
2. create a new USER (using the CREATE USER command)
3. import exported tables into a new user's schema
4. grant privileges (SELECT might be enough) in order to be able to view data stored in another user's schema
5. write your queries and compare records


how can i import the exported tables into a new user's schema in OEM? When i do it through the import from export files option,
the source schema in the re-map table is empty
Re: Guidelines [message #318049 is a reply to message #318047] Mon, 05 May 2008 07:48 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I wouldn't know. I prefer command line Export and Import utilities.
Re: Guidelines [message #318286 is a reply to message #318024] Tue, 06 May 2008 06:33 Go to previous message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
I may not be reading this question correctly, but if you just want to compare the DATA between two tables, then you can use the set operators such as MINUS.

And if you just want to copy the data of a table you can do things like create table newtable as select * from oldtable.
Previous Topic: Utl_file
Next Topic: which OS user has dropped the objects
Goto Forum:
  


Current Time: Thu Dec 08 16:20:57 CST 2016

Total time taken to generate the page: 0.08101 seconds