Why is CREATE table missing from DDL export script in SQL Developer?

Question

I'm trying to retrieve the CREATE table statement for multiple tables from oracle SQL Developer so I can run it in SQL Management to create new tables.

However, when highlighting multiple tables and right clicking > Quick DLL> Save to File, my file looks like this:

GRANT INSERT ON "OPSR"."BOOTH" TO "OPSWEB";
  GRANT UPDATE ON "OPSR"."BOOTH" TO "OPSWEB";
  GRANT SELECT ON "OPSR"."BOOTH" TO "OPSWEB";
GRANT DELETE ON "OPSR"."CAAR_BOOTH" TO "OPSWEB";
  GRANT INSERT ON "OPSR"."CAAR_BOOTH" TO "OPSWEB";
  GRANT SELECT ON "OPSR"."CAAR_BOOTH" TO "OPSWEB";
  GRANT UPDATE ON "OPSR"."CAAR_BOOTH" TO "OPSWEB";

Why is there no CREATE table statements in here?

Are you connected as "OPSWEB", and looking at tables under "Other users->OPSR"? You can't see the DDL for other user's tables; you'd see the same if you clicked the table and looked at the SQL tab. You need to connect as the owner and export as them. — Alex Poole
– Alex Poole, Commented Feb 9, 2023 at 19:03
@AlexPoole Yes, I'm connected as Opsweb and the only tables I can see are under the OPSR user. — jj243
– jj243, Commented Feb 9, 2023 at 20:09

Alex Poole · Accepted Answer · 2023-02-09 20:32:26Z

I'm connected as Opsweb and the only tables I can see are under the OPSR user.

You can't see the create DDL for other user's objects. SQL Developer is using dbms_metadata in the background, and from the documentation:

The object views of the Oracle metadata model implement security as follows:

Nonprivileged users can see the metadata of only their own objects.

Nonprivileged users can also retrieve public synonyms, system privileges granted to them, and object privileges granted to them or by them to others. This also includes privileges granted to PUBLIC.

If callers request objects they are not privileged to retrieve, no exception is raised; the object is simply not retrieved.

If nonprivileged users are granted some form of access to an object in someone else's schema, they will be able to retrieve the grant specification through the Metadata API, but not the object's actual metadata.

and so on. As the last bullet above says, you cen get the grants - which is what you are seeing now - but not the actual metadata.

If your user was granted the select_catalog_role you would be able to get the DDL for OPSR's objects, but you'd have to ask your DBA for that and it would probably be easier to connect as that user, or ask someone else who can to do that to perform the extract for you.

Collectives™ on Stack Overflow

Why is CREATE table missing from DDL export script in SQL Developer?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related