SUBSTR Versus LIKE in Oracle 11.2

I was reading an old SQL tuning book which was printed in 2002. It said that a where clause with like could often use an index whereas a similar clause using substr could not. I wondered if this might still be the case in an Oracle 11.2.0.1 database. To find out, I created a table:

SQL> conn andrew/reid
Connected.
SQL> create table tab1 as
  2  select table_name from dba_tables
  3  (more...)

Making a Hot Backup and Doing an Incomplete Recovery

This post shows how to do a hot backup followed by an incomplete recovery. I ran it on an Oracle 11.2.0.4 test database. First I checked that the database was in ARCHIVELOG mode:

SQL> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG

SQL>


Then I decided where to copy the hot backup.

The directory listing below shows a sub-directory called backup. This contains a cold backup I made earlier in case the test (more...)

ORA-39710 and ORA-00704

I tried to use dbua to upgrade a database from Oracle 11.2.0.4 to Oracle 12.1.0.2. Part way through, my PC lost connection with the UNIX server hosting the database. I tried to connect to the database but got an ORA-39710 so I forced the database to close with shutdown abort:

NLFINUT1 /export/home/oracle > sqlplus /

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jul 18 14:03:08 2016

(more...)

ARCHIVE_LAG_TARGET on Oracle 11.2.0.4

I read about this parameter, which is supposed to force a redo log switch after a certain number of seconds. I decided to try it out in an Oracle 11.2.0.4 database. This is the record from the alert log showing me setting the parameter:

Wed May 25 18:00:39 2016
ALTER SYSTEM SET archive_lag_target=1800 SCOPE=BOTH;
Wed May 25 18:00:39 2016


I expected this to force a redo log switch every 30 minutes but (more...)

ORA-01109

I read that you cannot take a tablespace offline if the database is only mounted so I decided to test this in an Oracle 11.2.0.4 database. I mounted the database and tried to take the USERS tablespace offline. Oracle returned an ORA-01109. I opened the database then I was able to take the tablespace offline:
 
SQL> startup mount
ORACLE instance started.

Total System Global Area  521936896 bytes
Fixed Size                  2252448 bytes
(more...)

How to See When an Oracle Role Was Created

You can get the date and time a role was created from the CTIME column in SYS.USER$. You can see what I mean in the example below, which I tested in an Oracle 11.2.0.4 database. First I created a role between DATE_AND_TIME1 and DATE_AND_TIME2. As they were the same, to the nearest second, the role must have been created at 16:11:05 on 27th May 2016.

I then waited 5 seconds and (more...)

%TYPE Declaration Gives PLS-00302

A developer reported a problem with a %TYPE declaration which was returning PLS-00302 in an Oracle 10 database. The cause turned out to be a variation on a problem which I have already reported. However, as it took me some time to work out, I have reproduced it below. First I created a user, called USER1, who would own a table:

SQL> conn / as sysdba
Connected.
SQL> create user user1
  2  identified (more...)

Bind Variables

This example, tested on Oracle 11, shows how you can define bind variables in SQL*Plus, assign values to them in PL/SQL then display those values afterwards back in SQL*Plus:

SQL> variable bv1 varchar2(3)
SQL> variable bv2 number
SQL> begin
  2  select 'ABC' into :bv1 from dual;
  3  select 123 into :bv2 from dual;
  4  end;
  5  /

PL/SQL procedure successfully completed.

SQL> print bv1

BV1
--------------------------------
ABC

SQL> print bv2

       (more...)

RMAN Backup With Compression

I repeated the test from the previous post but this time I tried to compress the backup. At this stage I need your help. I believe I have used the free compression which does not require an extra licence. If somebody who knows more about this than me could add a comment below, telling me if I am right or wrong, that would be very helpful. I backed up the database as before:

C:UsersAJ0294094>rman nocatalog (more...)

RMAN Backup Without Compression

I wanted to test the effect of RMAN backup compression on an Oracle 11.2.0.1 database running on Windows. I configured the flash recovery area, closed the database and mounted it. Then I did a backup without compression:

C:UsersAJ0294094>rman nocatalog target=andrew/reid

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Mar 11 18:42:42 2016

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: (more...)

Creating Tables in the UNDO Tablespace??

I was reading an article written by Martin Widlake in Oracle Scene Issue 58 (Autumn/Winter 2015). It said:

The second new item is the UNDO tablespace. This is a special tablespace that is only used for internal purposes and one that users cannot put any tables or indexes into.

This seemed perfectly reasonable so I wondered what might happen if I tried to do it. In an Oracle 9.2.0.7 database Oracle returned (more...)

SELECTs Do Not Block UPDATEs

I went on my first DBA course in 1997 and the lecturer there explained that readers do not block writers in an Oracle database. I had an issue recently which appeared to contradict this so I have reproduced it below in an Oracle 11.2.0.1 database.
 
I noticed in OEM that there was some issue in the database. As usual, click on the images to enlarge them and bring them into focus (more...)

INSERT /*+ APPEND */ Hint Does Not Seem to Work Consistently

I ran the following SQL in an Oracle 11.2.0.1 database:

SQL> create table tab1
  2  (col1 number)
  3  /
 
Table created.
 
SQL> alter session set sql_trace = true
  2  /
 
Session altered.
 
SQL> insert /*+ append */ into tab1 select 1 from dual
  2  /
 
1 row created.
 
SQL> commit
  2  /
 
Commit complete.
 
SQL> insert /*+ append */ (more...)

Clustering_Factor = 0

I looked at clustering_factor in the previous post and wondered what might cause it to be well below the number of blocks in the table. Chris Saxon suggested it might be due to rows with null values. I decided to look first at the two indexes with a clustering_factor of zero in the same Oracle 11.2 database and checked which columns they were on. Then I looked to see how many of the rows (more...)

Clustering_Factor

I attended the UKOUG conference early in December. While I was there, I went to a presentation by Chris Saxon, where he explained how Oracle decides when to use an index. The example below, which I ran in an Oracle 11.1 database, is based on what I learnt.
 
I used to believe that if a where clause only returned a small percentage of the rows in a table, Oracle would use an (more...)

ORA-28405

This post is an update to an earlier one, which I have now deleted. I tested the first part of it in an Oracle 11.1.0.6 database. First I created a role which was identified by a password:
 
SQL> conn / as sysdba
Connected.
SQL> create role low identified by secret_password
  2  /
 
Role created.
 
SQL>
 
I granted the role to a user and made sure it had (more...)

Oracle Live SQL

I had the opportunity to attend the UKOUG Conference recently. While I was there, I went to a Roundtable run by Shakeeb Rahman from Oracle. He told us about an Oracle tool which allows you to run SQL online without installing Oracle yourself. All you need is an account on My Oracle Support. I logged onto it here. It’s immediately obvious how it works so, once I had connected, I decided to see which version (more...)

A Simple Example of an Index Organised Table Without Overflow

For a long time I have had a note on my task list to learn about index organized tables. I never got round to doing it because I thought I would never see one. However, I came across several in a 3rd party application recently. An index organized table is a kind of index and table combined. You can see how they work in the example below, which I tested in an Oracle 11. (more...)

A Problem with REVOKE

If you grant the DBA role to a user, Oracle also grants it the UNLIMITED TABLESPACE system privilege. If you then revoke the DBA role from this user, Oracle also revokes its UNLIMITED TABLESPACE system privilege. This isn’t too much of an issue.
 
However, if you grant the UNLIMITED TABLESPACE system privilege to a user by itself THEN grant it the DBA role, Oracle seems to have no idea where the UNLIMITED TABLESPACE system (more...)

Segment Creation Deferred not Available in Standard Edition

If you use Oracle Standard Edition to create a production database, you need to be sure to create any corresponding test databases in Oracle Standard Edition too. Otherwise you may find that some SQL might be tested successfully, only to fail when you implement it in production. You can see what I mean in the example below. First I created a table with segment creation deferred in the Oracle Enterprise Edition test database:
 
C:UsersAJ0294094>sqlplus (more...)