Monday, April 27, 2015

Change max value for Oracle Sequence

After data refresh/copy from one environment to another, usually developer realizes that they are using sequence and need to change the sequence's max value. To perform this I have developed this handy script:

This script assumes that the executor of this script has DBA (or DBA like privileges). Ability to select from DBA views and alter any sequence.

set serveroutput on

   seq_owner  varchar2(30) := upper('&1');
   seq_name   varchar2(30) := upper('&2');
   desiredVal number := &3;
   oldIncrVal number;
   diff       number;
   newDiff    number;
   sqlStmt    varchar2(4000);
   currSeqVal number;
   select INCREMENT_BY into oldIncrVal from dba_sequences where SEQUENCE_OWNER = seq_owner and SEQUENCE_NAME = seq_name;
   sqlStmt := 'select ' || seq_owner || '.' || seq_name || '.nextval from dual';
   execute immediate sqlStmt into currSeqVal;
   diff := desiredVal - currSeqVal;
   dbms_output.put_line('alter sequence ' || seq_owner || '.' || seq_name || ' increment by ' || diff);
   execute immediate 'alter sequence ' || seq_owner || '.' || seq_name || ' increment by ' || diff;
   execute immediate sqlStmt into currSeqVal;
   newDiff := desiredVal - currSeqVal;
   dbms_output.put_line('alter sequence ' || seq_owner || '.' || seq_name || ' increment by ' || oldIncrVal);
   execute immediate 'alter sequence ' || seq_owner || '.' || seq_name || ' increment by ' || oldIncrVal;

Monday, April 6, 2015

DB_Unique_Name vs. DB_Name

I have used DB_Unique_Name when I create a standby databases. This morning one of my colleague, when duplicating database using the production backup on non-production cluster,  asked me the question why does files in the ASM are being created in folder with the old database name and not with the new database name. I reviewed and found out that DB_Unique_Name parameter was left unchanged which caused this issue. This prompted me to believe that DB_Unique_Name is being used for creating the OMF file structure.  This is not documented clearly in Oracle Documentation, but I found this nice article which explains the difference between DB_Name and DB_Unique_Name.

SSRS Report Format options

SSRS reports can be exported in different format. Default text format is comma separated values. If we need different format we can do so by updating RSReportServer.config file.

On SSRS server add following entry to file D:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer\RSReportServer.config.

The entry below needs to be added to the <Render> node:

<Extension Name="PIPE" Type="Microsoft.ReportingServices.Rendering.DataRenderer.CsvReport,Microsoft.ReportingServices.DataRendering">
            <Name Language="en-US">Text (Pipe Delimited)</Name>