Skip to Main Content
  • Questions
  • sql developer v4 how to extract schema - user creation ddl ?

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: November 24, 2015 - 7:35 am UTC

Last updated: March 07, 2024 - 6:38 am UTC

Version: 4

Viewed 10K+ times! This question is

You Asked

Hello,
I am just lost here. I have a very simple requirement using sql developer.

I need to extract user creation script for a list of users.
How on earth do I do this please help .

Why have they made so simple things so difficult to do or even find.

I click on other users there is no export or extract script ?

There is Database export in Tools which will create objects within the schema but not the schema creation script.

Thanks

and Chris said...

If you go to the DBA panel (View -> DBA if you can't see it), then you can find something similar by expanding your connection and going to:

Security -> Users

Then right click the appropriate user and select "Create Like..."

You'll need to enter a username and password. It has all the other command you need to re-create the user.

Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

I love Oracle but Hate Sql developer

A reader, November 26, 2015 - 6:30 am UTC

I tried your suggestion, it didnt work.
I am looking for user creation script generation for a list of selective users.

Create like does not give me any script option.

Is this simple task impossible in sql developer ?

I am really shocked ?

I know it should be there somewhere, need to find it.

I hope the makers of sql developer are listening. Why they make such a simple Task so difficult apart from my ignorance of this user-not-at-all-friendly tool?


Any other suggestions ?
Connor McDonald
November 26, 2015 - 4:59 pm UTC

What exactly is it you want to be in the scripts? The users and their permissions, the DDL for all the objects in the schema or something else?

This might be close.

Paul, November 26, 2015 - 6:57 pm UTC

But here is what I would do.

1-Create a connection with correct privileges to access the DBA features. If you don't have that it's not the tools fault.
2-Open the DBA View (View->DBA)
3-Open the connection created in step 1
4-Open Security, users
5-Right-click user to script.
6-Select Create Like and provide the username and password and select the options you need (Like Object Privileges)
7-Select the SQL tab.

This will have something like

-- USER SQL
CREATE USER newUSER IDENTIFIED BY Welcome1 
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP";

-- QUOTAS
ALTER USER newUSER QUOTA UNLIMITED ON USERS;

-- ROLES
GRANT "DBA" TO newUSER ;
GRANT "JAVAIDPRIV" TO newUSER ;
GRANT "CONNECT" TO newUSER ;
GRANT "JAVASYSPRIV" TO newUSER ;
GRANT "RESOURCE" TO newUSER ;
ALTER USER newUSER DEFAULT ROLE "CONNECT","RESOURCE";

-- SYSTEM PRIVILEGES
GRANT UNLIMITED TABLESPACE TO newUSER ;

-- OBJECT PRIVILEGES


Hope that helps.


Big Limitation

A reader, November 26, 2015 - 9:57 pm UTC

Thanks for all the inputs.

My requirement is so simple and so basic.

As a dba I want to extract just the user creation and grants for a list of users.

I don't want to go thru 100 users and manually do create like.

This is what is frustrating about this tool sql developer.

I am hoping they fix this,
They should learn from Toad, nothing wrong in getting ideas from better tools, see how SQL server guys always learn from Oracle and quickly implement oracles good features into SQL server.

Sorry to say how can they be so dumb and oracle is all out supporting this weak tool.

Database export in tools does not give option of users but it has all other object options.

Dba module does not give option to select multiple users and generate create script.

Please look at toad, unfortunately in my restricted env I don't have toad so have to live with this really good for nothing sql developer.

Again I hope the SQL developers are listening. I am sure I am not the first person trying to do this basic thing from SQL developer.

Oh can someone please show me the light. I know I am ignorant but this tools really sucks.

How can they miss something so basic ?

Chris Saxon
November 27, 2015 - 2:26 am UTC

Well lets not forget the name of the tool, SQL *Developer*. First and foremost, it is a tool about providing facilities for developers. That there are some features for DBA's is a bonus rather than the guarantee :-)

In the time you typed your review, you could have searched our site instead and come up with this piece of code:

select dbms_metadata.get_ddl ('USER', username) 
from dba_users d
UNION ALL
select (case 
        when ((select count(*)
               from   dba_ts_quotas
               where  username = d.username) > 0)
        then  dbms_metadata.get_granted_ddl( 'TABLESPACE_QUOTA', username) 
        else  to_clob ('   -- Note: No TS Quotas found!')
        end ) from dba_users d
UNION ALL
select (case 
        when ((select count(*)
               from   dba_role_privs
               where  grantee = d.username) > 0)
        then  dbms_metadata.get_granted_ddl ('ROLE_GRANT', username) 
        else  to_clob ('   -- Note: No granted Roles found!')
        end ) from dba_users d
UNION ALL
select (case 
        when ((select count(*)
               from   dba_sys_privs
               where  grantee = d.username) > 0)
        then  dbms_metadata.get_granted_ddl ('SYSTEM_GRANT', username) 
        else  to_clob ('   -- Note: No System Privileges found!')
        end ) from dba_users d
UNION ALL
select (case 
        when ((select count(*)
               from   dba_tab_privs
               where  grantee = d.username) > 0)
        then  dbms_metadata.get_granted_ddl ('OBJECT_GRANT', username) 
        else  to_clob ('   -- Note: No Object Privileges found!')
        end ) from dba_users d


Save that as a script, run it in SQL Developer (or Toad or SQLPlus or whatever) and there you go ....


So script it !!!

A reader, November 27, 2015 - 6:20 am UTC

Thanks for the script.

I guess that's the only way to overcome this limitation.

It's annoyingly funny sql developer can't do this while it has put a lot more advanced functionalities , they missed on the very basics. So dumb !!!

I even sent an email to the SQL developer product manager Jeff. Perhaps they may overcome this basic requirement in the next release. There is always room for improvement for any software.

My humble advise to them look at Toad features and learn from (sql developer) their mistakes.

Thanks a lot for the scripts. If you happen to meet them do convey this issue. I am a big fan of Oracle , so can't stand something bad about oracle or oracle's tools.


how to generate a user script

Lorenzo Cruz, March 06, 2024 - 5:41 pm UTC

Question: Does someone know if this deficiency has been resolved yet?
I need to get a script from my users.
I don't want to use Toad for this, but if there is no other option, I will have to.

Connor McDonald
March 07, 2024 - 6:38 am UTC

I'm lost - why not just create a Report in SQL Dev with the SQL provided earlier.

Then you just click on the report and there's your DDL.

How is that so different from a dedicated button?