Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog

June 22, 2008

Some Useful Oracle Database Utilities and Errorlogging Feature of 11g’s SQL*Plus

Filed under: Oracle 11g New Features,Oracle How To — H.Tonguç Yılmaz @ 4:12 pm

Some Oracle utilities are well documented in the Oracle Database Utilities Guide but some are not. Some are very familiar to us; exp, imp, expdp, impdp, sqlldr, dbverify, csscan, tnsping, lsnrctl, emctl, tkprof etc. But some may be not; trcroute, oradebug, dbshut, dbstart, oerr, orakill etc. Here are some simple examples I use frequently on Linux.


[oracle@tonghost bin]$ oerr ora 600
00600, 00000, "internal error code, arguments: [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s]"
// *Cause:  This is the generic internal error number for Oracle program
//         exceptions.  This indicated that a process encountered an
//         exceptional condition.
// *Action: Report as a bug - the first argument is the internal error number.

[oracle@tonghost bin]$ dbhome
/u01/app/oracle/product/11.1.0/db_1

[oracle@tonghost bin]$ dbfsize /u01/app/oracle/oradata/tong/example01.dbf

Database file: /u01/app/oracle/oradata/tong/example01.dbf
Database file type: file system
Database file size: 12800 8192 byte blocks

[oracle@tonghost bin]$ dbshut /u01/app/oracle/product/11.1.0/db_1
Processing Database instance "tong": log file /u01/app/oracle/product/11.1.0/db_1/shutdown.log

[oracle@tonghost bin]$ dbstart /u01/app/oracle/product/11.1.0/db_1
Processing Database instance "tong": log file /u01/app/oracle/product/11.1.0/db_1/startup.log
   

With 11g sql*plus’s errorlogging feature we can store any SQL, PL/SQL or SQL*Plus errors by default in table SPERRORLOG.


conn / as sysdba
create user tong identified by tong;
grant create session to tong;
grant unlimited tablespace to tong;
create table tong.tong_sperrorlog(username   varchar(256),                  
				timestamp  TIMESTAMP,                     
				script     varchar(1024),                 
				identifier varchar(256),                  
				message    CLOB,                          
				statement  CLOB);                         
                                                                           
connect tong/tong
set linesize 1200
show errorlogging
set errorlogging on table tong_sperrorlog   

create table tong ( c1 number ) ;

set errorlogging off

select timestamp, statement, message from tong_sperrorlog;

TIMESTAMP       22-JUN-08 07.00.27.000000 PM                                                                                                                                     
STATEMENT		create table tong ( c1 number )
MESSAGE			ORA-01031: insufficient privileges
   

This feature can be also used with 11g client against 9i and 10g servers. Check $ORACLE_HOME/sqlplus/doc/elgsetup.txt for details.

1 Comment »

  1. Also queries in SQL*Plus after 11g Release 1 support BLOB and BFILE columns. :)

    Comment by H.Tonguç Yılmaz — September 28, 2008 @ 9:23 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: