APPS DBA
How to find the Oracle E-Business Suite R12 application URL?
$ sqlplus apps/appsdrdg
SQL*Plus: Release 10.2.0.4.0 – Production on Fri Oct 20 16:55:32 2017
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select home_url from icx_parameters;
HOME_URL
——————————————————————————-
http://PROD.EBS.COM:8000/OA_HTML/AppsLogin
How to rename a Datafile with Extension .dbf?
For example we have added a datafile but we forgot to give the extension .dbf
Step 1. Shut down the Database
SQL> shutdown immediate;
Step 2. Startup the Database in Mount State
SQL> startup mount
Step 3. Now copy the datafile by renaming it with .dbf extension
$cp /u01/app/oracle/user2 /u01/app/oracle/user2.dbf
Step 4. Now rename database file at Database Level
SQL> alter database rename file ‘/u01/app/oracle/user2’ to ‘/u01/app/oracle/user2.dbf’;
Step 5. Now Open the Database
SQL> alter database open;
How To Move Table to Different TableSpace
SYNTAX
ALTER TABLE table_name move TABLESPACE tablespace_name (INITIAL=64k MINEXTENTS=1 MAXEXTENTS=UNLIMITED);
EXAMPLE
ALTER TABLE hr.employee_details move TABLESPACE hr;
OR
ALTER TABLE hr.employee_details move TABLESPACE hr (INITIAL=64k MINEXTENTS=1 MAXEXTENTS=UNLIMITED);
How To Trace Concurrent Programs
CASE 1 : Concurrent Program Tracing without bind variables
1) Follow the following navigation to enable logging for Concurrent Program
- Goto Sysadmin > Concurrent > Program > Define
- Query the concurrent program
- Check the trace box to enable trace
2) Execute the concurrent program and note down the request id
CASE 2: Concurrent Program Tracing with bind variables and waits
1) Note down the following values
- SQL>SELECT value FROM v$parameter WHERE name = “max_dump_file_size”;
- SQL>SELECT value FROM v$parameter WHERE name = “timed_statistics”;
2) Execute the following commands as sysdba
- SQL>ALTER SYSTEM SET max_dump_file_size = unlimited;
- SQL>ALTER SYSTEM SET timed_statistics = true;
- SQL>ALTER SYSTEM SET EVENTS ‘10046 trace name context forever, level 12’;
3) Execute the concurrent program and note down the request id
4)Turn off tracing the reset the values.
- SQL>ALTER SYSTEM SET EVENTS ‘10046 trace name context off’;
- SQL>ALTER SYSTEM SET max_dump_file_size = <value from step 1>;
- SQL>ALTER SYSTEM SET timed_statistics = <value from step 1>;
CASE 3: Enabling the trace for a concurrent request for which you donot have privileges to run the concurrent Request.
1) Now, Run the concurrent program and get the request id .
2) Get the oracle_process_id for that concurrent request.
SELECT request_id,
oracle_process_id
FROM fnd_concurrent_requests
WHERE request_id IN (“xxxxxxx”);
3) Now get the session details ( SID and Serial ) using value obtained from step 2
col “SID/SERIAL” format a10 col username format a15
col osuser format a15 col program format a40
SELECT s.sid
|| “,”
|| s.serial# “SID/SERIAL”,
s.username,
s.osuser,
s.status,
p.spid “OS PID”,
s.inst_id,
s.MODULE
FROM sys.gv_$session s,
sys.gv_$process p
WHERE s.paddr = p.addr
AND s.inst_id = p.inst_id
AND p.spid = &value_from_step2
ORDER BY To_number(p.spid);
4) Execute the following command to enable the trace :
EXECUTE dbms_support.start_trace_in_session (&sid, &serial, binds=>true, waits=>
true);
5) Collect the trace from udump location and investigate the issue.
Reference Metalink Note: 296559.1 to know more about tracing.