Generate schema script combine scripts of synonym with target object not working

Toad for Oracle
1

Hi there,

I am trying to generate schema scripts and it seems the synonyms are not included in the table's script.

EDIT:

Sry my post is lacking details.

I checked the combine synonyms with target objects

image

It works when I do export DDL. But I wish to automate this process and it seems generate schema scripts is my only option.

My version :
Toad for Oracle Xpert (64-bit)
17.0.353.2906

2

Hi Francis,

I just tried this and it worked ok for me.

I created this user:

create user syntest identified by syntest;
grant dba to syntest;

create table syntest.atbl as select * from dual;

create synonym syntest.atbl_syn for syntest.atbl;

create public synonym syntest_atbl for syntest.atbl;

Then logged in as syntest using Toad 17.0.

I went to Generate Schema Script and set it up like this:

image
image1053×861 68.5 KB

image
image1057×857 42.6 KB

When I ran it, this file was created:

--
-- ATBL  (Table) 
--
--   Row Count: 1
CREATE TABLE SYNTEST.ATBL
(
  DUMMY  VARCHAR2(1 BYTE)
)
TABLESPACE USERS
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           );

--
-- SYNTEST_ATBL  (Synonym) 
--
CREATE OR REPLACE PUBLIC SYNONYM SYNTEST_ATBL FOR SYNTEST.ATBL;

--
-- ATBL_SYN  (Synonym) 
--
CREATE OR REPLACE SYNONYM SYNTEST.ATBL_SYN FOR SYNTEST.ATBL;

Please provide more details so I can reproduce the problem.

Thanks.

3

Ok, now I see your update. You can do ExportDDL from automation designer. I just typed "ex" in the search box:

image
image1052×873 54.2 KB

4

Hi Jorn,

Here are my object types

image
image1048×460 37.7 KB

Output > Script

image

With File Encoding: "ANSI"

Options > Script

image

Hope these upload paths work on your end because I unfortunately cannot embed multiple images in the same post.

If not let me know and Ill make multiple posts

5

Let me see if it would work. I remember trying it on Toad 16 and generate schema script being my only option. Maybe I was mistaken or Toad 17 added it.

Will check if export DDL will do the trick for my needs and get back to you.

Thanks!

6

I just updated your profile so you can post multiple images if necessary.

I tried it again with settings as in your first screen shot and the result was the same.

If there is a bug here, I'd like to fix it, even if you can work around it. If you don't mind, please edit your prior post to add output -> Script (the others don't matter).

Are you logged in as the schema being exported or a different one?
And do your synonyms have the same name as your table or different?
And are they public or private or both?

Thanks

7

I have edited my previous post.

Here is the info you asked for:

I am logged in as my own User/Schema with SELECT_CATALOG_ROLE. Trying to export other schema's Scripts.

The synonyms are different than the table names and I'm assuming it is private as the owner is the same schema I am extracting from.

I guess it would be noteworthy to say that I am generating schema scripts for multiple schemas at the same time.

Maybe I can explain my specific need so you can point me in the right direction as I have been looking for an answer for a while now.

I am trying to Export the DDL scripts to git as a backup after a change as a sort of version control. This might not be the best solution but these scripts need to be reused after and devs do not have access to the Prod database. So this repository is their main source for DB scripts when they need to make changes.

So far, I have been able to manually use the Export DDLs but my main goal is to have this process as automated as possible.

Using the Export DDLs with the Automation Designer, one of my main problems is the Advanced object search. I cannot figure out a way to search for multiple objects at once as those objects usually change every time I do the export.

So far, I have not been able to fully figure out or find what the Regex patterns are for the Search Term text box below

image

The Filters in Generate Schema Script on the other hand, I have been able to figure out. So that is why I am trying to make it work.

So key elements are:

  • Changes happen frequently on a bi-weekly basis
  • Changes are across multiple schemas
  • My list of objects to export is ever changing
  • Ideally, I would not export the whole schema/database as there are multiple teams working on them and some schemas are not useful to the specific team I am doing this for but it could be an option.

If you have an idea or something to point me to I would be glad. But I don't expect you to move mountains for me. I know you probably have other things to do :stuck_out_tongue:

8

I've just clicked on your profile and seen your dropbox documents.

One of them seems to be useful to my case. I will try it.

9

Generate Schema Script is what you really want for your case. Export DDL is going to be much slower if there are a lot of objects involved. And with Export DDL you'd have to make a list of objects to export, which is not convenient if the list keeps changing.

I've tried every combination of synonyms (public and private, names the same, names different) that I can think of, but for me it still works.

Can you give me a small example of a table and its synonyms, along with the file it creates for that table?

You can send it to me here privately if you don't want to post it for the world to see.

Thanks

10

I've sent you a private message