Is there a way to find ddl scripts for creating users in Oracle Sql Developer

Delmonte picture Delmonte · Feb 22, 2013 · Viewed 12.9k times · Source

In Toad, it's easy to look for a ddl script that creates a user with all its grants and roles:

enter image description here

In Oracle SQL Developer, I find a similar option but it's not complete:

enter image description here

Is there a way to have same Toad's information in Oracle SQL Developer?

Answer

Alex Poole picture Alex Poole · Feb 22, 2013

It's a bit more convoluted, and might make more sense with pictures but I can't create those right now... but if you go to the 'View' menu and choose 'DBA' you get a second panel on the left (titled 'DBA', unsurprisingly).

Click the green + button and pick your connection from the drop-down list in the dialog box; it will then appear under 'Connections' in that panel. Expand that connection, then 'Security', then 'Users'. You'll see all the database users listed.

Right-click on a user and choose 'Create like...' from the contextual menu, which will give a new dialog that's very similar to the one you already found.

Enter a user name and password to make the next step make more sense, then go go the 'SQL' tab, and you'll see something similar to what Toad produced.

You can also use the DBMS_METADATA package to extract the DDL manually; that has been covered here and on DBA.SE.