ORA-09817 error and full file systems

Let's say that you're trying to log into your database and suddenly got this error message:

oracle@mydb$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.1.0.7.0 - Production on Mon Feb 13 15:47:27 2012

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

ERROR:
ORA-09817: Write to audit file failed.
SVR4 Error: 28: No space left on device
ORA-01075: you are currently logged on


Enter user-name: 

oracle@mydb$ 

This is an easy problem to guess and the key is in the message No space left on device. The instance cannot write audit files because the file system is full so you have to figure out why is that file system full:

oracle@mydb$ set | grep ORA
ORACLE_BASE=/oracle
ORACLE_HOME=/oracle/orahome
ORACLE_SID=mydb

oracle@mydb$ strings $ORACLE_HOME/dbs/*mydb.ora | grep audit_file_dest
*.audit_file_dest='/oracle/admin/mydb/adump'

oracle@mydb$ df -k | egrep '(Filesystem)|(/oracle)'
Filesystem                 kbytes    used   avail capacity  Mounted on
/dev/vx/dsk/orafs/oracle 16327680 16327680       0   100%    /oracle

In this case the audit file destination is in the Oracle base file system, therefore the problem might be in other directory:

oracle@mydb$ du -k /oracle | sort -nr | head 
16035094        /oracle
8412062 /oracle/diag
8297523 /oracle/diag/rdbms/mydb/mydb
8297523 /oracle/diag/rdbms/mydb
8297523 /oracle/diag/rdbms
8250027 /oracle/diag/rdbms/mydb/mydb/trace
6926099 /oracle/orahome
1631834 /oracle/orahome/mydirectory
938085  /oracle/orahome/mydirectory/somefiles
727017  /oracle/orahome/bin

oracle@mydb$ du -ka /oracle/diag/rdbms/mydb/mydb/trace | sort -nr | head
8250027 /oracle/diag/rdbms/mydb/mydb/trace
5737533 /oracle/diag/rdbms/mydb/mydb/trace/mydb_ora_5198.trc
2506955 /oracle/diag/rdbms/mydb/mydb/trace/mydb_ora_18305.trc
1946    /oracle/diag/rdbms/mydb/mydb/trace/mydb_ora_5198.trm
969     /oracle/diag/rdbms/mydb/mydb/trace/mydb_ora_18305.trm
457     /oracle/diag/rdbms/mydb/mydb/trace/mydb_ora_3695.trc
380     /oracle/diag/rdbms/mydb/mydb/trace/alert_mydb.log
314     /oracle/diag/rdbms/mydb/mydb/trace/mydb_j002_8800.trc
314     /oracle/diag/rdbms/mydb/mydb/trace/mydb_j002_13295.trc
314     /oracle/diag/rdbms/mydb/mydb/trace/mydb_j000_23311.trc

Here is the problem: there are two very big trace files that occupy 8 gigabytes, half the file system size. When you know why your file system is full you have to decide what to do, like erasing, moving or compressing files, just be sure not to discard files you might need.

Creating partitioned tables and ORA-00922 error

There is a lot of different version of the Oracle Database software and even if the logic and operation of the database is more or less the same across versions, it is also more or less different across versions; for example, there is no drop database sentence in Oracle 9i.

So you work usually with 11g databases and love the tables partitioned by intervals, and you create one:

oracle@my11gdb~$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.3.0 Production on Wed Feb 8 12:43:42 2012

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> CREATE TABLE MY_TABLE
(   
SOMETEXT     VARCHAR2(4000),
MOREINFO     VARCHAR2(256),
ADATE        DATE
) PARTITION BY RANGE (ADATE)
INTERVAL (NUMTODSINTERVAL(1,'day'))
(partition p0 values less than (to_date('01-jan-2000','dd-mon-yyyy'))
);

Table created.

And you try to create that same table in a 10g database just to find that you can't:

oracle@my10gdb~$ sqlplus '/ as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Feb 8 12:46:15 2012

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> CREATE TABLE MY_TABLE
(   
SOMETEXT     VARCHAR2(4000),
MOREINFO     VARCHAR2(256),
ADATE        DATE
) PARTITION BY RANGE (ADATE)
INTERVAL (NUMTODSINTERVAL(1,'day'))
(partition p0 values less than (to_date('01-jan-2000','dd-mon-yyyy'))
);
INTERVAL (NUMTODSINTERVAL(1,'day'))
*
ERROR at line 7:
ORA-00922: missing or invalid option

Look no more: the ORA-00922 error in a 10g database means that you're trying to use a functionality that doesn't exist; in a 11g database it would mean missing brackets or syntax errors.

oracle@my11gdb~$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.3.0 Production on Wed Feb 8 12:50:54 2012

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> CREATE TABLE MY_TABLE
(   
SOMETEXT     VARCHAR2(4000),
MOREINFO     VARCHAR2(256),
ADATE        DATE
) PARTITION BY RANGE (ADATE)
INTERVAL (NUMTODSINTERVAL(1,'day'))
partition p0 values less than (to_date('01-jan-2000','dd-mon-yyyy'));
partition p0 values less than (to_date('01-jan-2000','dd-mon-yyyy'))
*
ERROR at line 8:
ORA-00922: missing or invalid option

More information:

CREATE TABLE (10g Release 2)
CREATE TABLE (11g Release 1)
Partition Administration

Adding numbers in a file

If you have a small file filled with numbers and are tasked to add all of them, you can do it with bc and paste:

cronos@olimpo:~$ cat file.txt 
1|11
2|22
3|33
4|44
5|55
|
6|66
7|77
8|88
9|99

cronos@olimpo:~$ cat file.txt | cut -d '|' -f 1 | grep -v '^$' | paste -sd '+' - | bc
45

With cut you select the field to sum, with grep you select out empty fields, with paste you add at the end of each line a + sign, and with bc you do the addition.

But if you have a very big file this won't work; you can instead use awk:

cronos@olimpo:~$ cat file.txt | awk 'BEGIN {FS="|";OFMT="%.2f"} {a+=$1;b+=$2} END {print "First field:",a,"Second field:",b}'

First field: 45 Second field: 495

With FS you set the field delimiter and with OFMT you set the number format, in this case two decimal places with no scientific notation. With awk a nice extra is to be able to add two or more columns at the same time.

More information:

Sum of numbers in file - UNIX alternatives