Rename schema in Oracle 11g with Loopback DBLink

This post is also available in: Português

In this article, we will deal a very common situation where the DBA is tasked to rename or clone a user into the same database. In the following lines, I will explain how to make this copy without higher complexities and with no need to generate intermediate dump files, using DataPump import utility with a loopback dblink.

This task can be done in 3 easy steps:

1. Create a temporary loopback dblink.
2. Run the Import DataPump to clone the user.
3. Remove the temporary dblink created in item 1.

Let's start:

1) Create a temporary loopback dblink.

In this example, we will clone the whole user SCOTT with its objects and data to a new, SCOTT_BKP. We will do this task with the user "DBADMIN", which is a DBA this database.

The first step is to create a temporary loopback dblink. Assuming that the SERVICE_NAME DB is "ORCL" and he is running on port "1521":

create database link TMP_DBLINK
using '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL= TCP) (HOST=127.0.0.1)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=ORCL)))';

You could also just use the TNS entry name, if there is one that already locally point to this DB. Assuming, for example, that there one TNS Names "ORCL":

create database link TMP_DBLINK using 'ORCL';

Note that on both cases I do omit the "connect to" clause to force connection to the database as the locally connected user (DBADMIN, in this example). Once you create it, test it by running:

SQL> select * from dual@TMP_DBLINK;

D
-
X

2) Run the Import DataPump to clone the user.

The next step is to clone the user. If you are using this tutorial to rename a user, ensure that there is no transaction, DDL or DML in its objects.

Run the PL/SQL block:

DECLARE
	H1 NUMBER;
	HSTATUS VARCHAR2(200);
	FROMUSER VARCHAR2(30);
	TOUSER VARCHAR2(30);
BEGIN
	FROMUSER := 'SCOTT';
	TOUSER := 'SCOTT_BKP';
	H1 := DBMS_DATAPUMP.OPEN(OPERATION => 'IMPORT', JOB_MODE => 'SCHEMA', REMOTE_LINK => 'TMP_DBLINK');
	DBMS_DATAPUMP.METADATA_FILTER(HANDLE => H1, NAME => 'SCHEMA_LIST', VALUE => '''' || FROMUSER || '''');
	DBMS_DATAPUMP.METADATA_REMAP(HANDLE => H1, NAME => 'REMAP_SCHEMA', OLD_VALUE => FROMUSER, VALUE => TOUSER);
	DBMS_DATAPUMP.START_JOB(HANDLE => H1);
	DBMS_DATAPUMP.WAIT_FOR_JOB(HANDLE => H1, JOB_STATE => HSTATUS);
	DBMS_OUTPUT.PUT_LINE('STATUS = ' || HSTATUS);
END;
/

The runtime of the script varies depending on the amount of objects and user data. Check whether the output was "STATUS = COMPLETED". If not, follow the steps in the last part of this article.

3) Remove the temporary dblink created in item 1.

Finally, check that all objects were duplicated with the SQL below:

select   owner,object_type,status,count(*)
from     dba_objects
where    owner in ('SCOTT','SCOTT_BKP')
group by owner,object_type,status
order by 2,1,3;

If everything is similar, remove the temporary dblink created:

drop database link TMP_DBLINK;

Okay, now you have an identical copy of your schema in your database! If you are using this tutorial to rename a user, you can now remove the old user or keep it as a backup.

Checking in case of Failures

If you have problems during the checking of objects, it is possible to enable logging of import DataPump to identify the cause of failure. To do so, you must create a temporary directory for the log:

create directory TMP_DIR as '/tmp';

Then add the following statement below the line of PL/SQL with DBMS_DATAPUMP.OPEN command:

DBMS_DATAPUMP.ADD_FILE(
	HANDLE => H1,
	FILENAME => 'imp.log',
	DIRECTORY => 'TMP_DIR',
	FILETYPE => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE
);

Finally, after correcting the error and run the command correctly, do not forget to remove the temporary directory:

drop directory TMP_DIR;

+22