4

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.

Improve this question
2
  • See if the cached version is available
    – Sathyajith Bhat
    Commented Jan 19, 2012 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
    Commented Jan 20, 2012 at 14:54

2 Answers 2

Reset to default
5

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.

Improve this answer
1
  • Agreed. This is so much easier with Data Pump (2 commands).
    – Aaron
    Commented Jan 19, 2012 at 13:33
1

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.