Drop temp tablespace taking long time

Leave a reply

Created new temporary tablespace:

CREATE TEMPORARY TABLESPACE TEMP01 TEMPFILE ‘/u01/oracle/oradata/temp02.dbf’ SIZE 2g;

Make new temporary tablespace as default:

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP01;

Dropping old temporary tablespace, Which is taking long time to complete:

DROP TABLESPACE TEMP INCLDING CONTENTS AND DATAFILES;

The issue because of temp is being used by oracle sessions

SOLUTION 1:

SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP
SQL>SELECT NAME,OPEN_MODE,LOG_MODE FROM V$DATABASE;
SQL>DROP TABLEPSACE TEMP INCLUDIING CONTENTS AND DATAFILES;

SOLUTION 2:

Run below query and see any sessions using temp, Find out the all the sessions that are not active and kill those sessions.

select  tu.username,s.sid,s.serial# from v$tempseg_usage tu, v$session s
where tu.session_addr=s.saddr;

alter system kill session ‘sid,serial’;

Thank You !!

How to compile forms in 11i

2 Replies

we have to use f60gen for compiling forms and libraries in Oracle Applications Version 11i, But this is deprecated in R12 and we need to use frmcmp(Form Compiler)  to compile forms,menus,PL/SQL libraries.

1) Login to application server.

2) Go to the directory $AU_TOP/forms/US

3) Place “.fmb” file in binary mode

4) Execute the below “f60gen” command to generate “.fmx”.

f60gen module=<formname>.fmb userid=apps/<apps_passwd> output_file=<Form_Name>.fmx module_type=form batch=no compile_all=special

EXAMPLE:

f60gen module=$AU_TOP/forms/US/APXSUMBA.fmb userid=apps/appspasswd output_file=$PROD_TOP/forms/US/APXSUMBA.fmx module_type=form  batch=no compile_all=special

Thank You !!

 

How to compile forms in R12

Leave a reply

we have to use frmcmp(Form Compiler) for compiling forms and libraries in Oracle Applications Version R12, But in 11i we need to use f60gen to compile forms and libraries which is deprecated in R12.

1) Login to application server.

2) Go to the directory $AU_TOP/forms/US

3) Place “.fmb” file in binary mode

4) Execute the below command to generate “.fmx”.

frmcmp_batch userid=apps/<apps_paswd> module=<Form_Name>.fmb output_file=<Form_Name>.fmx module_type=form batch=no compile_all=special

EXAMPLE:

frmcmp_batch userid=apps/apps module=RCVRCERC.fmb output_file=$AU_TOP/forms/US/RCVRCERC.fmx module_type=form batch=no compile_all=special

Thank You !!

DROP DATABASE THROUGH RMAN

Leave a reply

In Oracle Database 10gR1 Oracle introduced the RMAN command DROP DATABSE. This one simple statement has the ability to completely remove a database including all RMAN backups with the optional INCLUDING BACKUPS clause.

sqlplus “/ as sysdba”

SQL> shutdown immediate;

SQL> startup mount exclusive

SQL> alter system enable restricted session

SQL> exit

rman target /

RMAN> drop database including backups;

 

Thank You !!

CHECK TABLESPACE SIZE, FREE AND USED SPACE

Leave a reply

Below query is used for finding total tablespace size and free, used space.

select tablespace_name, ROUND(bytes_free,2) free,ROUND(bytes_total-bytes_free,2) used, ROUND(bytes_total,2) total,perused
from (select a.tablespace_name, a.bytes_free,b.bytes_total,
ROUND((100-(100*a.bytes_free/b.bytes_total)),2) perused from
(Select tablespace_name,sum(bytes)/1024/1024 bytes_free From dba_free_space Group by tablespace_name) a,
(Select tablespace_name,sum(bytes)/1024/1024 bytes_total From dba_data_files Group by tablespace_name) b
where a.tablespace_name=b.tablespace_name) where perused >= 0 ORDER BY tablespace_name;

 

Thank You !!

ORA-12537: TNS:connection closed

1 Reply

Giving below error when trying to connect oracle user

ERROR:

Sqlplus scott/tiger@PROD

SQL*Plus: Release 10.2.0.1.0 – Production on Thu Oct 16 15:06:46 2008
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

ERROR:
ORA-12537: TNS:connection closed

SOLUTION:

Add the relevant LD_LIBRARY_PATH into the SID_DESC section in listener.ora file

(SID_DESC =
(ORACLE_HOME = /home/oracle/product/1020)
(ENVS=”LD_LIBRARY_PATH=/home/oracle/product/1020/lib:/usr/dt/lib:/usr/openwin/lib”)
(SID_NAME = PROD)
)

 

Thank You !!

Oracle ORA-21561 : OID generation failed

Leave a reply

Giving below error when trying to connect from Oracle client

“ORA-21561 : OID generation failed”

ERROR:

sqlplus <test_user>/<test_user_password>@<database_name>
xxxxx xxxx
xxxx xxxx
ERROR:
ORA-21561 : OID generation failed

Enter user-name: <username>/<password> @ <tns connect string>

SOLUTION:

Then the problem is most likely in the client machine hosts file.
Check that there is client machine fully qualified name and short name in the client machine hosts file. If these are missing you’ll get ORA-21561 errors when trying to connect server.
Example:

127.0.0.1            localhost.localdomain      localhost
10.160.116.10   dbamasters.oracle.com     dbamasters

Thank You !!

REP-0004: Warning: Unable to open user preference file

2 Replies

Symptoms:

All concurrent requests that run REPORTS are printing the following warning in the request log.

“REP-0004: Warning: Unable to open user preference file”
Your local (customized) Oracle Reports preference file could not be opened.
This is just a warning, so the product will continue to run even if this occurs.

The possible causes of this error include the following:

CAUSE 1: The file was not found under the specified name in the specified location
CAUSE 2: You lacked the necessary privileges to open the file.

SOLUTION [ID 1120529.1] :

To resolve the warning, copy the prefs.ora file from your Reports Builder $ORACLE_HOME/tools/admin/ directory into the Applications $HOME directory.

Example:

Copy of the file prefs.ora in the directory /u01/prod/apps/tech_st/10.1.2/tools/admin to the directory  /home/applprod/
Thank You !!

Create New Application user from existing user in EBS R12

1 Reply

We can create new application user from existing user with same responsibility, Which can be achieved from FNDLOAD utility.

Here I would like to create new user with name ‘APPADMIN’ (Target user) from existing user SYSADMIN (Source user).
SOURCE USER : SYSADMIN
TARGET USER : APPADMIN

To find the responsibilities of source user:
set pages 1200
set lines 200
SELECT frt.RESPONSIBILITY_NAME, furg.end_date
FROM
fnd_user_resp_groups furg,
FND_RESPONSIBILITY fr,fnd_responsibility_tl frt,fnd_user fu
WHERE fu.user_name = ‘&&username’
AND   fu.user_id = furg.user_id
AND   furg.responsibility_id = fr.RESPONSIBILITY_ID
AND   frt.responsibility_id = fr.RESPONSIBILITY_ID
ORDER BY 1;
 

STEP 1: Use FNDLOAD to download user SYSADMIN from your instance:

FNDLOAD apps/<apps password> 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct  SYSADMIN_USER.ldt FND_USER USER_NAME=SYSADMIN

STEP 2: Modify the ldt file (SYSADMIN_USER.ldt for the example above):

Change the line <BEGIN FND_USER “SYSADMIN”> to <BEGIN FND_USER “APPADMIN”>

 
BEGIN FND_USER “APPADMIN”
OWNER = “APPSMGR”
LAST_UPDATE_DATE = “2014/01/06”
START_DATE = “1951/01/01”

STEP 3: Use FNDLOAD to upload new user to your instance:

FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct  SYSADMIN_USER.ldt  FND_USER   USER_NAME=APPADMIN

STEP 4: Change the password of the new user from application and communicate.

 
Thank You !!

ORA-01031: insufficient privileges when connect as sysdba

1 Reply

Not able to connect Oracle database, When issue command — > sqlplus “/ as sysdba”

[oraprod@oracle-dba ~]$ sqlplus “/ as sysdba”
SQL*Plus: Release 10.2.0.1.0 Production on Wed Jan 22 02:09:17 2014
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
ERROR:
ORA-01031: insufficient privileges

SOLUTION:

SET CORRECT ORACLE SID
———————-
export ORACLE_SID=PROD         — LINUX
D:\oracle>set ORACLE_SID=PROD  — WINDOWS

SET ORACLE_HOME
—————
export ORACLE_HOME=/u01/oracle/product/10.2.0   — LINUX
set ORACLE_HOME=D:\oracle                                           — WINDOWS

SET SQLNET.ORA FILE
——————-
Check sqlnet.ora file for SQLNET.AUTHENTICATION_SERVICES parameter. It should be set to NTS for windows and for linux set it to ALL.

SQLNET.AUTHENTICATION_SERVICES=NTS  —WINDOWS
SQLNET.AUTHENTICATION_SERVICES=ALL  —LINUX

Please reproduce the issue and see if it resolves !!