Cause :: Your table is already locked by some query.
Like you have executed "select for update" and has yet not
committed/rollback and again fired select query. Do a commit/rollback before
executing your query.
Find query which caused
the error ::
SELECT O.OBJECT_NAME, S.SID, S.SERIAL#, P.SPID,
S.PROGRAM,S.USERNAME,
S.MACHINE,S.PORT , S.LOGON_TIME,SQ.SQL_FULLTEXT
FROM V$LOCKED_OBJECT L, DBA_OBJECTS O, V$SESSION S,
V$PROCESS P, V$SQL SQ
WHERE L.OBJECT_ID = O.OBJECT_ID
AND L.SESSION_ID = S.SID AND S.PADDR = P.ADDR
AND S.SQL_ADDRESS = SQ.ADDRESS;
select sid, serial#, program,module, osuser,machine from
v$session where paddr= (select addr from v$process where spid=4357)
here 4357 is OS level
id using TOP
select
object_name,
object_type,
session_id,
type, -- Type or system/user lock
lmode, --
lock mode in which session holds lock
request,
block,
ctime -- Time since current mode was
granted
from
v$locked_object,
all_objects, v$lock
where
v$locked_object.object_id =
all_objects.object_id AND
v$lock.id1 = all_objects.object_id AND
v$lock.sid = v$locked_object.session_id
order by
session_id, ctime desc, object_name
select S.USERNAME, s.sid, s.serial#,s.osuser, t.sql_id, sql_text
from v$sqltext_with_newlines t,V$SESSION s
where t.address =s.sql_address
and t.hash_value =
s.sql_hash_value
and s.status = 'ACTIVE'
and s.username not
in('SYSTEM','SYS','SYSMAN')
order by s.sid,t.piece;
by using the above sid we can also know the percentage of
query execution done by below sql
COLUMN percent FORMAT 999.99
SELECT sid, to_char(start_time,'hh24:mi:ss') stime,
message,( sofar/totalwork)* 100 percent
FROM v$session_longops
WHERE sid=24;
select TABLESPACE_NAME, BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER;
select df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/
df.totalspace))
"Pct. Free"
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace,
tablespace_name
from dba_segments
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name ;
SELECT O.OBJECT_NAME, S.SID, S.SERIAL#, P.SPID, S.PROGRAM,S.USERNAME,
S.MACHINE,S.PORT , S.LOGON_TIME,SQ.SQL_FULLTEXT
FROM V$LOCKED_OBJECT L, DBA_OBJECTS O, V$SESSION S,
V$PROCESS P, V$SQL SQ
WHERE L.OBJECT_ID = O.OBJECT_ID
AND L.SESSION_ID = S.SID AND S.PADDR = P.ADDR
AND S.SQL_ADDRESS = SQ.ADDRESS;
select TEXT FROM DBA_VIEWS where OWNER = '<owner_name>' and
VIEW_NAME = '<view_name>';
select sid, start_time, totalwork sofar,
(sofar/totalwork) * 100 pct_done from gv$session_longops where totalwork >
sofar AND opname NOT LIKE '%aggregate%' AND opname like 'RMAN%';
SELECT
opname,
target,
ROUND( (
sofar/totalwork ), 4 ) * 100 Percentage_Complete,
start_time,
CEIL( time_remaining
/ 60 ) Max_Time_Remaining_In_Min,
FLOOR(
elapsed_seconds / 60 ) Time_Spent_In_Min
FROM
v$session_longops
WHERE sofar !=
totalwork;
SELECT
tablespace_name, SUM (bytes_used), SUM (bytes_free) FROM v$temp_space_header
GROUP BY tablespace_name;
11) ORA-01940:
cannot drop a user that is currently connected
SQL>drop
user username cascade;
drop user username cascade
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently
connected
SQL>select s.sid, s.serial#, s.status, p.spid
from v$session s, v$process p where s.username = 'ISL_DF' and p.addr (+) =
s.paddr;
SID
SERIAL# STATUS SPID
----------
---------- --------
------------
159
29 INACTIVE 12349
SQL>alter system kill session '159,29';
System altered.
SQL>!kill -9
12349 - (kill
the process in OS level)
SQL>drop user username cascade;
User dropped.
Error Message:
ORA-39083: Object type TYPE failed to create
with error:
ORA-02304: invalid object identifier literal
Failing sql is:
CREATE TYPE "TEST"."TTTTT"
OID '8E21EF4C1F6EF167E04011AC99643EB0' IS TABLE OF VARCHAR2(40);
This error happened when expdp and impdp a
schema to a new one in the same database.
Cause: OID should be unique in a database. the
OID in the impdp create statemene was used by old schema.
solution:
1. remove the OID from failing sql and
rerun it manually.
2. impdp again with parameter transform=OID:n
ERROR :: Checking for make...
found - /usr/bin/make
Checking for
ld... found - /usr/bin/ld
Checking for
gcc... found - /usr/bin/gcc
Checking for
g++... found - /usr/bin/g++
Checking for
ar... found - /usr/bin/ar
Checking for ksh...
Unable to find PD KSH version.
Unable to locate all utilities with system path.
PATH =
/u01/oracle/PROD/db/tech_st/11.2.3/appsutil/clone/bin/../jre/bin:/usr/bin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin
Solution :: export KSH_VERSION='@(#)PD
KSH v5.2.14 99/07/13.2'
Solution ::
yum remove $(rpm -qa | grep PACKAGENAME)
* Change PACKAGENAME with your Package name
* For disabling plugins just add --disableplugin=PLUGIN-NAME
* If you can't access the Internet, just add this options to
the line above --disablerepo=*
Solution::
SELECT l.inst_id,SUBSTR(L.ORACLE_USERNAME,1,8) ORA_USER,
SUBSTR(L.SESSION_ID,1,3) SID,
S.serial#,
SUBSTR(O.OWNER||'.'||O.OBJECT_NAME,1,40) OBJECT, P.SPID
OS_PID,
DECODE(L.LOCKED_MODE, 0,'NONE',1,'NULL',2,'ROW SHARE',3,'ROW
EXCLUSIVE',4,'SHARE',
5,'SHARE ROW EXCLUSIVE',
6,'EXCLUSIVE',
NULL) LOCK_MODE
FROM sys.GV_$LOCKED_OBJECT L, DBA_OBJECTS O, sys.GV_$SESSION
S, sys.GV_$PROCESS P
WHERE L.OBJECT_ID = O.OBJECT_ID
and l.inst_id = s.inst_id
AND L.SESSION_ID = S.SID
and s.inst_id = p.inst_id
AND S.PADDR = P.ADDR(+)
order by l.inst_id ;
Sql>select * from
fnd_product_installations where patch_level like '%ATG%’;
SELECT host_name,
instance_name,
TO_CHAR(startup_time,
'DD-MM-YYYY HH24:MI:SS') startup_time,
FLOOR(sysdate-startup_time)
days
FROM sys.v_$instance;
rpm -qa --queryformat
"%{NAME}-%{VERSION}.%{RELEASE} (%{ARCH})\n" | grep elfuti
$ADPERLPRG
$FND_TOP/patch/115/bin/TXKScript.pl
-script=$FND_TOP/patch/115/bin/txkInventory.pl -txktop=$APPLTMP
-contextfile=$CONTEXT_FILE -appspass=Today2014
-outfile=$APPLTMP/Report_App_Inventory.html
For 15 min ---sysdate
-1/96
For 1 hour --- sysdate -1/24
For 1 day -- sysdate -1
select
i.limit_time,F.USER_NAME,i.limit_connects, to_char(last_connect, 'DD-MON-RR HH:MI:SS')
"Last Connection time",i.user_id, i.disabled_flag from icx_sessions
i, FND_USER F where last_connect >
sysdate - 1/96 and i.user_id=F.user_id ;
select
count(distinct d.user_name) from apps.fnd_logins a, v$session b, v$process c,
apps.fnd_user d where b.paddr = c.addr
and a.pid=c.pid and a.spid = b.process and d.user_id = a.user_id and
(d.user_name = 'USER_NAME' OR 1=1);
multilib_policy=all
SELECT
'CREATE '||DECODE(U.NAME,'PUBLIC','public ')||'DATABASE LINK '||CHR(10)
||DECODE(U.NAME,'PUBLIC',Null,
'SYS','',U.NAME||'.')|| L.NAME||chr(10)
||'CONNECT TO
' || L.USERID || ' IDENTIFIED BY "'||L.PASSWORD||'" USING
'''||L.HOST||''''
||chr(10)||';'
TEXT
FROM
SYS.LINK$ L, SYS.USER$ U
WHERE
L.OWNER# = U.USER#;
Check the profile option value from
backend
SELECT
fpo.profile_option_name SHORT_NAME,
fpot.user_profile_option_name NAME,
DECODE (fpov.level_id,
10001, 'Site',
10002, 'Application',
10003, 'Responsibility',
10004, 'User',
10005, 'Server',
'UnDef')
LEVEL_SET,
DECODE (TO_CHAR (fpov.level_id),
'10001', '',
'10002',
fap.application_short_name,
'10003',
frsp.responsibility_key,
'10005', fnod.node_name,
'10006', hou.name,
'10004', fu.user_name,
'UnDef')
"CONTEXT",
fpov.profile_option_value VALUE
FROM fnd_profile_options fpo,
fnd_profile_option_values fpov,
fnd_profile_options_tl fpot,
fnd_user fu,
fnd_application fap,
fnd_responsibility frsp,
fnd_nodes fnod,
hr_operating_units hou
WHERE
fpo.profile_option_id = fpov.profile_option_id(+)
AND fpo.profile_option_name =
fpot.profile_option_name
AND fu.user_id(+) = fpov.level_value
AND frsp.application_id(+) =
fpov.level_value_application_id
AND frsp.responsibility_id(+) =
fpov.level_value
AND fap.application_id(+) =
fpov.level_value
AND fnod.node_id(+) = fpov.level_value
AND hou.organization_id(+) =
fpov.level_value
AND fpot.user_profile_option_name IN
('&User_Profile_Option_Name')
ORDER BY
short_name;
