Table_Lock

A colleague asked what the table_lock column in dba_tables was for. I did not know so I decided to investigate.First I looked at the distribution of table_lock values in one of our test databases:

SQL> select table_lock, count(*)
  2  from dba_tables
  3  group by table_lock
  4  /

TABLE_LOCK  COUNT(*)
---------- ----------
ENABLED          825

SQL>
 

This suggested to me that the default setting is enabled so I tried this out by creating a test table:

SQL> create table andrew (col1 number)
  2  /

Table created.

SQL> 

As expected, the table_lock column was set to enabled for this new table:

SQL> select table_lock from dba_tables
  2  where table_name = 'ANDREW'
  3  /

TABLE_LOCK
----------
ENABLED

SQL> 

You can alter this as follows:

SQL> alter table andrew disable table lock
  2  /

Table altered.

SQL>

And this sets the table_lock value to disabled:

SQL> select table_lock from dba_tables
  2  where table_name = 'ANDREW'
  3  /

TABLE_LOCK
----------
DISABLED

SQL> 

Setting table_lock to disabled stops you locking that table:

SQL> lock table andrew in share mode
  2  /
lock table andrew in share mode
*
ERROR at line 1:
ORA-00069: cannot acquire lock -- table locks
disabled for ANDREW

SQL> 

It also stops you running other DDL against it:

SQL> rename andrew to fred
  2  /
rename andrew to fred
*
ERROR at line 1:
ORA-00069: cannot acquire lock -- table locks
disabled for ANDREW

SQL>

And setting it back to enabled allows you to lock the table:

SQL> alter table andrew enable table lock
  2  /

Table altered.

SQL>

And run other DDL on the table:

SQL> rename andrew to fred
  2  /

Table renamed.

SQL>

So the main purpose of the table_lock column is to show whether you are allowed to lock a table or not.

set esc

You can use set esc to specify an escape character to SQL*Plus. This will often be a back slash (\). Then, if you have a variable e.g. $TAX_YEAR, which has a value of 2011, SQL*Plus will interpret $TAX_YEAR as 2011 but it will interpret \$TAX_YEAR as a string of characters with a value of $TAX_YEAR. I have an example somewhere demonstrating this and, when I find it, I will put it in a blog post. In the meantime, here is an example showing how NOT to use it. A few days ago, I was given a data patch to run. It contained a set esc / statement so the escape character was a forward slash (/). After I had run it, I found I could not reconnect to the database. To show how this happened, I need a couple of users: 

SQL> conn / as sysdba

Connected.

SQL> create user andrew

  2  identified by reid

  3  /

 

User created.

 

SQL> create user andrewreid

  2  identified by andrewreid

  3  /

 

User created.

 

SQL> grant create session to

  2  andrew, andrewreid

  3  /

 

Grant succeeded.

 

SQL>
  

Then I connect as the first user, andrew. Note that esc is off at the start:

SQL> show esc

escape OFF

SQL> conn andrew/reid

Connected.

SQL> show user

USER is "ANDREW"

SQL>
  

Next I set esc /, just like the data patch did:

SQL> set esc /

SQL> show esc

escape "/" (hex 2f)

SQL>
  

Finally, I try to reconnect to the database. Oracle normally treats a forward slash as the separator between the username and password in a connect string. However, if the forward slash is the escape character, it does not do this any more. When this happened for real, I was unable to connect at all. For the example I have changed things to show exactly what happens. I try to connect as user andrew, who has a password of reid. Oracle treats the forward slash as the escape character and assumes I want to connect as andrewreid. I enter the password for andrewreid and login successfully. Then I run a show user command to verify that the username is really andrewreid, not andrew:
  

SQL> conn andrew/reid

Enter password:

Connected.

SQL> show user

USER is "ANDREWREID"

SQL>

ORA-03113

I found some DBA interview questions on the Internet recently. One of them asked what might cause an ORA-03113. This message appears when you lose connection to a database as shown in the example below, which was tested on an Oracle 9 database. First a user connects to the database remotely and checks the time:

 

SQL> conn andrew/reid@test9

Connected.

SQL> select to_char(sysdate,'hh24:mi:ss')

  2  time_now from dual

  3  /

 

TIME_NOW

--------

17:40:13

 

SQL>

 

A little later, another user, connected to the server, closes the database:

 

TEST9 > sqlplus '/ as sysdba'

 

SQL*Plus: Release 9.2.0.7.0 - Production on Fri Sep 23 17:41:59 2011

 

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

 

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.7.0 - Production

 

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

 

Then, when the remote connection tries to check the time again, it fails with an ORA-03113:

 

SQL> select to_char(sysdate,'hh24:mi:ss')

  2  time_now from dual

  3  /

select to_char(sysdate,'hh24:mi:ss')

*

ERROR at line 1:

ORA-03113: end-of-file on communication channel

 

SQL>

 

The database is reopened:

 

TEST9 > sqlplus '/ as sysdba'

 

SQL*Plus: Release 9.2.0.7.0 - Production on Fri Sep 23 18:01:48 2011

 

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

 

Connected to an idle instance.

 

SQL> startup

ORACLE instance started.

 

Total System Global Area   68423632 bytes

Fixed Size                   733136 bytes

Variable Size              46137344 bytes

Database Buffers           20971520 bytes

Redo Buffers                 581632 bytes

Database mounted.

Database opened.

SQL>

 

If the remote user runs the query again, it works but only after he has reconnected to the database:

 

SQL> l

  1  select to_char(sysdate,'hh24:mi:ss')

  2* time_now from dual

SQL> /

select to_char(sysdate,'hh24:mi:ss')

*

ERROR at line 1:

ORA-03113: end-of-file on communication channel

 

SQL> conn andrew/reid@test9

Connected.

SQL> /

 

TIME_NOW

--------

18:03:21

 

SQL>

 

Incidentally, I have noticed that reconnecting to the database does not always work. Sometimes you have to start a fresh SQL*Plus session as well.

Synonyms (Part 2)

A colleague had an ORA-01775 error so I ran some tests on Oracle 11.2 to see how this might happen. First I created a table:

SQL> create table andrew (col1 number)
  2  /

Table created.

SQL>

Then I created a public synonym for that table:

SQL> create public synonym andrew for andrew
  2  /

Synonym created.

SQL>

Next I dropped the table:

SQL> drop table andrew
  2  /

Table dropped.

SQL>

Then when I tried to use the public synonym, Oracle returned an ORA-01775:

SQL> select count(*) from andrew
  2  /
select count(*) from andrew
                     *
ERROR at line 1:
ORA-01775: looping chain of synonyms

SQL>

But when I described it, I got a different error:

SQL> desc andrew
SP2-0749: Cannot resolve circular path of synonym "andrew"
SQL>

You get a similar problem if you create a public synonym for a non-existent table:

SQL> create public synonym joe for joe
  2  /

Synonym created.

SQL> desc joe
SP2-0749: Cannot resolve circular path of synonym "joe"
SQL>

You cannot do this with a private synonym as Oracle does not allow a private synonym to have the same name as the object to which it refers:

SQL> create table desmond (col1 number)

  2  /

Table created.

SQL> create synonym desmond for desmond

  2  /

create synonym desmond for desmond

*

ERROR at line 1:

ORA-01471: cannot create a synonym with same name as

object

SQL>

But, if you want to produce an ORA-01775 with private synonyms, you can do it like this:

SQL> create synonym andrew1 for andrew2
  2  /

Synonym created.

SQL> create synonym andrew2 for andrew1
  2  /

Synonym created.

SQL> select count(*) from andrew1
  2  /
select count(*) from andrew1
                     *
ERROR at line 1:
ORA-01775: looping chain of synonyms

SQL> desc andrew1
SP2-0749: Cannot resolve circular path of synonym "andrew1"
SQL>

Renaming Columns

Tested on an Oracle 9 database. You can rename columns with the alter table command:

SQL> create table with_columns_to_rename
  2  (old_name number)
  3  /

Table created.

SQL> desc with_columns_to_rename
Name                    Null?    Type
----------------------- -------- ----------------
OLD_NAME                        NUMBER

SQL> alter table with_columns_to_rename
  2  rename column old_name to new_name
  3  /

Table altered.

SQL> describe with_columns_to_rename
Name                    Null?    Type
----------------------- -------- ----------------
NEW_NAME                        NUMBER

SQL> 

But if the column to be renamed does not exist, you will get an ORA-00904:

SQL> alter table with_columns_to_rename
  2  rename column missing to found
  3  /
rename column missing to found
              *
ERROR at line 2:
ORA-00904: "MISSING": invalid identifier

SQL>

This has nothing to do with this post, but Oracle allows you to do desc, descr, descri, describ or describe to show the layout of a table:

SQL> descri with_columns_to_rename
Name                    Null?    Type
----------------------- -------- ----------------
NEW_NAME                        NUMBER

SQL>

Password Expire (again)

I created a user in an Oracle 11 database for Tiago
(http://uk.linkedin.com/pub/tiago-vasconcelos/0/652/494), a colleague of mine. Then I E-mailed the password to him and expired it. I do this quite often as it forces the user to change his password when he next logs in, so that he can set it to a value known only to him. He phoned me back soon after, saying that he was unable to alter the password so I decided to investigate. I found that if you connect to an Oracle 11 database using an Oracle 11 client, you can change your own password with the alter user and password commands. You can also reset your password if it has expired. This is demonstrated in the example below, which I ran on a Solaris server:

TEST11 > sqlplus system@test11

 

SQL*Plus: Release 11.1.0.6.0 - Production on Tue Sep 20 14:58:16 2011

 

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

 

Enter password:

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> grant create session to andrew

  2  identified by andrew

  3  /

 

Grant succeeded.

 

SQL> conn andrew/andrew@test11

Connected.

SQL> alter user andrew identified by andrew1

  2  /

 

User altered.

 

SQL> conn andrew/andrew1@test11

Connected.

SQL> password

Changing password for ANDREW

Old password:

New password:

Retype new password:

Password changed

SQL> conn andrew/andrew2@test11

Connected.

SQL> conn system@test11

Enter password:

Connected.

SQL> alter user andrew password expire

  2  /

 

User altered.

 

SQL> conn andrew/andrew2@test11

ERROR:

ORA-28001: the password has expired

 

Changing password for andrew

New password:

Retype new password:

Password changed

Connected.

SQL> conn andrew/andrew3@test11

Connected.

SQL>

However, if you try to do this using an Oracle 10 client (connected to an Oracle 11 database), the final step, i.e. resetting your password after it has expired, does not work. The example below illustrates this. I ran it on a Windows Vista client:

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Sep 20 15:08:37 2011

 

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> conn system@test11

Enter password: *********

Connected.

SQL> grant create session to andrew

  2  identified by andrew

  3  /

 

Grant succeeded.

 

SQL> conn andrew/andrew@test11

Connected.

SQL> alter user andrew identified by andrew1;

 

User altered.

 

SQL> conn andrew/andrew1@test11

Connected.

SQL> password

Changing password for ANDREW

Old password: *******

New password: *******

Retype new password: *******

Password changed

SQL> conn andrew/andrew2@test11

Connected.

SQL> conn system@test11

Enter password: *********

Connected.

SQL> alter user andrew password expire

  2  /

 

User altered.

 

SQL> conn andrew/andrew2@test11

ERROR:

ORA-28001: the password has expired

 

Changing password for andrew

New password: ********

Retype new password: ********

ERROR:

ORA-01017: invalid username/password; logon denied

 

Password unchanged

Warning: You are no longer connected to ORACLE.

SQL>

Exporting and Importing Table Statistics

Oracle lets you export and import table statistics. This allows you to restore statistics from a given point in time. To illustrate this, first set the system date back a few months:

SQL> alter system set fixed_date = '01-MAR-2011'
  2  /

System altered.

SQL> select sysdate from dual
  2  /

SYSDATE
---------
01-MAR-11

SQL> 

Now create a table and analyze it:

SQL> create table andrews_table
  2  as select * from dba_tables
  3  /

Table created.

SQL> exec dbms_stats.gather_table_stats -
> (ownname => 'ANDREW', tabname => 'ANDREWS_TABLE');

PL/SQL procedure successfully completed.

SQL>
 

Check the date the table was last analyzed. This will match the sysdate set above:

SQL> select last_analyzed from user_tables
  2  where table_name = 'ANDREWS_TABLE'
  3  /

LAST_ANALYZED
-------------
01-MAR-11

SQL> 

Before exporting the statistics, you need to create a table to hold them:

SQL> exec dbms_stats.create_stat_table -
> (ownname => 'ANDREW', stattab => 'ANDREWS_STATS');

PL/SQL procedure successfully completed.

SQL> 

Now export the table's statistics. The stattab parameter tells Oracle where to store the statistics. The statid parameter gives this set of statistics a name within the table:

SQL> exec dbms_stats.export_table_stats -
> (ownname => 'ANDREW', tabname => 'ANDREWS_TABLE', -
>  stattab => 'ANDREWS_STATS', statid => 'MAR2011');

PL/SQL procedure successfully completed.

SQL> 

And you can see this identifier by running a select against the table storing the statistics:

SQL> select distinct statid from andrews_stats
  2  /

STATID
------------------------------
MAR2011

SQL>
  
Now reinstate the sysdate:

SQL> alter system set fixed_date = 'NONE'
  2  /

System altered.

SQL> select sysdate from dual
  2  /

SYSDATE
---------
19-SEP-11

SQL> 

And analyze the table again:

SQL> exec dbms_stats.gather_table_stats -
> (ownname => 'ANDREW', tabname => 'ANDREWS_TABLE');

PL/SQL procedure successfully completed.

SQL> /

SYSDATE
---------
19-SEP-11

SQL>

This sets the last_analyzed date to the new sysdate:

SQL> select last_analyzed from user_tables
  2  where table_name = 'ANDREWS_TABLE'
  3  /

LAST_ANALYZED
-------------
19-SEP-11

SQL>

dbms_stats.import_table_stats allows you to reimport the statistics which were exported earlier:

SQL> exec dbms_stats.import_table_stats -
> (ownname => 'ANDREW', tabname => 'ANDREWS_TABLE', -
>  stattab => 'ANDREWS_STATS', statid => 'MAR2011');

PL/SQL procedure successfully completed.

SQL> 

This has the effect of resetting the last_analyzed  date to the earlier sysdate so that you can be sure that the correct statistics have been reimported:

SQL> select last_analyzed from user_tables
  2  where table_name = 'ANDREWS_TABLE'
  3  /

LAST_ANALYZED
-------------
01-MAR-11

SQL>

Division by Zero (again)

I have looked at division by zero in earlier posts and I recently received the following E-mail about it from Laurent Schneider who, incidentally, wrote the book advertised above:

... au fait, tu savais que 

SELECT * FROM DUAL WHERE EXISTS (SELECT 1/0 FROM DUAL)

ne retournait pas d'erreur ?

Bon week-end

Laurent

So I decided to give it a try and it's true! Oracle does not check that the select in the subquery can return a valid value. It only seems to be concerned with whether any where clause included in the statement is satisfied:

SQL> SELECT * FROM DUAL WHERE EXISTS
  2  (SELECT 1/0 FROM DUAL)
  3  /

D
-
X

SQL> SELECT * FROM DUAL WHERE EXISTS
  2  (SELECT 1/0 FROM DUAL WHERE 1=2)
  3  /

no rows selected

SQL> SELECT * FROM DUAL WHERE EXISTS
  2  (SELECT 1/0 FROM DUAL WHERE 1=1)
  3  /

D
-
X

How to Gather Database Statistics

This post was tested on an Oracle 9 database. You can calculate optimizer statistics for an entire database using dbms_stats.gather_database_stats. The estimate_percent parameter allows you to base the statistics on a given percentage of the data, if you wish to do so. Note that statistics are not calculated for objects owned by SYS or DBSNMP. Conversely, you can delete database statistics with dbms_stats.delete_database_stats:

 

TEST9 > sqlplus '/ as sysdba'

 

SQL*Plus: Release 9.2.0.5.0 - Production on Mon Sep 12 16:02:16 2011

 

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

 

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.5.0 - Production

 

SQL> select trunc(last_analyzed), count(*)

  2  from dba_tables

  3  group by trunc(last_analyzed);

 

TRUNC(LAS   COUNT(*)

--------- ----------

                 748

 

SQL> exec dbms_stats.gather_database_stats -

> (estimate_percent => 10);

 

PL/SQL procedure successfully completed.

 

SQL> select trunc(last_analyzed), count(*)

  2  from dba_tables

  3  group by trunc(last_analyzed);

 

TRUNC(LAS   COUNT(*)

--------- ----------

12-SEP-11        424

                 324

 

SQL> select distinct owner

  2  from dba_tables

  3  where last_analyzed is null;

 

OWNER

------------------------------

DBSNMP

SYS

 

SQL> exec dbms_stats.delete_database_stats;

 

PL/SQL procedure successfully completed.

 

SQL> select trunc(last_analyzed), count(*)

  2  from dba_tables

  3  group by trunc(last_analyzed);

 

TRUNC(LAS   COUNT(*)

--------- ----------

                 748

 

SQL>

The following statement was tested on Oracle 11.1. You can gather statistics just on tables with stale statistics as follows: 

SQL> exec dbms_stats.gather_database_stats -

> (options=>'gather stale',cascade=>true);

PL/SQL procedure successfully completed.

SQL>