2021年4月22日星期四

Oracle row lock and unlock

SELECT SID, SERIAL# FROM V$SESSION WHERE SID IN ( SELECT SID FROM V$LOCK WHERE TYPE IN ('TM','TX') );


SID SERIAL# ---------- ---------- 70 1604 197 868

alter system kill session 'SID, SERIAL#'

2021年4月21日星期三

Oracle sqlplus connect remote db sqlplus [username]/[passowrd]@[IP Addr]:[Port Number]/[Service Name]

 sqlplus [username]/[passowrd]@[IP Addr]:[Port Number]/[Service Name]

Oracle Table Data Row Counter

 select
    table_name
    , to_number( 
        extractvalue( 
            xmltype( 
                dbms_xmlgen.getxml('select count(*) c from ' || table_name)
            ) 
            , '/ROWSET/ROW/C'
        )
    ) count 
from
    user_tables 
WHERE
    TABLE_NAME NOT LIKE 'BIN$%' 
    and (iot_type != 'IOT_OVERFLOW' or iot_type is null) 
order by
    table_name

Oracle Drop User and User Schema

 DROP USER username CASCADE; -- drop user and user schema.

 DROP USER username ; -- drop user only.