Systems Engineering and RDBMS

Archive for January, 2008

Oracle 11g: WHEN OTHERS THEN NULL

Posted by decipherinfosys on January 31, 2008

As most of you know, we can enable compile time warnings to make programs more robust and to avoid run time error(s) later on.  These warnings do not prevent the routines from compiling but it gives compile time warnings.  This is just to alert an end user that there could be a potential problem. We need to use ‘PLSQL_WARNINGS’  initialization parameter to enable the warnings. It can be altered at system level or session level.

In 11g, Oracle introduced a new warning message (PLW-06009) to warn against the most dangerous and the most ignored PL/SQL construct:

WHEN OTHERS THEN
NULL;

This construct suppresses the most serious error, pretending like no error has occurred and the program finishes successfully.  By enabling the compile time warning, all the sub-programs which use this construct get a compile time warning. As mentioned earlier, subprogram will be compiled successfully but it will display warning that “OTHERS” exception does not end in “RAISE” error. Let us first enable the compile time warning and then create a test procedure to demonstrate this.

SQL> ALTER SESSION SET PLSQL_WARNINGS=’ENABLE:ALL’;

Session altered.

Now let us create the following procedure.

CREATE OR REPLACE PROCEDURE TEST_PROC
AS
v_col2 VARCHAR(10);
BEGIN

SELECT col2
INTO v_col2
FROM test_compute
WHERE col1 = ‘ORACLE’;

EXCEPTION
WHEN OTHERS THEN
NULL;

END;
/

SP2-0804: Procedure created with compilation warnings

As shown, it will be created with compilation warning. Let us see what warning it gives.

SQL> show errors
Errors for PROCEDURE TEST_PROC:

LINE/COL ERROR
——– ————————————————————
12/9     PLW-06009: procedure “TEST_PROC” OTHERS handler does not end in
RAISE or RAISE_APPLICATION_ERROR

This warning indicates that procedure has WHEN OTHERS exception block which does not use RAISE or RAISE_APPLICATION_ERROR clause and there could be potential problem at run time.

This warning is displayed only when PLSQL_WARNINGS is enabled. If it is disabled, then procedure will be compiled in normal way without giving any errors. Disable the warning by issuing following statement.

SQL> ALTER SESSION SET PLSQL_WARNINGS=’DISABLE:ALL’;

Re-create the stored procedure shown above and it will compile without any problem. Introducing warning for WHEN OTHERS is a good warning measure but it is ultimately the DBA/database developer who has to avoid such coding practices.

Posted in Oracle | 1 Comment »

Read Only Accounts in Oracle

Posted by decipherinfosys on January 30, 2008

While working on a project recently, a question came up about read only accounts.  As you might already be aware of, a user that has been granted only select access can still execute a “select for update” or even a “Lock Table” command to place locks on the table.  It comes as a surprise to many (me included) when first faced with such a scenario.  One would have thought that there would be enough granular level of privileges to make “select for update” and “lock table” different than just select privileges but that is not the case (haven’t tested in 11g yet).

This is true for not only direct user sessions but is also true for database links.  It is pretty easy to test this out:

SQL> create table lock_test(col1 varchar2(10));

Table created.

SQL> insert into lock_test values (‘ABCD’);
SQL> commit;

SQL>
SQL> grant create session to testuser identified by testuser;

Grant succeeded.

SQL>
SQL> grant select on lock_test to testuser;

Grant succeeded.

Now, let’s connect using this user and issue the select, select for update and Lock Table commands:

SQL> connect testuser/testuser@ani
Connected.
SQL>
SQL> select * from lock_test for update;

col1
———-
ABCD

SQL>
SQL> lock table lock_test in exclusive mode;

Table(s) Locked.

SQL>

So, as you can see from above, using the “read only”user account, we were able to lock the table by using both the “select for update” as well as the “Lock Table” commands.

This is a potential security issue.  A would be hacker or a disgruntled employee, using a read only account, could easily wreak havoc on a database system by running a “select for update” statement on vital tables.  For 24×7 OLTP systems, this could mean serious down time and loss of $$$.  It would be up to the DBA’s responsible for the database to track down and kill the session or sessions that are running the select for update.  By the time the DBA’s get things under control, a lot of damage could have been done.  In less malicious situations, an innocent user may actually execute such a statement and cause down time unknowingly.

There are ways (should rather say workarounds) to make this user account a real “read only” user account.  One way to work around this issue is to create a view to mask the table and grant the select privilege directly on the view itself rather than the table.  In addition, you would also need to modify the view such that a select for update cannot be done on it.  You can choose to create a view like this:

create view vw_lock_test as select distinct col1 from lock_test;

And then when you try to do a select for update against it, you will get this error:

ERROR at line 1:
ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc.

Another way to prevent the “select for update” issue is by having a log on trigger and within that trigger modifying the session to have “set transaction read only”.  That way, there will be no need to create the view with a DISTINCT clause since that is a bad option as well – forces sort operations un-necessarily.

So, the “select for update” issue is taken care of by this “workaround”.  What about the LOCK TABLE issue?  We can disable the table locks on that table:

SQL> Alter table lock_test disable table lock;

And now when the read only user account tries to issue a LOCK TABLE command, he will get this error:

ERROR at line 1:
ORA-00069: cannot acquire lock — table locks disabled for T

Be aware though that before doing any DDL or truncate commands on the tables with disabled locks, you would need to enable table lock on such tables which is a good thing in production environments anyways since it prevents any accidental drops/truncates of the tables.

Posted in Oracle | Leave a Comment »

SQL Server 2008 Roadmap

Posted by decipherinfosys on January 29, 2008

Seems like the RTM version of SQL Server 2008 will now be released in Q3 instead of at the end of Q1 as was originally stated by MSFT. You can read more on the blog post here:

http://blogs.technet.com/dataplatforminsider/archive/2008/01/25/microsoft-sql-server-2008-roadmap-clarification.aspx

Posted in SQL Server | Leave a Comment »

SQL Server 2005 PerfStats Scripts

Posted by decipherinfosys on January 29, 2008

If you have used the MSFT PSS group’s blocker script from version 2000, you would really love these sets of scripts that they have released for version 2005. These are a collection of T-SQL scripts that use the rich functionality of DMVs and DMFs that is present in SQL Server 2005. The scripts are very useful for troubleshooting performance issues, blocking issues, missing indexes issues etc. and we would recommend this to everyone. Just by reading the scripts themselves you will also get an understanding of how the dynamic management views and functions relate to each other and what those different columns are for.

Here is the post from the PSS SQL Server Engineers from where you can download these scripts and use them. You can then even create your own SSRS reports atop those scripts to help you create your own dashboard for troubleshooting issues.

http://blogs.msdn.com/psssql/archive/2007/02/21/sql-server-2005-performance-statistics-script.aspx

Posted in SQL Server | Leave a Comment »

Password Management Enhancements in Oracle 11g

Posted by decipherinfosys on January 28, 2008

Starting with Oracle 11g, passwords have become case sensitive, provided  you choose to upgrade to “new security standards” during the database creation. Before 11g, database passwords were case insensitive. One can connect to user using password in upper case, lower case or mixed case no matter how it is created. But starting 11g, it is not the same. We already have a user decipher defined in our database. We will alter it to start with.

SQL> alter user decipher identified by DECIPHER;

User altered.

Now let us try to connect to user using lower case password.

SQL> connect decipher/decipher@orcl
ERROR:
ORA-01017: invalid username/password; logon denied

Now if we try to connect to user using lower case password, connection will be successful.

SQL> connect decipher/DECIPHER@orcl
Connected.

There is an option to change behavior to make it case insensitive. There is a system parameter which can be set to true or false. Setting it to false, will change behavior to case insensitive. We need to connect as sysdba to alter the value of the parameter.

SQL> ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON=FALSE;

System altered.

Now we can try to connect to user using any combination of the case.

SQL> conn decipher/DECipher@orcl
Connected.

There is also new data dictionary view DBA_USERS_WITH_DEFPWD which indicates which users have default passwords. Prior to 11g, there was no straight forward way to check the users with default passwords.   Querying this view, we can know what users have default passwords which could be a security threat.

SQL> SELECT username FROM dba_users_with_defpwd;

USERNAME
——————————
DIP
MDSYS
WK_TEST
CTXSYS
OUTLN
EXFSYS
SCOTT
MDDATA
ORDPLUGINS
ORDSYS
XDB

Once we change the user’s default password to a non-default password, it will no longer display in this view.

Posted in Oracle | Leave a Comment »

Extracting data into Excel or text file from SQL Server

Posted by decipherinfosys on January 24, 2008

There are many ways of extracting the data from a SQL Server table to Excel or a text file. Here are the options that you can chose from:

  1. Using DTS (SQL Server 2000) or SSIS (SQL Server 2005) – either through the Export/Import Wizard or through a custom package.
  2. BCP: Using BCP, you can extract the data out and dump it to an excel spreadsheet or a text file.
  3. Custom code written using SQL-DMO (SQL Server Distributed Management Objects) in SQL Server 2000 or SQL-SMO (SQL Server Management Objects) in SQL Server 2005.
  4. Directly through SQL Server Management Studio (Save Results AS).
  5. T-SQL:

a) Using a Linked Server.
b) Using the OPENROWSET() function.

In this post, we will take a look at #5 from above. Let’s create a linked server first (and it’s login information):

EXEC sp_addlinkedserver EXCEL_LS,
‘Jet 4.0’,
‘Microsoft.Jet.OLEDB.4.0’,
‘c:\Login_Data.xls’,
NULL,
‘Excel 8.0’
GO

EXEC sp_addlinkedsrvlogin
EXCEL_LS, /** remote server name **/
false, /** True or False **/
NULL, /** local login Name **/
NULL, /** Remote userName **/
NULL /** Remote password **/
GO

This creates the linked server and the login. The caveat to this is that the excel spreadsheet Login_Data.xls should already be present at the drive that is listed and should already have the columns in it that you intend to insert into it. So, in this case, we created the Login_Data.xls spreadsheet with two columns in it on the first tab (Sheet1): LoginName and CreateDate (with the right format). And now, let’s insert the data into it using the two options mentioned in point #5:

a) Using the linked server:

insert into EXCEL_LS…[Sheet1$] SELECT loginname, createdate from master.dbo.syslogins

(16 row(s) affected)

b) Using the OPENROWSET() function:

INSERT INTO OPENROWSET(‘Microsoft.Jet.OLEDB.4.0’, ‘Excel 8.0; Database=C:\Login_Data.xls;’, ‘SELECT loginname, createdate FROM [Sheet1$]’)
SELECT loginname, createdate from master.dbo.syslogins

(16 row(s) affected)

So, this is yet another way to extract the data. My personal preference is still bcp and #3 (Custom Code using SQL_DMO and SQL-SMO) but it is always good to know the different options that one has.

Posted in SQL Server | Leave a Comment »

Virtual Columns in Oracle 11g

Posted by decipherinfosys on January 23, 2008

Virtual columns is a new feature in Oracle 11g. This is something that is already available in SQL Server and DB2 LUW albeit with a different name. You can read more about computed columns (virtual columns) in one of our previous blog post here. In Oracle 10g and prior releases, the only way we can achieve computed column functionality is via a trigger. In the latest release, Oracle introduced virtual columns for which values are calculated at run time. Syntax is more like DB2 syntax. Let’s follow that up using an example:

CREATE TABLE TEST_COMPUTE
(
COL1 VARCHAR2(15),
COL2 GENERATED ALWAYS AS (REVERSE(COL1)) VIRTUAL
);

In the above table, we are creating a virtual column with ‘GENERATED ALWAYS’ syntax. Column value is generated at run time based on the value that gets into the COL1 column of the table. Let’s create some data to see this in practice:
INSERT INTO TEST_COMPUTE(COL1) VALUES(‘DECIPHER’);
INSERT INTO TEST_COMPUTE(COL1) VALUES(‘INFORMATION’);
INSERT INTO TEST_COMPUTE(COL1) VALUES(‘SYSTEMS’);

Let us check the output now which shows col2 contains reverse value of col1.
SQL> SELECT * FROM TEST_COMPUTE;

COL1            COL2
 ------------    ------------
 DECIPHER          REHPICED
 INFORMATION       NOITAMROFNI
 SYSTEMS          SMETSYS

For creating virtual column(s), column expression or a function should be a deterministic function which means that for a given input, virtual column should always return the same result. If that is not the case, then we can not define virtual column for such expressions. Following is an example.

SQL> ALTER TABLE TEST_COMPUTE
ADD COL3 GENERATED ALWAYS AS (TRUNC(SYSDATE));

ERROR at line 2:
ORA-54002: only pure functions can be specified in a virtual column expression

Since we can create virtual columns only for deterministic values, we can also create index(es) on virtual columns just like regular columns. Index will be always function based index.

SQL> CREATE INDEX TEST_COMPUTE_IND_1 ON TEST_COMPUTE(COL2);

We can verify it using the following SQL.

SQL> SELECT a.index_name, a.index_Type, b.column_Expression
FROM user_indexes a
INNER JOIN user_ind_Expressions b
ON a.index_name = b.index_name
WHERE a.index_name = ‘TEST_COMPUTE_IND_1’;

Here is the output.

INDEX_NAME            INDEX_TYPE                        COLUMN_EXPRESSION
 --------------------  -------------------------         --------------------
 TEST_COMPUTE_IND_1    FUNCTION-BASED NORMAL             REVERSE("COL1")

One can even partition on this column. There are some restrictions as well for virtual columns:

1. We cannot explicitly enter the data in virtual columns. Attempt to enter value for a column in the insert statement will result into ORA-54013 error.

2. We cannot update entire row using SET ROW command with TABLE%ROWTYPE operator. Virtual columns will not be ignored when we update entire record and hence will result into an error. Following is an example with an error.

DECLARE
V_TESTREC TEST_COMPUTE%ROWTYPE;
BEGIN

SELECT *
INTO v_testrec
FROM TEST_COMPUTE
WHERE col1 = ‘DECIPHER’;

v_testrec.COL1 := ‘ORACLE’;

UPDATE TEST_COMPUTE
SET ROW = v_testrec
WHERE col1 = ‘DECIPHER’;

COMMIT;

END;

ORA-54017: UPDATE operation disallowed on virtual columns
ORA-06512: at line 12

This is yet another step in the right direction – supporting an application across three different RDBMS (Oracle, MS SQL Server, DB2 LUW) would be so much more easier with such common features.

Posted in Oracle | Leave a Comment »

Two new management features in Oracle 11g

Posted by decipherinfosys on January 23, 2008

When doing migration of the schema from one version to the other, there are different modus operandi that are adopted by different DBA’s/Database Developers. You can read more on two common approaches in one our previous posts over here.  In the event of a DBA/developer chosing to use the DDLs, say that you have added a new NOT NULL constraint column to an existing table in the new release and that column has a default value associated to it.  And suppose that that table has over 100 million records in it.  Prior to Oracle 11g, when you issue such an alter statement, it would need to add the column, update the existing records with the default value and mark the column as NOT NULL.  The time taken to do so would be huge given the size of the table and given the fact that a large amount of undo and redo will be generated.  This is no longer an issue in Oracle 11g.  In 11g, when you issue such a command, say:

Alter table BIG_TABLE add New_Column varchar2(10)  default ‘test’ not null;

This statement will not issue an update to the existing records in the table.  New records will have their values set to the default value of “test” and when a query comes along that selects an older record, Oracle then derives that value out of the data dictionary and presents it.  So, bottom line is that you will no longer incur the penalty of redo and undo generation when issuing such alter commands.  This should help greatly in the conversion/migration/upgrade projects where one uses the DDL approach over the ETLM approach.

Another neat feature for schema management that has been introduced in 11g is the DDL_LOCK_TIMEOUT option.  This is something that the development DBAs/DB Developers will really appreciate.  Prior to Oracle 11g, if say you are executing the same alter statement as was shown above and if someone is holding a lock on that table, then you will get the following error:

ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

What this means is that you cannot get an exclusive lock on the table in order to complete the alter command.  The only option prior to Oracle 11g was to keep on trying or to find the sessions holding the locks and terminate them.  In 11g, one can use the ddl_lock_timeout session/system setting.  Example:

SQL> Alter session set ddl_lock_timeout = 30;

Now, when this particular session encounters a lock that prevents it from getting the exclusive lock, instead of timing out, it will try the DDL operation for 30 seconds (similar to “select for update wait N” feature).  This can also be issued at the system level:

SQL> Alter system set ddl_lock_timeout = 30;

Setting it at the system level would be a better choice so that all the sessions get the same setting.  You can of course, override it at the session level.

Posted in Oracle | Leave a Comment »

Read Only Tables in Oracle 11g

Posted by decipherinfosys on January 23, 2008

Prior to Oracle 11g, one could create read only tablespaces and not read only tables.  This is now possible in the new version of Oracle.  You can convert a table from read/write to read only and vice-versa.  Example:

SQL> create table testcase (col1 varchar2(10));
Table Created.

SQL>Alter table testcase read only;
Table altered.

The DBA_TABLES/USER_TABLES/ALL_TABLES views have a new column called READ_ONLY which can be used to see which tables are read only in the schema.  Likewise, you can turn it back to be a read/write table:

SQL> Alter table testcase read write;

Needless to state, once a table has been put into the read only mode, the update/delete/insert DML operations are not allowed on it.  The select for update statement is also not allowed.  The DDL statements like alter table are allowed though.  You can use this feature to implement security or you can also use this feature during the maintenance mode when you want to prevent changes to the data in a given table or set of tables or it can also be used to protect the data in the static/look-up/configuration tables in a given schema.

Posted in Oracle | Leave a Comment »

SQL Server 2005 Best Practices Analyzer

Posted by decipherinfosys on January 22, 2008

Microsoft released their SQL Server 2005 best practices analyzer recently. Here is the URL from where you can download it from:

http://www.microsoft.com/downloads/details.aspx?FamilyId=da0531e4-e94c-4991-82fa-f0e3fbd05e63&displaylang=en

The installation is pretty simple.  There have been a good number of enhancements including detecting any design issues in cube design for Analysis Services, memory issues etc..  The best thing about this edition is the rich documentation that comes along with it explaining the observations as well as URLs pointing to more material available at MSDN and KB articles which would help in getting a good grasp on the issue.  In the next release (SQL Server 2008), BPA is supposed to be integrated into the tools and not as a separate download.

Posted in SQL Server | 1 Comment »