Saturday, September 24, 2011

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>

Friday, September 23, 2011

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.

Thursday, September 22, 2011

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>

Wednesday, September 21, 2011

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>

Monday, September 19, 2011

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>

Sunday, September 18, 2011

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

Friday, September 16, 2011

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>

Wednesday, September 14, 2011

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>

Tuesday, September 13, 2011

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: http://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):


Wednesday, September 07, 2011

optimizer_features_enable



In an earlier post, I pointed out that, starting with Oracle 10, you need to include an ORDER BY after a GROUP BY if you want the output to be in order.
 
While I was researching this on the Internet, I came across several people who had discovered unexpected behaviour after an upgrade. With proper testing, this should not happen but, if it does, you can try using optimizer_features_enable as a temporary workaround.
 
The example below demonstrates this. It runs a query in an Oracle 10 database with a GROUP BY but no ORDER BY. The output is not in order. The query is then rerun with optimizer_features_enable set to 9.2.0. This time the output is in order.
 
Setting this parameter disables all new features provided by an upgrade, not just the ones you do not like, so you should implement a long term solution ASAP:
 
TEST10 > sqlplus /
 
SQL*Plus: Release 10.2.0.3.0 - Production on Mon Sep 5 13:58:05 2011
 
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
 
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
 
SQL> @group_by
SQL> set pages 100
SQL> col first_letter format a12
SQL> col value format a30
SQL> select value from v$parameter
  2  where name = 'optimizer_features_enable'
  3  /
 
VALUE
------------------------------
10.2.0.3
 
SQL> select substr(segment_name,1,1) first_letter,
  2  count(*)
  3  from dba_segments
  4  group by substr(segment_name,1,1)
  5  /
 
FIRST_LETTER   COUNT(*)
------------ ----------
P                   161
O                    37
V                    83
D                   157
_                    68
M                   448
N                    98
W                   742
Q                    40
Y                     3
C                   135
I                   550
B                   120
F                    55
U                    20
S                   717
T                   250
A                   316
J                    21
E                   143
L                   178
R                   303
H                   662
K                     9
b                     1
G                     7
X                    23
 
27 rows selected.
 
SQL> alter session
  2  set optimizer_features_enable = '9.2.0'
  3  /
 
Session altered.
 
SQL> select substr(segment_name,1,1) first_letter,
  2  count(*)
  3  from dba_segments
  4  group by substr(segment_name,1,1)
  5  /
 
FIRST_LETTER   COUNT(*)
------------ ----------
A                   316
B                   120
C                   135
D                   157
E                   143
F                    55
G                     7
H                   662
I                   550
J                    21
K                     9
L                   178
M                   448
N                    98
O                    37
P                   161
Q                    40
R                   303
S                   717
T                   250
U                    20
V                    83
W                   742
X                    23
Y                     3
_                    68
b                     1
 
27 rows selected.
 
SQL>