Tuesday, April 24, 2012

DBMS_ROWID and PCTFREE

This was tested in an Oracle 9 database. It works the same way in Oracle 11 although the group by statement no longer returns the results in block number order (see here for details). 
DBMS_ROWID lets you break down a row's rowid to see the file number and block number where that row is stored:

SQL> create table andrew as
  2  select sysdate todays_date from dual
  3  /

Table created.

SQL> select dbms_rowid.rowid_relative_fno(rowid)
  2  from andrew
  3  /

DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)
------------------------------------
                                  22

SQL> select dbms_rowid.rowid_block_number(rowid)
  2  from andrew
  3  /

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
                                2838

SQL> drop table andrew
  2  /

Table dropped.

SQL>

You can use this information to see the effect of the PCTFREE clause when you create a table. This specifies the percentage of each block which should be left free for updates to existing rows in a table. If it is set to zero, no space is left for updates. This means that the table occupies fewer blocks. In the first example below, table ANDREW fits into 6 blocks:

SQL> create table andrew
  2  pctfree 0
  3  as select owner, table_name
  4  from dba_tables
  5  /

Table created.

SQL> select
  2  dbms_rowid.rowid_relative_fno(rowid) as file_num,
  3  dbms_rowid.rowid_block_number(rowid) as block_num,
  4  count(*)
  5  from andrew
  6  group by
  7  dbms_rowid.rowid_relative_fno(rowid),
  8  dbms_rowid.rowid_block_number(rowid)
  9  /

  FILE_NUM  BLOCK_NUM   COUNT(*)
---------- ---------- ----------
        22       2838        204
        22       2839        146
        22       2840        137
        22       2841        158
        22       2842        134
        22       2843         56

6 rows selected.

SQL> drop table andrew
  2  /

Table dropped.

SQL>

In the next example, table ANDREW contains the same data but with a PCTFREE of 25. This means that it occupies more blocks (8 in all) with fewer rows in each:

SQL> create table andrew
  2  pctfree 25
  3  as select owner, table_name
  4  from dba_tables
  5  /

Table created.

SQL> select
  2  dbms_rowid.rowid_relative_fno(rowid) as file_num,
  3  dbms_rowid.rowid_block_number(rowid) as block_num,
  4  count(*)
  5  from andrew
  6  group by
  7  dbms_rowid.rowid_relative_fno(rowid),
  8  dbms_rowid.rowid_block_number(rowid)
  9  /

  FILE_NUM  BLOCK_NUM   COUNT(*)
---------- ---------- ----------
        22       2838        156
        22       2839        119
        22       2840        111
        22       2841        100
        22       2842        120
        22       2843        107
        22       2844         98
        22       2845         24

8 rows selected.

SQL> drop table andrew
  2  /

Table dropped.

SQL>

In the final example, the table has a PCTFREE of 50 so half of each block is reserved for updates to existing rows. This time it uses 11 blocks:

SQL> create table andrew
  2  pctfree 50
  3  as select owner, table_name
  4  from dba_tables
  5  /

Table created.

SQL> select
  2  dbms_rowid.rowid_relative_fno(rowid) as file_num,
  3  dbms_rowid.rowid_block_number(rowid) as block_num,
  4  count(*)
  5  from andrew
  6  group by
  7  dbms_rowid.rowid_relative_fno(rowid),
  8  dbms_rowid.rowid_block_number(rowid)
  9  /

  FILE_NUM  BLOCK_NUM   COUNT(*)
---------- ---------- ----------
        22       2838        105
        22       2839         98
        22       2840         71
        22       2841         75
        22       2842         66
        22       2843         70
        22       2844         76
        22       2845         80
        22       2846         70
        22       2847         64
        22       2848         60

11 rows selected.

SQL>

Saturday, April 21, 2012

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>

Friday, April 20, 2012

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)

Wednesday, April 18, 2012

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.

Tuesday, April 17, 2012

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>

Monday, April 16, 2012

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>