Continuing Ranit post from my previous thread.
ranit B wrote:
Instead, can we try this --
create table X(ename varchar2(10));
BEGIN
DBMS_ERRLOG.CREATE_ERROR_LOG(dml_table_name => 'X');
END;
Insert into X
Select ename from <your_table>
LOG ERRORS
REJECT LIMIT UNLIMITED;
/* Here using DML ERROR LOGGING technique we can trap all records into this table - ERR$_X */
select count(ename) from ERR$_X;
This should be fast coz we'll not be doing a FTS(Full Table Scan) on the main table. But... NOT TESTED.
Please try this and let me know if any concerns.
Hope this Helps,
Ranit B.
Edited by: ranit B on Dec 14, 2012 3:46 PM
HI Ranit
As you said i have created the above table and done step 2 and 3.
And i have stopped step 3 abruptly as i want the column size of 11(not 10). I am doing all these process in sql developer.
Now when am trying to dropping/altering the table it is giving the below error, kindly look into it.
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
and tested the below for finding the locked state
SQL> SELECT O.OBJECT_NAME, S.SID, S.SERIAL#, P.SPID, S.PROGRAM,S.USERNAME,
2 S.MACHINE,S.PORT , S.LOGON_TIME,SQ.SQL_FULLTEXT
3 FROM V$LOCKED_OBJECT L, DBA_OBJECTS O, V$SESSION S,
4 V$PROCESS P, V$SQL SQ
WHERE L.OBJECT_ID = O.OBJECT_ID
5 6 AND L.SESSION_ID = S.SID AND S.PADDR = P.ADDR
7 AND S.SQL_ADDRESS = SQ.ADDRESS;
FROM V$LOCKED_OBJECT L, DBA_OBJECTS O, V$SESSION S,
*
ERROR at line 3:
ORA-00942: table or view does not exist
but no previlages to see :(
Folks kindly let me know how to drop or alter
Edited by: Updated the lock table status