Generating DDL script for object without schema name baked in using DBMS_METADATA.GET_DDL?

Question

How can I generate the DDL script for my object with DBMS_METADATA.GET_DDL without the schema name baked in?

With DBMS_METADATA.GET_DDL:

CREATE TABLE "MYSCHEMA"."MYTABLE"
(
  "COL1"  NUMBER(10,0)
)

SQL Developer can do that, and I think it's also uses the DBMS_METADATA to achive this goal and generale DDL scripts.

With SQL Developer:

CREATE TABLE "MYTABLE"
(
  "COL1"  NUMBER(10,0)
)

Here's one example... http://www.myoraclesupports.com/content/how-get-ddl-statement-using-dbmsmetadatagetddl-object-without-having-object-owner-ddl — Rajesh Chamarthi, Jul 21 '11 at 20:45
The example link no longer works, here is [an archived version](https://web.archive.org/web/20101022053403/http://www.myoraclesupports.com/content/how-get-ddl-statement-using-dbmsmetadatagetddl-object-without-having-object-owner-ddl) — vadipp, Jul 25 '18 at 14:50

score 22 · Answer 1 · answered Jul 30 '15 at 13:48

22

I recently stumbled upon the following which allows you to get ddl without the schema name.

It looks a lot simpler than any other way I have seen so far although its not included in any Oracle documentation. I spotted it in the Statements Log in SQL Developer, which generates ddl without the schema name.

DBMS_METADATA.SET_TRANSFORM_PARAM(dbms_metadata.SESSION_TRANSFORM, 'EMIT_SCHEMA', false);

You don't need to get handles or anything nasty just EXEC the above before calling DBMS_METADATA.GET_DDL

answered Jul 30 '15 at 13:48

David Norris-Hill

291
7
13

Nice find, easier / more straightforward than my solution! – Frank Schmitt Sep 20 '21 at 08:33
This worked for me but the accepted answer did not. (I am using oracle 19.) – justadeveloper Sep 20 '21 at 09:41

score 8 · Accepted Answer · answered Jul 21 '11 at 20:44

8

Use SET_REMAP_PARAM with the REMAP_SCHEMA option:

DBMS_METADATA.SET_REMAP_PARAM(th,'REMAP_SCHEMA','HR',NULL);

This will map the HR schema to NULL (you'll need a job handle, though); for a full example, see metadata_api documentation

answered Jul 21 '11 at 20:44

Frank Schmitt

30,195
12
73
107

This answer did not work for me. The answer with EMIT_SCHEMA worked though. – justadeveloper Sep 20 '21 at 13:11

score 1 · Answer 3 · answered May 04 '20 at 21:10

In addition to removing schema(As suggested by David Norris above), if you want to remove storage and other attributes. Basically, just a plain DDL, then use below :

BEGIN
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', TRUE);
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', TRUE);
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SEGMENT_ATTRIBUTES', FALSE);
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'STORAGE', FALSE);      
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'EMIT_SCHEMA', FALSE);
END;

Generating DDL script for object without schema name baked in using DBMS_METADATA.GET_DDL?

3 Answers3

Linked

Related