select * from table_name where ... for update

The statement "select * from table_name where ... for update;" will hold rows of data for next DML statements in the same session. The selected rows is locked until a "commit" or a "rollback" command is issued. Any other session (even only a "select" statement) accessing those rows will have to wait until the lock is released. The Select For Update is easy to create a deadlock in the database. The lock can be seen from views:

SQL> select * from v$locked_object;
SQL> select * from v$transaction;


In a normal select, REDO is not used. But, the Select For Update will use REDO space.

SQL> set autotrace traceonly statistics
SQL> select * from test4update for update;

412167 rows selected.

Statistics
------------------------------------------------------
288 recursive calls
419550 db block gets
6003 consistent gets
0 physical reads
85955168 redo size 31676930 bytes sent via SQL*Net to client
192619 bytes received via SQL*Net from client
27479 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
412167 rows processed

Grant ACL to APPS account after database upgraded to 11g

After database for E-Buiness Suite is upgraded to 11g, you have to grant ACL privilege to APPS account for some EBS modules to work. Here phebsapp is the EBS Apps server name.

1. Check if view dba_network_acls has any row. If yes, run below script to add privilege to APPS account:

DECLARE
acl_path VARCHAR2(4000);
BEGIN
SELECT acl INTO acl_path FROM dba_network_acls
WHERE host = 'phebsapp' AND lower_port IS NULL AND upper_port IS NULL;
IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(acl_path,'APPS','connect') IS NULL THEN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl_path,'APPS', TRUE, 'connect');
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl_path,'APPS', TRUE, 'resolve');
END IF;
EXCEPTION
WHEN no_data_found THEN
-- below lines will create a ACL
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('apps.xml','ACL description', 'APPS', TRUE, 'connect');
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('apps.xml','APPS', TRUE, 'resolve');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('apps.xml','phebsapp');
END;
/

SQL> commit;

Please note "commit;" is necessary and very important. After that, you should see the entries in two views:

SQL> select * FROM dba_network_acls;
SQL> select * from dba_network_acl_privileges;

If you do not see rows, something is wrong.

And below two queries should return 1:

SQL> select DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE('/sys/acls/apps.xml','APPS','connect') from dual;

SQL> select DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE('/sys/acls/apps.xml','APPS','resolve') from dual;

2. if view dba_network_acls has no data. You can create ACL for the user:

SQL> show user
USER is "SYS"

BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('apps.xml','APPS acess for UTL', 'APPS', TRUE, 'connect');
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'apps.xml', principal => 'APPS',is_grant => true, privilege => 'resolve');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('apps.xml','phebsapp');
END;
/

Sometimes, the domain name makes difference. It does not hurt to add full name to the access list:

BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('apps.xml','phebsapp.domain.com');
END;
/

SQL> commit;

3. If things does not work, you can start over by dropping entries in dba_network_acls.

SQL> begin
DBMS_NETWORK_ACL_ADMIN.DROP_ACL('apps.xml');
commit;
end;
/

Now dba_network_acls, dba_network_acl_privileges, net$_acl should have nothing on apps.xml and everything no ACL is cleaned up.
SQL> select * from dba_network_acls;
SQL> select * from dba_network_acl_privileges;
SQL> select * from net$_acl;

Then, you can start over again.

Use DBMS_SCHEDULER to schedule jobs

In Oracle 10g and 11g, there are two places holding scheduled jobs:

DBA_SCHEDULER_JOBS
DBA_JOBS

Jobs in DBA_SCHEDULER_JOBS are managed by package DBMS_SCHEDULER, while jobs in DBA_JOBS are managed by package DBA_JOBS.

Here is an example on using DBMS_SCHEDULER to schedule a job:

BEGIN
      DBMS_SCHEDULER.CREATE_JOB(
      job_name=>'FIXED_OBJECTS_STATUS',
      job_type=>'PLSQL_BLOCK',
      job_action=>'BEGIN DBMS_STATS.GATHER_FIXED_OBJECTS_STATS; DBMS_STATS.GATHER_SCHEMA_STATS(''SYS'');END;',
      REPEAT_INTERVAL=>'FREQ=MONTHLY;BYMONTH=1,7;BYDAY=1 SUN;BYHOUR=14;BYMINUTE=16;BYSECOND=0',
      ENABLED=>TRUE
      );
END;
/

After that, you can see the job:

SQL> SELECT owner, job_name, job_class, enabled, next_run_date
FROM dba_scheduler_jobs WHERE job_name ='FIXED_OBJECTS_STATUS';

And, run it and check the log. View DBA_SCHEDULER_JOB_RUN_DETAILS holds the execution history for jobs in DBA_SCHEDULER_JOBS.
 
SQL> exec DBMS_SCHEDULER.run_job('FIXED_OBJECTS_STATUS');
SQL> select job_name, actual_start_date, status, job_name
FROM dba_scheduler_job_run_details WHERE job_name = 'FIXED_OBJECTS_STATUS' ;

Below line will disable a job:

SQL> exec dbms_scheduler.disable('AUTO_SPACE_ADVISOR_JOB');


Recently, a database gave errors in alert log:

Errors in file /u01/app/oracle/admin/dw/bdump/dw_j000_31068352.trc:
ORA-12012: error on auto execute of job 8912
ORA-08103: object no longer exists

I ran query to verify it is a scheduled job and to identify the job name:

SQL> select obj# , object_name FROM sys.scheduler$_job , dba_objects
WHERE obj# = object_id and obj# = 8912;

OBJ#  OBJECT_NAME
--------  -----------------
8912 AUTO_SPACE_ADVISOR_JOB

Then I used dbms_scheduler to disable the job.

NOTES: 
When I query DBA_SCHEDULER_JOBS using Sql*plus on the database server, I do not get error. But I get error when using Sql*plus on my Windows PC:

SQL> select * from DBA_SCHEDULER_JOBS;
ERROR:
ORA-01882: timezone region %s not found
no rows selected

Check document 414590.1 indicating the DST package on my PC needs an update.

RMAN script for duplicating database

I use below script to refresh our Dev database from Production database monthly, by using the Veritas backup of production database on the tape. It worked well.

ORACLE_SID=devebs ## target database name
export ORACLE_SID
export TNS_ADMIN=$ORACLE_HOME/network/admin
ORACLE_USER=oracle
NLS_LANG=AMERICAN_AMERICA.AL32UTF8
NLS_DATE_FORMAT='YYYY-MM-DD hh24:mi:ss'
export NLS_LANG
export NLS_DATE_FORMAT
NB_ORA_CLASS=ebsdb1p ## server name hosting source database PRODEBS
export NB_ORA_CLASS

DATE=`date +"%h%d%y_%H%M"`

rman catalog rman/rman_pwd@rman_db target sys/source_db_pwd@PRODEBS auxiliary / msglog devEBS_refresh.${DATE}.log << EOF
run {
ALLOCATE AUXILIARY CHANNEL ch00 TYPE 'SBT_TAPE';
ALLOCATE AUXILIARY CHANNEL ch01 TYPE 'SBT_TAPE';
ALLOCATE AUXILIARY CHANNEL ch02 TYPE 'SBT_TAPE';
SEND 'NB_ORA_CLIENT=ebsdb1p';
# Optional: put Temp in /u04 because of the size
SET NEWNAME FOR TEMPFILE 1 to '/u04/oracle/oradata/devebs/temp01.dbf';
SET NEWNAME FOR TEMPFILE 2 to '/u03/oracle/oradata/devebs/temp02.dbf';
duplicate target database to devebs
UNTIL scn = 5960105731382
;
}
EOF


In the script, statement "UNTIL scn = 5960105731382" is important. Without it, the script will NOT restore necessary archive logs for recovering the database and the refresh will fail (as tested in 10.2.0.4) unless you can manually get the archive logs from production server (which is not easy in ASM environment) and then do a manual recovery on the dev server.

In that case, the script only restores the data files from the latest backup to the target. You need to get all archive logs generated during the period when the latest backup started to the latest backup finished.

If you need to duplicate the target database on the same host with a different database SID, similar script can be used. See Document 73912.1 for more details.

CPU Usage by User Session

Oracle system views can be used to find the amount of CPU consumed by each database session. This script will display the CPU information:

SELECT
s.username, t.SID, p.spid DB_OS_ID,
t.VALUE/100 cpu_usage_seconds
FROM v$session s, v$sesstat t, v$statname n, v$process p
WHERE t.STATISTIC# = n.STATISTIC#
and n.NAME like '%CPU used by this session%'
and t.SID = s.SID
and s.paddr(+) = p.addr
and s.status='ACTIVE'
and s.username is not null
order by VALUE desc;

The OS commend "ps -ef" also gives CPU info in the 4th column of the output.

To troubleshoot performance issue, you can use below query and the session ID from above query to find the SQL statement for tuning candidate:

SELECT s.username, s.osuser, s.machine, s.program, s.status, s.sid, s.serial#, s.process client_pid, p.spid DB_os_pid, t.sql_text, t.disk_reads, t.executions
FROM v$session s, v$process p, v$sqlarea t
WHERE s.paddr = p.addr and s.sql_address = t.address(+)
and s.sid = &SID

In Oracle 11g, if the init parameter audit_trail is set to DB level by

SQL> alter system set audit_trail='DB' scope=spfile;

And then bounce the database, view dba_audit_session (or sys.aud$) has a column SESSION_CPU displaying session's CPU usage.