Generate Schema Script with roles has table grants before table creates when generating from multiple schemas

Toad for Oracle - Beta
1

Happy Friday!

In 12.12.0.29, I’m doing a Database->Export->Generate Schema Script. In the “Source and Output” tab, I check two schemas. I then check “User’s Roles” at the top of the Object Types tab, as well as Tables. In Script Options, I have “Sort for Creation” checked. The resulting code has the roles generated, but some object GRANTs are generated before the objects are created. e.g.:

CREATE ROLE MY_ROLE NOT IDENTIFIED;
GRANT ALTER ON SCHEMA2.TABLE2 TO MY_ROLE;
CREATE TABLE SCHEMA1.TABLE1 (BLEAH1 NUMBER);
GRANT ALTER ON SCHEMA1.TABLE1 TO MY_ROLE;
CREATE TABLE SCHEMA2.TABLE2 (BLARF2 NUMBER);
GRANT ALTER ON SCHEMA2.TABLE2 TO MY_ROLE;

Both SCHEMA1 and SCHEMA2 have the MY_ROLE granted. So it seems the generator forgets that SCHEMA2 also needs objects created before granting to the role while it’s dealing with SCHEMA1 objects.

The workaround is obvious and easy…

Thanks!
Rich

2

Hi Rich,

When you are making a script for multiple schemas, Toad is handling them one at a time. (Extract DDL for a schema, write to file, repeat as needed). I can’t do any multi-schema object sorting because I only have info in memory about one schema at a time. So you’ll have to stick with the easy and obvious workaround.

-John

3

Hmmm…I thought there used to be a “thumbs-up” button on replies?

In any case, no sweat. Thanks, John!

Rich

Disclaimer: I may be obvious, but I’m not easy.