Database compare object set

Toad for Oracle & DevOps Toolkit
1

Hello all,

Is there an easy way to load object sets into the compare. For example I want to compare dev, stage and prod.
I know there are differents between the envs but I also dont care about oracle stuff so I am using the following

select null owner, 'ROLE' object_type, role object_name, null status, null last_ddl_time, null info
from dba_roles
where 1=1
and oracle_maintained='N'
UNION ALL
select null owner, 'USER' object_type, username object_name, null status, null last_ddl_time, 'Created on: ' || to_char(created) info
from sys.DBA_USERS
where 1=1
and username NOT IN
(
'XDB', 'SYSTEM', 'SYS', 'LBACSYS',
'DVSYS', 'DVF', 'SYSMAN_RO', 'SYSMAN_BIPLATFORM',
'SYSMAN_MDS', 'SYSMAN_OPSS', 'SYSMAN_STB', 'PUBLIC',
'DBSNMP', 'SYSMAN', 'APEX_040200', 'WMSYS',
'SYSDG', 'SYSBACKUP', 'SPATIAL_WFS_ADMIN_USR',
'SPATIAL_CSW_ADMIN_US',
'SI_INFORMTN_SCHEMA', 'OUTLN', 'ORDSYS', 'ORDDATA',
'OJVMSYS', 'ORACLE_OCM', 'MDSYS', 'ORDPLUGINS',
'GSMADMIN_INTERNAL', 'FLOWS_FILES', 'DIP', 'CTXSYS',
'AUDSYS', 'APPQOSSYS', 'APEX_PUBLIC_USER', 'ANONYMOUS',
'SPATIAL_CSW_ADMIN_USR', 'SYSKM', 'SYSMAN_TYPES',
'MGMT_VIEW', 'EUS_ENGINE_USER', 'GSMCATUSER', 'OLAPSYS',
'CLOUD_SWLIB_USER', 'GSMUSER', 'MDDATA', 'XS$NULL', 'CLOUD_ENGINE_USER' )
and oracle_maintained='N'
order by 1,2,3

but i noticed its only querying the source out the gate and missed roles and users in the target that wasnt in the object set.

I 100% could be doing something wrong, just discovered how useful the object set is.
I did try modifing the DBOS by hand but didnt seem to help because looks like it still filters out via query

Im using
Toad for Oracle Xpert (64-bit) (Multiple Keys Active)
Add-Ons: DB Admin Module
17.0.341.1977
Thanks
Dave

2

My first thought is 'We really ought to have a "ignore oracle maintained" option in there.' but I see that we don't. It's now on my to-do list.

Oh are you saying that when you click here, you only see objects in source but not target added to the list? I guess that's a bug.

image
image736×399 16.6 KB

Yeah, you could either save/modify that DBOS file, or just tell schema compare to ignore objects only in target DB by unchecking them in the results. Although, I guess you don't want to ignore all of them or you wouldn't be asking.

image

You could:

  • Create a DBOS file.
  • Modify it
  • Use the "Load from file" button in DB Compare to load it.
3

cool that works thanks for the fast response as always.
I had tried modifying the DBOS but it wasnt populating the info but toad must have just been angry at the time because it worked after a close and reopen of the compare