ORACLE TIPS
& TRICKSRenaming Columns - Renaming columns is a
time-honored problem when it comes to Oracle... Once a table is created, there is no way
to change the name of a column, other than to drop the table and re-create it with
the changed column name. That is, there is no official way to do it.... An unofficial way
is to manipulate one of the underlying fixed tables.
Un-documented SQL*Net
Parameters -
This section shows in summary some un-documented or misunderstood configuration parameters
and tools for Net V2 and Net8.
Useful Alter Commands - This document was
created to help users become familiar with new Oracle8 & Oracle8i functionality. It by
no means is comprehensive but it does point out matters of interest that might be of
assistance to the DBA community in general.
Which, When and Whys:
Rebuilding Indexes - Periodically, indexes become skewed due to deletions and, therefore,
become good candidates for rebuild.
Archiver Halted - There are times when the
database ARCH process (ARCHIVER) will become hung. This problem is typically due to a disk
out of space or a hardware problem. The method described will permit recovery of the
database archiver without downtime.
Corrupted Rollback
Segments -
There are two undocumented parameters in Oracle to help in the resolution of problems
related to corrupted rollback segments.
Fast Full Scan - The Fast Full scan
permits the database to perform full-index scan more efficiently by resolving queries in
which all of the columns in the SELECT and WHERE clause are included in the specified
index.
Invoker/Definer Rights - The purpose of the
document is to explain object resolution and procedure privileges that are executed. These
are called using either invoker's or definer's rights.
Pipelined/Parallel
PL/SQL Table Functions - New pipelined and parallel PL/SQL table functions available in
Oracle9i. Contributed by Claudiu Ariton.
PL/SQL Wrapper - The PL/SQL Wrapper
utility can be used to encrypt PL/SQL source code, while still providing a platform
independent solution, it protects hard-earned development techniques.
Recover Control File - In extreme cases, it is
necessary for the DBA to re-build the control file for the database. The havoc that this
can cause can be devistating. And ultimately, the DBA would be required to re-build or
recover the database from a previous good backup. Now, there is another option....
Remote Database
Links/Synonyms - The first thing to note is that Oracle's Export and Import utilities
are not designed to work with SQL*Net. As a result, database links and synonyms that
reference remote databases are not exported.
Resizing Redo Logs - It has never been
possible to resize the redo logs within the controlfile. If a CREATE CONTROLFILE is
attempted with a RESETLOGS, an I/O error will be triggered. This is due to the fact that
the redo log header doesn't match the newly created controlfile. Currently, there is no
direct way to change the size of the members of a redo log group. This technique
identifies how to change database redo logs without incurring downtime.
MORE
|