Thursday, March 29, 2012

How to Partition an Existing Table

This example was tested on Oracle 9. First create an ordinary table with some data in and count the number of rows it contains:

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

Table created.

SQL> select count(*) from andrew
  2  /

  COUNT(*)
----------
       831

SQL>

Next create an empty partitioned version of the table and show that it is empty:

SQL> create table andrew_partitioned
  2  partition by range(owner)
  3  (partition part01
  4  values less than (maxvalue))
  5  as select * from andrew
  6  where 1=2
  7  /

Table created.

SQL> select count(*) from andrew_partitioned
  2  /

  COUNT(*)
----------
         0

SQL>

Now move the data from the ordinary table to the partitioned table:

SQL> alter table andrew_partitioned
  2  exchange partition part01
  3  with table andrew
  4  /

Table altered.

SQL>

Show that the ordinary table is empty and that the partitioned table now has all the data:

SQL> select count(*) from andrew
  2  /

  COUNT(*)
----------
         0

SQL> select count(*) from andrew_partitioned
  2  /

  COUNT(*)
----------
       831

SQL>

Give the partitioned table the same name as the original non-partitioned one:

SQL> drop table andrew
  2  /

Table dropped.

SQL> rename andrew_partitioned to andrew
  2  /

Table renamed.

SQL> 

Finally, you can split up the new partitioned table as you wish:

SQL> alter table andrew
  2  split partition part01 at ('O')
  3  into (partition part01, partition part02)
  4  /

Table altered.

SQL>

Wednesday, March 28, 2012

Sort Key too Long

This is an example of the following error:

UNIX > oerr ora 01467
01467, 00000, "sort key too long"
// *Cause:
// *Action:
UNIX >

It was tested on Oracle 9. The maximum sort key you can use depends on the block size, which is 8192 in this database:

SQL> col value format a5
SQL> select value from v$parameter
  2  where name = 'db_block_size'
  3  /

VALUE
-----
8192

SQL>

First create a table with 3 columns of 4000 characters each, followed by a number. Use the NOPRINT clause to stop SQL*Plus printing these long columns:

SQL> col a noprint
SQL> col b noprint
SQL> col c noprint
SQL> col substr(c,1,92) noprint
SQL> col substr(c,1,93) noprint
SQL> create table andrew
  2  (a varchar2(4000),
  3   b varchar2(4000),
  4   c varchar2(4000),
  5   x number)
  6  /

Table created.

SQL>

Fill columns a, b and c with repeated characters and put a small number in column x.

SQL> insert into andrew values (rpad('?',4000,'?'),
  2  rpad('*',4000,'*'),rpad('!',4000,'!'),1)
  3  /

1 row created.

SQL> insert into andrew values (rpad(';',4000,';'),
  2  rpad('£',4000,'£'),rpad('^',4000,'^'),2)
  3  /

1 row created.

SQL>

Sort the rows. The sort key is 12000 characters long but the order by statement seems to handle the columns individually:

SQL> select x from andrew order by a,b,c
  2  /

         X
----------
         2
         1

SQL>

However, the group by statement appears to concatenate the columns in the sort key. The maximum permitted sort key length in this case is 4000 + 4000 + 92 = 8092:

SQL> select a,b,substr(c,1,92),sum(x)
  2  from andrew
  3  group by a,b,substr(c,1,92)
  4  /

    SUM(X)
----------
        2
        1

SQL> select a,b,substr(c,1,93),sum(x)
  2  from andrew
  3  group by a,b,substr(c,1,93)
  4  /
from andrew
    *
ERROR at line 2:
ORA-01467: sort key too long

SQL>

Tuesday, March 27, 2012

Typing oracle at the Command Prompt

This applies to Oracle 9 and below. If you get an ORA-00600 in your alert log and the first and only argument which follows is [12235], this may well have been caused by somebody typing the word oracle at the command prompt. Here is one I created earlier:
 
ORA-00600: internal error code, arguments: [12235], [], [], [], [], [], [], []

This only happens if the database is open at the time. The message appears to go to the alert log of the database in the $ORACLE_SID.

Saturday, March 24, 2012

SQL*Plus SET NULL Statement

I have used the NVL function in several other posts but here is another example. First a table is created. It has never been analyzed so its last_analyzed date is null. The NVL function spots this and replaces the null by the text, Not yet:

SQL> create table andrew (col1 number)
  2  /

Table created.

SQL> select nvl(to_char(last_analyzed),'Not yet')
  2  from dba_tables
  3  where table_name = 'ANDREW'
  4  /

NVL(TO_CH
---------
Not yet

SQL>

If you don't like the NVL function, you can use the SQL*Plus SET NULL statement instead. Here is one way to use it:

SQL> set null 'Null'
SQL> select last_analyzed from dba_tables
  2  where table_name = 'ANDREW'
  3  /

LAST_ANAL
---------
Null

SQL>

And here is another:

SQL> col last_analyzed null Never
SQL> select last_analyzed from dba_tables
  2  where table_name = 'ANDREW'
  3  /

LAST_ANAL
---------
Never

SQL>

Friday, March 23, 2012

How to Test a Database Link

Before creating a database link, you need a schema to connect to in the remote database:
 
SQL> conn /@remotedb
Connected.
SQL> grant create session to link_schema
  2  identified by link_schema_password
  3  /
 
Grant succeeded.
 
SQL>
 
Then you can create a database link in the local database as follows:
 
SQL> create database link andrews_link
  2  connect to link_schema
  3  identified by link_schema_password
  4  using 'REMOTEDB'
  5  /
 
Database link created.
 
SQL>
 
... and you can test it like this:
 
SQL> select * from dual@andrews_link
  2  /
 
D
-
X
 
SQL>
 
If things go wrong in the remote database, the test will fail in the local database and will often give you a good idea of what is wrong. Here is one example:
 
SQL> conn /@remotedb
Connected.
SQL> revoke create session from link_schema
  2  /
 
Revoke succeeded.
 
SQL> conn /@localdb
Connected.
SQL> select * from dual@andrews_link
  2  /
select * from dual@andrews_link
                   *
ERROR at line 1:
ORA-01045: user LINK_SCHEMA lacks CREATE SESSION privilege; logon denied
ORA-02063: preceding line from ANDREWS_LINK
 
SQL>
 
... and here is another:
 
SQL> conn /@remotedb
Connected.
SQL> alter user link_schema
  2  identified by new_password
  3  /
 
User altered.
 
SQL> conn /@localdb
Connected.
SQL> select * from dual@andrews_link
  2  /
select * from dual@andrews_link
                   *
ERROR at line 1:
ORA-01017: invalid username/password; logon denied
ORA-02063: preceding line from ANDREWS_LINK
 
SQL>

Alter Index ... Rebuild Online

Some people suggest that you should rebuild an index if its height is greater than 3. I am not totally convinced by this but I was asked to do so recently and this is what happened. I checked the index first as follows:
 
SQL> analyze index auabopen_1
  2  validate structure
  3  /
 
Index analyzed.
 
SQL> select name, height from index_stats
  2  /
 
NAME                               HEIGHT
------------------------------ ----------
AUABOPEN_1                              4
 
SQL>
 
I checked the size of the index, saw what tablespace it is in and made sure there was enough space there to do the rebuild:
 
SQL> l
  1  select bytes from dba_segments
  2* where segment_name = 'AUABOPEN_1'
SQL> /
 
     BYTES
----------
2306867200
 
SQL> select tablespace_name
  2  from dba_indexes
  3  where index_name = 'AUABOPEN_1'
  4  /
 
TABLESPACE_NAME
------------------------------
UABOPEN_INDEX_1
 
SQL> select max(bytes) from dba_free_space
  2  where tablespace_name = 'UABOPEN_INDEX_1'
  3  /
 
MAX(BYTES)
----------
7340032000
 
SQL>
 
The index is on an online system so I rebuilt it as follows:
 
SQL> alter index auabopen_1
  2  rebuild online
  3  tablespace uabopen_index_1
  4  /
 
Index altered.
 
SQL>
 
Then I rechecked its height but it had not changed. You can't win them all!
 
SQL> analyze index auabopen_1
 2  validate structure
  3  /
 
Index analyzed.
 
SQL> select name, height from index_stats
  2  /
 
NAME                               HEIGHT
------------------------------ ----------
AUABOPEN_1                              4
 
SQL>

Introduction to Auditing

A database’s audit trail is stored in the SYS.AUD$ table:

SQL> desc sys.aud$
Name                       Null?    Type
-------------------------- -------- ------------------
SESSIONID                  NOT NULL NUMBER
ENTRYID                    NOT NULL NUMBER
STATEMENT                  NOT NULL NUMBER
TIMESTAMP#                 NOT NULL DATE
USERID                              VARCHAR2(30)
USERHOST                            VARCHAR2(128)
TERMINAL                            VARCHAR2(255)
ACTION#                    NOT NULL NUMBER
RETURNCODE                 NOT NULL NUMBER
OBJ$CREATOR                         VARCHAR2(30)
OBJ$NAME                            VARCHAR2(128)
AUTH$PRIVILEGES                     VARCHAR2(16)
AUTH$GRANTEE                        VARCHAR2(30)
NEW$OWNER                           VARCHAR2(30)
NEW$NAME                            VARCHAR2(128)
SES$ACTIONS                         VARCHAR2(19)
SES$TID                             NUMBER
LOGOFF$LREAD                        NUMBER
LOGOFF$PREAD                        NUMBER
LOGOFF$LWRITE                       NUMBER
LOGOFF$DEAD                         NUMBER
LOGOFF$TIME                         DATE
COMMENT$TEXT                        VARCHAR2(4000)
CLIENTID                            VARCHAR2(64)
SPARE1                              VARCHAR2(255)
SPARE2                              NUMBER
OBJ$LABEL                           RAW(255)
SES$LABEL                           RAW(255)
PRIV$USED                           NUMBER
SESSIONCPU                          NUMBER
 
SQL>
 
You can tell Oracle what to monitor using the AUDIT command. The example below will record when the SYSTEM user connects to the database:
 
SQL> audit create session by system;
 
Audit succeeded.
 
SQL>
 
This database currently has auditing turned off i.e the audit_trail initialisation parameter is set to NONE:
 
SQL> l
  1  select value from v$parameter
  2* where name = 'audit_trail'
SQL> /
 
VALUE
------------------------------
NONE
 
SQL>
 
So subsequent logins by SYSTEM will not be recorded in SYS.AUD$:
 
SQL> conn system/manager
Connected.
SQL> select count(*) from sys.aud$
  2  /
 
  COUNT(*)
----------
         0
 
SQL>
 
In versions up to and including Oracle 11, you cannot turn auditing on while a database is open:
 
SQL> alter system set audit_trail = true;
alter system set audit_trail = true
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot
be modified
 
SQL>
 
You have to alter the pfile or spfile as appropriate and bounce the database. Once audit_trail is set to true, auditing will start working and a row will appear in SYS.AUD$ after SYSTEM has connected to the database:
 
SQL> select value from v$parameter
  2  where name = 'audit_trail'
  3  /
 
VALUE
------------------------------
TRUE
 
SQL> conn system/manager
Connected.
SQL> select count(*) from sys.aud$
  2  /
 
  COUNT(*)
----------
         1
 
SQL>
 
You can query records in SYS.AUD$ in the normal way. The TIMESTAMP# column has the logon time and a PRIV$USED of 5 stands for CREATE SESSION:
 
SQL> l
  1* select userid, timestamp#, priv$used from sys.aud$
SQL> /
 
USERID     TIMESTAMP#  PRIV$USED
---------- ---------- ----------
SYSTEM     21-MAR-12           5
 
SQL>
 
Various views record the auditing which you have asked Oracle to do in your database. The audit request we made above is stored in DBA_PRIV_AUDIT_OPTS:
 
SQL> select user_name, privilege
  2  from dba_priv_audit_opts
  3  /
 
USER_NAME            PRIVILEGE
-------------------- --------------------
SYSTEM               CREATE SESSION
 
SQL>
 
You can stop an audit request with the NOAUDIT command:
 
SQL> noaudit create session by system
  2  /
 
Noaudit succeeded.
 
SQL>
 
Once you have done this, the relevant entry disappears from DBA_PRIV_AUDIT_OPTS:
 
SQL> select user_name, privilege
  2  from dba_priv_audit_opts
  3  /
 
no rows selected
 
SQL>
 
And connections by SYSTEM will no longer be recorded in SYS.AUD$:
 
SQL> select count(*) from sys.aud$
  2  /
 
  COUNT(*)
----------
         1
 
SQL> conn system/manager
Connected.
SQL> select count(*) from sys.aud$
  2  /
 
  COUNT(*)
----------
         1
 
SQL>
 
Unlike other objects owned by SYS, you are allowed to delete rows from SYS.AUD$.
 
SQL> delete sys.aud$
  2  /
 
1 row deleted.
 
SQL>
 
However, you should check first that it does not contain rows produced by your colleagues with separate audit requests!

Monday, March 19, 2012

How to List Chained Rows in a Partioned Table

I will be looking at partititioning and chaining elsewhere but this simple example shows how you can look for chained rows in a partitioned table. First create a partitioned table:

SQL> create table andrew
  2  partition by range (owner)
  3  (partition p1 values less than ('M'),
  4   partition p2 values less than (maxvalue))
  5  as select * from dba_tables
  6  /
 
Table created.
 
SQL> 

Then look for any chained rows in it. If a table is partitioned , you have 2 options. You can analyze the whole table or you can analzye individual partitions:

SQL> analyze table andrew list chained rows
  2  /
 
Table analyzed.
 
SQL> analyze table andrew partition(p1)
  2  list chained rows
  3  /
 
Table analyzed.
 
SQL> 

As you might expect, if you specify a partition which does not exist, you get an appropriate error message:

SQL> analyze table andrew partition(does_not_exist)
  2  list chained rows
  3  /
analyze table andrew partition(does_not_exist)
                               *
ERROR at line 1:
ORA-02149: Specified partition does not exist
 
SQL>

You also get an error if you try to use this syntax on a non-partitioned table:

SQL> create table fred
  2  as select * from dba_objects
  3  /
 
Table created.
 
SQL> analyze table fred
  2  partition(in_non_partitioned_table)
  3  list chained rows
  4  /
analyze table fred
              *
ERROR at line 1:
ORA-14501: object is not partitioned
 
SQL>