exitcommit

There is a new SQL*Plus system variable in Oracle 11.2 called exitcommit. The default is on but, if you set it to off, Oracle does not commit pending transactions when you exit. In the first example, some rows are inserted into a table with exitcommit set to on before typing exit to return to the OS:

 

SQL> show exitcommit

exitcommit ON

SQL> create table andrew1 as

  2  select * from dba_tables

  3  where 1=2

  4  /

 

Table created.

 

SQL> insert into andrew1

  2  select * from dba_tables

  3  /

 

3061 rows created.

 

SQL> select count(*) from andrew1

  2  /

 

  COUNT(*)

----------

      3061

 

SQL> exit

 

In the second example, exitcommit is set to off:

 

SQL> set exitcommit off

SQL> show exitcommit

exitcommit OFF

SQL> create table andrew2 as

  2  select * from dba_tables

  3  where 1=2

  4  /

 

Table created.

 

SQL> insert into andrew2

  2  select * from dba_tables

  3  /

 

3062 rows created.

 

SQL> select count(*) from andrew2

  2  /

 

  COUNT(*)

----------

      3062

 

SQL> exit

 

When you look at the tables afterwards, the second one is empty:

 

SQL> select count(*) from andrew1

  2  /

 

  COUNT(*)

----------

      3061

 

SQL> select count(*) from andrew2

  2  /

 

  COUNT(*)

----------

         0

 

SQL>

Oracle 8 Bug

I don't suppose many of you are using Oracle 8 nowadays. Due to a bug in that version, if you tried to reference a synonym pointing to an object which did not exist, this produced a core dump:

  

SQL> create public synonym andrew for andrew;

 

Synonym created.

 

SQL> desc andrew

Stack overflow: pid 9058, proc sqlplus, addr 0x11fdfffd0, pc 0x3ff805900b8

Memory fault(coredump)

Create Any Table

This post looks at the Create Any Table privilege. It was tested on Oracle 9. First create a user called A with unlimited quota on tablespace TSA. This means that user A can take up all available space in tablespace TSA if he wants to. There is no particular reason for this. I just wanted to contrast it with the quota for user B, which is limited to 5 megabytes:

SQL> create user a identified by a

  2  quota unlimited on tsa

  3  /

 

User created.

SQL>

User A needs to be able to connect to the database and create tables:

 

SQL> grant create session, create table to a

  2  /

 

Grant succeeded.

SQL>

Now create a user called B. For the purposes of this example, he only needs some quota in a different tablespace:

 

SQL> create user b identified by b

  2  quota 5m on tsb

  3  /

 

User created.

SQL>

Connect to the database as user A and create a table in schema A in tablespace TSA:

 

SQL> conn a/a

Connected.

SQL> create table tab1 (col1 number)

  2  tablespace tsa

  3  /

 

Table created.

SQL>

Try to create a table belonging to user B. This fails:

 

SQL> create table b.tab2 (col1 number)

  2  tablespace tsa

  3  /

create table b.tab2 (col1 number)

*

ERROR at line 1:

ORA-01031: insufficient privileges

SQL>

User A needs the create any table privilege if he wants to create tables in other schemas. Grant this privilege and try it out. It still fails because although user A is creating the table, it belongs to user B and it is he who needs the quota in the tablespace:

 

SQL> conn / as sysdba

Connected.

SQL> grant create any table to a

  2  /

 

Grant succeeded.

 

SQL> conn a/a

Connected.

SQL> create table b.tab3 (col1 number)

  2  tablespace tsa

  3  /

create table b.tab3 (col1 number)

*

ERROR at line 1:

ORA-01950: no privileges on tablespace 'TSA'

SQL>

This time, user A creates the table in tablespace TSB. This works as user B owns the table AND has a quota in the tablespace: 

 

SQL> create table b.tab4 (col1 number)

  2  tablespace tsb

  3  /

 

Table created.

 

SQL>

With the create any table privilege, user A is now able to carry out a denial of service attack on user B. He can do this by creating a large table belonging to user B in tablespace TSB. This will use up all B's quota there and as soon as he runs any SQL which needs a new extent to be allocated, that SQL will fall over.

ORA-00376

This was tested on an Oracle 10 database running on Linux. If you get an ORA-00376, your DBA may have taken a tablespace offline. If that is the case, the problem should go away once the tablespace is back on line again:

SQL> create table andrew
  2  tablespace users
  3  as select * from dba_tables
  4  /

Table created.

SQL> alter tablespace users offline
  2  /

Tablespace altered.

SQL> select count(*) from andrew
  2  /
select count(*) from andrew
                     *
ERROR at line 1:
ORA-00376: file 4 cannot be read at this time
ORA-01110: data file 4: '/home/oracle/DB1/users01.dbf'

SQL> alter tablespace users online
  2  /

Tablespace altered.

SQL> select count(*) from andrew
  2  /

  COUNT(*)
----------
      1521

SQL>

P.S. I forgot to add that you can check the status of a tablespace (and change its status if you wish) as shown below:

SQL> select status from dba_tablespaces

  2  where tablespace_name = 'USER_DATA';

STATUS

---------

ONLINE

SQL> alter tablespace user_data offline;

Tablespace altered.

SQL> select status from dba_tablespaces

  2  where tablespace_name = 'USER_DATA';

STATUS

---------

OFFLINE

SQL> alter tablespace user_data online;

Tablespace altered.

SQL> select status from dba_tablespaces

  2  where tablespace_name = 'USER_DATA';

STATUS

---------

ONLINE

SQL>

Oracle Flashback

This was introduced in Oracle 9. It allows you to query the database as it was at some recent point in the past.

You enable Flashback at the session level and use a system time or a system change number (SCN) to tell Oracle how far back to go. Then you will be able to see data which has been committed at that point.

This feature uses the Automatic Undo Management functionality. The undo_retention initialisation parameter tells Oracle how many seconds to keep undo information for. It therefore determines how far back you can go.

I produced the example below on an Oracle 10 database running on Linux. First check that undo_management is set to auto, that you have an undo tablespace and that the undo_retention parameter is large enough:

SQL> select name, value
  2    from v$parameter
  3    where name like '%undo%'
  4  /

NAME                 VALUE
-------------------- --------------------
undo_management      AUTO
undo_tablespace      UNDOTBS1
undo_retention       900

SQL> 

Then create a test user and give it permission to use dbms_flashback:

SQL> create user flash identified by gordon
  2    default tablespace users
  3    quota unlimited on users
  4    temporary tablespace temp
  5  /

User created.

SQL> grant create session,
  2        create table to flash
  3  /

Grant succeeded.

SQL> grant execute on dbms_lock to flash
  2  /

Grant succeeded.

SQL> grant execute on dbms_flashback to flash
  2  /

Grant succeeded.

SQL>

Create a table called time_now containing 1 row. This will be updated regularly with the current time:

SQL> conn flash/gordon
Connected.
SQL> create table time_now as
  2    select sysdate current_time from dual
  3  /

Table created.

SQL>

Also create a table called control_times. Whenever time_now is updated with sysdate, that sysdate will be inserted into control_times:

SQL> create table control_times
  2    (control_time date)
  3  /

Table created.

SQL>

Update time_now and insert into control_times as described above 20 times with a second between each iteration. As you do this, display the values updated and inserted:

SQL> declare
  2    display_time date;
  3  begin
  4    for a in 1..20 loop
  5       update time_now
  6         set current_time = (select sysdate from dual);
  7       commit;
  8       insert into control_times values (sysdate);
  9       commit;
 10       select current_time
 11         into display_time
 12         from time_now;
 13       dbms_output.put_line('Current Time: '||
 14         to_char(display_time,'hh24:mi:ss'));
 15       select max(control_time)
 16         into display_time from control_times;
 17       dbms_output.put_line('Control Time: '||
 18         to_char(display_time,'hh24:mi:ss'));
 19       dbms_output.put_line('**********');
 20       dbms_lock.sleep(1);
 21    end loop;
 22  end;
 23  /
Current Time: 00:01:32
Control Time: 00:01:32
**********
Current Time: 00:01:33
Control Time: 00:01:33
**********
Current Time: 00:01:34
Control Time: 00:01:34
**********
Current Time: 00:01:35
Control Time: 00:01:35
**********
Current Time: 00:01:36
Control Time: 00:01:36
**********
Current Time: 00:01:37
Control Time: 00:01:37
**********
Current Time: 00:01:38
Control Time: 00:01:38
**********
Current Time: 00:01:39
Control Time: 00:01:39
**********
Current Time: 00:01:40
Control Time: 00:01:40
**********
Current Time: 00:01:41
Control Time: 00:01:41
**********
Current Time: 00:01:42
Control Time: 00:01:42
**********
Current Time: 00:01:43
Control Time: 00:01:43
**********
Current Time: 00:01:44
Control Time: 00:01:44
**********
Current Time: 00:01:45
Control Time: 00:01:45
**********
Current Time: 00:01:46
Control Time: 00:01:46
**********
Current Time: 00:01:47
Control Time: 00:01:47
**********
Current Time: 00:01:48
Control Time: 00:01:48
**********
Current Time: 00:01:49
Control Time: 00:01:49
**********
Current Time: 00:01:50
Control Time: 00:01:50
**********
Current Time: 00:01:51
Control Time: 00:01:51
**********

PL/SQL procedure successfully completed.

SQL>

Finally, read each time from the control_times table and try to flashback the database to that point. Then query the time from the time_now table. This should be equal to the value from the control_times table but there is often a slight difference. Apparently, if you flashback to an SCN, this difference disappears. I will try this out in a future post:

SQL> declare
  2   display_time date;
  3   cursor c1 is
  4    select control_time from control_times
  5    order by 1;
  6  begin
  7   for x in c1 loop
  8  --
  9  -- Try to flashback the database to each control_time:
 10  --
 11    dbms_flashback.enable_at_time(x.control_time);
 12    dbms_output.put_line('Control Time: '||
 13     to_char(x.control_time,'hh24:mi:ss'));
 14    declare
 15     unable_to_read_data exception;
 16     pragma exception_init(unable_to_read_data,-01466);
 17     consistent_read_failure exception;
 18     pragma exception_init(consistent_read_failure,-08176);
 19    begin
 20  --
 21  -- Now get the corresponding time from the TIME_NOW table.
 22  -- It should be the same:
 23  --
 24     select current_time
 25      into display_time from time_now;
 26     dbms_output.put_line('Data Restored: '||
 27      to_char(display_time,'hh24:mi:ss'));
 28     dbms_output.put_line('**********');
 29     dbms_flashback.disable;
 30    exception
 31  --
 32  -- The flashback times are not always accurate
 33  -- so you may not be able to read the table.
 34  --
 35     when unable_to_read_data then
 36      dbms_output.put_line('Cannot read current_time from time_now');
 37      dbms_output.put_line('**********');
 38      dbms_flashback.disable;
 39     when consistent_read_failure then
 40      dbms_output.put_line('Consistent read failure');
 41      dbms_output.put_line('**********');
 42      dbms_flashback.disable;
 43    end;
 44   end loop;
 45  end;
 46  /
Control Time: 00:01:32
Cannot read current_time from time_now
**********
Control Time: 00:01:33
Cannot read current_time from time_now
**********
Control Time: 00:01:34
Data Restored: 00:01:34
**********
Control Time: 00:01:35
Data Restored: 00:01:34
**********
Control Time: 00:01:36
Data Restored: 00:01:34
**********
Control Time: 00:01:37
Data Restored: 00:01:37
**********
Control Time: 00:01:38
Data Restored: 00:01:37
**********
Control Time: 00:01:39
Data Restored: 00:01:37
**********
Control Time: 00:01:40
Data Restored: 00:01:40
**********
Control Time: 00:01:41
Data Restored: 00:01:40
**********
Control Time: 00:01:42
Data Restored: 00:01:40
**********
Control Time: 00:01:43
Data Restored: 00:01:43
**********
Control Time: 00:01:44
Data Restored: 00:01:43
**********
Control Time: 00:01:45
Data Restored: 00:01:43
**********
Control Time: 00:01:46
Data Restored: 00:01:46
**********
Control Time: 00:01:47
Data Restored: 00:01:46
**********
Control Time: 00:01:48
Data Restored: 00:01:46
**********
Control Time: 00:01:49
Data Restored: 00:01:49
**********
Control Time: 00:01:50
Data Restored: 00:01:49
**********
Control Time: 00:01:51
Data Restored: 00:01:49
**********

PL/SQL procedure successfully completed.

SQL>

ORA-01408 and ORA-01418

As I was applying a software release recently, I saw the two error messages above and have recreated them in the example below:

SQL> create table table1 (column1 number)

  2  /

 

Table created.

SQL> create index indexl on table1(column1)

  2  /

 

Index created.

SQL>

TABLE1 has an index called INDEXL but the SQL below tries to drop an index called INDEX1, which does not exist. This produces an ORA-01418:

SQL> drop index index1

  2  /

drop index index1

           *

ERROR at line 1:

ORA-01418: specified index does not exist

SQL>

The SQL below tries to add an index to COLUMN1. As the DROP INDEX statement above failed, TABLE1 still has an index on COLUMN1. This causes the SQL to fail with an ORA-01408:

 

SQL> create index index1 on table1(column1)

  2  /

create index index1 on table1(column1)

                              *

ERROR at line 1:

ORA-01408: such column list already indexed

 

SQL>

ORA-04043 and ORA-00980

This example was tested on Oracle 9. First create a table:

 

SQL> create table table1 (col1 number)

  2  /

 

Table created.

 

SQL>

 

Then create a synonym pointing to that table:

 

SQL> create synonym synonym1 for table1

  2  /

 

Synonym created.

 

SQL> desc synonym1

Name                       Null?    Type

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

COL1                                NUMBER

 

SQL>

 

Rename the underlying the table:

 

SQL> rename table1 to table2

  2  /

 

Table renamed.

 

SQL>

 

Now, if you describe the table via the synonym, you get an ORA-04043, which I have shown elsewhere:

 

SQL> desc synonym1

ERROR:

ORA-04043: object "ORACLE"."TABLE1" does not exist

 

SQL>

 

But, if you select from the table via the synonym, you get an ORA-00980:

 

SQL> select * from synonym1

  2  /

select * from synonym1

              *

ERROR at line 1:

ORA-00980: synonym translation is no longer valid

 

SQL>

 

Depending on the circumstances, you could fix this as follows:

(1)  Rename the table back again, if it was renamed by mistake in the first place.

(2)  Drop and recreate the synonym so that it points to the new table name.

DBMS_UTILITY.ANALYZE_SCHEMA

I read about dbms_utility recently and decided to give it a try. According to the documentation for Oracle 10.2, the analyze_schema procedure is obsolete (it says you should use dbms_stats.gather_schema_stats instead, which seems reasonable). However, when I tried dbms_utility.analyze_schema on that version, it seemed to work OK. I will try to look at some other dbms_utility subprograms in future posts:

SQL> create user andrew

  2  identified by reid

  3  default tablespace users

  4  quota unlimited on users

  5  /

 

User created.

 

SQL> grant create session, create table to andrew

  2  /

 

Grant succeeded.

 

SQL> conn andrew/reid

Connected.

SQL> create table table_list as

  2  select * from all_tables

  3  /

 

Table created.

 

SQL> select last_analyzed from user_tables

  2  where table_name = 'TABLE_LIST'

  3  /

 

LAST_ANAL

---------

 

 

SQL> exec dbms_utility.analyze_schema('ANDREW', 'ESTIMATE');

 

PL/SQL procedure successfully completed.

 

SQL> select last_analyzed from user_tables

  2  where table_name = 'TABLE_LIST'

  3  /

 

LAST_ANAL

---------

11-APR-12

 

SQL>

ORA-01779

Here is another example, tested on Oracle 11, where you cannot update a view. The view definition includes a join between two tables. If you were able to update it, the row concerned could be dropped from the view altogether as the join condition might no longer be satisfied. In this case, an ORA-01779 is returned:

SQL> create table table_list

  2  as select * from dba_tables

  3  /

 

Table created.

 

SQL> create table object_list

  2  as select * from dba_objects

  3  /

 

Table created.

 

SQL> create view view1 as

  2  select t.owner, t.table_name, o.object_id

  3  from table_list t, object_list o

  4  where t.owner = o.owner

  5  and t.table_name = o.object_name

  6  /

 

View created.

 

SQL> update view1 set owner = 'ANDREW'

  2  /

update view1 set owner = 'ANDREW'

                 *

ERROR at line 1:

ORA-01779: cannot modify a column which maps to a non

key-preserved table

 

SQL>