Archive for January, 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.

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.

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:

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:

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.

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.

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.

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.

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:

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:

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;

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.

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.

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:

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.

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.

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.