DBACTIONS INC - ORACLE DBA - TOOLS - CONSULTING
ORACLE DBA TOOLS - FREE SOFTWARE - FROM PUBLISHED ARTICLES
DOWNLOADS - UPDATES - FAQ - ERRATA
CREDITS AND LEGAL STATUS
Thank you for your visit.
I am always glad that people actually
read and try to use this Oracle tools software.
This website contains only Oracle tools
which have been published,
and are mature, tested, and ready to run.
I have used these Oracle tools for years in production,
on Unix (HP-UX, Sun Solaris, IBM AIX, Linux)
and Microsoft Windows servers (NT4, 2000),
and with Oracle 7.3.4, 8.0.x, 8.1.x.
The Auto-Table-Reorg package needs
Oracle 8.1.5 or higher.
The legal status of these Oracle tools,
referred to as "the software",
or as "the Oracle tools software", is:
- the copyright to the articles
is owned by their respective publishers,
as noted in the software comments
- the copyright and ownership of the code for these Oracle tools
belongs to my respective client or employer,
where I was working at the time of the development,
as noted in the software comments
- the Oracle tools software is freeware - you can freely use it,
at your own risk and responsibility,
however, if you change it significantly,
I may not be able to help you too much, should you need it
- as the author of the Oracle tools software, I will continue to maintain it;
you can send me feedback, bug reports, and suggestions,
and I'll try to work with them
and possibly include them in future updates
- I will do my best to respond as promptly as possible
to reasonable and moderate help requests,
but I am unable to provide commercial-style support
to these Oracle tools software
- you do not need to register or anything like that
in order to download or use the Oracle tools software,
and I do not collect any records other than hit counts
- should you contact me (at mike.hordila @ hordila.com),
I would appreciate if you could mention
your location: city, state, country...
(just for my personal curiosity)
- If products are listed in the install scripts
(which are my actual production scripts)
and are not present in the download files,
they are Oracle tools that I wrote for various clients,
and will be available on this website
when their legal status permits so.
It could take a while, but there is hope...
For now, good luck with the first ones.
FREQUENTLY ASKED QUESTIONS
1. A very common error looks like the following:
EXCEPTION IN:
BEGIN PKG_NDXSYS.P_MAIN; END;
*
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "SYS.UTL_FILE", line 98
ORA-06512: at "SYS.UTL_FILE", line 157
ORA-06512: at "MHSYS.PKG_NDXSYS", line 1006
ORA-06512: at "MHSYS.PKG_NDXSYS", line 339
ORA-06512: at "MHSYS.PKG_NDXSYS", line 313
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "MHSYS.PKG_NDXSYS", line 2302
ORA-06512: at line 1
This usually means that your
UTL_FILE_DIR parameter in INIT.ORA
is not configured properly.
The packages must write a log to /tmp in Unix
and to C:\TEMP in Windows.
Make sure the right directory exists
and that the oracle user can write there.
You need to have in INIT.ORA:
UTL_FILE_DIR = *
(remove all other lines containing "UTL_FILE_DIR"
and leave only UTL_FILE_DIR = *
as you can fine-tune this parameter later
for allowing access to just certain directories)
JOB_QUEUE_PROCESSES = 2
(or higher if you want to use DBMS_JOB,
make it 5 or 6 if you want to use more packages)
Then bounce the instance, and check V$PARAMETER:
select name, value
from v$parameter
where name = 'utl_file_dir';
NAME VALUE
-------------- --------------
utl_file_dir *
If it does not look like that,
you may be having typing mistakes,
or you may still have other "utl_file_dir" lines,
or you may be editing the wrong INIT.ORA file.
___________________________________________________________
2. Another very common error looks like the following:
Warning: Package Body created with compilation errors.
SQL> show errors
Errors for PACKAGE BODY PKG_......:
LINE/COL ERROR
-------- -----------------------------------------------------------------
269/3 PL/SQL: SQL Statement ignored
271/10 PL/SQL: ORA-00942: table or view does not exist
883/1 PL/SQL: Item ignored
884/20 PLS-00201: identifier 'SYS.DBA_SNAPSHOTS' must be declared
1031/7 PL/SQL: SQL Statement ignored
1031/13 PLS-00306: wrong number or types of arguments in call to
'CUR_DBA_SNAPSHOTS_NAMES'
1063/7 PL/SQL: SQL Statement ignored
1063/37 PLS-00364: loop index variable 'C1' use is invalid
:
:
:
SQL> show user
USER is "MHSYS"
SQL> desc sys.dba_snapshots
ERROR:
ORA-04043: object sys.dba_snapshots does not exist
This usually happens when the package owner
cannot read data dictionary views.
Check privileges, synonyms, the catalog.
Roles (even like DBA) do not work in stored procedures,
you have to grant explicit system/object privileges
directly to the user.
(Try to mimic the privileges included in the roles
SELECT_CATALOG_ROLE and HS_ADMIN_ROLE.)
Also, check INIT.ORA for:
O7_DICTIONARY_ACCESSIBILITY = TRUE.
___________________________________________________________
3. Tips on using the packages in controlled sessions
Sometimes you may need to do everything in one session
or maybe just run one session for selected objects
(analyze, or index rebuilds, or table moves, etc.)
- login as MHSYS, using SQLPlus
- truncate the %LIST table corresponding to the package to be run;
(this will force a new processing cycle)
- execute PKG_%SYS.P_MAIN;
(run the package once - check first that the P_MAIN procedure reads:
P_NEW_CYCLE; -- this module runs when the %LIST table is empty
-- P_RUN_SESSION; -- this module is commented out
This is the default in all my packages,
which means only the list of all objects is generated
and no actual processing is done on the first run of a new cycle.)
- update %LIST set SESSION_NUM = 0;
commit;
(all objects will be marked for not being processed)
- update %LIST set SESSION_NUM = 1 where "condition";
commit;
(selected objects will be marked for being processed in one session)
- review and update the pairs of tablespaces (source and target)
in the tables NDXSYS_TS and TABSYS_TS.
The first column is the location where you want to move
the object from (initial or source tablespace)
and the second column holds the location where you want
to move the object to (final or target or destination tablespace).
A "correct tablespace" is NOT listed in the first column,
but only in the second column, or not at all, and vice versa.
This move will be executed automatically by the package
if the current location of the object matches the tablespace
in the first column.
- If your object needs to be moved from a correct tablespace
(e.g. NDX_TS_4M) to another correct tablespace (e.g. NDX_TS_64M),
because, let's say, it has grown and needs larger extents,
then update the TABLESPACE_NAME column in the %LIST table,
with the name of the destination tablespace.
Do this NOW, after you have built the list and selected the objects,
and the package will move the object for you.
- execute PKG_%SYS.P_MAIN;
(run the package again to actually process the selected objects)
___________________________________________________________
4. Tips on using the packages with locally managed tablespaces
- The packages do not cater (yet) for locally managed tablespaces.
It's more difficult to ensure backwards compatibility
and make the software support all Oracle versions...
However, the reorgs should work just fine,
only the tablespace space management (PCTINCREASE and COALESCE)
will throw some errors that you normally can ignore.
- As a fix, you can either leave it as is, if you are not familiar with PL/SQL,
and normally it will generate errors and continue with the reorg,
or if you don't like error messages, you can just modify the declaration
of the cursor (cur_dba_tablespaces) to pick only
the dictionary managed tablespaces...
CURSOR cur_dba_tablespaces
IS
SELECT *
FROM dba_tablespaces
WHERE extent_management NOT LIKE '%LOCAL%';
- Or you can get more detailed control (depending on the package,
you may have to edit more sections) with something like this:
gc_module := cc_package_name||'.SET_FOR_SMON_WAKEUP';
gc_comment := 'set tablespaces for SMON cleanup on dictionary managed tablespaces';
P_DISPLAY_INIT;
FOR c1 IN cur_dba_tablespaces
LOOP
BEGIN
IF c1.extent_management LIKE '%LOCAL%' THEN
NULL;
ELSIF c1.contents = 'TEMPORARY'
OR c1.tablespace_name LIKE '%TEMP%'
OR c1.tablespace_name LIKE '%TMP%'
OR c1.tablespace_name LIKE '%RBS%' THEN
gc_sql_string := 'ALTER TABLESPACE '||c1.tablespace_name||
' DEFAULT STORAGE (PCTINCREASE 0)';
ELSIF c1.contents != 'TEMPORARY'
OR c1.tablespace_name NOT LIKE '%TEMP%'
OR c1.tablespace_name NOT LIKE '%TMP%'
OR c1.tablespace_name NOT LIKE '%RBS%' THEN
gc_sql_string := 'ALTER TABLESPACE '||c1.tablespace_name||
' DEFAULT STORAGE (PCTINCREASE 1)';
END IF;
P_DYNAMIC_SQL(gc_sql_string);
gc_display_01 := (gc_sql_string);
P_DISPLAY_ROW;
EXCEPTION WHEN OTHERS THEN
P_EXCEPTIONS;
END;
END LOOP;
___________________________________________________________
5. Tips on using the packages with Oracle9i
- Most modules already work with Oracle9i,
still there are some problems reported...
- I am working on it...
___________________________________________________________
6. A few things you can try if the packages seem to hang...
- If an error message is returned the packages try
to capture it. If it's not in the message column
in the '%LIST' table, it may be in the /tmp/....log file.
If the session hangs for some reason, and then times out,
it's possible that it's nothing to capture.
- You can also increase the amount of capture to display,
by un-commenting the relevant display lines, recompiling,
and running the package manually from SQLPlus.
Most display modules are commented out to avoid crashing the package
because of overloading the server output buffer,
uncomment them selectively for databases with very large numbers of objects.
- Re-organizing involves a lot of sorting,
try tuning the sort area and temp segments,
and also the online redo logs
- On some versions of Oracle, temporary TEMP tablespaces
can cause hangs, so they benefit from making
the TEMP tablespaces permanent
- Try using the package without ANALYZE VALIDATE, which waits
for lock on table and can hang if it cannot obtain it
(myself, I almost never use VALIDATE, for speed reasons)
- Try with no parallel (known bug - parallel hangs
when index re-builder is not index owner)
- Try no online (online is buggy - can hang and corrupt)
- Try nologging, if you do NOT have a standby database.
You MUST use logging/recoverable if you do have a standby,
otherwise you invalidate the standby...
- Go for smaller sessions, as on some versions
SMON is really slow in releasing the temp segments
- On some platforms (mostly if blocksize < 8K),
even data dictionary queries can take a very long time.
Try to use first a test database with a larger block size.
- If a package started from DBMS_JOB seems to hang
and you want to kill it,
first remove it from DBA_JOBS and then kill the session,
otherwise if you only kill the session
it will just be restarted again after one "job_queue_interval"
(default 60 seconds)
- If the package was started manually and seems to hang
you can just kill the session; sometimes you may need
to kill the OS process also
- Watch the archivelogs directory for space.
Reorgs of very large objects generate a lot of redo.
- Basically, try various scenarios, to get to know the package
and how your system reacts to it. Use Tablespace Manager
(from Oracle Enterprise Manager 1.6) or Tablespace Map
(in newer versions of OEM) to watch the space
moving around, especially the temp segments,
and Top Sessions to monitor what is being executed.
Good luck...
About "un-balanced" indexes
The main purpose of my article Automated Index-Rebuilding System
in Oracle Magazine Online, Sept 2001,
is to demonstrate a practical method
for a procedure that many DBA's still use, for various reasons.
The Oracle manuals, and also note 30405.1 state that
B*tree indexes are always balanced since
"the definition of a 'Balanced Tree' is that
all the data is on the same level".
This is a clear, elegant and un-equivocal theoretical concept,
which was introduced with the first indexing theories,
and which I believe was also introduced very early in Oracle.
I am more a practitioner than a theoretician,
and I think that this type of balance
may face some difficulties to implement perfectly in practice,
especially if other factors, like algorithms for compression,
for space and performance optimization, for allowing growth, etc.,
are present beside the B*tree algorithm.
As such, a number of practical imbalances can arise.
E.g., in the case of adding a lot of new sequential keys
they will accumulate at only one end of the tree,
causing a distribution imbalance, or "skewing".
Also, splitting repeatedly at the same level
will probably cause, in time, inefficient usage
of data blocks, causing a performance imbalance.
Also, large indexes tend to have higher levels than small indexes.
Oracle has introduced other types of indexes
to address some of the B*tree indexes imperfections,
and would recommend sometimes index rebuilds,
based on some criteria, to fix some of these problems.
You can see also my article in Oracle Internals, Mar 2003,
Algorithms And Methods For Oracle Defragmentation,
which tries to explain how index trees can grow upwards.
For the current view and an in-depth analysis
of B*tree indexes structure and re-building criteria
please see the excellent articles published
in DBAzine.com by Jonathan Lewis
Unbalanced Indexes? and When Should You Rebuild an Index?
DOWNLOAD FILES - ORACLE DBA TOOLS - FREE SOFTWARE - FROM PUBLISHED ARTICLES
LAST UPDATE --- 21-Jan-2005
ORACLE TECHNICAL BOOKS AUTHOR
Oracle Space Management Handbook
by Donald Burleson, Jonathan Lewis, Mike Hordila, et.al.
ISBN: 0-9744355-0-3, Rampant TechPress, Oracle eBooks, Sep. 2003
ORACLE TECHNICAL ARTICLES PUBLISHED - available on demand
Troubleshooting Java Error ArrayIndexOutOfBoundsException, Part 2 (DBAzine.com, Jan 2004)
Troubleshooting Java Error ArrayIndexOutOfBoundsException, Part 1 (DBAzine.com, Dec 2003)
Automated Data Conversion Management (DBAzine.com, Oct 2003)
Automated Data Encryption Management (DBAzine.com, Aug 2003)
Auditing Past Transactions With Oracle LogMiner (DBAzine.com, Jun 2003)
Diagnosing The Efficiency Of Disk Striping And Load Balancing (Oracle Internals, Apr 2003)
Algorithms And Methods For Oracle Defragmentation (Oracle Internals, Mar 2003)
Automated Oracle Tuning Initial Procedures, Part 2 (DBAzine.com, Feb 2003)
Oracle Multilingual Systems Setup (DBAzine.com, Jan 2003)
Architecture Principles Of Web-Enabled Oracle Applications (DBAzine.com, Dec 2002)
Diagnosing Oracle Replication Timings (Oracle Internals, Dec 2002)
Automated Oracle Tuning Initial Procedures, Part 1 (DBAzine.com, Nov 2002)
Automated Replication Management (DBAzine.com, Oct 2002)
Automated Generic Performance Monitor (DBAzine.com, June 2002)
Automated Space Cleanup In Oracle (DBAzine.com, May 2002)
Automated Recompiling Of Invalid Objects In Oracle (DBAzine.com, Mar 2002)
Automated Table/Index Reorganization In Oracle8i (DBAzine.com, Feb 2002)
Automated Index-Rebuilding System (Oracle Magazine Online, Sept 2001)
Automated Cost Based Optimizer (Oracle Magazine Online, Sept 2000)
ORACLE TECHNICAL BOOKS EDITOR
