Invoking and Executing a Script from OEM

When including script names in OEM you must carefully include the fully-qualified path for the script: (i.e. /u01/app/oracle/scripts/myscript.sql). Placing custom scripts within OEM is a great way to improve reporting. Here is a quick overview of script execution from OEM:

1. Create a "new event" from the OEM console
2. Choose "enable unsolicited event" and then "test unsolicited event"
3. Enter the script name with the full path.

You can also use the oemevent command to invoke a SQL script from OEM:

root> oemevent /u01/app/oracle/scripts/myscript.sql destination alert "Monitored destination message"

Database Link From Oracle to SQL Server

After you succeed connecting Oracle with SQL Server probably you want to create database link between it. Oracle heterogeneous services allow you to define a database link between Oracle and SQL Server, as well as links to DB2 and other inferior databases.

Here are complete notes in creating a database link between Oracle and SQL Server.

1. Install Oracle ODBC drivers on the server (your local Oracle database) that will access the remote SQL Server database using the database link.
2. Setup the ODBC connection on the local Oracle database using the Windows ODBC Data Source Administrator
3. Test the ODBC drivers to ensure that connectivity is made to the SQL Server database.
4. Ensure that your global_names parameter is set to False.
5. Configure the Oracle Heterogeneous services by creating an initodbc.ora file within the Oracle database.

6. Modify the Listener.ora file.

        SID_NAME is the DSN for the remote database.
        ORACLE_HOME is the actual Oracle home file path.
        PROGRAM tells Oracle to use heterogeneous services.

        SID_LIST_LISTENER =
        (SID_LIST =
        (SID_DESC =
        (SID_NAME=Cas30C) -- Enter the DSN on this line
        (ORACLE_HOME = c:\oracle10gdb) -- Enter your Oracle home on this line
        (PROGRAM = hsodbc) ) )

7. Modify the Tnsnames.ora file. This is the Oracle database installation accessed by the users to connect to the ODBC drivers

        (DESCRIPTION=
        (ADDRESS_LIST=
        (Address=(PROTOCOL=TCP)
        (HOST=
        -- (Server x)
        (PORT=1521))) -- Enter the port on which the server x Oracle installation
        -- is listening
        (CONNECT_DATA=(SID=Cas30c)) - Enter the DSN name
        (HS=OK) -- Enter this value. It tells Oracle to use hetergeneous services
        )

8. Reload the listener on local Oracle database
9. Create a database link on the local Oracle installation that accesses the heterogeneous connection, which, in turn, connect to SQL Server.
10. Run a SQL Server Select statement from the Oracle installation using the database link.

Accessing Oracle Alert Log via SQL with External Tables

Starting in Oracle9i you can map external flat files to Oracle tables.
Mapping the Oracle alert log is easy and once defined, all you have to do is query it with standard SQL syntax:

create directory BDUMP as '/u01/app/oracle/admin/mysid/bdump';

create table
   alert_log ( msg varchar2(80) )
organization external (
   type oracle_loader
   default directory BDUMP
   access parameters (
      records delimited by newline
   )
   location('alrt_mysid.log')
)
reject limit 1000;

Now we can easily extract important Oracle alert log information without leaving SQL*Plus:

select msg from alert_log where msg like '%ORA-00600%';

ORA-00600: internal error code, arguments: [17034], [2940981512], [0], [], [], [ ], [], []
ORA-00600: internal error code, arguments: [18095], [0xC0000000210D8BF8], [], [], [], [], []
ORA-00600: internal error code, arguments: [4400], [48], [], [], []

Computing date differences

One of the confounding problems with Oracle DATE datatypes is the computation of elapsed time.

Oracle supports date arithmetic and you can make expressions like "date1 - date2" to get the difference between the two dates. Once you have the date difference, you can use simple techniques to express the difference in days, hours, minutes or seconds.

To get the values for data differences, you must choose you unit of measurement, and this is done with the data format mask:

It might be tempting to use sophisticated conversion functions to convert a data, but we will see that this is not the most elegant solution:

round(to_number(end-date-start_date))– elapsed days



round(to_number(end-date-start_date)*24)– elapsed hours


round(to_number(end-date-start_date)*1440)– elapsed minutes

How are elapsed time data displayed by default?  To find out, we issue a simple SQL*plus query:

SQL> select sysdate-(sysdate-3) from dual;



SYSDATE-(SYSDATE-3)

-------------------

                  3

Here we see that elapsed times are expressed in days. Hence, we can use easy conversion functions to convert this to hours or minutes:

However, when the minutes are not a whole number, we have the problem of trailing decimal places:

select

   (sysdate-(sysdate-3.111))*1440

from

   dual;


(SYSDATE-(SYSDATE-3.111))*1440

------------------------------

                    4479.83333

Of course, we can overcome this with the ROUND function, remembering that we must first convert the DATE datatype to a NUMBER:

select

   round(to_number(sysdate-(sysdate-3.111))*1440)

from

   dual;



ROUND(TO_NUMBER(SYSDATE-(SYSDATE-3.111))*1440)

----------------------------------------------

                                          4480

Hence, we can use these functions to convert an elapsed time into rounded elapsed minutes, and place the value inside an Oracle table. In this example, we have a logoff system-level trigger that computes the elapsed session time and places it inside a Oracle STATSPACK user_log extension table:

update

   perfstat.stats$user_log

set

   elapsed_minutes =

   round(to_number(logoff_time-logon_time)*1440)

where

   user = user_id

and

   elapsed_minutes is NULL;

Oracle SQL : Coding a matrix of display values

Oracle has a fascinating non-ANSI feature called an in-line view. The in-line view allows for a query to be placed into the FROM clause of an SQL statement, where you would normally place a table name.

By performing a SELECT in the FROM clause, we can summarize two columns, and display a third value based on two other columns.

In the example below, we take the employee name (ename) and the department number (deptno) and display the salary of the employee by their department.

prompt Display of Salary by Department

 

SELECT 

   *

FROM  (SELECT

         ename,

         sum(decode(deptno,10,sal)) DEPT10,

         sum(decode(deptno,20,sal)) DEPT20,

         sum(decode(deptno,30,sal)) DEPT30,

         sum(decode(deptno,40,sal)) DEPT40

       FROM

         emp

       GROUP BY

         ename)

ORDER BY 1;

 

Display of Salary by Department

 

ENAME          DEPT10     DEPT20     DEPT30     DEPT40

---------- ---------- ---------- ---------- ----------

ADAMS                       1100

ALLEN                                  1600

BLAKE                                  2850

CLARK            2450

FORD                        3000

JAMES                                   950

JONES                       2975

KING             5000

MARTIN                                 1250

MILLER           1300

SCOTT                       3000

SMITH                        800

TURNER                                 1500

WARD                                   1250

This type of query is very useful for Oracle data warehouse system where you must display information from a FACT table according to the values of two other columns.

Oracle Package Initialization

Did you notice that Oracle PL/SQL packages can have an initialization section? The initialization part of a package is run only once, the first time you reference the package. Here is an example:

CREATE PACKAGE emp_actions AS
     /* Declare externally callable subprograms. */
     FUNCTION hire_employee (
        ename  VARCHAR2,
        job    VARCHAR2,
        mgr    REAL,
        sal    REAL,
        comm   REAL,
        deptno REAL) RETURN INT;
  END emp_actions;

  CREATE PACKAGE BODY emp_actions AS
     number_hired INT;  -- visible only in this package

     /* Fully define subprograms specified in package. */
     FUNCTION hire_employee (
        ename  VARCHAR2,
        job    VARCHAR2,
        mgr    REAL,
        sal    REAL,
        comm   REAL,
        deptno REAL) RETURN INT IS
        new_empno INT;
     BEGIN
        SELECT empno_seq.NEXTVAL INTO new_empno
        FROM dual;
        INSERT INTO emp VALUES (new_empno, ename, job,
           mgr, SYSDATE, sal, comm, deptno);
        number_hired := number_hired + 1;
        RETURN new_empno;
     END hire_employee;

  BEGIN  -- initialization part starts here
     INSERT INTO emp_audit
     VALUES (SYSDATE, USER, 'EMP_ACTIONS');
     number_hired := 0;
  END emp_actions;

The initialization part of a package is run just once, the first time you reference the package. So, in the last example, only one row is inserted into the database table emp_audit. Likewise, the variable number_hired is initialized only once.

Every time the procedure hire_employee is called, the variable number_hired is updated. However, the count kept by number_hired is session specific. That is, the count reflects the number of new employees processed by one user, not the number processed by all users.

Using rsync to Copy Oracle Home Directories

If you're using UNIX for your ORACLE database server, it is possible to copy the Oracle Database Software (the Oracle Home directory) from one server to another using rsync.

Here is the example rsync syntax to copy the Oracle Home from one server (svr1) to another server (svr2). This process will ensure that all symbolic links and files owned by root get copied over correctly. Login to the source database server (svr1) as the root user account and run the following:

[root@svr1 ~]# rsync -auvzpogl -e ssh /u01/app/oracle/product/10.2.0/db_1/ svr2:/u01/app/oracle/product/10.2.0/db_1/

Sending E-Mail in PL/SQL

Here some example code you can use for sending email from within PL/SQL

CREATE OR REPLACE PROCEDURE SEND_MAIL_TCP (
    msg_from     VARCHAR2 := 'sender@testing.com'
  , msg_to       VARCHAR
  , msg_subject  VARCHAR2 := 'E-Mail message from your database'
  , msg_text     VARCHAR2 := ''
)
IS
  c   UTL_TCP.CONNECTION;
  rc  INTEGER;
BEGIN
  c  := UTL_TCP.OPEN_CONNECTION('localhost', 25);       -- open the SMTP port 25 on local machine
  rc := UTL_TCP.WRITE_LINE(c, 'HELO localhost');
  rc := UTL_TCP.WRITE_LINE(c, 'MAIL FROM: '||msg_from);
  rc := UTL_TCP.WRITE_LINE(c, 'RCPT TO: '||msg_to);
  rc := UTL_TCP.WRITE_LINE(c, 'DATA');                  -- Start message body
  rc := UTL_TCP.WRITE_LINE(c, 'Subject: '||msg_subject);
  rc := UTL_TCP.WRITE_LINE(c, '');
  rc := UTL_TCP.WRITE_LINE(c, msg_text);
  rc := UTL_TCP.WRITE_LINE(c, '.');                     -- End of message body
  rc := UTL_TCP.WRITE_LINE(c, 'QUIT');
  UTL_TCP.CLOSE_CONNECTION(c);                          -- Close the connection
EXCEPTION
  WHEN others THEN
    RAISE_APPLICATION_ERROR(-20000, 'Unable to send e-mail message from PL/SQL routine.');
END;

Another Oracle apps DBA Interview Questions (with answer)

Questions:

1. What happens if the ICM goes down?
2. How will you speed up the patching process?
3. How will you handle an error during patching?
4. Provide a high-level overview of the cloning process and post-clone manual steps.
5. Provide an introduction to AutoConfig. How does AutoConfig know which value from the XML file needs to be put in which file?
6. Can you tell me a few tests you will do to troubleshoot self-service login problems? Which profile options and files will you check?
7. What could be wrong if you are unable to view concurrent manager log and output files?
8. How will you change the location of concurrent manager log and output files?
9. If the user is experiencing performance issues, how will you go about finding the cause?
10. How will you change the apps password?
11. Provide the location of the DBC file and explain its significance and how applications know the name of the DBC file.

Answers:

1. All the other managers will keep working. ICM only takes care of the queue control requests, which means starting up and shutting down other concurrent managers.
2. 
   
   • You can merge multiple patches.
   • You can create a response file for non-interactive patching.
   • You can apply patches with options (nocompiledb, nomaintainmrc, nocompilejsp) and run these once after applying all the patches.
3. Look at the log of the failed worker, identify and rectify the error and restart the worker using adctrl utility.
4. Run pre-clone on the source (all tiers), duplicate the DB using RMAN (or restore the DB from a hot or cold backup), copy the file systems and then run post-clone on the target (all tiers).
   Manual steps (there can be many more):
   
   • Change all non-site profile option values (RapidClone only changes site-level profile options).
   • Modify workflow and concurrent manager tables.
   • Change printers.
5. AutoConfig uses a context file to maintain key configuration files. A context file is an XML file in the $APPL_TOP/admin directory and is the centralized repository.
   When you run AutoConfig it reads the XML files and creates all the AutoConfig managed configuration files.
   For each configuration file maintained by AutoConfig, there exists a template file which determines which values to pick from the XML file.
6. 
   
   • Check guest user/password in the DBC file, profile option guest user/password, the DB.
   • Check whether apache/jserv is up.
   • Run IsItWorking, FND_WEB.PING, aoljtest, etc.
7. Most likely the FNDFS listener is down. Look at the value of OUTFILE_NODE_NAME and LOGFILE_NODE_NAME in the FND_CONCURRENT_REQUESTS table. Look at the FND_NODES table. Look at the FNDFS_ entry in tnsnames.ora.
8. The location of log files is determined by parameter $APPLCSF/$APPLLOG and that of output files by $APPLCSF/$APPLOUT.
9. 
   
   • Trace his session (with waits) and use tkprof to analyze the trace file.
   • Take a statspack report and analyze it.
   • O/s monitoring using top/iostat/sar/vmstat.
   • Check for any network bottleneck by using basic tests like ping results.
10. 
    
    • Use FNDCPASS to change APPS password.
    • Manually modify wdbsvr.app/cgiCMD.dat files.
    • Change any DB links pointing from other instances.
11. 
    
    • Location: $FND_TOP/secure directory.
    • Significance: Points to the DB server amongst other things.
    • The application knows the name of the DBC file by using profile option "Applications Database Id."

Five DBA Best Practices

Multiple Oracle Homes
My favorite best practice is the one about multiple Oracle Homes. Here it how it goes. When applying a patch or a patchset, I recommend against applying to the existing Oracle Home. Instead, I suggest creating a new Oracle Home, and apply the patches there.

I create the first Oracle Home at /app/oracle/db_1, for instance. When a patch comes out, I install the whole Oracle software in a different home -- /app/oracle/db_2 -- and then apply the patch there. During the process of installation and patch application, the database is up and running, as it runs off the home /db_1. When the outage window comes, all I have to do is to shut down Oracle; change Oracle Home to db_2 and bring the database up. If there is a problem, I can reset the Oracle Home back to the old one.

So, here is the conventional approach:

1. Shut down the database
2. Apply patch to the Oracle Home
3. Start the database
4. In case of problems:
5. Shut down the database
6. Roll back the patch
7. Start the database

Steps 2 and 6 could take as much as three hours depending on the amount of patching. The database is down during these times.

In the new approach:

1. Install new Oracle Home
2. Apply the patch to the new Home
3. Shut down the database
4. Change Oracle Home to the new location
5. Start the database
6. In case of problems:
7. Shut down the database
8. Change Oracle Home to the old one
9. Start the database

The database is down only during steps 4 and 8, which takes a couple of minutes at the most, not hours.

So, here are the advantages:

1. The downtime is significantly reduced, to one 60th of the original time.
2. The risk is reduced significantly, as there is no rolling back a patch; you just go to the older version.
3. You can perform a "diff" on these two homes to see what changed. You can see the differences across multiple homes as well.
4. You can take several databases running on the same server to the new Oracle Home one by one.
5. You can see the various Oracle Homes and what patch level they are on using the inventory.

The only negative part is the space consumption -- you need space for two Oracle Homes. But considering a typical Oracle Home takes about 4 GB or less, this aspect of the suggestion is trivial.

Set audit trail to DB
Set the audit trail to DB by placing the parameter AUDIT_TRAIL = DB in the initialization parameter file during the database creation. Setting this parameter does not start the auditing, because an explicit AUDIT command must be given on the object. But the parameter must be set to a value other than FALSE (the default) for the command to take effect. Being a non-dynamic parameter, the database must be bounced to change the value of AUDIT_TRAIL. To save the trouble and avoid an outage, always set the value to DB, even if you never intend to audit anything. It does not break anything and you will always be ready to audit when the time comes.

Don't use .log
Don't use .log as the extension of redo logs. Someone may run a script to remove all the log files assuming they are redundant and you will end up losing the online redo logs as well, forcing a database recovery. Instead, name them with extension "redo" or "rdo."

Preview RMAN Restore
Preview RMAN Restore to identify all the various backup pieces that will be used in the recovery process without doing an actual recovery. This eliminates any surprises from missing pieces during an actual recovery process.

Create a new Oracle user for clients running on the same server as the DB
The Oracle Database server software also contains the client piece, which allows the clients to connect to the database on the same server. But as a best practice do not use the same user or the software; use a new one. For instance, if "oracle" is the user to install Oracle software, create a new user called, say, "oraapp" and install the client-only software using that user. The user "oraapp" should not be part of the dba or the oinstall group; so this user can't log on to the database as sysdba. Create a new group called "appgrp" and assign the user oraaap to this group. All the application users on the box should also be part of the appgrp group. This way they can use the sqlplus, sqlldr and other executables on the server, but be able to connect as sysdba.

The common practice is to use the client software in the same user as the database software owner; but starting with 10.2, Oracle has changed the security policy that takes away the global execution permissions from the Oracle Home. So the only option is to let app users be part of the dba group or change the permissions on Oracle Home -- both make the database extremely vulnerable.

dbms_backup_restore Package

The dbms_backup_restore package is used as a PL/SQL command-line interface for replacing native RMAN commands, and it has very little documentation.

The Oracle docs note how to install and configure the dbms_backup_restore package:

“The DBMS_BACKUP_RESTORE package is an internal package created by the dbmsbkrs.sql and prvtbkrs.plb scripts. This package, along with the target database version of DBMS_RCVMAN, is automatically installed in every Oracle database when the catproc.sql script is run. This package interfaces with the Oracle database server and the operating system to provide the I/O services for backup and restore operations as directed by RMAN.”

The docs also note that “The DBMS_BACKUP_RESTORE package has a PL/SQL procedure to normalize filenames on Windows NT platforms.”

Oracle DBA John Parker gives this example of dbms_backup_restore to recover a controlfile:

declare
devtype varchar2(256);
done boolean;
begin
devtype:=dbms_backup_restore.deviceallocate( type=>'sbt_tape',
params=>'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=rdcs,OB2BARLIST=ORA_RDCS_WEEKLY)',
ident=>'t1');
dbms_backup_restore.restoresetdatafile;
dbms_backup_restore.restorecontrolfileto('D:\oracle\ora81\dbs\CTL1rdcs.ORA');
dbms_backup_restore.restorebackuppiece(
'ORA_RDCS_WEEKLY.dbf', DONE=>done );
dbms_backup_restore.restoresetdatafile;
dbms_backup_restore.restorecontrolfileto('D:\DBS\RDCS\CTL2RDCS.ORA');
dbms_backup_restore.restorebackuppiece(
'ORA_RDCS_WEEKLY.dbf', DONE=>done );
dbms_backup_restore.devicedeallocate('t1');
end;

Here are some other examples of using dbms_backup_restore:

DECLARE
devtype varchar2(256);
done boolean;
BEGIN
devtype := dbms_backup_restore.DeviceAllocate (type => '',ident => 'FUN');
dbms_backup_restore.RestoreSetDatafile;
dbms_backup_restore.RestoreDatafileTo(dfnumber => 1,toname => 'D:\ORACLE_BASE\datafiles\SYSTEM01.DBF');
dbms_backup_restore.RestoreDatafileTo(dfnumber => 2,toname => 'D:\ORACLE_BASE\datafiles\UNDOTBS.DBF');
--dbms_backup_restore.RestoreDatafileTo(dfnumber => 3,toname => 'D:\ORACLE_BASE\datafiles\MYSPACE.DBF');
dbms_backup_restore.RestoreBackupPiece(done => done,handle => 'D:\ORACLE_BASE\RMAN_BACKUP\MYDB_DF_BCK05H2LLQP_1_1', params => null);
dbms_backup_restore.DeviceDeallocate;
END;
/

--restore archived redolog
DECLARE
devtype varchar2(256);
done boolean;
BEGIN
devtype := dbms_backup_restore.DeviceAllocate (type => '',ident => 'FUN');
dbms_backup_restore.RestoreSetArchivedLog(destination=>'D:\ORACLE_BASE\achive\');
dbms_backup_restore.RestoreArchivedLog(thread=>1,sequence=>1);
dbms_backup_restore.RestoreArchivedLog(thread=>1,sequence=>2);
dbms_backup_restore.RestoreArchivedLog(thread=>1,sequence=>3);
dbms_backup_restore.RestoreBackupPiece(done => done,handle => 'D:\ORACLE_BASE\RMAN_BACKUP\MYDB_LOG_BCK0DH1JGND_1_1', params => null);
dbms_backup_restore.DeviceDeallocate;
END;
/

Creating Oracle Database Link

Oracle has invested heavily in distributed database technology and the creation of a database link is very straightforward. You specify the database link name, the remote user to connect to, the password for the remote user and the TNS service name for the database link connection:

create public database link
  mylink
connect to
  remote_username
identified by
  mypassword
using 'tns_name';

You can also create a database link to non-Oracle databases, here are steps for a database link to MySQL:

1. Your first step is having installed the TRANSPARENT GATEWAY (it comes in as of the options when you install Oracle).
2. You must have a user in the SQL Server.
3. In the directory \tg4msql\admin look for the file inittg4msql.ora and have the following options:
   

   HS_FDS_CONNECT_INFO="SERVER=name_server;DATABASE=name_db"
   HS_FDS_TRACE_LEVEL=OFF
   HS_FDS_RECOVERY_ACCOUNT=user_sqlserver
   HS_FDS_RECOVERY_PWD=pass_user_sqlserver

4. Configure the listener and add the following
   

   (SID_DESC =
   (PROGRAM = tg4msql)
   (SID_NAME = MSQL)
   (ORACLE_HOME = C:\oracle1)
   )
   )

   
   and on tnsnames.ora add the following
   

   MSQL=
   (DESCRIPTION =
   (ADDRESS_LIST =
   (ADDRESS = (PROTOCOL = TCP)(HOST = servidor)(PORT = 1521))
   )
   (CONNECT_DATA =
   (SID = MSQL)
   )
   (HS=OK)
   )

   
   where HS set up as a heterogonous service
5. Create a link to sql server.
   

   create database link msql connect to user_sqlserver identified by password using 'MSQL';

6. You can now use the database link to a foreign database:
   

   select * from table@msql

   
   

Connect Oracle to SQL Server

In order to connect to SQL*Server from Oracle, you must do several things:

1. Install ODBC drivers for the SQL*Server database. The drivers are installed on your laptop or on the server that contains the Oracle code tree.
2. Setup the ODBC connection on your laptop using the Windows ODBC Data Source Administrator
3. Test the ODBC drivers to ensure that connectivity is made to the non-Oracle database.
4. Ensure the Global_names parameter is set to False in the Oracle database.
5. Configure the Hetergeneous services. This consists of creating an initodbc.ora file within the Oracle
6. Oracle database installation.
7. Modify the Listener.ora file on the database installation to connect to the Oracle instance and ODBC drivers.
8. Modify the Tnsnames.ora file to point to the proper code tree.
9. Reload the listener
10. Create a database link on the Oracle installation.
11. Run a Select statement for the Oracle installation using the database link.

Finding Tables References Inside PL/SQL Store Procedure

The Oracle data dictionary tracks the object types referenced in PL/SQL with the dba_dependencies view. To track the dependency among packages and tables, try this dictionary query:

select
  referenced_owner,
  referenced_name,
  referenced_type
from
  dba_dependencies
where
  name= 'MY_STORE_PROC'
and
  owner = 'SCOTT'
order by
  referenced_owner, referenced_name, referenced_type;

you can also select the referenced_type, ie. tables

Oracle listener lsnrctl command

Here are lsnrctl command :

C:\>lsnrctl

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 12-PEB-2008 18:56:26

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> help
The following operations are available
An asterisk (*) denotes a modifier or extended command:

start               stop                status
services            version             reload
save_config         trace               change_password
quit                exit                set*
show*

LSNRCTL>

The following commands are used to manage the listener:

• start – Starts the listener with the name specified, otherwise LISTENER will be used. For Windows systems, the listener can also be started from the Control Panel.
• stop – Stops the listener. For Windows systems, the listener can also be stopped from the Control Panel.
• status – Provides status information about the listener, including start date, uptime, and trace level.
• services – Displays each service available, along with the connection history.
• version – Displays the version information of the listener.
• reload – Forces a read of the configuration file in order for new settings to take effect without stopping and starting the listener.
• save_config – Creates a backup of the existing listener.ora file and saves changes to the current version.
• trace – Sets the trace level to one of the following – OFF, USER, ADMIN, or SUPPORT.
• spawn – Spawns a program that runs with an alias in the listener.ora file.
• dbsnmp_start – Starts the DBSNMP subagent.
• dbsnmp_stop – Stops the DBSNMP subagent.
• dbsnmp_status – Displays the status of the DBSNMP subagent.
• change_password – Sets a new password for the listener.
• quit and exit – Exits the utility.
• set – Changes the value of any parameter. Everything that can be shown can be set.
• show – Displays current parameter settings.

Preventing Oracle DDL Unauthorized Schema Changes

One of the biggest problems in Oracle are "unexpected" schema changes. In production environments changes are often allowed only during special maintenance times, and changes such as re-analyzing schema statistics and DDL to change table/index structures must be prohibited at other times.

This can be tracked with an Oracle DDL trigger, but the DBA can disable DDL at the object level with a simple script to "disable table lock" syntax:

alter table mbsdba.t_dept disable table lock;

This script could be used to lock all tables within a schema:

connect sys/xxx as sysdba;
spool runme.sql
select "alter table "||owner||"."||table_name||" disable table lock;"
from
dba_tables
where
owner = "mbsdba";

spool off;
@runme.sql

While locked, any attempted schema changes will be rejected

SQL>ALTER TABLE mbsdba.t_dept ADD (NEWCOL NUMBER);

ALTER TABLE mbsdba.t_dept
*

ERROR at line 1:
ORA-00069: cannot acquire lock-- table locks disabled for mbsdba.t_dept 

Oracle SQL Sort Merge Join

The use of a sort merge join in Oracle SQL is quite common, especially in cases where there are missing join predicate against one of the tables or a missing index.
In a sort merge join, Oracle must perform full scans on the target tables, sort the keys and join the rows together.
It's important not to confuse a merge join with a merge join cartesian, which is a special case which is usually avoided.
The Oracle docs note these hints for merge joins:

• Sort merge join - Force a sort merge join (use_merge) The use_merge hint forces a sort merge join.
• Merge anti join (merge_aj) - Transforms a NOT IN subquery into a merge anti-join.
• Merge semi-join (merge_sj) - The merge_sj hint is placed into an EXISTS subquery; This converts the subquery into a special type of merge join between t1 and t2 that preserves the semantics of the subquery. That is, even if there is more than one matching row in t2 for a row in t1, the row in t1 is returned only once.
• Turn off sort merge join (_sortmerge_inequality_join_off) - This hidden parameter will disable a sort merge join in cases where the predicate is an inequality.

ORA-00918: column ambiguously defined

The Oracle docs note this on the ora-00918 error*:

ORA-00918 column ambiguously defined

    Cause: A column name used in a join exists in more than one table and is thus referenced ambiguously. In a join, any column name that occurs in more than one of the tables must be prefixed by its table name when referenced. The column should be referenced as TABLE.COLUMN or TABLE_ALIAS.COLUMN. For example, if tables EMP and DEPT are being joined and both contain the column DEPTNO, then all references to DEPTNO should be prefixed with the table name, as in EMP.DEPTNO or E.DEPTNO.
    
    Action: Prefix references to column names that exist in multiple tables with either the table name or a table alias and a period (.), as in the examples above.

*As a note, the ORA-00918 error does not exist in Oracle 10g, according to the Oracle documentation

When ORA-00918 is thrown, you have a column which has been ambiguously defined. If a column name in a join is referenced ambiguously, it exists in multiple tables.

• Column names which occur in multiple tables should be prefixed when it is referenced by its table name.
• Columns must be referenced as TABLE.COLUMN or TABLE_ALIAS.COLUM . Oracle documentation which reference ORA-00918 give the following example:
  if tables EMP and DEPT are being joined and both contain the column DEPTNO, then all references to DEPTNO should be prefixed with the table name, as in EMP.DEPTNO or E.DEPTNO.

To correct ORA-00918, references should be prefixed to column names existing in multiple tables (either with the table name or table alias and a period)

ORA-00904: STRING: invalid identifier tips

The Oracle docs note this on the ora-00904 error*:

ORA-00904 string: invalid identifier
    Cause: The column name entered is either missing or invalid.

    Action: Enter a valid column name. A valid column name must begin with a letter, be less than or equal to 30 characters, and consist of only alphanumeric characters and the special characters $, _, and #. If it contains other characters, then it must be enclosed in double quotation marks. It may not be a reserved word.

*According to Oracle documentation, ORA-00904 does not occur in Oracle 10g

When ORA-00904 occurs, you must enter a valid column name as it is either missing or the one entered is invalid.

To avoid ORA-00904, column names cannot be a reserved word, and must contain these four criteria to be valid:

• begin with a letter
• be less than or equal to twenty characters
• consist only of alphanumeric and the special characters ($_#); other characters need double quotation marks around them

Another important factor in correcting ORA-00904 is remembering to run catproc.sql

You can also check your trace file to find the particular error which is causing the ORA-00904 to occur.

ORA-00304: requested instance_number is busy

The Oracle docs note this on the ora-00304 error:

ORA-00304: requested INSTANCE_NUMBER is busy
 Cause: An instance tried to start by using a value of the initialization parameter INSTANCE_NUMBER that is already in use.
 Action: Either a) specify another INSTANCE_NUMBER, b) shut down the running instance with this number c) wait for instance recovery to complete on the instance with this number.

You will see ORA 00304 if an INSTANCE_NUMBER parameter value is being used at the time of initializing an Oracle instance
To resolve ORA-00304, you may try one of the three options:

1. Use a different INSTANCE_NUMBER
   In the initialization file, modify the initialization of the INSTANCE_NUMBER
2. Shut down the instance number causing the ORA-00304 error
3. On the instance with the ORA-00304, allow recovery to conclude