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>

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>

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.

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>

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>

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!

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>

XS$NULL

Oracle introduced a new user in version 11 called XS$NULL. It is for Oracle’s internal use only and you should leave it alone. For this reason it is locked and expired when it is created:

 

SQL> select account_status from dba_users

  2  where username = 'XS$NULL';

 

ACCOUNT_STATUS

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

EXPIRED & LOCKED

 

SQL>

 

You are advised not to alter this account in any way, even if an auditor asks you to. Oracle does not let you change its password:

 

SQL> conn / as sysdba

Connected.

SQL> alter user xs$null identified by new_pwd

  2  /

alter user xs$null identified by new_pwd

                                 *

ERROR at line 1:

ORA-01031: insufficient privileges

 

SQL>

 

Although you can do so with the password command:

 

SQL> select password from sys.user$

  2  where name = 'XS$NULL';

 

PASSWORD

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

DC4FCC8CB69A6733

 

SQL> password xs$null

Changing password for xs$null

New password:

Retype new password:

Password changed

SQL> select password from sys.user$

  2  where name = 'XS$NULL';

 

PASSWORD

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

C17AE3B0A14EA63F

 

SQL>

 

This is because of bug 12822989 and you must not do this.

Users Created Before the Database Existed

This example is from a version 9 database but I understand it also applies to later Oracle versions. There are several ways to create a database. One method is to copy datafiles from another database and create a new control file. If you do this, the user creation dates in the DBA_USERS view will come from the source database. They will therefore be before the creation date of the target database. You can see what I mean in the SQL below, which I ran in a copy of a production database. The source database was created in 2005 but the target database was not set up until 2012.

 

SQL> select min(created) from dba_users;

 

MIN(CREATED

-----------

15-JUN-2005

 

SQL> select created from v$database;

 

CREATED

-----------

13-MAR-2012

 

SQL>

 

This can be confusing when you first see it. I understand the same thing happens if you create a new database with dbca using a template.

Do Not Try This at Work

I ran this example on my home PC, which has Oracle 10 installed purely for testing and educational purposes. You should not try it at work, at least not if you want to keep your job. I started doing Oracle DBA work in the late 1990's after several years as a COBOL programmer. An electrician pulled the plug on one of our production servers. Once the power had been restored, I started the databases and raised a TAR with Oracle to ask if this had done any damage. I was told that Oracle would save anything which had been committed and roll back uncommitted transactions. This was fortunate as a few days later he did the same thing again. I decided to recreate this scenario for my blog. First I created a table, added 1527 rows and did a commit:

SQL> create table andrews_table
  2  as select * from dba_tables
  3  where 1 = 2
  4  /

Table created.

SQL> insert into andrews_table
  2  select * from dba_tables
  3  /

1527 rows created.

SQL> commit
  2  /

Commit complete.

SQL> select count(*) from andrews_table
  2  /

  COUNT(*)
----------
      1527

SQL>

I would expect these rows to be saved if the computer's power was switched off. Then I duplicated the table's contents:

SQL> insert into andrews_table
  2  select * from andrews_table
  3  /

1527 rows created.

SQL> select count(*) from andrews_table
  2  /

  COUNT(*)
----------
      3054

SQL>

I would not expect these new rows to be saved if the computer's power was switched off i.e.
the table should have 1527 rows, not 3054. Next I turned the power off and on and restarted the database:

[oracle@localhost crash_recovery]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Feb 26 18:54:46 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1218316 bytes
Variable Size              79694068 bytes
Database Buffers           83886080 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
SQL>

Looking in the alert log, Oracle did a crash recovery:

Sun Feb 26 18:55:00 2012
Beginning crash recovery of 1 threads
Sun Feb 26 18:55:00 2012
Started redo scan
Sun Feb 26 18:55:01 2012
Completed redo scan
 35 redo blocks read, 22 data blocks need recovery
Sun Feb 26 18:55:01 2012
Started redo application at
 Thread 1: logseq 48, block 5057
Sun Feb 26 18:55:01 2012
Recovery of Online Redo Log: Thread 1 Group 2 Seq 48 Reading mem 0
  Mem# 0 errs 0: /home/oracle/andrew/ANDREW/redo02.log
Sun Feb 26 18:55:01 2012
Completed redo application
Sun Feb 26 18:55:01 2012
Completed crash recovery at
 Thread 1: logseq 48, block 5092, scn 1087729
 22 data blocks read, 22 data blocks written, 35 redo blocks read

... and when I looked in the table, it had the expected number of rows:

SQL> select count(*) from andrews_table
  2  /

  COUNT(*)
----------
      1527

SQL>