track ddl change (part 2)

I wrote about tracking ddl changes with a trigger there : track ddl changes

Another option is to use auditing.

A new and cool alternative is to use enable_ddl_logging (11gR2). This will track all ddl’s in the alert log

ALTER SYSTEM SET enable_ddl_logging=TRUE

Then later you issue

create table t(x number)

and you see in the alertLSC01.log

Tue Apr 05 14:43:32 2011
create table t(x number)

Wait, that’s not really verbose !?

Remember the alert log is just there for backward compatibility, it is time you start looking in the xml file 🙂


<msg time='2011-04-05T14:43:42.210+02:00' org_id='oracle' comp_id='rdbms'
 msg_id='opiexe:3937:4222333111' client_id='' type='NOTIFICATION'
 group='schema_ddl' level='16' host_id='srv01'
 host_addr='192.168.0.141' module='TOAD Beta 11.0.0.52' pid='2777799'>
 <txt>create table t(x number)
 </txt>
</msg>

There is not really much more there but the module, which indeed reveals someone is using TOAD to access my database !

my first ADR package

You got an internal error and want to create a zip of all relevant files.

First, let’s generate an internal error. I found a quick way to generate an ora-600 or an ora-700 (which is a harmless ora-600 in 11g, read 737878.1) on oradeblog

SQL> oradebug unit_test dbke_test dde_flow_kge_soft foo bar baz
Statement processed.

Now start the command line interface, and set the ORACLE HOME

$ adrcli
adrci> show home
ADR Homes: 
diag/tnslsnr/precision/listener
diag/tnslsnr/localhost/listener
diag/rdbms/lsc02/LSC02
diag/rdbms/lsc03/LSC03
diag/rdbms/lsc01/LSC01
adrci> set homepath diag/rdbms/lsc02/LSC02
adrci> show home
ADR Homes: 
diag/rdbms/lsc02/LSC02

Check the incidents :

adrci> show incident

ADR Home = /u01/app/oracle/diag/rdbms/lsc02/LSC02:
*************************************************************************
INCIDENT_ID          PROBLEM_KEY       CREATE_TIME                              
-------------------- ----------------- --------------------------------- 
53065                ORA 700 [foo]     2011-03-14 18:20:24 +01:00       
1 rows fetched

Create the package metadata :


adrci> IPS CREATE PACKAGE INCIDENT 53065
Created package 1 based on incident id 53065, correlation level typical
adrci> ips SHOW PACKAGE 1
DETAILS FOR PACKAGE 1:
   PACKAGE_ID             1
   PACKAGE_NAME           ORA700foo_20110314182607
   PACKAGE_DESCRIPTION    
   DRIVING_PROBLEM        1
   DRIVING_PROBLEM_KEY    ORA 700 [foo]
   DRIVING_INCIDENT       53065
   DRIVING_INCIDENT_TIME  2011-03-14 18:20:24.304000 +01:00
   STATUS                 New (0)
   CORRELATION_LEVEL      Typical (2)
   PROBLEMS               1 main problems, 0 correlated problems
   INCIDENTS              1 main incidents, 0 correlated incidents
   INCLUDED_FILES         4
   SEQUENCES              Last 0, last full 0, last base 0
   UNPACKED               FALSE
   CREATE_TIME            2011-03-14 18:26:07.566961 +01:00
   UPDATE_TIME            2011-03-14 18:26:07.620324 +01:00
   BEGIN_TIME             N/A
   END_TIME               N/A
   FLAGS                  0

The metadata files (in $ORACLE_BASE/rdbms/db_name/sid/*.ams) are in an Oracle binary format

Create the package zip file :

adrci> IPS GENERATE PACKAGE 1 in /home/lsc/foo
Generated package 1 in file /home/lsc/foo/ORA700foo_20110314182607_COM_1.zip, 
mode complete

This zip file contains all traces and alerts that you may ever need to diagnose/resolve the analysis

adrci>  ips show files package 1
   FILE_ID                1
   FILE_LOCATION          <ADR_HOME>/incident/incdir_53065
   FILE_NAME              LSC02_ora_14163_i53065.trm
   LAST_SEQUENCE          1
   EXCLUDE                Included

   FILE_ID                2
   FILE_LOCATION          <ADR_HOME>/incident/incdir_53065
   FILE_NAME              LSC02_ora_14163_i53065.trc
   LAST_SEQUENCE          1
   EXCLUDE                Included

   FILE_ID                3
   FILE_LOCATION          <ADR_HOME>/trace
   FILE_NAME              LSC02_ora_14163.trc
   LAST_SEQUENCE          1
   EXCLUDE                Included

   FILE_ID                4
   FILE_LOCATION          <ADR_HOME>/trace
   FILE_NAME              LSC02_ora_14163.trm
   LAST_SEQUENCE          1
   EXCLUDE                Included

   FILE_ID                5
   FILE_LOCATION          <ADR_HOME>/alert
   FILE_NAME              log.xml
   LAST_SEQUENCE          1
   EXCLUDE                Included

   FILE_ID                6
   FILE_LOCATION          <ADR_HOME>/trace
   FILE_NAME              alert_LSC02.log
   LAST_SEQUENCE          1
   EXCLUDE                Included

   FILE_ID                7
   FILE_LOCATION          <ADR_HOME>/trace
   FILE_NAME              LSC02_diag_5247.trc
   LAST_SEQUENCE          1
   EXCLUDE                Included

   FILE_ID                8
   FILE_LOCATION          <ADR_HOME>/trace
   FILE_NAME              LSC02_diag_5247.trm
   LAST_SEQUENCE          1
   EXCLUDE                Included

   FILE_ID                12
   FILE_LOCATION          <ADR_HOME>/trace
   FILE_NAME              LSC02_mmon_5265.trc
   LAST_SEQUENCE          1
   EXCLUDE                Included

   FILE_ID                13
   FILE_LOCATION          <ADR_HOME>/trace
   FILE_NAME              LSC02_mmon_5265.trm
   LAST_SEQUENCE          1
   EXCLUDE                Included

   FILE_ID                2007
   FILE_LOCATION          <ADR_HOME>/incpkg/pkg_1/seq_1/export
   FILE_NAME              IPS_CONFIGURATION.dmp
   LAST_SEQUENCE          1
   EXCLUDE                Included

   FILE_ID                2008
   FILE_LOCATION          <ADR_HOME>/incpkg/pkg_1/seq_1/export
   FILE_NAME              IPS_PACKAGE.dmp
   LAST_SEQUENCE          1
   EXCLUDE                Included

   FILE_ID                2009
   FILE_LOCATION          <ADR_HOME>/incpkg/pkg_1/seq_1/export
   FILE_NAME              IPS_PACKAGE_INCIDENT.dmp
   LAST_SEQUENCE          1
   EXCLUDE                Included

   FILE_ID                2010
   FILE_LOCATION          <ADR_HOME>/incpkg/pkg_1/seq_1/export
   FILE_NAME              IPS_PACKAGE_FILE.dmp
   LAST_SEQUENCE          1
   EXCLUDE                Included

   FILE_ID                2011
   FILE_LOCATION          <ADR_HOME>/incpkg/pkg_1/seq_1/export
   FILE_NAME              IPS_PACKAGE_HISTORY.dmp
   LAST_SEQUENCE          1
   EXCLUDE                Included

   FILE_ID                2012
   FILE_LOCATION          <ADR_HOME>/incpkg/pkg_1/seq_1/export
   FILE_NAME              IPS_FILE_METADATA.dmp
   LAST_SEQUENCE          1
   EXCLUDE                Included

   FILE_ID                2013
   FILE_LOCATION          <ADR_HOME>/incpkg/pkg_1/seq_1/export
   FILE_NAME              IPS_FILE_COPY_LOG.dmp
   LAST_SEQUENCE          1
   EXCLUDE                Included

   FILE_ID                2014
   FILE_LOCATION          <ADR_HOME>/incpkg/pkg_1/seq_1/export
   FILE_NAME              DDE_USER_ACTION_DEF.dmp
   LAST_SEQUENCE          1
   EXCLUDE                Included

   FILE_ID                2015
   FILE_LOCATION          <ADR_HOME>/incpkg/pkg_1/seq_1/export
   FILE_NAME              DDE_USER_ACTION_PARAMETER_DEF.dmp
   LAST_SEQUENCE          1
   EXCLUDE                Included

   FILE_ID                2016
   FILE_LOCATION          <ADR_HOME>/incpkg/pkg_1/seq_1/export
   FILE_NAME              DDE_USER_ACTION.dmp
   LAST_SEQUENCE          1
   EXCLUDE                Included

   FILE_ID                2017
   FILE_LOCATION          <ADR_HOME>/incpkg/pkg_1/seq_1/export
   FILE_NAME              DDE_USER_ACTION_PARAMETER.dmp
   LAST_SEQUENCE          1
   EXCLUDE                Included

   FILE_ID                2018
   FILE_LOCATION          <ADR_HOME>/incpkg/pkg_1/seq_1/export
   FILE_NAME              DDE_USER_INCIDENT_TYPE.dmp
   LAST_SEQUENCE          1
   EXCLUDE                Included

   FILE_ID                2019
   FILE_LOCATION          <ADR_HOME>/incpkg/pkg_1/seq_1/export
   FILE_NAME              DDE_USER_INCIDENT_ACTION_MAP.dmp
   LAST_SEQUENCE          1
   EXCLUDE                Included

   FILE_ID                2020
   FILE_LOCATION          <ADR_HOME>/incpkg/pkg_1/seq_1/export
   FILE_NAME              INCIDENT.dmp
   LAST_SEQUENCE          1
   EXCLUDE                Included

   FILE_ID                2021
   FILE_LOCATION          <ADR_HOME>/incpkg/pkg_1/seq_1/export
   FILE_NAME              INCCKEY.dmp
   LAST_SEQUENCE          1
   EXCLUDE                Included

   FILE_ID                2022
   FILE_LOCATION          <ADR_HOME>/incpkg/pkg_1/seq_1/export
   FILE_NAME              INCIDENT_FILE.dmp
   LAST_SEQUENCE          1
   EXCLUDE                Included

   FILE_ID                2023
   FILE_LOCATION          <ADR_HOME>/incpkg/pkg_1/seq_1/export
   FILE_NAME              PROBLEM.dmp
   LAST_SEQUENCE          1
   EXCLUDE                Included

   FILE_ID                2024
   FILE_LOCATION          <ADR_HOME>/incpkg/pkg_1/seq_1/export
   FILE_NAME              HM_RUN.dmp
   LAST_SEQUENCE          1
   EXCLUDE                Included

   FILE_ID                2025
   FILE_LOCATION          <ADR_HOME>/incpkg/pkg_1/seq_1/export
   FILE_NAME              EM_USER_ACTIVITY.dmp
   LAST_SEQUENCE          1
   EXCLUDE                Included

   FILE_ID                2026
   FILE_LOCATION          <ADR_HOME>/incpkg/pkg_1/seq_1
   FILE_NAME              config.xml
   LAST_SEQUENCE          1
   EXCLUDE                Included

   FILE_ID                2027
   FILE_LOCATION          <ADR_HOME>/incpkg/pkg_1/seq_1/crs
   FILE_NAME              crsdiag.log
   LAST_SEQUENCE          1
   EXCLUDE                Included

   FILE_ID                2028
   FILE_LOCATION          <ADR_HOME>/incpkg/pkg_1/seq_1
   FILE_NAME              metadata.xml
   LAST_SEQUENCE          1
   EXCLUDE                Included

   FILE_ID                2029
   FILE_LOCATION          <ADR_HOME>/incpkg/pkg_1/seq_1
   FILE_NAME              manifest_1_1.xml
   LAST_SEQUENCE          1
   EXCLUDE                Included

   FILE_ID                2030
   FILE_LOCATION          <ADR_HOME>/incpkg/pkg_1/seq_1
   FILE_NAME              manifest_1_1.html
   LAST_SEQUENCE          1
   EXCLUDE                Included

   FILE_ID                2031
   FILE_LOCATION          <ADR_HOME>/incpkg/pkg_1/seq_1
   FILE_NAME              manifest_1_1.txt
   LAST_SEQUENCE          1
   EXCLUDE                Included

Even an html file

Manifest for package 1

Manifest details

Package ID 1
Creation time 2011-03-14 18:26:07.566961 +01:00
Archive time 2011-03-14 18:37:14.499389 +01:00
Sequence 1
Package mode Complete
Package status Generating
Package flags Flags: (No flags set)

Contents summary

Main problems 1
Correlated problems 0
Main incidents 1
Correlated incidents 0

ADR details

Product rdbms
Target lsc02
Instance LSC02
ADR base /u01/app/oracle
ADR home /u01/app/oracle/diag/rdbms/lsc02/LSC02

Main problems

Problem ID Problem key Incidents included Incidents total
1 ORA 700 [foo] 1 1

Correlated problems

Problem ID Problem key Incidents included Incidents total

Main incidents

Incident ID Problem ID Error Message Incident time
53065 1 ORA-700 [foo] [bar] [baz] 2011-03-14 18:20:24.304000 +01:00

Correlated incidents

Incident ID Problem ID Error Message Incident time

Files

File name Location Size File time
LSC02_ora_14163_i53065.trm <ADR_HOME>/incident/incdir_53065 54828 2011-03-14 18:20:26.000000 +01:00
LSC02_ora_14163_i53065.trc <ADR_HOME>/incident/incdir_53065 2433968 2011-03-14 18:20:26.000000 +01:00
LSC02_ora_14163.trc <ADR_HOME>/trace 1308 2011-03-14 18:20:26.000000 +01:00
LSC02_ora_14163.trm <ADR_HOME>/trace 210 2011-03-14 18:20:24.000000 +01:00
log.xml <ADR_HOME>/alert 885849 2011-03-14 18:20:27.000000 +01:00
alert_LSC02.log <ADR_HOME>/trace 164969 2011-03-14 18:20:27.000000 +01:00
LSC02_diag_5247.trc <ADR_HOME>/trace 1287 2011-03-14 18:20:26.000000 +01:00
LSC02_diag_5247.trm <ADR_HOME>/trace 77 2011-03-14 18:20:26.000000 +01:00
LSC02_mmon_5265.trc <ADR_HOME>/trace 8703 2011-03-14 18:33:43.000000 +01:00
LSC02_mmon_5265.trm <ADR_HOME>/trace 838 2011-03-14 18:33:43.000000 +01:00
IPS_CONFIGURATION.dmp <ADR_HOME>/incpkg/pkg_1/seq_1/export 2818 2011-03-14 18:37:13.000000 +01:00
IPS_PACKAGE.dmp <ADR_HOME>/incpkg/pkg_1/seq_1/export 476 2011-03-14 18:37:13.000000 +01:00
IPS_PACKAGE_INCIDENT.dmp <ADR_HOME>/incpkg/pkg_1/seq_1/export 193 2011-03-14 18:37:13.000000 +01:00
IPS_PACKAGE_FILE.dmp <ADR_HOME>/incpkg/pkg_1/seq_1/export 1126 2011-03-14 18:37:14.000000 +01:00
IPS_PACKAGE_HISTORY.dmp <ADR_HOME>/incpkg/pkg_1/seq_1/export 280 2011-03-14 18:37:13.000000 +01:00
IPS_FILE_METADATA.dmp <ADR_HOME>/incpkg/pkg_1/seq_1/export 2888 2011-03-14 18:37:14.000000 +01:00
IPS_FILE_COPY_LOG.dmp <ADR_HOME>/incpkg/pkg_1/seq_1/export 214 2011-03-14 18:37:14.000000 +01:00
DDE_USER_ACTION_DEF.dmp <ADR_HOME>/incpkg/pkg_1/seq_1/export 908 2011-03-14 18:37:13.000000 +01:00
DDE_USER_ACTION_PARAMETER_DEF.dmp <ADR_HOME>/incpkg/pkg_1/seq_1/export 708 2011-03-14 18:37:13.000000 +01:00
DDE_USER_ACTION.dmp <ADR_HOME>/incpkg/pkg_1/seq_1/export 204 2011-03-14 18:37:13.000000 +01:00
DDE_USER_ACTION_PARAMETER.dmp <ADR_HOME>/incpkg/pkg_1/seq_1/export 198 2011-03-14 18:37:13.000000 +01:00
DDE_USER_INCIDENT_TYPE.dmp <ADR_HOME>/incpkg/pkg_1/seq_1/export 353 2011-03-14 18:37:13.000000 +01:00
DDE_USER_INCIDENT_ACTION_MAP.dmp <ADR_HOME>/incpkg/pkg_1/seq_1/export 166 2011-03-14 18:37:13.000000 +01:00
INCIDENT.dmp <ADR_HOME>/incpkg/pkg_1/seq_1/export 700 2011-03-14 18:37:13.000000 +01:00
INCCKEY.dmp <ADR_HOME>/incpkg/pkg_1/seq_1/export 303 2011-03-14 18:37:13.000000 +01:00
INCIDENT_FILE.dmp <ADR_HOME>/incpkg/pkg_1/seq_1/export 268 2011-03-14 18:37:13.000000 +01:00
PROBLEM.dmp <ADR_HOME>/incpkg/pkg_1/seq_1/export 395 2011-03-14 18:37:13.000000 +01:00
HM_RUN.dmp <ADR_HOME>/incpkg/pkg_1/seq_1/export 342 2011-03-14 18:37:14.000000 +01:00
EM_USER_ACTIVITY.dmp <ADR_HOME>/incpkg/pkg_1/seq_1/export 207 2011-03-14 18:37:14.000000 +01:00
config.xml <ADR_HOME>/incpkg/pkg_1/seq_1 56180 2011-03-14 18:37:14.000000 +01:00
crsdiag.log <ADR_HOME>/incpkg/pkg_1/seq_1/crs 184 2011-03-14 18:37:14.000000 +01:00
metadata.xml <ADR_HOME>/incpkg/pkg_1/seq_1 556 2011-03-14 18:37:14.000000 +01:00

But did Oracle Support ever asked you for an ADR package? Or do they still ask for RDA

I used to select, zip and send traces files manually, I may consider ADR packages by my next ORA-600 !

List events in session, process or system

There is a new command in 11g to display the current events, which is oradebug eventdump.

For instance :

SQL> alter session set events '10046 trace name context forever,level 12:942 trace name ERRORSTACK level 3';

SQL> oradebug setmypid
Statement processed.
SQL> oradebug eventdump session
sql_trace level=12
942 trace name ERRORSTACK level 3

Read metalink note 436036.1

In 10g and before, the command was oradebug dump events 1 and the list was dumped in a trace file, 11g directly outputs to the console.

Note there is no backward compatibility with unsupported tools like oradebug.
In 11g you will get an ORA-76 with dump events

SQL> oradebug setmypid
Statement processed.
SQL> oradebug dump events 1
ORA-00076: dump EVENTS not found
$ oerr ora 76
00076, 00000, "dump %s not found"
// *Cause:  An attempt was made to invoke a dump that does not exist.
// *Action: Type DUMPLIST to see the list of available dumps.

Which index can you rebuild?

I recently wrote on table reorg and rebuild index

Rule number one : you cannot rebuild a partitioned index in whole. You need to rebuild each individual (sub-)partition

Rule number two : to rebuild an iot, move the table instead of trying to rebuild the underlying index

Rule number three : a LOB index is not really an index. Do not rebuild this

Rule number four : a NOSEGMENT index is not a supported type of index, but it may appear in your user_objects list. It is used internally by OEM and other tuning tools to do a what-if calculation on the explain plan. It is not listed in USER_INDEXES. Do not rebuild this

Test case :


SQL> CREATE CLUSTER c(x NUMBER);

Cluster created.

SQL> CREATE INDEX a01
  2    ON CLUSTER c;

Index created.

SQL> CREATE TABLE t
  2  (
  3    p     NUMBER PRIMARY KEY,
  4    a01   NUMBER,
  5    a02   NUMBER,
  6    a03   NUMBER,
  7    a04   NUMBER,
  8    a05   NUMBER,
  9    a06   NUMBER,
 10    a07   VARCHAR2 (40),
 11    a08   CLOB
 12  );

Table created.

SQL> CREATE INDEX a02
  2    ON t (a01);

Index created.

SQL> CREATE INDEX a03
  2    ON t (a02)
  3    REVERSE;

Index created.

SQL> CREATE INDEX a04
  2    ON t (SQRT (a01));

Index created.

SQL> CREATE INDEX a05
  2    ON t (COS (a01))
  3    REVERSE;

Index created.

SQL> CREATE BITMAP INDEX a06
  2    ON t (a03);

Index created.

SQL> CREATE BITMAP INDEX a07
  2    ON t (SIGN (a04));

Index created.

SQL> CREATE INDEX a08
  2    ON t (a07)
  3    INDEXTYPE IS ctxsys.context;

Index created.

SQL> CREATE INDEX a09
  2    ON t (a05)
  3    GLOBAL PARTITION BY HASH (a05)
  4       (PARTITION p);

Index created.

SQL> CREATE TABLE i (x NUMBER CONSTRAINT A10 PRIMARY KEY)
  2  ORGANIZATION INDEX;

Table created.

SQL> CREATE INDEX A11 on T(A06) NOSEGMENT;

Index created.

SQL>   SELECT index_name,
  2          index_type,
  3          partitioned,
  4          generated
  5     FROM user_indexes
  6  ORDER BY 1;

INDEX_NAME                     INDEX_TYPE                  PAR G
------------------------------ --------------------------- --- -
A01                            CLUSTER                     NO  N
A02                            NORMAL                      NO  N
A03                            NORMAL/REV                  NO  N
A04                            FUNCTION-BASED NORMAL       NO  N
A05                            FUNCTION-BASED NORMAL/REV   NO  N
A06                            BITMAP                      NO  N
A07                            FUNCTION-BASED BITMAP       NO  N
A08                            DOMAIN                      NO  N
A09                            NORMAL                      YES N
A10                            IOT - TOP                   NO  N
DR$A08$X                       NORMAL                      NO  N
SYS_C009276                    NORMAL                      NO  Y
SYS_IL0000028076C00009$$       LOB                         NO  Y
SYS_IL0000028087C00006$$       LOB                         NO  Y
SYS_IL0000028092C00002$$       LOB                         NO  Y
SYS_IOT_TOP_28090              IOT - TOP                   NO  Y
SYS_IOT_TOP_28095              IOT - TOP                   NO  Y

SQL> ALTER INDEX a01 REBUILD;

Index altered.

SQL> ALTER INDEX a02 REBUILD;

Index altered.

SQL> ALTER INDEX a03 REBUILD;

Index altered.

SQL> ALTER INDEX a04 REBUILD;

Index altered.

SQL> ALTER INDEX a05 REBUILD;

Index altered.

SQL> ALTER INDEX a06 REBUILD;

Index altered.

SQL> ALTER INDEX a07 REBUILD;

Index altered.

SQL> ALTER INDEX a08 REBUILD;

Index altered.

SQL> ALTER INDEX a09 REBUILD;
ALTER INDEX a09 REBUILD
            *
ERROR at line 1:
ORA-14086: a partitioned index may not be rebuilt as a whole

SQL> ALTER INDEX a09 REBUILD PARTITION P;

Index altered.

SQL> ALTER INDEX a10 REBUILD;
ALTER INDEX a10 REBUILD
*
ERROR at line 1:
ORA-28650: Primary index on an IOT cannot be rebuilt

SQL> ALTER TABLE i MOVE;

Table altered.

SQL> ALTER INDEX A11 REBUILD;
ALTER INDEX A11 REBUILD
*
ERROR at line 1:
ORA-08114: can not alter a fake index

SQL> ALTER INDEX SYS_IL0000028076C00009$$ REBUILD;
ALTER INDEX SYS_IL0000028076C00009$$ REBUILD
*
ERROR at line 1:
ORA-02327: cannot create index on expression with datatype LOB

A function-based domain index should be rebuildable too, I have not tested this for you

EZCONNECT and HOSTNAME resolution methods

EZCONNECT is the easy connect protocol, available in 10g, whenever you want to connect to a database without tnsnames and without ldap.

$ grep -iw directory_path $TNS_ADMIN/sqlnet.ora
names.directory_path=EZCONNECT
$ sqlplus scott/tiger@//srv01:1521/db01

connect to server srv01 on port 1521 for service db01

HOSTNAME was the old-fashion way to connect to a database, where hostname = sid and port = 1521. In this regard EZCONNECT is just an extension of the hostname method.

Typical HOSTNAME usage, that is the same as EZCONNECT with default port 1521.
sqlplus scott/tiger@db01
connect to server db01 on port 1521 for service db01

There is a behavior change between 10g and 11g. In 10g, the default service name defaulted to the DNS alias used to connect. In 11g, the default is null.

$ nslookup db01
Server:  ns001.example.com
Address:  198.0.0.30

Name:    srv01.example.com
Address:  198.0.0.60
Aliases:  db01.example.com

$ nslookup db02
Server:  ns001.example.com
Address:  198.0.0.30

Name:    srv01.example.com
Address:  198.0.0.60
Aliases:  db02.example.com

Both DB01 and DB02 DNS aliases point to the same server.

Let’s try with 10g

$ sqlplus -L scott/tiger@db01.example.com

SQL*Plus: Release 10.2.0.3.0 - Production on Mon Feb 7 15:46:53 2011

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

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

SQL> select global_name from global_name;
GLOBAL_NAME
---------------------------------------
DB01.EXAMPLE.COM
SQL> quit
$ sqlplus -L scott/tiger@db02.example.com

SQL*Plus: Release 10.2.0.3.0 - Production on Mon Feb 7 15:47:33 2011

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

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

SQL> select global_name from global_name;
GLOBAL_NAME
---------------------------------------
DB02.EXAMPLE.COM

Let’s try with 11g sqlplus

$ sqlplus -L scott/tiger@db01.example.com

SQL*Plus: Release 11.2.0.2.0 Production on Mon Feb 7 15:50:27 2011

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

ERROR:
ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

SP2-0751: Unable to connect to Oracle.  Exiting SQL*Plus

It no longer works. Period. This is documented as Problem 556996.1 in Metalink.

A 10g tnsping will reveal

$ tnsping db01.example.com:1521

TNS Ping Utility for IBM/AIX RISC System/6000: Version 10.2.0.3.0 - Production on 07-FEB-2011 15:52:34

Copyright (c) 1997, 2006, Oracle.  All rights reserved.

Used parameter files:
/home/lsc/sqlnet.ora

Used HOSTNAME adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=db01.example.com))(ADDRESS=(PROTOCOL=TCP)(HOST=198.0.0.60)(PORT=1521)))
OK (80 msec)

In 10g the service_name is the connection dns alias used

In contrary, the 11g tnsping service name is null

$ tnsping db01.example.com:1521

TNS Ping Utility for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production on 07-FEB-2011 15:56:55

Copyright (c) 1997, 2010, Oracle.  All rights reserved.

Used parameter files:
/home/lsc/sqlnet.ora

Used HOSTNAME adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=198.0.0.60)(PORT=1521)))
OK (10 msec)

The tnsping works, but the service_name is empty.

How to fix this?

1) you specify the SID in easy connect (yes, this is easy!)

$ tnsping db01.example.com:1521/db01.example.com

TNS Ping Utility for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production on 07-FEB-2011 15:59:10

Copyright (c) 1997, 2010, Oracle.  All rights reserved.

Used parameter files:
/home/lsc/sqlnet.ora

Used HOSTNAME adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=db01.example.com))(ADDRESS=(PROTOCOL=TCP)(HOST=198.0.0.60)(PORT=1521)))
OK (10 msec)

2) you use 10g, or 10g behavior in 11g with patch 9271246 (available only on a limited number of plateforms, os and db versions),

3) you specify a default service for your listener

$ vi listener.ora
DEFAULT_SERVER_LISTENER=DB01
$ lsnrctl reload
$ sqlplus -L scott/tiger@db01 

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

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

SQL> 

This is a bit confusing as if you are servicing more than one database per listener, all dns aliases will default to the same database. So I would not recommend a default service name if there is more than one service name.

Return NULL if the column does not exist

It is a very common challenge for a dba to create scripts that work on every version.

How do you return NULL if a column does not exists?

Imagine I have a view that returns the table_name, column_name and retention_type of my LOBS.


SQL> create table t1(c clob) lob(c) store as (retention);

Table created.

SQL> create table t2(c clob) lob(c) store as (pctversion 10);

Table created.

SQL> create or replace force view v as select table_name,
  column_name,retention_type from user_lobs;

View created.

SQL> select * from v where table_name in ('T1','T2');
TAB COL RETENTION_TYPE
--- --- --------------
T1  C   YES
T2  C   NO

Let’s imagine I try to run this on an antique version of Oracle


SQL> select version from v$instance;
VERSION
-----------------
11.2.0.1.0

SQL> create table t1(c clob) lob(c) store as (retention);

Table created.

SQL> create table t2(c clob) lob(c) store as (pctversion 10);

Table created.

SQL> create or replace force view v as select table_name,column_name,retention_type from user_lobs;

Warning: View created with compilation errors.

SQL> select * from v where table_name in ('T1','T2');
select * from v where table_name in ('T1','T2')
              *
ERROR at line 1:
ORA-04063: view "SCOTT.V" has errors

Obviously the RETENTION_TYPE did not exist in that version.

Let’s default this to NULL !


SQL> create or replace function retention_type return varchar2 is 
  begin return null; end;
/

Function created.

SQL> select * from v where table_name in ('T1','T2');
TAB COL RETENTION_TYPE
--- --- --------------
T1  C
T2  C

Very simple workaround, is not it?

On table reorg and index rebuild

Before you start reading : do not rebuild all your indexes and reorganize all your tables every Sunday morning. One day you may find one of your table missing or one index invalid.

Ok, let’s take a case where table reorg and index rebuild is good.

One of your table was never cleaned up, it grew to 100000000 rows over the last 5 years and you need only the last 2 weeks.

One of your task will be to create a job to clean up your table on a weekly basis to delete rows older than 14 days. This is beyond the scope of this post.

Now you have deleted more than 99% of your rows and you want to reorganize your table and rebuild the index, to gain disk space and performance.

Here is the demo


SQL> DROP TABLE t1;

Table dropped.

SQL> 
SQL> CREATE TABLE t1
  2  (
  3    r     NUMBER,
  4    txt   VARCHAR2 (4000),
  5    y     NUMBER
  6  );

Table created.

SQL> 
SQL> CREATE INDEX i1
  2    ON t1 (r);

Index created.

SQL> 
SQL> INSERT INTO t1
  2    WITH t
  3         AS (    SELECT *
  4             FROM DUAL
  5       CONNECT BY LEVEL < 1001)
  6    SELECT ROWNUM r, LPAD ('X', 100, '.') txt, MOD (ROWNUM, 2) y
  7      FROM t, t;

1000000 rows created.

SQL> 
SQL> DROP TABLE t2;

Table dropped.

SQL> 
SQL> CREATE TABLE t2
  2  (
  3    r     NUMBER,
  4    txt   VARCHAR2 (4000),
  5    y     NUMBER
  6  )
  7  PARTITION BY HASH (r)
  8    (PARTITION T2_P1);

Table created.

SQL> 
SQL> CREATE INDEX i2
  2    ON t2 (r)
  3    LOCAL (PARTITION i2_p1);

Index created.

SQL> 
SQL> INSERT INTO t2
  2    WITH t
  3         AS (    SELECT *
  4             FROM DUAL
  5       CONNECT BY LEVEL < 1001)
  6    SELECT ROWNUM r, LPAD ('X', 100, '.') txt, MOD (ROWNUM, 2) y
  7      FROM t, t;

1000000 rows created.

SQL> 
SQL> DROP TABLE t3;

Table dropped.

SQL> 
SQL> CREATE TABLE t3
  2  (
  3    r     NUMBER,
  4    txt   VARCHAR2 (4000),
  5    y     NUMBER
  6  )
  7  PARTITION BY RANGE (r)
  8    SUBPARTITION BY HASH (r)
  9       SUBPARTITION TEMPLATE (SUBPARTITION s1 )
 10    (PARTITION T3_P1 VALUES LESS THAN (maxvalue));

Table created.

SQL> 
SQL> CREATE INDEX i3
  2    ON t3 (r)
  3    LOCAL (PARTITION i3_p1
  4        (SUBPARTITION i3_p1_s1));

Index created.

SQL> 
SQL> INSERT INTO t3
  2    WITH t
  3         AS (    SELECT *
  4             FROM DUAL
  5       CONNECT BY LEVEL < 1001)
  6    SELECT ROWNUM r, LPAD ('X', 100, '.') txt, MOD (ROWNUM, 2) y
  7      FROM t, t;

1000000 rows created.

SQL> 
SQL> COMMIT;

Commit complete.

SQL> 
SQL>  SELECT segment_name,
  2          segment_type,
  3          partition_name,
  4          sum(bytes),
  5          count(*)
  6     FROM user_extents
  7    WHERE segment_name IN ('T1', 'T2', 'T3', 'I1', 'I2', 'I3')
  8  group by
  9    segment_name,
 10          segment_type,
 11          partition_name
 12  ORDER BY segment_name, partition_name;

SEGMENT_NA SEGMENT_TYPE       PARTITION_     SUM(BYTES)       COUNT(*)
---------- ------------------ ---------- -------------- --------------
I1         INDEX                             16,777,216             31
I2         INDEX PARTITION    I2_P1          16,777,216             31
I3         INDEX SUBPARTITION I3_P1_S1       16,777,216             31
T1         TABLE                            134,217,728             87
T2         TABLE PARTITION    T2_P1         134,217,728             16
T3         TABLE SUBPARTITION T3_P1_S1      134,217,728             16

I created 3 tables, T1, T2 which is partitioned, T3 which is subpartitioned. There is a slight difference in the number of extents between partitioned and non-partitioned table, but this ASSM, so it is fine.


SQL> DELETE FROM t1
  2       WHERE r > 1;

999999 rows deleted.

SQL> 
SQL> COMMIT;

Commit complete.

SQL> 
SQL> DELETE FROM t2
  2       WHERE r > 1;

999999 rows deleted.

SQL> 
SQL> COMMIT;

Commit complete.

SQL> 
SQL> DELETE FROM t3
  2       WHERE r > 1;

999999 rows deleted.

SQL> 
SQL> COMMIT;

Commit complete.

SQL> 
SQL>  SELECT segment_name,
  2          segment_type,
  3          partition_name,
  4          sum(bytes),
  5          count(*)
  6     FROM user_extents
  7    WHERE segment_name IN ('T1', 'T2', 'T3', 'I1', 'I2', 'I3')
  8  group by
  9    segment_name,
 10          segment_type,
 11          partition_name
 12  ORDER BY segment_name, partition_name;

SEGMENT_NA SEGMENT_TYPE       PARTITION_     SUM(BYTES)       COUNT(*)
---------- ------------------ ---------- -------------- --------------
I1         INDEX                             16,777,216             31
I2         INDEX PARTITION    I2_P1          16,777,216             31
I3         INDEX SUBPARTITION I3_P1_S1       16,777,216             31
T1         TABLE                            134,217,728             87
T2         TABLE PARTITION    T2_P1         134,217,728             16
T3         TABLE SUBPARTITION T3_P1_S1      134,217,728             16

I deleted the completed table but one row, however the size of the table and the number of extents did not change.


SQL> ALTER TABLE t1 MOVE;

Table altered.

SQL> 
SQL> ALTER INDEX I1 REBUILD;

Index altered.

SQL> 
SQL> ALTER TABLE t2 MOVE PARTITION T2_P1;

Table altered.

SQL> 
SQL> ALTER INDEX I2 REBUILD PARTITION I2_P1;

Index altered.

SQL> 
SQL> ALTER TABLE t3 MOVE SUBPARTITION T3_P1_S1;

Table altered.

SQL> 
SQL> ALTER INDEX I3 REBUILD SUBPARTITION I3_P1_S1;

Index altered.

SQL> 
SQL>  SELECT segment_name,
  2          segment_type,
  3          partition_name,
  4          sum(bytes),
  5          count(*)
  6     FROM user_extents
  7    WHERE segment_name IN ('T1', 'T2', 'T3', 'I1', 'I2', 'I3')
  8  group by
  9    segment_name,
 10          segment_type,
 11          partition_name
 12  ORDER BY segment_name, partition_name;

SEGMENT_NA SEGMENT_TYPE       PARTITION_     SUM(BYTES)       COUNT(*)
---------- ------------------ ---------- -------------- --------------
I1         INDEX                                 65,536              1
I2         INDEX PARTITION    I2_P1              65,536              1
I3         INDEX SUBPARTITION I3_P1_S1           65,536              1
T1         TABLE                                 65,536              1
T2         TABLE PARTITION    T2_P1           8,388,608              1
T3         TABLE SUBPARTITION T3_P1_S1        8,388,608              1

Now I have reorganized my tables and rebuilt my indexes.

The sized dropped to 64K or 8M and the fragmentation disappeard as the number of extents dropped to 1.

Note you cannot rebuild a whole partitioned index (ORA-14086) nor reorganize a whole partitioned table (ORA-14511). You need to loop through each partition or subpartition.

EXECUTE IMMEDIATE ‘SELECT’ does not execute anything

I am not sure whether some tuning guy at Oracle decided to ignore any SELECT statement after execute immediate to save time doing nothing.

exec execute immediate 'select 1/0 from dual connect by level<9999999999999'

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00

But it is really annoying… and not documented as far as I know.

Imagine I want to increase all my sequences by 1000


SQL> create sequence s;

Sequence created.

SQL> select s.nextval from dual;

   NEXTVAL
----------
         1

SQL> begin
  2    for f in (select sequence_name n from user_sequences)
  3    loop
  4      execute immediate
  5        'select '||f.n||'.nextval from dual connect by level<=1000';
  6    end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> select s.currval from dual;

   CURRVAL
----------
         1

Hmm, it does not work. Does SELECT work at all? Yes when it is a SELECT INTO 🙂


SQL> drop sequence s;

Sequence dropped.

SQL> create sequence s;

Sequence created.

SQL> select s.nextval from dual;

   NEXTVAL
----------
         1

SQL> declare
  2    type t is table of number index by pls_integer;
  3    c t;
  4  begin
  5    for f in (select sequence_name n from user_sequences)
  6    loop
  7      execute immediate
  8        'select '||f.n||'.nextval from dual connect by level<=1000'
  9        bulk collect into c;
 10    end loop;
 11  end;
 12  /

PL/SQL procedure successfully completed.

SQL> select s.currval from dual;

   CURRVAL
----------
      1001

I wonder in which version this optimization/bug was introduced…

RMAN duplicate does change your DB_NAME !

I had a very serious issue last Friday with errors as weird as ORA-00322: log name of thread num is not current copy. After a clone from Prod to Test, the prod crashed. Both databases are located on the same server (I am not a virtualization fanatic) and clone from prod to test have been done by most of my dba readers.

What did change in 11g ?

Incredibly, in 11g, rman issues the following statement before restore

sql clone "alter system set  db_name = ''PROD'' ...
restore clone primary controlfile...

This is probably related to the capability of cloning a database without connecting to the target database.

At the end of the clone, rman is setting back the db_name to TEST and recreate the TEST controlfile

sql statement: alter system set  db_name = ''TEST'' ...
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "TEST" ...
...
LOGFILE
GROUP 1 ('/.../TEST/redo1.dbf')...

So what’s wrong with this? Howcome could a clone from prod to test screw up the prod db???

Simple, the RMAN job did not complete

1) set new name, restore prod controlfile to test
2) restore issue, for instance ORA-19870: error while restoring backup piece archive1234
3) RMAN-03002: failure of Duplicate Db command

At this point, the complete restore was finished, we restored the missing archivelog, recover and open resetlog.
What happened then???
At this point, remember you still have the prod controlfile (and the prod db_name), so by doing an alter resetlogs, the production redologs will get overwritten without notice !

This is a fairly important change that could really hurt if you are cloning two databases on the same server.

In case you are trying to save a failed database clone, make sure you check db_name and also v$logfile before doing an alter database resetlogs!!!

How to solve ORA-4068

I was amazed by this oneliner in stackoverflow.

First, let me introduce you my old foe, ORA-04068 :
Session 1:

SQL> CREATE OR REPLACE PACKAGE P AS 
  2  X NUMBER;Y NUMBER;END;
  3  /

Package created.

SQL> exec P.X := 1

PL/SQL procedure successfully completed.

Session 2:

SQL> CREATE OR REPLACE PACKAGE P AS 
  2  X NUMBER;Z NUMBER;END;
  3  /

Package created.

Session 1:

SQL> exec P.X := 2
BEGIN P.X := 2; END;

*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package "SCOTT.P" has been invalidated
ORA-04065: not executed, altered or dropped package "SCOTT.P"
ORA-06508: PL/SQL: could not find program unit being called: "SCOTT.P"
ORA-06512: at line 1

Changing the package in session 2 did invalidate the package variable in session 1.

And the PRAGMA that saves the world : PRAGMA SERIALLY_REUSABLE

Session 1:

SQL> CREATE OR REPLACE PACKAGE P AS 
  2  PRAGMA SERIALLY_REUSABLE;X NUMBER;Y NUMBER;END;
  3  /

Package created.

SQL> exec P.X := 1

PL/SQL procedure successfully completed.

Session 2:

SQL> CREATE OR REPLACE PACKAGE P AS 
  3  PRAGMA SERIALLY_REUSABLE;X NUMBER;Z NUMBER;END;
  2  /

Package created.

Session 1:

SQL> exec P.X := 2

PL/SQL procedure successfully completed.

Oh yes!

make count(*) faster

I just install Oracle Enterprise Linux on my new notebook.

I wanted to check how far could I improve the performance of a count(*)


SQL> drop table big_emp;

table big_emp dropped.
258ms elapsed

SQL> create table big_emp as 
  with l as(select 1 from dual connect by level<=3000) 
  select rownum empno,ename,job,mgr,hiredate,sal,comm,deptno from emp,l,l

table big_emp created.
330,390ms elapsed

SQL> alter table big_emp add primary key(empno)

table big_emp altered.
481,503ms elapsed

SQL> alter system flush buffer_cache

system flush altered.
2,701ms elapsed

SQL> alter system flush shared_pool
system flush altered.
137ms elapsed

SQL> select count(*) from big_emp
COUNT(*)               
---------------------- 
126000000              

9,769ms elapsed

SQL> select count(*) from big_emp
COUNT(*)               
---------------------- 
126000000              

8,157ms elapsed

SQL> alter table big_emp drop primary key

table big_emp altered.
905ms elapsed

SQL> alter table big_emp add primary key(empno) 
  using index (
    create index big_i on big_emp(empno) 
    global partition by hash(empno) 
    partitions 16 parallel 16)

table big_emp altered.
974,300ms elapsed

SQL> alter system flush buffer_cache

system flush altered.
601ms elapsed

SQL> alter system flush shared_pool

system flush altered.
140ms elapsed

SQL> select count(*) from big_emp

COUNT(*)               
---------------------- 
126000000              

5,201ms elapsed

SQL> select count(*) from big_emp

COUNT(*)               
---------------------- 
126000000              

2,958ms elapsed

As it is on a notebook, I suppose the benefit of partitioning is not as good as you could get on your server with lots of fast disks and lot’s of CPUs, but I am pretty happy with the results.

It is still counting 126 Million rows in less than 3 seconds 🙂

Thanks for the very kind sponsor of the notebook !

This version of TOAD provides READONLY access!

This Toad 11 beta new feature made me so happy !

Not only you can specify a color for your connection (ex: red=prod, green=devl), and this color is much more visible than it was in previous version of TOAD, but now you can set your connection to be read-only.

For any reason, bored, ill, under pressure, tired, you may screw up your database one of those days.

I feel so good that I can now setup my prod connection to be read-only. It is REALLY cool.

Setting the connection read-only is a TOAD feature. As it is in beta it may not be 100% bullet-proof. Mostly it allows you to do only SELECT in the editor, even if you are logged in as SYSTEM or SYS.

Anything comparable in SQL Developer?

On FAILED_LOGIN_ATTEMPTS

I wrote about the new defaults in 10gR2 more than 5 years ago
FAILED_LOGIN_ATTEMPTS default to 10 in 10gR2

This new DEFAULT increases the security by preventing the users from trying millions of different passwords. However the locking of application users is noticeably decreasing the database availability 🙁

My recommendation :
– create a separate profile for the application users with high availabilty requirement with a default of 1000
– add 2 characters to the password of those accounts


SQL> create user u identified by a4sec2pw;
User created.
SQL> grant create session to u;
Grant succeeded.

“U” is a critical user in your application where account locking would mean downtime !

Let’s try to make the schema 10 times more secure and 100 times more available :


SQL> create profile failed1000 limit failed_login_attempts 1000;
Profile created.
SQL> alter user u identified by a4sec2pwx1 profile failed1000;
User altered.

OK?

SQL developer for dba

Historically and semantically, SQL Developer is a developer tool. There is very little comparison with TOAD for the dba.

But… Oracle is working on this! In the latest release, 3.0 EA1, you will be able to see Tablespaces, Redo logs and controlfile, finally.

Still no session browser, but I am delighted that the database administrators are not getting forgotten !

Thanks Surachart for spreading the news!

How to check if I have a pending transaction?

Pretty straightforward, check if dbms_transaction.step_id is null!

SQL> select dbms_transaction.step_id from dual;

        STEP_ID
---------------

SQL> insert into t values (1);

1 row created.

SQL> select dbms_transaction.step_id from dual;

        STEP_ID
---------------
114352430549782

SQL> commit;

Commit complete.

SQL> select dbms_transaction.step_id from dual;

        STEP_ID
---------------

SQL> insert into t values (2);

1 row created.

SQL> select dbms_transaction.step_id from dual;

        STEP_ID
---------------
124248035235852

SQL> rollback;

Rollback complete.

SQL> select dbms_transaction.step_id from dual;

        STEP_ID
---------------

where is the TRIGGER ANY TABLE privilege?

You have your table data in one schema and your procedures in another one. But can you have triggers and tables in different schemas?


SYS@lsc01> create user u1 identified by u1;

User created.

SYS@lsc01> create user u2 identified by u2;

User created.

SYS@lsc01> grant create table, unlimited tablespace to u1;

Grant succeeded.

SYS@lsc01> grant create session, create trigger to u2;

Grant succeeded.

SYS@lsc01> create table u1.t(x number);

Table created.

SYS@lsc01> grant select on u1.t to u2;

Grant succeeded.

SYS@lsc01> connect u2/u2
Connected.
U2@lsc01> create trigger u2.tr after insert on u1.t for each row
  2  begin
  3  null;
  4  end;
  5  /
create trigger u2.tr after insert on u1.t for each row
                                        *
ERROR at line 1:
ORA-01031: insufficient privileges

What’s the missing privilege? To create a trigger on another schema, you need the CREATE ANY TRIGGER privilege.

 
U2@lsc01> connect / as sysdba
Connected.
SYS@lsc01> grant CREATE ANY TRIGGER to u2;

Grant succeeded.

SYS@lsc01> 
SYS@lsc01> connect u2/u2
Connected.
U2@lsc01> create trigger u2.tr after insert on u1.t for each row
  2  begin
  3  null;
  4  end;
  5  /

Trigger created.

.plz dump file

What are those .plz dump files in my user dump directory ?


-rw-r--r--   1 oracle   dba        15168 Oct  6 14:34 _anon__3ca8c5e38__AB.plz
-rw-r-----   1 oracle   dba        15883 Oct  6 14:45 db01_ora_10061.trc
-rw-r--r--   1 oracle   dba        15168 Oct  6 14:45 _anon__3c929b088__AB.plz
-rw-r-----   1 oracle   dba        15895 Oct  6 14:47 db01_ora_10666.trc
-rw-r--r--   1 oracle   dba        15168 Oct  6 14:47 _anon__3c8651198__AB.plz

let’s check one, briefly :


*** ASSERT at file pdw4.c, line 2080; Type 0xffffffff7d79fb40 has no MAP method.
Source Location = _anon__3d2474b28__AB[1, 7]

== Dump of OPT Context Object ffffffff7c519ec8. ==
  Tue Oct  6 16:31:11 2009
  Event 10944 = 0
  plsp          = ffffffff7fff67a8
  lu            = 3c9c18210
  Diana root    = 0x20014 = 131092
  Diana proc    = 0x20012 = 131090
  Graph         = ffffffff7d774d70
  Dump file     = /app/oracle/admin/DB01/udump/_anon__3d2474b28__AB.plz
  CG            = 0
...

It is a kind of dump file, apparently. I could not find details on metalink. I generate the one above in 10.2.0.4 sparc with the following code

create or replace type t1 as object (x number)
/
create or replace type t2 as table of t1
/
exec if t1(1)member of t2()then null;end if

BEGIN if t1(1)member of t2()then null;end if; END;
Error at line 10
ORA-06550: line 1, column 7:
PLS-00801: internal error [*** ASSERT at file pdw4.c, line 2080; Type 0xffffffff7d7ba280 has no MAP method.; _anon__3c929b088__AB[1, 7]]

future release dates of 11gR2

HPUX Itanium, Sun Sparc/x86_64, AIX –> october-december 2009
Windows, HPUX Risc –> april-june 2010
Ref: Release Schedule of Current Database Patch Sets

Dates will change, keep an eye on the schedule, and do not base your business on expected dates ! Please be patient 🙂

11.1.0.7 is the latest patchset for 11gR1

You will get CPU and even PSU (Patch Set Updates are proactive cumulative patches comprised of recommended bug fixes that are released on a regular and predictable schedule)

But no more patchset. This the first time I see a release with only 1 pachset.

Well, if you use Linux, you can go to 11gR2. Otherwise you may better stick to 10.2.0.4 for a few more months

Ref: 742060.1
11.1.0.7 is the last patch set for Release 11.1

CONNECT no longer has CREATE VIEW, what’s next?

In my current project we are going to move from 9i to 10g really soon. One of the typical issue is to get the right privileges, because the schema owners typically had only CONNECT and RESOURCE, and that does no longer include CREATE VIEW in 10gR2.

I was just reading the 11gR2 readme today :
7.2 UNLIMITED TABLESPACE Privilege Changes
The UNLIMITED TABLESPACE system privilege will be removed from the RESOURCE role in a future Oracle Database release (reference Bug 7614645).

So, probably I should ask for TABLESPACE quotas before we go to 12g 😉

How to tune WHERE NAME LIKE ‘%ABC%’

More than once customers wanted me to tune queries where the LIKE clause made the query very slow…

The easy answer is : you cannot. If you want to search for some characters in the middle of the string, Oracle will not use an index.

Is it a correct answer? Maybe not.

Let’s imagine my application allow you to search for a string in the middle of the name, but to avoid scanning too large amount of data, the application enforces the string to be at least 3 characters long. For example ABC.

In this case, instead of doing a full table scan of the table to retrieve only a few rows, we can use an hint to tell Oracle to use an index on the name:

CREATE TABLE lsc_t AS
SELECT
  ROWNUM ID,
  SUBSTR(DBMS_RANDOM.STRING ('n', DBMS_RANDOM.VALUE (2, 8))
    || ' '
    || DBMS_RANDOM.STRING ('n', DBMS_RANDOM.VALUE (2, 8))
    ,1,17) NAME,
  TRUNC(SYSDATE-ABS(10000*DBMS_RANDOM.NORMAL)) birthdate,
  LPAD('X',4000,'X') address
FROM DUAL
CONNECT BY LEVEL <= 1e5;

ALTER TABLE lsc_t ADD PRIMARY KEY(ID);

CREATE INDEX lsc_i ON lsc_t(NAME);

EXEC dbms_stats.gather_table_stats(user,'LSC_T',cascade=>true)

let’s measure the time for a full table scan

SQL> set timi on
SQL> SELECT ID, NAME, birthdate
  FROM lsc_t WHERE NAME LIKE '%ABC%';

       ID NAME              BIRTHDATE
--------- ----------------- ---------
    60249 ABCBIFAB KRKBCRN  11-MAR-90
    16714 AF YABCG          09-OCT-95
    55571 BABCIQ GESGLW     27-MAR-50
    77561 BP GABC           24-APR-90
    80027 DALSABC TZLOAWDV  05-NOV-01
    49817 EABCTFIY XWB      10-FEB-88
    23283 EMMOGGBF DABCB    20-DEC-87
    39530 FMABCKB AB        18-SEP-87
    68605 FTPGOHE ABCC      28-SEP-28
    74615 KIFDWABC CSSUQ    08-AUG-82
    31772 KNOABCT BO        08-SEP-77
    68730 KRYIEN LMABC      10-APR-07
    43317 LUFJKZJT AUABCZR  19-DEC-88
    76851 MZABC TEIFG       14-SEP-92
    54589 NXE YABCDX        03-MAY-88
     6940 OIWABCZ DLFFXY    29-MAR-88
    59070 ONIB ADGABCI      29-JUL-07
    27264 PGHOABC ZY        05-OCT-90
    38157 QABC OPZHE        13-JUN-87
    17511 QPDKD CIABCJ      08-AUG-69
    25507 RX OWULOABC       24-FEB-92
    62159 SEABC DAILK       25-JUN-02
     3845 SK CCABCG         22-JAN-80
    50059 SPABC BVHRHW      18-MAR-86
    54700 UABCPC WUHAJS     28-OCT-71
    70207 UKY OIDUABC       23-APR-88
    39484 WABC TJLYHVJZ     14-MAR-78
    14561 WDRWABC XZKDH     29-MAR-86
    61501 YBYU RYABCGI      28-JUN-78
    30578 YEWENGX ABCHARA   12-SEP-67
    35397 YHBEABC HFKO      25-AUG-85
    26450 YOABCVG HJT       23-DEC-98
    87224 ZKNLNY YAABC      13-NOV-61

33 rows selected.

Elapsed: 00:00:02.56

about 3 seconds for retrieving 33 rows out of 100000

let’s try with an index

SQL> SELECT /*+INDEX(LSC_T,LSC_I)*/  ID, NAME, birthdate
  FROM lsc_t WHERE NAME LIKE '%ABC%';

       ID NAME              BIRTHDATE
--------- ----------------- ---------
    60249 ABCBIFAB KRKBCRN  11-MAR-90
    16714 AF YABCG          09-OCT-95
    55571 BABCIQ GESGLW     27-MAR-50
    77561 BP GABC           24-APR-90
    80027 DALSABC TZLOAWDV  05-NOV-01
    49817 EABCTFIY XWB      10-FEB-88
    23283 EMMOGGBF DABCB    20-DEC-87
    39530 FMABCKB AB        18-SEP-87
    68605 FTPGOHE ABCC      28-SEP-28
    74615 KIFDWABC CSSUQ    08-AUG-82
    31772 KNOABCT BO        08-SEP-77
    68730 KRYIEN LMABC      10-APR-07
    43317 LUFJKZJT AUABCZR  19-DEC-88
    76851 MZABC TEIFG       14-SEP-92
    54589 NXE YABCDX        03-MAY-88
     6940 OIWABCZ DLFFXY    29-MAR-88
    59070 ONIB ADGABCI      29-JUL-07
    27264 PGHOABC ZY        05-OCT-90
    38157 QABC OPZHE        13-JUN-87
    17511 QPDKD CIABCJ      08-AUG-69
    25507 RX OWULOABC       24-FEB-92
    62159 SEABC DAILK       25-JUN-02
     3845 SK CCABCG         22-JAN-80
    50059 SPABC BVHRHW      18-MAR-86
    54700 UABCPC WUHAJS     28-OCT-71
    70207 UKY OIDUABC       23-APR-88
    39484 WABC TJLYHVJZ     14-MAR-78
    14561 WDRWABC XZKDH     29-MAR-86
    61501 YBYU RYABCGI      28-JUN-78
    30578 YEWENGX ABCHARA   12-SEP-67
    35397 YHBEABC HFKO      25-AUG-85
    26450 YOABCVG HJT       23-DEC-98
    87224 ZKNLNY YAABC      13-NOV-61

33 rows selected.

Elapsed: 00:00:00.06

Much better 🙂

reposted due to % in url

on recycle bin

more than one user may wondered who created those BIN$ when they first connected to a 10g database.


create table lsc_t(x number) 
partition by range(x) 
(partition LESS_THAN_ONE values less than (1));

drop table lsc_t;

select object_name, subobject_name, created 
from user_objects 
where object_name like 'BIN$%';
OBJECT_NAME                    SUBOBJECT_NAME  CREATED  
------------------------------ --------------- ---------
BIN$bh2VJ6FqFJ3gRAAUT+rFpg==$0 LESS_THAN_ONE   07-JUL-09

Ok, it is quite easy to get rid of it. Either at DROP time with a DROP TABLE LSC_T PURGE or later with PURGE RECYCLEBIN. Most of the objects disappear from USER_OBJECTS when dropped actually. The recyclebin view is called : RECYCLEBIN.


purge recyclebin;

select object_name, subobject_name, created 
from user_objects 
where object_name like 'BIN$%';

no rows selected.

select * from recyclebin;

no rows selected.

So far so good…

Let’s see what’s happening with my primary keys


purge recyclebin;
create table lsc_t(x number constraint lsc_t_pk primary key);
drop table lsc_t;
select object_name, original_name, type from recyclebin;

OBJECT_NAME                    ORIGINAL_NAME TYPE 
------------------------------ ------------- -----
BIN$bh23ggtBHALgRAAUT+rFpg==$0 LSC_T         TABLE
BIN$bh23ggtAHALgRAAUT+rFpg==$0 LSC_T_PK      INDEX

The primary key index is now in the recycle bin as well.

let’s recover the recycle bin version :


flashback table lsc_t to before drop;
select index_name from user_indexes where table_name='LSC_T';

INDEX_NAME                    
------------------------------
BIN$bh3GgNi1HR3gRAAUT+rFpg==$0

select object_name, original_name, type from recyclebin;

no rows selected.

👿

So if you cannot exclude BIN$ objects from your dba maintenance scripts, you will need to deal with thoses as they may be recovered indexes!

How to reuse connection in shell

It is Friday, I wanted to give my readers some stuff for the week-end 😉

Imagine that piece of code :

countlines() {
  c=$(sqlplus -s /nolog <<EOF | grep -v Connected
connect scott/tiger
set feed off head off
select count(*) from $1;
EOF
)
  echo there are $c lines in $1
}

countlines EMP
countlines DEPT

I can run this

time ./script1
there are 14 lines in EMP
there are 4 lines in DEPT

real    0m0.46s
user    0m0.06s
sys     0m0.09s

Sounds like a regular shell script. How could we optimize the countlines function? Well, we could create the connection only once and use coprocess pipe (with |& that is pipe ampersand)

sqlplus -s /nolog |&

print -p "connect scott/tiger"

read -p line
if [ $line != Connected. ]
then
  exit 1
fi

print -p "set feed off head off"

countlines() {
  print -p "select count(*) from $1;"
  read -p c
  echo "there is $c lines in $1"
}

countlines EMP
countlines DEPT

print -p disconnect

A two-ways pipe is opened with sqlplus. There is only one connect, and one disconnect.

Let’s check the performance :

$ time ./script2
there is 14 lines in EMP
there is 4 lines in DEPT

real    0m0.23s
user    0m0.00s
sys     0m0.01s

About twice as fast! Note the “Connected” output may not exist in recent version of sqlplus in silent mode. If you have a script that generates hundreds of connections, or which create a connection every 5 seconds or so, think about it 🙂

Enjoy your week-end

return code before grep

In my previous post hide-password-from-ps-output-sql-loader I mentioned a way to pass the password to the loader thru a parameter file. As correctly suggested by Brian Tkatch, the password could be passed as standard input

sqlldr control=x.ctl silent=header,feedback <<EOF
scott/tiger
EOF
Username:

The Username: prompt is displayed 🙁   🙁

How do we get rid of this ?

sqlldr control=x.ctl silent=header,feedback <<EOF | grep -v "^Username:"
scott/tiger
EOF

There is no output. But what’s the error code

echo $?
1

The return code is 1 🙁

This is not the error code from sqlldr, but the error code from grep !

Ok, here is the trick, a bit cryptic if you are not familiar with file descriptors

( ( (sqlldr control=x <<EOF;echo $? >&3) |grep -v "^Username:" >&4 ) 3>&1 |(read x;exit $x) )4>&1
scott/tiger
EOF
echo $?
0

The return code is 0 🙂

Hide password from ps output : sql loader

By reporting the process status with ps, any Unix user will see the command line arguments

ps -ef 
     UID   PID  PPID  C    STIME TTY      TIME CMD
lsc      13837 13825  0   May 11 pts/17   0:01 -ksh
oracle    4698  6294  0 12:00:40 ?        0:00 sqlplus -s system/manager
appluser  4229  4062  0 12:00:03 ?        0:00 sqlldr scott/tiger
applrun0 28445 28281  0 11:54:03 ?        0:00 imp king/gold full=y
...

What you see here above is definitely a security issue. For sqlplus, the trick is to use sqlplus /nolog and then pass connect system/manager as input or script.

For sqlldr (and exp/imp etc…), the trick is to use a parameter file.

To make it as safe as possible, the file must be unique, readable only for owner and removed after usage.

Ex:

umask 0077
TMPFILE=$(mktemp)
echo "userid=scott/tiger" >$TMPFILE
sqlldr parfile=$TMPFILE control=x.ctl silent=header,feedback
rm $TMPFILE

mktemp is an Unix utility that creates temp files with unique names.

ora-984 and sqlerrm

What’s wrong with this code ?


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.8.0 - Production

SQL> drop table lsc_t ;

Table dropped.

SQL> create table lsc_t (x varchar2(255), y date);

Table created.

SQL> begin
  2    null;
  3  exception when others then
  4    insert into lsc_t (x,y)
  5          values (sqlerrm, sysdate);
  6  end;
  7  /

PL/SQL procedure successfully completed.

I was in the process of migrating a database with 10g and this piece of code became invalid 🙁

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

SQL> drop table lsc_t ;

Table dropped.

SQL> create table lsc_t (x varchar2(255), y date);

Table created.

SQL> begin
  2    null;
  3  exception when others then
  4    insert into lsc_t (x,y)
  5          values (sqlerrm, sysdate);
  6  end;
  7  /
        values (sqlerrm, sysdate);
                *
ERROR at line 5:
ORA-06550: line 5, column 17:
PL/SQL: ORA-00984: column not allowed here
ORA-06550: line 4, column 3:
PL/SQL: SQL Statement ignored

SQL> 

Ok, the code is wrong. It has been running for half a decade in production but the exception was never reached.

Note it is a bug, and putting sqlerrm after sysdate would have generate the exception even in 9i

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.8.0 - Production

SQL> begin
  2    null;
  3  exception when others then
  4    insert into lsc_t (y,x)
  5          values (sysdate,sqlerrm);
  6  end;
  7  /
        values (sysdate,sqlerrm);
                        *
ERROR at line 5:
ORA-06550: line 5, column 25:
PL/SQL: ORA-00984: column not allowed here
ORA-06550: line 4, column 3:
PL/SQL: SQL Statement ignored

Just having fun with 10g migration 😉

grant access to trace files

As a developer, you sometimes need to contact your dba to get an user trace. As a dba, sending trace files to developer is not much fun.

But how do you get access to your traces without any dba ?

One way could be to set _trace_files_public=true, but this is bad. It is undocumented, it is unsupported, there is no control to who can access the files, and probably it will not help if you have no access to the database server.

I prefer to provide a function that reads from the trace directory.

Ok, here we go :


CREATE DIRECTORY user_dump_dest AS
  '/app/oracle/admin/DB01/udump';

CREATE OR REPLACE FUNCTION get_tracefile (file_name VARCHAR2)
   RETURN VARCHAR2
IS
   dest_loc   CLOB;
   src_loc    BFILE;
   ret        VARCHAR2 (4000);
BEGIN
   src_loc := BFILENAME ('USER_DUMP_DEST', file_name);
   DBMS_LOB.OPEN (src_loc, DBMS_LOB.lob_readonly);
   DBMS_LOB.createtemporary (dest_loc, TRUE);
   DBMS_LOB.loadfromfile (dest_loc, src_loc, 4000);
   ret := DBMS_LOB.SUBSTR (dest_loc, 4000);
   DBMS_LOB.CLOSE (src_loc);
   RETURN ret;
END;
/

Just a small function that returns the first 4000 characters of the trace file. I could then grant execute on that function to the developers.

it works quite well

SELECT get_tracefile ('db01_ora_6224.trc')
  FROM DUAL;
GET_TRACEFILE('DB01_ORA_6224.TRC')                                      
----------------------------------------------------------------------
/app/oracle/admin/DB01/udump/db01_ora_6224.trc
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.8.0 - Production
ORACLE_HOME = /app/oracle/product/dbms/DB01
System name:  SunOS
Node name:  dbsrv01
Release:  5.8
Version:  Generic_117000-05
Machine:  sun4u
Instance name: DB01
Redo thread mounted by this instance: 1
Oracle process number: 21
Unix process pid: 6224, image: oracle@dbsrv01 (TNS V1-V3)

*** SESSION ID:(273.54591) 2009-04-27 12:13:57.292
*** 2009-04-27 12:13:57.292
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [17069], [0x3EED05050], 
[], [], [], [], [], []
ORA-00928: missing SELECT keyword
ORA-00928: missing SELECT keyword
ORA-00928: missing SELECT keyword
ORA-00928: missing SELECT keyword
ORA-00928: missing SELECT keyword
ORA-00928: missing SELECT keyword
ORA-00928: missing SELECT keyword
ORA-00928: missing SELECT keyword
ORA-00928: missing SELECT keyword
ORA-00928: missing SELECT keyword
Current SQL statement for this session:
alter PACKAGE "LSC_PKG" compile body
----- PL/SQL Call Stack -----
...

I should mention than granting access to trace files is a security issue as a hacker could dump some security modules. But it will be fine for your trusted developers.

package version control

Oracle does not offer any kind of version control like CVS or subversion in the database. CVS and subversion could be used in the filesystem, then the code could be deployed with sqlplus.

To quickly compare packages in Test and Production I used :


select 
  env,name,type,line,text 
from (
  select 
    env,name,type,line,text,
    count(distinct text) over(partition by name,type,line) c
  from (
    select 
      'TEST' env,name,type,line,text 
    from 
      user_source 
    union all
    select 
      'PROD' env,name,type,line,text 
    from 
      user_source@prod
  )
)
where c>1 ;

where “PROD” is a database link to production database. My security friends will scream to hear I have a database link to production in my test schema, but you are free to do it the other way round :mrgreen:

I also explained TOAD method in how to compare schema a few months ago.

commit suicide;

Tanel Poder directed me to Miladin Modrakovic blog, I will definitely add this to my prefered feed.

You have to try this :


SQL> alter session set events 'immediate crash';
alter session set events 'immediate crash'
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel

A very decent method for killing yourself 😈

track DDL changes

Why is my package being invalidated? This is the question I asked myself a few times those days. In order to find out what is happening in my schema, I created this simple DDL trigger which tracks all DDL changes in my schema.


CREATE TABLE AUDIT_DDL (
  d date,
  OSUSER varchar2(255),
  CURRENT_USER varchar2(255),
  HOST varchar2(255),
  TERMINAL varchar2(255),
  owner varchar2(30),
  type varchar2(30),
  name varchar2(30),
  sysevent varchar2(30),
  sql_txt varchar2(4000));
   
create or replace trigger audit_ddl_trg after ddl on schema
declare
  sql_text ora_name_list_t;
  stmt VARCHAR2(4000) := '';
  n number;
begin
  if (ora_sysevent='TRUNCATE')
  then
    null;
  else
    n:=ora_sql_txt(sql_text);
    for i in 1..n
    loop
      stmt:=substr(stmt||sql_text(i),1,4000);
    end loop; 
    insert into audit_ddl(d, osuser,current_user,host,terminal,owner,type,name,sysevent,sql_txt)
    values(
      sysdate,
      sys_context('USERENV','OS_USER') ,
      sys_context('USERENV','CURRENT_USER') ,
      sys_context('USERENV','HOST') , 
      sys_context('USERENV','TERMINAL') ,
      ora_dict_obj_owner,
      ora_dict_obj_type,
      ora_dict_obj_name,
      ora_sysevent,
      stmt
    );
  end if;
end;
/

Thanks Yas for your comment, I added the SQL text

high cost

What’s wrong with this query ?


select
(t6.column_value).getstringval() t6
from
table(xmlsequence(extract(xmltype(‘<x/>’),’/x’))) t1,
table(xmlsequence(t1.column_value))t2,
table(xmlsequence(t2.column_value))t3,
table(xmlsequence(t3.column_value))t4,
table(xmlsequence(t4.column_value))t5,
table(xmlsequence(t5.column_value))t6;
T6
————————
<x/>

Elapsed: 00:00:00.01

Well, let’s check the plan :


--------------------------------------------------------------------
| Id  | Operation          | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT   |    18E|    15E|    18E  (0)|999:59:59 |
|   1 |  NESTED LOOPS      |    18E|    15E|    18E  (0)|999:59:59 |
|   2 |   NESTED LOOPS     |    18E|    15E|    99P  (3)|999:59:59 |
|   3 |    NESTED LOOPS    |  4451T|    31P|    12T  (3)|999:59:59 |
|   4 |     NESTED LOOPS   |   544G|  3045G|  1490M  (3)|999:59:59 |
|   5 |      NESTED LOOPS  |    66M|   254M|   182K  (3)| 00:36:31 |
|   6 |       COLLECTION I |       |       |            |          |
|   7 |       COLLECTION I |       |       |            |          |
|   8 |      COLLECTION IT |       |       |            |          |
|   9 |     COLLECTION ITE |       |       |            |          |
|  10 |    COLLECTION ITER |       |       |            |          |
|  11 |   COLLECTION ITERA |       |       |            |          |
--------------------------------------------------------------------

It is returning 18 quadrillions of rows, 15 exabytes, the cost is 1.8E19 and the time is about one month :mrgreen: