Another idea would be to simply set up a nightly job that does a copy_only backup and restores it on the dev server (or on the same server, if you only have one, but that might not be a great idea). The nice thing about this is that the restore can go to any server (or multiple servers), and can be completely decoupled from any activity on the primary database.
On server 1:
BACKUP DATABASE db TO DISK = '\\someshare\file.bak'
WITH COPY_ONLY, INIT, COMPRESSION;
On server 2:
RESTORE DATABASE db_dev FROM DISK = '\\someshare\file.bak'
WITH REPLACE, RECOVERY;
You may need to also add MOVE
commands if the disk layout between the servers is different (or if you're putting the copy on the same server).
RESTORE DATABASE db_dev FROM DISK = '\\someshare\file.bak'
WITH REPLACE, RECOVERY,
MOVE 'data_file_name' TO 'D:\somepath\somefile.mdf',
MOVE 'log_file_name' TO 'E:\somepath\somefile.ldf';
If you're restoring on the same server, you shouldn't have any issues with users. If you restore to a different server, your users will exist but the server-level logins may not. There are scripts to fix that, and a new feature in SQL Server 2012 (Contained Databases) which eliminates the problem altogether.
There are a lot of ways to do this one that go beyond what I listed, but here's a start.
1) You could try the SQL Server Migration Assistant for Oracle. It's free and usually works well for me for smaller or medium sized datasets.
http://www.microsoft.com/en-us/download/details.aspx?id=28766
2) Create a SSIS package.
3) You could create a linked server in SQL and then select * from Oracle into SQL table (messy and probably not a good idea).
http://support.microsoft.com/kb/280106
4) Export to flat files and import into SQL. Again, a ton of caveats with this one.
Best Answer
Recreate is the better option, because:
The other option is to write something that looks through the schema, identifies tables with foreign key constraints so they can be dropped, drops them, then finds the identities and reseeds them, then puts the FKs back
Or if you are a masochist, you could write (well try to) something to use the dependency order
Both of options 2 and 3 are high maintenance.