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>

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:

... 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>

Locking and Unlocking Table Statistics

DBMS_STATS.LOCK_TABLE_STATS allows you to lock a table’s statistics. This stops the table being analyzed, even with an old ANALYZE command. DBMS_STATS.UNLOCK_TABLE_STATS has the reverse effect and allows you to analyze the table again:

 

SQL> exec dbms_stats.lock_table_stats -

> (ownname => 'ANDREW', tabname => 'LOCK_TEST');

 

PL/SQL procedure successfully completed.

 

SQL> exec dbms_stats.gather_table_stats -

> (ownname => 'ANDREW', tabname => 'LOCK_TEST');

BEGIN dbms_stats.gather_table_stats  (ownname => 'ANDREW', tabname => 'LOCK_TEST'); END;

 

*

ERROR at line 1:

ORA-20005: object statistics are locked (stattype

= ALL)

ORA-06512: at "SYS.DBMS_STATS", line 13182

ORA-06512: at "SYS.DBMS_STATS", line 13202

ORA-06512: at line 1

 

SQL> analyze table andrew.lock_test

  2  compute statistics

  3  /

analyze table andrew.lock_test

*

ERROR at line 1:

ORA-38029: object statistics are locked

 

SQL> exec dbms_stats.unlock_table_stats -

> (ownname => 'ANDREW', tabname => 'LOCK_TEST');

 

PL/SQL procedure successfully completed.

 

SQL> exec dbms_stats.gather_table_stats -

> (ownname => 'ANDREW', tabname => 'LOCK_TEST');

 

PL/SQL procedure successfully completed.

 

SQL>

Adding a Comment to a Column Description

This simple example, tested on an Oracle 9 database, shows how to add a comment to a column description. First create a table:

 

SQL> create table blah (col1 varchar2(1))

  2  /

 

Table created.

 

SQL>

 

And check that there are no comments on it:

 

SQL> select column_name, comments

  2  from user_col_comments

  3  where table_name = 'BLAH'

  4  /

 

COLUMN_NAME          COMMENTS

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

COL1

 

SQL>

 

Then add a comment to column col1 restrospectively and query user_col_comments again:

 

SQL> comment on column blah.col1 is 'Column 1'

  2  /

 

Comment created.

 

SQL> select column_name, comments

  2  from user_col_comments

  3  where table_name = 'BLAH'

  4  /

 

COLUMN_NAME          COMMENTS

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

COL1                 Column 1

 

SQL>

ORA-00972: identifier is too long

Tested on an Oracle 9 database. The maximum length of a password is 30 characters. If you try and set one longer than that, Oracle returns an ORA-00972:

 

SQL> CREATE USER A IDENTIFIED BY

  2  ABCDEFGHIJKLMNOPQRSTUVWXYZ1234

  3  /

 

User created.

 

SQL> ALTER USER A IDENTIFIED BY

  2  ABCDEFGHIJKLMNOPQRSTUVWXYZ12345

  3  /

ABCDEFGHIJKLMNOPQRSTUVWXYZ12345

*

ERROR at line 2:

ORA-00972: identifier is too long

 

SQL> CREATE USER B IDENTIFIED BY

  2  ABCDEFGHIJKLMNOPQRSTUVWXYZ12345

  3  /

ABCDEFGHIJKLMNOPQRSTUVWXYZ12345

*

ERROR at line 2:

ORA-00972: identifier is too long

 

SQL>

Differences between PL/SQL Developer and SQL*Plus

Tested on an Oracle 11 database. I have been trying out PL/SQL Developer, a product by allroundautomations. You can see details here: https://www.allroundautomations.com/ If you use it,  you need to be aware of subtle differences between this product and Oracle’s SQL*Plus.

 

If you have a file like this:

 

set echo on

prompt -

prompt - Check the date:

prompt -

select sysdate

from dual

/

 

And you try to run it in SQL*Plus, it will fail:

 

SQL> get todays_date

  1  set echo on

  2  prompt -

  3  prompt - Check the date:

  4  prompt -

  5  select sysdate

  6* from dual

SQL> @todays_date

SQL> set echo on

SQL> prompt -

> prompt - Check the date:

prompt - Check the date:

SQL> prompt -

> select sysdate

select sysdate

SQL> from dual

SP2-0042: unknown command "from dual" - rest of line ignored.

SQL> /

SP2-0552: Bind variable "PROMPT" not declared.

SQL>

 

That’s because SQL*Plus treats single hyphens as continuation characters so Oracle thinks that the select sysdate is part of the prompt on the preceding line. PL/SQL Developer does not do this and is able to execute the SQL (as usual, click the image to display it at its correct size and bring it into focus):