How to Clone User in Oracle

How to clone user in Oracle

Briefly (from here)

select dbms_metadata.get_ddl('USER', '...') FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','...') FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','...') FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','...') FROM DUAL;
SELECT DBMS_METADATA.GET_granted_DDL('TABLESPACE_QUOTA', '...') FROM dual;

Then just replace the username with the new one you want to create.

Does SQLDeveloper have a tool to copy/clone users like Toad?

In version 3 there is a DBA view. It can be displayed or hidden using the view menu.
Once you have it displayed open the connection in the DBA section.
Drill down to Security->Users and right click on the user you want to copy.
The option you are looking for is create like.

How to clone details of one user to another | Oracle |

Insert + not exists might be one option:

insert into employee (name, age, access, url)
select 'Bob', b.age, b.access, b.url            --> new user
from employee b
where b.name = 'Raj'                            --> existing user
and not exists (select null
                from employee c
                where c.name = 'Bob'            --> new user
                  and c.age = b.age
                  and c.access = b.access
                  and c.url = b.url);

Parametrize it, if you want (i.e. pass old/new names as parameters).

---

BTW, access is a reserved word and can't be used as a column name:

SQL> create table test (access number);
create table test (access number)
                   *
ERROR at line 1:
ORA-00904: : invalid identifier

SQL>

Copy all table data and table structures from one user into an another user

Are using deferred segment creation?

Export will not work with tables that don't have a segment. You'll probably want to use Export DataPump instead.

Or you may be able to get it to work by manually creating a segment:

alter table your_table_name allocate extent;

How to assign all the privileges of existing user to new user in Oracle 11g?

In connection to @Littlefoot answer.

you can copy the user privileges from one of the tables in the answer here How to find the privileges and roles granted to a user in Oracle?
for the first user and copy them into a script that will grant the second user the desired privileges. its a bit of a workaround but it should work just fine

Create a user with all privileges in Oracle

There are 2 differences:

2 methods creating a user and granting some privileges to him

create user userName identified by password;
grant connect to userName;

and

grant connect to userName identified by password;

do exactly the same. It creates a user and grants him the connect role.

different outcome

resource is a role in oracle, which gives you the right to create objects (tables, procedures, some more but no views!). ALL PRIVILEGES grants a lot more of system privileges.

To grant a user all privileges run you first snippet or

grant all privileges to userName identified by password;

---

Related Topics