Refresh Database Objects, Stats Gathering and Password update with value

# Full Schema Refresh
SQL> EXEC DBMS_UTILITY.compile_schema(schema => 'CSI');

# How to change Oracle password and restore with old one if you don't know current password

Step 1: Get the password value of user
SQL> select USER#,NAME,PASSWORD from user$ where name='CSI';

     USER# NAME    PASSWORD
---------- ------- ------------------
        63 CSI     A31A78DDCB990AE2

Step2: Change the user password with a new value and perform the activity.
SQL> ALTER USER CSI IDENTIFIED BY mynewpassword;

SQL> select USER#,NAME,PASSWORD from user$ where name='CSI';
     USER# NAME    PASSWORD
---------- ------- ------------------
        63 CSI     D3998D50331ED867

Step3: After completion of activity revert back to original password.
SQL> ALTER USER CSI IDENTIFIED BY VALUES 'A31A78DDCB990AE2';

How to Find Oracle Locked Objects and Session Issue

# Query to Identify Locked object in session
set pages 200 lines 200
SELECT B.Owner, B.Object_Name, A.Oracle_Username, A.OS_User_Name, A.Session_id FROM V$Locked_Object A, All_Objects B WHERE A.Object_ID = B.Object_ID;
select SID, SERIAL#, USER#, USERNAME,PROGRAM,SCHEMANAME,SCHEMA#, STATUS from v$session where USERNAME='SYS';
select SID,SERIAL#,SCHEMANAME,OSUSER, status from v$session where username='SYS';

# Create and Kill the session using below statements
select 'alter system kill session '''||SID||','||SERIAL#||''' immediate;' from v$session where username='USER';
alter system kill session '223,987' immediate;