Monthly Archives: August 2013

How to choose a name for Linked Server object in SQL Server

At first glance, there is nothing to talk about. Linked server object name defined as a sysname datatype and it means that it is an Unicode string with no more than 128 characters. Hopefully, you won’t use even half of the maximum length. Shorter is better, but there are few tips that might be helpful:

  1. Don’t use generic names like ‘LINKEDSERVER’ or ‘APP’. The name has to give a basic idea where this linked server pointing to without going into details. Not every user (even developers) can see options of linked server objects and they’ll start bugging you or other DBAs with questions.
  2. Don’t use common words that can be found anywhere in the code. Sooner or later you’ll need to find all references to this linked server in a database and it will be painful to look for all occurrences of the same string, especially if it’s a part of the tables’ or columns’ names.
  3. Don’t use an instance name as a linked server name. When you start moving databases around you will quickly find out that the instance name is not relevant anymore. Even moving a copy of databases from production to development or test environments will become problematic.
  4. Create a new linked server for every new application even if an existing linked server object can serve the purpose. In many cases you will find that permissions required for one application are not exactly the same as for another one. As the result one application or another will have higher level of access that it’s required. Troubleshooting on another side of the database link also will be easier if you have separate users associated with different applications.
  5. Ideally a name should identify an application name of the database this linked server pointing to and a name of the application it’s going to be used by. In this case everybody will see where they are going to and which application it should be used in.
  6. Stick to a naming convention in all instances of SQL Server. So, if you use ‘DYNSAGE’ linked server everybody would now that it is used ‘from’ Microsoft Dynamics ‘to’ Sage ERP and not other way around.

If you have other tips please leave them in the comments section. Thanks!

Move a subset of public synonyms from one Oracle database to another

Today I had a task to move some public synonyms from a production database to one of the development instance. The keyword here is ‘some’.

The source database has thousands of objects and the destination database just a subset of several hundreds. Public synonyms has to be created for existing objects only, but not for all of them and just for the ones that exist in production. If I had DBLINK between databases that would be a matter of joining DBA_SYNONYMS from the source database to the DBA_OBJECTS of the destination. However, it wasn’t possible because of firewalls and many other reasons.

So, what I did is pretty straightforward and simple:

1. Create ALL synonyms in the development database from the production. The trick here is to eliminate all of the Oracle default users. This list depends on options you have installed in the database.

SELECT 'create public synonym '|| s.synonym_name ||' for '
|| s.table_owner || '.' || s.synonym_name || ';'
FROM dba_synonyms s
WHERE owner='PUBLIC'
AND table_owner NOT IN ('SYS','SYSTEM','SYSMAN','OUTLN','XDB','ANONYMOUS',
'ORACLE_OCM','FLOWS_FILES','DBSNMP','SI_INFORMTN_SCHEMA','APEX_030200','APPQOSSYS',
'OLAPSYS','CTXSYS','DMSYS','DSSYS','EXFSYS','ORDSYS','MDSYS','WMSYS','TSMSYS')
ORDER BY table_owner;

2.  Run the result from the previous step on the development database. It will create all synonyms, but as the result you will be getting “ORA-01775: looping chain of synonyms” for non-existent objects.

3. Now we have to drop some synonyms for non-existent objects on the development side. To get this list we need to execute something like this:

SELECT 'drop public synonym ' || synonym_name || ';'
FROM dba_synonyms s
WHERE s.owner='PUBLIC' AND NOT EXISTS 
(select 1 from dba_objects o 
 where s.table_owner=o.owner and s.table_name=o.object_name);

4. Run the result from the step 3 in the development. It will drop some of the synonyms created previously.

Now we have subset of synonyms from the production database for the objects that are exists in the development.

If you have a better way of doing it without a database link please let me know in the comments.

Oracle 12c Certification Available in Beta

So, I decided to pursue Oracle 12c OCP certification. Being a long time OCP it’s much easier for me to maintain the status than getting a new one. I’ve done it 3 times already upgrading from OCP DBA 8i all the way up to 11g. There is an upgrade exam 1Z0-060 available from Oracle and currently it’s in beta which means it’s cheap. Current price is around $50 and it will be available till October 5, 2013. Will see how it goes and if I have enough time to pass it before deadline. I will be posting my thoughts on new features and issues I find in the new version of Oracle’s most popular product.

From the Oracle 12c launch event I visited last week and product overviews around the Web, I would highlight these top 10 features as most exciting and valuable:

  1. Multitenant Container Database and Pluggable Databases
  2. Flexible ASM and flexible clusters
  3. Oracle Data Redaction
  4. Heat Map and Automatic Data Optimization (ILM – Information Lifecycle Management)
  5. Online Datafile Move
  6. Privilege Analysis (yeah!)
  7. Real-Time and Compare Period ADDM
  8. Top-N queries
  9. Temporary Undo
  10. Application Continuity

There are also many other enhancement about Oracle Database 21c we should be excited about. Well, at least I’m excited and will see if I can cover it in next 2 months.