Friday, November 27, 2015

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.2 database:
 
First I created a sequence. You don’t need a sequence to create an index organized table. I just used it to ensure that the index always contained unique values:

SQL> create sequence seq1
  2  /
 
Sequence created.

SQL>

I read that an index organized table cannot contain a LONG column. When I tried to do this, Oracle returned an ORA-02160:

SQL> create table iot1
  2  (owner varchar2(30),
  3   object_name varchar2(30),
  4   seq_no number,
  5   column_not_allowed long,
  6   constraint iot1_pk
  7   primary key (owner, object_name, seq_no))
  8  organization index
  9  /
organization index
             *
ERROR at line 8:
ORA-02160: index-organized table can not contain
columns of type LONG
 
SQL>

Without the LONG column, the index organized table was created successfully. This was an index organized table without overflow. I will try to look at overflow in a future post:

SQL> create table iot1
  2  (owner varchar2(30),
  3   object_name varchar2(30),
  4   seq_no number,
  5   constraint iot1_pk
  6   primary key (owner, object_name, seq_no))
  7  organization index
  8  /
 
Table created.
 
SQL>

After creating the index organized table, it had an IOT_TYPE of IOT. The IOT_NAME column was empty as the index organized table did not have overflow:

SQL> select iot_type, nvl(iot_name,'NULL')
  2  from user_tables
  3  where table_name = 'IOT1'
  4  /
 
IOT_TYPE             NVL(IOT_NAME,'NULL')
-------------------- ------------------------------
IOT                  NULL

SQL>

I added data to the index organized table like this:

SQL> begin
  2   for i in 1..15 loop
  3    insert into iot1
  4    select owner, object_name, seq1.nextval
  5    from dba_objects;
  6   end loop;
  7  end;
  8  /
 
PL/SQL procedure successfully completed.
 
SQL> select count(*) from iot1
  2  /
 
  COUNT(*)
----------
    996060

SQL>

The index organized table had an entry in DBA_TABLES:

SQL> select count(*) from dba_tables
  2  where table_name = 'IOT1'
  3  /
 
  COUNT(*)
----------
         1

SQL>

... but it did not appear in DBA_SEGMENTS:

SQL> select count(*) from dba_segments
  2  where segment_name = 'IOT1'
  3  /
 
  COUNT(*)
----------
         0

SQL>

... and its TABLESPACE_NAME entry in DBA_TABLES was empty: 

SQL> select nvl(tablespace_name,'NULL')
  2  from dba_tables
  3  where table_name = 'IOT1'
  4  /
 
NVL(TABLESPACE_NAME,'NULL')
------------------------------
NULL

SQL>

The index associated with the index organized table had an entry in DBA_SEGMENTS so I checked how big it was:

SQL> select bytes from dba_segments
  2  where segment_name = 'IOT1_PK'
  3  /
 
     BYTES
----------
  83886080

SQL>

Then I deleted several rows from the index organized table:

SQL> delete from iot1
  2  where owner = 'SYS'
  3  /
 
460995 rows deleted.

SQL>

... and checked that this deleted some leaf rows from the index:

SQL> analyze index iot1_pk validate structure
  2  /
 
Index analyzed.
 
SQL> select name, lf_rows, del_lf_rows
  2  from index_stats
  3  /
 
NAME          LF_ROWS DEL_LF_ROWS
---------- ---------- -----------
IOT1_PK        848331      313266
 
SQL>

When this happens, you can usually just rebuild the index but when I tried to do this to the index for the index organized table, Oracle returned an ORA-28650:

SQL> alter index iot1_pk rebuild
  2  /
alter index iot1_pk rebuild
*
ERROR at line 1:
ORA-28650: Primary index on an IOT cannot be rebuilt
 
SQL>

So I moved the table instead:

SQL> alter table iot1 move online
  2  /
 
Table altered.

SQL>

... then I analyzed the index again:

SQL> analyze index iot1_pk validate structure
  2  /
 
Index analyzed.

SQL>

... and saw that the deleted leaf rows had disappeared:
 
SQL> select name, lf_rows, del_lf_rows
  2  from index_stats
  3  /
 
NAME          LF_ROWS DEL_LF_ROWS
---------- ---------- -----------
IOT1_PK        535065           0

SQL>

... and the index was much smaller:

SQL> select bytes from dba_segments
  2  where segment_name = 'IOT1_PK'
  3  /
 
     BYTES
----------
  25165824

SQL>

Thursday, November 19, 2015

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 privilege came from. If you then revoke the DBA role from this user, Oracle still revokes the UNLIMITED TABLESPACE system privilege from it. This may not be what you intended to do. You can see what I mean in the example below, which I tested in an Oracle 11.2 database.
 
First I created a user:
 
SQL> create user a identified by b
  2  /
 
User created.
 
SQL>
 
I checked that it had no roles nor system privileges:
 
SQL> select granted_role from dba_role_privs
  2  where grantee = 'A'
  3  /
 
no rows selected
 
SQL> select privilege from dba_sys_privs
  2  where grantee = 'A'
  3  /
 
no rows selected
 
SQL>
 
I granted the DBA role to the user and checked that this also gave it the UNLIMITED TABLESPACE system privilege:
 
SQL> grant dba to a
  2  /
 
Grant succeeded.
 
SQL> select granted_role from dba_role_privs
  2  where grantee = 'A'
  3  /
 
GRANTED_ROLE
------------------------------
DBA
 
SQL> select privilege from dba_sys_privs
  2  where grantee = 'A'
  3  /
 
PRIVILEGE
----------------------------------------
UNLIMITED TABLESPACE
 
SQL>
 
I revoked the DBA role and checked that Oracle also revoked the UNLIMITED TABLESPACE system privilege:
 
SQL> revoke dba from a
  2  /
 
Revoke succeeded.
 
SQL> select granted_role from dba_role_privs
  2  where grantee = 'A'
  3  /
 
no rows selected
 
SQL> select privilege from dba_sys_privs
  2  where grantee = 'A'
  3  /
 
no rows selected
 
SQL>
 
I granted the UNLIMITED TABLESPACE system privilege to the user independently:
 
SQL> grant unlimited tablespace to a
  2  /
 
Grant succeeded.
 
SQL> select granted_role from dba_role_privs
  2  where grantee = 'A'
  3  /
 
no rows selected
 
SQL> select privilege from dba_sys_privs
  2  where grantee = 'A'
  3  /
 
PRIVILEGE
----------------------------------------
UNLIMITED TABLESPACE
 
SQL>
 
I granted the DBA role to the user:
 
SQL> grant dba to a
  2  /
 
Grant succeeded.
 
SQL> select granted_role from dba_role_privs
  2  where grantee = 'A'
  3  /
 
GRANTED_ROLE
------------------------------
DBA
 
SQL> select privilege from dba_sys_privs
  2  where grantee = 'A'
  3  /
 
PRIVILEGE
----------------------------------------
UNLIMITED TABLESPACE
 
SQL>
 
I revoked the DBA role from the user. Oracle revoked the UNLIMITED TABLESPACE system privilege at the same time despite the fact that I had granted it separately:
 
SQL> revoke dba from a
  2  /
 
Revoke succeeded.
 
SQL> select granted_role from dba_role_privs
  2  where grantee = 'A'
  3  /
 
no rows selected
 
SQL> select privilege from dba_sys_privs
  2  where grantee = 'A'
  3  /

no rows selected

SQL>

Tuesday, November 17, 2015

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:\Users\AJ0294094>sqlplus cmp
 
SQL*Plus: Release 11.2.0.1.0 Production on Mon Nov 16 12:35:04 2015
 
Copyright (c) 1982, 2010, 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> create table tab1 (col1 number)
  2  segment creation deferred
  3  /
 
Table created.
 
SQL>
 
Later, when I tried to create the same table in the Oracle Standard Edition production database, Oracle returned an ORA-00439:
 
C:\Users\AJ0294094>sqlplus cmp
 
SQL*Plus: Release 11.2.0.1.0 Production on Mon Nov 16 12:37:13 2015
 
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
 
Enter password:
 
Connected to:
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
 
SQL> create table tab1 (col1 number)
  2  segment creation deferred
  3  /
create table tab1 (col1 number)
*
ERROR at line 1:
ORA-00439: feature not enabled: Deferred Segment Creation

SQL>

Friday, November 13, 2015

GRANT SELECT Updates LAST_DDL_TIME

DDL stands for Data Definition Language. The CREATE TABLE, ALTER TABLE and DROP TABLE statements are examples of DDL. LAST_DDL_TIME is a column in the USER_OBJECTS view. It records the date and time of the most recent DDL statement applied to the object in question. Even granting SELECT access on a table will update its LAST_DDL_TIME. You can see this in the example below, which I tested in an Oracle 11.2 database.
 
First I created a table and checked that its LAST_DDL_TIME matched the creation time:
 
SQL> select to_char(sysdate,'hh24:mi:ss') time_now
  2  from dual
  3  /
 
TIME_NOW
--------
18:39:50
 
SQL> create table tab1(col1 number)
  2  /
 
Table created.
 
SQL> select to_char(last_ddl_time,'hh24:mi:ss') time_now
  2  from user_objects
  3  where object_name = 'TAB1'
  4  /
 
TIME_NOW
--------
18:39:50
 
SQL>
 
Then I waited 10 seconds, noted the time again, ran some DDL on the table and checked that this had updated the LAST_DDL_TIME:
 
SQL> exec sys.dbms_lock.sleep(10);
 
PL/SQL procedure successfully completed.
 
SQL> select to_char(sysdate,'hh24:mi:ss') time_now
  2  from dual
  3  /
 
TIME_NOW
--------
18:40:00
 
SQL> alter table tab1 add(col2 number)
  2  /
 
Table altered.
 
SQL> select to_char(last_ddl_time,'hh24:mi:ss') time_now
  2  from user_objects
  3  where object_name = 'TAB1'
  4  /
 
TIME_NOW
--------
18:40:00
 
SQL>
 
Finally, I waited a further 10 seconds, noted the time, did a GRANT SELECT on the table to another user and checked that the LAST_DDL_TIME had been updated again:
 
SQL> exec sys.dbms_lock.sleep(10);
 
PL/SQL procedure successfully completed.
 
SQL> select to_char(sysdate,'hh24:mi:ss') time_now
  2  from dual
  3  /
 
TIME_NOW
--------
18:40:10
 
SQL> grant select on tab1 to fred
  2  /
 
Grant succeeded.
 
SQL> select to_char(last_ddl_time,'hh24:mi:ss') time_now
  2  from user_objects
  3  where object_name = 'TAB1'
  4  /
 
TIME_NOW
--------
18:40:10

SQL>

Thursday, November 12, 2015

PRAGMA EXCEPTION_INIT

You can use one of these to link an exception name with an Oracle error number. Once you have done this, you can use the exception name in the exception block which follows the declaration. You can see what I mean in the example below, which I tested in an Oracle 11.2 database. First I set up a table so I could test the procedure I was going to create:

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

Table created.

SQL> 


Then I created a procedure to count the rows in a table. On line 5, I created an exception called table_not_found. On line 6, I associated this with ORA-00942. On line 12, I tested for this exception and displayed an error message if appropriate: 

SQL> create or replace procedure count_rows
  2  (tab in varchar2)
  3  is
  4   row_count number;
  5   table_not_found exception;
  6   pragma exception_init(table_not_found,-942);
  7  begin
  8   execute immediate 'select count(*) from '||tab
  9   into row_count;
 10   dbms_output.put_line(tab||' has '||row_count||' rows');
 11  exception
 12   when table_not_found then
 13   dbms_output.put_line('I cannot find '||tab);
 14  end;
 15  /

Procedure created.

SQL> 


When I tested the procedure, it counted the rows in real_table and told me it could not find imaginary_table:

SQL> set serveroutput on
SQL> exec count_rows('real_table');
real_table has 3172 rows

PL/SQL procedure successfully completed.

SQL> exec count_rows('imaginary_table');
I cannot find imaginary_table

PL/SQL procedure successfully completed.

SQL>

Tuesday, November 10, 2015

SELECT ANY SEQUENCE

I used to think that a user with SELECT ANY TABLE and SELECT ANY DICTIONARY could see anything in a database. I found out today that these 2 privileges do not allow you to SELECT from another user’s sequence. You can see what I mean in the example below, which I tested in an Oracle 11.1 database. First I created a user to own a sequence:
 
SQL> conn / as sysdba
Connected.
SQL> create user user1
  2  identified by user1
  3  /
 
User created.
 
SQL> grant create session, create sequence
  2  to user1
  3  /
 
Grant succeeded.
 
SQL>
 
Then I created a second user to SELECT from the first user’s sequence:
 
SQL> create user user2
  2  identified by user2
  3  /
 
User created.
 
SQL> grant create session,
  2  select any table,
  3  select any dictionary to user2
  4  /
 
Grant succeeded.
 
SQL>
 
USER1 then created a sequence:
 
SQL> conn user1/user1
Connected.
SQL> create sequence sequence1
  2  /
 
Sequence created.
 
SQL> select sequence1.nextval from dual
  2  /
 
   NEXTVAL
----------
         1
 
SQL>
 
… but USER2 could not SELECT from it:
 
SQL> conn user2/user2
Connected.
SQL> select user1.sequence1.nextval from dual
  2  /
select user1.sequence1.nextval from dual
             *
ERROR at line 1:
ORA-01031: insufficient privileges
 
SQL>
 
One way round this is to GRANT the SELECT ANY SEQUENCE privilege like this:
 
SQL> conn / as sysdba
Connected.
SQL> grant select any sequence to user2
  2  /
 
Grant succeeded.
 
SQL> conn user2/user2
Connected.
SQL> select user1.sequence1.nextval from dual
  2  /
 
   NEXTVAL
----------
         2
 
SQL>