The DDL command in SQLcl allows you to get the same information you’d see in an object editor’s SQL panel in SQL Developer.
Like so:
![](https://www.thatjeffsmith.com/wp-content/uploads/2019/02/ddl-1024x620.png)
But a user asked me recently about doing this for an actual Oracle USER, say, ‘HR’.
Hi Jeff @thatjeffsmith,
— Balu Reddy (@BaluRed19023071) February 18, 2019
Can we generate user ddl also using sqlcl why I asked is because we mostly use to get request for creating a mirror user.
So let’s make this happen TODAY, versus waiting for a product enhancement update.
Make Your Own USER Command
One of the SQLcl commands is called ‘ALIAS.’ It allows you to take a query or script and map it to a new command in SQLcl. It also allows for positional :binds in your code.
So, I’m going to steal the SQL that SQL Developer uses when generating DDL for a schema, and I’m going to re-purpose it to a new command in SQLcl called ‘USER.’
Getting the SQL We Need
We just do ‘the work’ in SQL Developer, and then observe the ‘Statements’ panel.
![](https://www.thatjeffsmith.com/wp-content/uploads/2019/02/ddl-2-1024x696.png)
Now that we have our code identified, copy and paste it into SQLcl (although I did format it first in SQL Developer so it’d be easier to read here.
The ALIAS Command
![](https://www.thatjeffsmith.com/wp-content/uploads/2019/02/ddl-3-713x1024.png)
There’s only one :bind in the query – that’s ‘:NAME’ – so when I invoke the command, I only need one parameter – the name of the USER I want the DDL for.
Here’s the SQL you need (tested on an 18c Oracle Database)
SELECT DBMS_METADATA.GET_DDL( 'USER', :NAME ) FROM DUAL UNION ALL SELECT DBMS_METADATA.GET_GRANTED_DDL( 'ROLE_GRANT', GRANTEE ) FROM DBA_ROLE_PRIVS WHERE GRANTEE = :NAME AND ROWNUM = 1 UNION ALL SELECT DBMS_METADATA.GET_GRANTED_DDL( 'SYSTEM_GRANT', GRANTEE ) FROM DBA_SYS_PRIVS SP, SYSTEM_PRIVILEGE_MAP SPM WHERE SP.GRANTEE = :NAME AND SP.PRIVILEGE = SPM.NAME AND SPM.PROPERTY <> 1 AND ROWNUM = 1 UNION ALL SELECT DBMS_METADATA.GET_GRANTED_DDL( 'OBJECT_GRANT', GRANTEE ) FROM DBA_TAB_PRIVS WHERE GRANTEE = :NAME AND ROWNUM = 1
So when you need a command that we haven’t built yet, build it yourself 🙂
But Wait, This Won’t Run – There’s no Semicolons!
Dawn pointed out in the comments, that DBMS_METADATA leaves out statement delimiters by default. This is by design as a program running SQL directly on a driver, say JDBC, doesn’t need to include semicolons.
But, we’re generating code which will ultimately be ran ad-hoc most likely in SQLcl or some other query interface.
To get the semicolons, just put this in your login.sql or run this before running your new USER command:
BEGIN dbms_metadata.set_transform_param( dbms_metadata.session_transform, 'SQLTERMINATOR',TRUE); END; /
And now when we run the command again, we get…
SQL> SET long 1000 SQL> USER HR DBMS_METADATA.GET_DDL('USER',:NAME) -------------------------------------------------------------------------------- CREATE USER "HR" IDENTIFIED BY VALUES 'S:74A7D802F0664676487836016A9F085517CC 153D4DE1384C459CCD17DD75;T:C7067A2E84D0376E29B317DF9CFE73087D379589842FB537C46D7 406C388F735CD6ED4FC83222D9F340DBDA9A3443192C690764BFE4BB46260B9B3B5980F839B7252F 969AA89FB889EBD1115D6A8955D' DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP"; GRANT "DBA" TO "HR"; GRANT "SELECT_CATALOG_ROLE" TO "HR"; DBMS_METADATA.GET_DDL('USER',:NAME) -------------------------------------------------------------------------------- GRANT "EXECUTE_CATALOG_ROLE" TO "HR"; GRANT "ROLE_TD" TO "HR" WITH ADMIN OPTION; GRANT "ROLE_TD3" TO "HR" WITH ADMIN OPTION;
Notice also I’ve set LONG to a bigger number as this package returns the DDL as a CLOB, and SQLcl will need more characters available to display the entire script.
6 Comments
I tried it with
Oracle SQLDeveloper Command-Line (SQLcl) version: 22.2.0.0 build: 22.2.0.173.1733
with no success.
You tried what, exactly? I listed quite a few steps, in that blog post.
But you can already do a ‘create like’ in sql developer for a user.
Just use the DBA view, security, right click the user you want and away you go.
Note, object level grants aren’t replicated unfortunately
But I’m at a cmd prompt, not in a GUI…I want my cake and I want to eat it 🙂
Also, if you want the grants in SQLDev, you have to enable them in preferences under Database, Export.
Now, if only dbms_metadata subprograms could return commands that you can just copy and paste to rerun, instead of manually having to add semi-colons all over the place! Looking at your example, that still doesn’t happen automatically!
Just need to set the transformer
dbms_metadata.set_transform_param(indexTransHandle,
‘SQLTERMINATOR’, TRUE);