H.Tonguç Yılmaz - Oracle Blog

If you are a DBA of a mission critical database which usually mean there is no tolerance of downtime, data loss and mean time to recover should be minimum, ARCHIVELOG mode and a RMAN hot backup will be a mandatory starting point for a strategy.

The easiest way of scheduling this kind of a backup is done by Database(or Grid) Control, after 10.2 using Oracle’s advised backup strategy option which is a Flash Recovery Area based incremental RMAN backup will be what I advice if you already have enough disk space for FRA. Lutz is not blogging for a while and I miss Lutz’s RMAN posts so I guess this one can be another good quick guide.

Why restore before recover? The new Oracle 10g Backup Strategy

But what if you only want to restore a parameter table with lets say 86 rows, or an apex or some pl/sql application’s previous release, or just a metadata of a simple database object which is now changed or dropped? Here we all have our own workarounds mostly depending on the database version and tools we use around the database. Data Guard and Flashback options if you do have may help or you can customize your Backup strategy for these kind of alternative needs.

In order to restore a parameter table with 86 rows it should be throwing a huge rock to a frog if you go with your RMAN backup, so choosing these kind of parameter tables and including them into a daily binary table level export may be the quickest way to revert them back.

What about a PL/SQL based need, here again a daily norows binary export backup may help a lot.

A data-pump norows backup example

# CREATE or REPLACE DIRECTORY backup_dir AS '/ods01/backup/daily_norows/dp';
/dwh0/app/oracle/product/11.1.0.1/bin/expdp $BACKUP_USERNAME/$BACKUP_PASSWORD
DUMPFILE=daily_full_norows_dp_backup.dmp.$BACKUP_DATE LOGFILE=daily_full_norows_dp_backup.log.$BACKUP_DATE
DIRECTORY=backup_dir FULL=y EXCLUDE=TABLE_DATA

A traditional export norows backup example

/dwh0/app/oracle/product/11.1.0.1/bin/exp $BACKUP_USERNAME/$BACKUP_PASSWORD
FILE=/ods01/backup/daily_norows/exp/daily_full_norows_dp_backup.dmp.$BACKUP_DATE
LOG=/ods01/backup/daily_norows/exp/daily_full_norows_dp_backup.log.$BACKUP_DATE
FULL=y ROWS=n

And what if you are using Apex applications or Warehouse Builder repository, then again taking daily tool level export backups will help.

OWB 11g Repository Shell Script Backup Example

There can be additional thoughts of course, like saving database structure as a SQL file daily or immediately after any structure change.

A Simple Backup Control File to Trace Shell Script Example

/dwh0/app/oracle/product/11.1.0.1/bin/sqlplus -s > /ods01/backup/daily_norows/backup_control_file_to_trace.log.$BACKUP_DATE
2> /ods01/backup/daily_norows/backup_control_file_to_trace.err.$BACKUP_DATE <<EOF
$BACKUP_USERNAME/$BACKUP_PASSWORD
@/ods01/backup/daily_norows/backup_control_file_to_trace.sql $BACKUP_DATE
exit ;
EOF

fatih@oracle $ cat /ods01/backup/daily_norows/backup_control_file_to_trace.sql
alter session set tracefile_identifier = backup_controlfile_to_trace;
alter database backup controlfile to trace;
SET ECHO OFF
SET FEEDBACK OFF
SET PAGESIZE 0
SET LINESIZE 1000
spool /ods01/backup/daily_norows/backup_control_file_to_trace.name.&1
-- available after 11.1
select value from v$diag_info where name like 'Default Trace%' ;
spool off
SET FEEDBACK ON
SET PAGESIZE 24
SET ECHO ON

But I think critical point of all is that whatever you do do not forget to use a scheduler to automatize these jobs and control the logs of them periodically :)

Simple unix cron settings to automatize daily backups

fatih@oracle $ crontab -l
40 17 * * * /ods01/backup/daily_norows/daily_norows.sh > /ods01/backup/daily_norows/daily_norows.log
2> /ods01/backup/daily_norows/daily_norows.err
00 18 * * * /ods01/backup/backup_repos.sh > /ods01/backup/backup_repos.log
2> /ods01/backup/backup_repos.err

Ravi R. is someone I admire because he puts a lot of his efforts in understanding the beautiful feature of 10g, the Scheduler, on OTN Database » Scheduler Forum. His two sticky like posts helped me before;

Answers to “Why are my jobs not running?”
http://forums.oracle.com/forums/thread.jspa?threadID=646581

GUIDE TO RUNNING EXTERNAL JOBS ON 10g WITH DBMS_SCHEDULER
http://forums.oracle.com/forums/thread.jspa?threadID=555102

There are more sticky like posts in other OTN forums like;

When your query takes too long of Rob van Wijk

Introduction to regular expressions of cd

One can reference these kind of sticky like posts to “please before asking search in this forum or documentation” type of questions like a documentation reference. But I was thinking of what if these were a blog post on wordpress or blogspot? I think they may get a higher return for the community, so this post may be a startup than :)

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.

Tonight as usual I was checking the Oracle blogs and popular OTN forum threads, Why the dropped table not in recyclebin? thread got my attention.

Especially after Oracle 10g below flashback features makes a big difference in Oracle DBAs’ and Developers’ lives;
• Flashback Database
• Flashback Drop
• Flashback Table
• Flashback Versions Query
• Flashback Transaction Query

Once upon a time I tried to summaries these features with my post; Oracle 10g Flashback Versions Query, Drop Table and Recyclebin Management But good things, especially new features, have their own alternative costs like bugs, limitations or restrictions which are usually lived and learned. These kind of information you can not find in the documentation or in the blog posts like mine above which usually try to give basic samples which are only designed to demonstrate the related features but not to stress them.

So lets start with some quick reminders; Flashback Table, Flashback Query, Flashback Transaction Query and Flashback Version Query are all based on undo(rollback) data which primarily provides read consistency and rolling back transactions in Oracle. But Flashback Database is based on before images in the flashback logs. And Flashback Drop is based on another 10g new feature called Recycle Bin which is there to manage dropped database objects until their space is needed to store new data. Each Flashback option has lots of interesting limitations or restrictions, I tried to summaries them from the related metalink notes, this place is like an unlimited gold mine for an Oracle man.

Note 249319.1 - Configure flashback database
Note 270060.1 - Use Flashback Table Feature and Resolve errors
Note 270535.1 - Restrictions on Flashback Table Feature
Note:435998.1 - What Do All 10g Flashback Features Rely on and what are their Limitations
Note 317499.1 - 10G Oracle Flashback Transaction Query - Introduction and usage
Note 369755.1 - Flashback Logs-Space management

Limitations of Flashback Database

• Flashback Database can only undo changes to a datafile made by an Oracle database. It cannot be used to repair media failures, or to recover from accidental deletion of datafiles.
• You cannot use Flashback Database to undo a shrink datafile operation.
• If the database control file is restored from backup or re-created, all accumulated flashback log information is discarded. You cannot use FLASHBACK DATABASE to return to a point in time before the restore or re-creation of a control file.
• When using Flashback Database with a target time at which a NOLOGGING operation was in progress, block corruption is likely in the database objects and datafiles affected by the NOLOGGING operation. If you expect to use Flashback Database to return to a point in time during an operation such as a direct-path INSERT, consider performing the operation in LOGGING mode.
• Shrinking a datafile or dropping a tablespace can prevent flashing back the Database

Limitations and Restrictions on Flashback Drop

• The recycle bin functionality is only available for non-system, locally managed tablespaces. If a table is in a non-system, locally managed tablespace, but one or more of its dependent segments(objects) is in a dictionary-managed tablespace, then these objects are also protected by the recycle bin.
• There is no fixed amount of space allocated to the recycle bin, and no guarantee as to how long dropped objects remain in the recycle bin. Depending upon system activity, a dropped object may remain in the recycle bin for seconds, or for months.
• While Oracle permits queries against objects stored in the recycle bin, you cannot use DML or DDL statements on objects in the recycle bin.
• You can perform Flashback Query on tables in the recycle bin, but only by using the recycle bin name. You cannot use the original name of the table.
• A table and all of its dependent objects (indexes (except for bitmap join indexes), LOB segments, nested tables, triggers, constraints and so on) go into the recycle bin together, when you drop the table. Likewise, when you perform Flashback Drop, the objects are generally all retrieved together , It is possible, however, that some dependent objects such as indexes may have been reclaimed due to space pressure. In such cases, the reclaimed dependent objects are not retrieved from the recycle bin.
• Due to security concerns, tables which have Fine-Grained Auditing (FGA) and Virtual Private Database (VPD) policies defined over them are not protected by the recycle bin.
• Partitioned index-organized tables are not protected by the recycle bin.
• The recycle bin does not preserve referential constraints on a table (though other constraints will be preserved if possible). If a table had referential constraints before it was dropped (that is, placed in the recycle bin), then re-create any referential constraints after you retrieve the table from the recycle bin with Flashback Drop.
• When you drop a table, all materialized view logs defined on the table are also dropped but are not placed in the recycle bin. Therefore, the materialized view logs cannot be flashed back along with the table.
• When you drop a table, any indexes on the table are dropped and put into the recycle bin along with the table. If subsequent space pressures arise, then the database reclaims space from the recycle bin by first purging indexes. In this case, when you flash back the table, you may not get back all of the indexes that were defined on the table.
• You cannot flash back a table if it has been purged, either by a user or by Oracle Database as a result of some space reclamation operation.
• Oracle Database retrieves all indexes defined on the table retrieved from the recycle bin except for bitmap join indexes. (Bitmap join indexes are not put in the recycle bin during a DROP TABLE operation, so cannot be retrieved.)

Limitations and Restrictions on Flashback Table

• Flashback Table operations are not valid for the following type objects: tables that are part of a cluster, materialized views, Advanced Queuing (AQ) tables, static data dictionary tables, system tables, remote tables, object tables, nested tables, or individual table partitions or subpartitions.
• The following DDL operations change the structure of a table, so that you cannot subsequently use the TO SCN or TO TIMESTAMP clause to flash the table back to a time preceding the operation: upgrading, moving, or truncating a table; adding a constraint to a table, adding a table to a cluster; modifying or dropping a column; adding, dropping, merging, splitting, coalescing, or truncating a partition or subpartition (with the exception of adding a range partition).
• You cannot rollback a FLASHBACK TABLE statement. However, you can issue another FLASHBACK TABLE statement and specify a time just prior to the current time. Therefore, it is advisable to record the current SCN before issuing a FLASHBACK TABLE clause.

Limitations and Restrictions on Flashback Query

• Flashback Query is Not available after restarting the database.
• You cannot specify a subquery in the expression of the AS OF clause.
• You cannot use the VERSIONS clause in flashback queries to temporary , external tables, fixed tables, or tables that are part of a cluster.
• You cannot use the VERSIONS clause in flashback queries to views. However, you can use the VERSIONS syntax in the defining query of a view.
• You cannot specify this clause if you have specified query_name in the query_table_expression.
• Flashback Query does not undo anything. It is only a query mechanism. You can take the output from a Flashback Query and perform an undo yourself in many circumstances.
• Flashback Query does not tell you what changed. LogMiner does that.
• Flashback Query can undo changes and can be very efficient if you know the rows that need to be moved back in time. You can use it to move a full table back in time, but this is very expensive if the table is large since it involves a full table copy.
• Flashback Query does not work through DDL operations that modify columns, or drop or truncate tables.
• LogMiner is very good for getting change history, but it gives you changes in terms of deltas (insert, update, delete), not in terms of the before and after image of a row. These can be difficult to deal with in some applications.

ps: football is a very interesting game, I believe we played good at most only 30 minutes at the first 3 games and this was enough for this young team to the semi-finals. Czech game was one of the most dramatic games I have ever watched, we came back from 2-0 against Chelsea’s goal keeper Petr Čech.

http://en.euro2008.uefa.com/tournament/matches/match=300699/index.html

Thanks to Alejandro Vargas for the alert on this paper; Note:560977.1 - Real Application Testing Now Available for Earlier Releases

Excel is one of the all time best tools one can develop, I totally agree.

But if you are a database developer and like benefiting from the power of SQL for any kind of your daily need, you also most probably try to accomplish whatever you do inside the your database. For example you also prefer DBMS_SCEDULER to unix cron, in order to query whatever happened by simple SQLs over ALL_SCHEDULER_JOB_LOG and ALL_SCHEDULER_JOB_RUN_DETAILS. Or you also use V$RMAN_BACKUP_JOB_DETAILS to query your RMAN backup logs, not telnet and grep error messages frmo the unix log files.

This morning I was lost inside an Excel document which was designed to guide a warehouse builder developer to map the Siebel source tables and columns. So I decided to attach the information I hardly mined onto my target tables’ and columns’ comments for future just in case :)

create table  SBL_ASSET  (
   ASSET_ID             VARCHAR2(60),
   GSM                  VARCHAR2(200),
   ACCOUNT_ID           VARCHAR2(60),
[truncated]
   NPRD                 VARCHAR2(400)
) PCTFREE 10 PCTUSED 89
[truncated]
NOLOGGING PARALLEL 8 ;

comment on table  SBL_ASSET is 'S_ASSET, S_PROD_INT' ;
comment on column SBL_ASSET.ASSET_ID is 'S_ASSET.ROW_ID' ;
comment on column SBL_ASSET.GSM is 'S_ASSET.X_GSM_NUMBER' ;
comment on column SBL_ASSET.ACCOUNT_ID is 'S_ASSET.OWNER_ACCNT_ID' ;
[truncated]
comment on column SBL_ASSET.NPRD is 'S_ASSET.ASSET_NUM' ; 

So what is the good for all of these extra commenting? Here is the catch, with a simple query now anyone who can SQL will get the mapping informations;

SELECT a.column_name || ',' column_name, a.comments || ',' comments
  FROM dba_col_comments a
 WHERE a.owner = 'ODS'
   AND a.table_name IN ('SBL_ASSET');

COLUMN_NAME                     COMMENTS
------------------------------- --------------------------------------------------------------------------------
ASSET_ID,                       S_ASSET.ROW_ID,
GSM,                            S_ASSET.X_GSM_NUMBER,
ACCOUNT_ID,                     S_ASSET.OWNER_ACCNT_ID,
[truncated]
NPRD,                           S_ASSET.ASSET_NUM,  

For example you can consume this information in order to build a simple OWB map or SQL loading script, just like PL/SQL commenting; on database documentation.

TRUNCATE TABLE ODS.SBL_ASSET REUSE STORAGE ;

INSERT /*+ APPEND */ INTO ods.sbl_asset
   (asset_id, gsm, account_id,
[truncated]
   nprd)
   SELECT s_asset.row_id,
          s_asset.x_gsm_number,
          s_asset.owner_accnt_id,
[truncated]
          s_asset.asset_num
     FROM src_siebel.s_asset, src_siebel.s_prod_int
    WHERE s_asset.prod_id = s_prod_int.row_id
      AND s_prod_int.name = 'TC_PRODUCT';

COMMIT;   

Like Tom Kyte advices, we, database developers, do things “in” the database. ;)

Knowledge comes with experience and experimentation, but of course only your experiences will be limited. Other than documentation I love reading support case studies very much. They are real life experiences, you can also find out some “don’t do”s and bugs here.

So I want to share one recent Ubtools support case study I liked very much;

“ORA-01187: cannot read from file” in one of the RAC Node

And two complementary reminders;

Ubtools support issuenavigator and RSS

Previously released Oracle support case studies

This topic was discussed today on our forum group OracleTURK. First things first, what may be the reasons you may need to find unused or read only Oracle segments in your database.

- of course if we now they are not needed we can easily get rid of them right :)
- if you can find your read only data you can put them into specific separate read only tablespaces, compress those segments as a result gain in backup duration and space.

If your database applications pro-actively handles this data lifecycle management need or you can easily gather this information from them then life will be much more easy for you. Otherwise your database management software, for us Oracle of course :), is your friend again as usual, all you have to do is to choose some combination of features already supplied within your Oracle release depending to your need and their advantages and disadvantages.

After 10g if you have the extra cost option Automatic Workload Repository(AWR-DBA_HIST% views) helps a lot, but what if you are still on 9i or you do not have that cost options? Auditing or Statspack data(PERFSTAT schema’s tables) will help similarly if you configure them. Before getting into the details of some options I tried be warned about the retention of the data you will be analyzing. For example if last 15 days information is relevant for you to decide if a segment is not used or can be read only and your AWR retention is 7 days then you will conclude wrong. So as an initial step controlling AWR or Statspack reports retentions will be important.

SELECT retention FROM dba_hist_wr_ control;

Or if you will be depending on V$SQL kind of dynamic performance views data you must ensure that your last instance startup time is enough behind for your analysis need.

Below mentioned options may have their own alternative performance costs, no pain no gain in the real world, so who can not pay for AWR, Statpack kind of a repository activity can prefer to use some kind of a network sniffer tool capturing any SQL requested from the instance.

1- Database auditing based options

a) Standart auditing;

audit_trail=DB

AUDIT SELECT ON BY ACCESS;

SELECT ..
FROM sys.aud$
WHERE .. ;

b) Fine grained access control;

BEGIN
dbms_fga.add_policy (object_schema=>’..’, object_name=>’..’, policy_name=>’..’);
commit;
END;
/

SELECT ..
FROM dba_fga_audit_trail a, dba_objects b
WHERE a.object_name = b.object_name
AND a.object_schema = b.owner
AND .. ;

2- 10g AWR based options

a) Historical SQL execution plan statistics;

SELECT ..
FROM dba_hist_sql_plan p, dba_hist_sqlstat s
WHERE p.sql_id = s.sql_id
AND .. ;

b) Historical segment statistics;

SELECT ..
FROM dba_hist_seg_stat ss, dba_hist_seg_stat_obj o
WHERE ss.obj# = o.obj#
AND ss.dataobj# = o.dataobj#
AND ss.dbid = o.dbid
AND .. ;

3- Statspack based options

a) Historical SQL statistics;

SELECT ..
COUNT(DECODE(s.command_type, 2, 1, 6, 1, 7, 1, 189, 1)) sql_change,
COUNT(DECODE(s.command_type, 3, 1)) sql_read
FROM perfstat.stats$sqltext s
WHERE s.sql_text LIKE ‘% ‘||t.table_name||’ %’
AND .. ;

b) Historical segment statistics;

SELECT ..
FROM perfstat.stats$seg_stat s, perfstat.stats$seg_stat_obj o
WHERE s.obj# = o.obj#
AND s.dataobj# = o.dataobj#
AND s.dbid = o.dbid
AND .. ;

4- Others require mentioning

DBA_TAB_MODIFICATIONS information is reset after statistics gathering, so you may easily capture the modification amounts for your segments from the last stats gathering time.

SELECT ..
FROM dba_tab_modifications
WHERE .. ;

V$ACCESS can show the accessed objects for the moment that you are querying;

SELECT ..
FROM v$access
WHERE .. ;

And lets meet with Oracle’s new answer to this kidn of a need; Information Lifecycle Management(ILM)

Each year I find myself in a project which involves a huge data migration or database upgrade somehow. So up to now I blogged several times on my previous experiences;

- http://tonguc.wordpress.com/2007/07/13/i-am-back/
- http://tonguc.wordpress.com/2007/08/31/some-best-practices-of-upgrading-oracle-database/
- http://tonguc.wordpress.com/2007/07/16/deffensive-upgrade-methods-but-still-no-pain-no-gain/
- http://tonguc.wordpress.com/2007/04/24/take-the-risk-and-migrate-10gr2/
- http://tonguc.wordpress.com/2007/03/17/some-migration-experiences-to-share/

For a quick summary on migration best practices I may advise you to take close attention on your SGA and PGA memory pools tuning, keep target database on NOARCHIVELOG mode with big(I prefer 10GB at least) single membered 10 or more redo groups during data transfer at target and test your migration setup several times until you are satisfied with the results. When you are within the expected time window leave it, tuning is an endless road do not let to loose control :)

This week we needed a strategy to easily switch between migration(much more OLAP) and normal(much more OLTP) parameters. So I choosed to use pfile over spfile for this kind of a need and created a special OLAP tuned parameter file and an OLTP pfile from current spfile. As a result a normal startup will open the instance with the original OLTP based parameters which comes from the pfile under $ORACLE_HOME/dbs

– clean shutdown and open with OLTP like default parameter file

SQL> shutdown immediate

SQL> startup

–

And whenever we need OLAP like migration parameters we will close the related instance and open with the special tuned OLAP pfile.

– clean shutdown and open with OLAP like parameter file

SQL> shutdown immediate

SQL> startup pfile=/../../FOR_MIGRATION_USE_ONLY_PFILE_BSCS.ora

–

In this pfile I used IFILE option of parameter file, so that I make sure I take all mandatory parameters like destinations first from the original OLTP like pfile and override only the ones needed for OLAP like settings. Below is a sample I share with you which was designed on a 84 dual core CPU, ~200GB physical memory, 10.2.0.3 IBM AIX instance.

FOR_MIGRATION_USE_ONLY_PFILE_BSCS.ora

So of course any time you need a similar OLAP like parameter file you need to customize and test the SGA, PGA and parallelism parameters depending to the instance’s hardware and Oracle release you will be working.

Reference Guide of your release and this query for hidden parameters may assist you during customization.

– how to query hidden parameters’ information based on x$ views

select a.ksppinm  "Parameter", b.ksppstvl "Session Value",
       c.ksppstvl "Instance Value", ksppdesc "Desc"
  from x$ksppi a, x$ksppcv b, x$ksppsv c
where a.indx = b.indx and a.indx = c.indx
--   and ksppinm in ('_smm_px_max_size', '_smm_max_size', '_pga_max_size')
 order by a.ksppinm;

–

This event is not only useful for tuning purposes, whenever I experience a weird behavior of the database engine usually also Oracle support requests the trace file produced with this event since the content shows any database calls made inside the traced application.

Today one of my colleagues asked me if he can unwrap a wrapped pl/sql application. I asked him if he really needs to unwrap it or what he is after may be the SQL calls inside that wrapped application. And showed him the tkprof report of the event 10046 trace of a sample wrapped application, he loved it since it was a kind of a reverse engineering of the pl/sql api they were supplied by a third party company which is not around anymore.

Code Listing - Tracing a Wrapped PL-SQL Code Example

Above tkprof report shows that wrapping a simple pl/sql code like that still adds 57 internal SQL statement calls, 20 hard parses and 208 consistent gets. So before buying a wrapped application and deploying it to your production it may be better to ask yourself twice if you could force more the vendor to get the sources to avoid any performance cost and a possible future reverse engineering effort.

Recently I used 10046 event output to troubleshoot a weird ORA-xxxxx error problem with DBMS_CDC_SUBSCRIBE supplied package and I tried to reverse the database calls of APEX application development environment by starting the trace from a database login trace trigger. After getting used to 10g’s beauties like ASH my needs to use 10046 tracing are getting very limited, but I am sure event 10046 will remain as a powerful tool in my Oracle toolbox for years.

Anyway if you need more information on wrapping a pl/sql application please check Note:341504.1 - FAQ: Wrapping PL/SQL Source Code or you persist to unwrap a wrapped pl/sql please check this presentation :)