Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm looking for a way to copy an Oracle user and its data to a new one without dump file.

Google search brings a tip by Steve Karam, which seems to be exactly what I want. However the page is not accessible to me.

@edited
Thank you for your answers, Justin and Leigh.
I'm considering a simulator of an enterprize system whose database scheme is not designed for it.
In order to apply current situation and to keep the simulated result, I decided to make a cloned database user and execute the simulation on that user.
The simulator will be handled by non-IT person, who could have no knowledge about dump file, so cloning the database must be done internally without any user interaction.
That's the reason why I'm afraid of using export/import tool.

share|improve this question
    
See if the cached version is available –  Sathya Jan 19 '12 at 5:53
    
@Shoner Sul - It should be easy enough to script the export and import, though, so that a regular user can just push a button and kick it off. The Data Pump version even provides tables that report on the status of the job so the user can push a button in an app to kick off the job and then monitor the progress of that job. It's no harder to start a DataPump job than it would be to kick off a process that ran the scripts Steve Karam put together (which also, presumably, would need to be scripted since you wouldn't want a regular user to have the privileges to run those). –  Justin Cave Jan 20 '12 at 14:54
add comment

migrated from stackoverflow.com Jan 19 '12 at 10:16

This question came from our site for professional and enthusiast programmers.

2 Answers

Steve is discussing how to copy a user and that user's grants from one database to another. That wouldn't copy the objects the user owns or the data in those objects.

Why don't you want to use the export and import utilities? Whatever restrictions you have that prevent the use of export and import might prevent you from using other alternatives. You could potentially copy the data over a database link, for example, but that wouldn't generally be preferred to just using export and import. If you want to do this on an ongoing basis, Oracle provides various tools for replicating data-- Streams, materialized views, etc.

share|improve this answer
    
Agreed. This is so much easier with Data Pump (2 commands). –  BryceAtNetwork23 Jan 19 '12 at 13:33
add comment

Datapump can do this without a dump file assuming you have a database link to the system you are importing from. Oracle Enterprise Manager has a GUI for this or you can just use the commands directly. It will look something like this:

declare
  h1   NUMBER;
begin
     h1 := dbms_datapump.open (operation => 'IMPORT', job_mode => 'SCHEMA'
        , remote_link => 'OTHERDB', job_name => 'MYJOB', version => 'COMPATIBLE'); 
    dbms_datapump.set_parallel(handle => h1, degree => 1); 
    dbms_datapump.set_parameter(handle => h1, name => 'KEEP_MASTER', value => 0); 
    dbms_datapump.metadata_filter(handle => h1, name => 'SCHEMA_EXPR', value => 'IN(''SCOTT'')'); 
    dbms_datapump.set_parameter(handle => h1, name => 'INCLUDE_METADATA', value => 1); 
    dbms_datapump.set_parameter(handle => h1, name => 'ESTIMATE', value => 'BLOCKS'); 
    dbms_datapump.set_parameter(handle => h1, name => 'TABLE_EXISTS_ACTION', value => 'SKIP'); 
    dbms_datapump.set_parameter(handle => h1, name => 'SKIP_UNUSABLE_INDEXES', value => 0); 
    dbms_datapump.start_job(handle => h1, skip_current => 0, abort_step => 0); 
    dbms_datapump.detach(handle => h1); 
end;
/

Update: Based on your update you could embed this code in a database job and run it once a day/week/etc.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.