1. If one or more not all control file/s got deleted/coruupted you can replace it by the good one. It is only hit and trial basis.

2. If you have backup of the control file you can restore it from the backup.

3. If none of these then create new control file.

DBA_BLOCKERS – Shows non-waiting sessions holding locks being waited-on
DBA_DDL_LOCKS – Shows all DDL locks held or being requested
DBA_DML_LOCKS – Shows all DML locks held or being requested
DBA_LOCK_INTERNAL – Displays 1 row for every lock or latch held or being requested with the username of who is holding the lock
DBA_LOCKS – Shows all locks or latches held or being requested
DBA_WAITERS – Shows all sessions waiting on, but not holding waited for locks
V$LOCK – view lists the locks currently held within the Oracle server, and outstanding requests for a lock or latch.
V$LOCKED_OBJECT – view combines session, rollback, and locked object information for locks acquired by transactions in the system.

Query to show locks for a specific user

SELECT
NVL(b.username,’SYS’) username,
session_id,lock_type,mode_held,
mode_requested,lock_id1,lock_id2
FROM
sys.dba_lock_internal a,
sys.v_$session b
WHERE …

Query using v$process and v$locked_object to see specific locks

SELECT s.sid, s.serial#, p.spid
FROM
v$session s,
v$process p
WHERE
s.paddr = p.addr
AND
s.sid IN (SELECT SESSION_ID FROM v$locked_object);

Query to Show locked objects

SET lines 100 pages 999
col username format a20
col sess_id format a10
col object format a25
col mode_held format a10
SELECT oracle_username || ‘ (‘ || s.osuser || ‘)’ username
, s.sid || ‘,’ || s.serial# sess_id
, owner || ‘.’ || object_name object
, object_type
, DECODE( l.block
, 0, ‘Not Blocking’
, 1, ‘Blocking’
, 2, ‘Global’) status
, DECODE(v.locked_mode
, 0, ‘None’
, 1, ‘Null’
, 2, ‘Row-S (SS)’
, 3, ‘Row-X (SX)’
, 4, ‘Share’
, 5, ‘S/Row-X (SSX)’
, 6, ‘Exclusive’, TO_CHAR(lmode)) mode_held
FROM v$locked_object v
, dba_objects d
, v$lock l
, v$session s
WHERE v.object_id = d.object_id
AND v.object_id = l.id1
AND v.session_id = s.sid
ORDER BY oracle_username
, session_id

SELECT sid, serial#, blocking_session_status, blocking_session
FROM v$session
WHERE blocking_session IS NOT NULL;

The PREVIEW option of the RESTORE command allows you to identify the backups required to complete a specific restore operation. The output generated by the command is in the same format as the LIST command. In addition the PREVIEW SUMMARY command can be used to produce a summary report with the same format as the LIST SUMMARY command. The following examples show how these commands are used:

# Spool output to a log file
SPOOL LOG TO c:\oracle\rmancmd\restorepreview.lst;

# Show what files will be used to restore the SYSTEM tablespace’s datafile
RESTORE DATAFILE 2 PREVIEW;

# Show what files will be used to restore a specific tablespace
RESTORE TABLESPACE users PREVIEW;

# Show a summary for a full database restore
RESTORE DATABASE PREVIEW SUMMARY;

# Close the log file
SPOOL LOG OFF;

select time_remaining
from v$session_longops
where sid =<sid of the session doing the rollback>;

Usage of v$session_longops:
===========================
SQL> desc v$session_longops;
SID NUMBER Session identifier
SERIAL# NUMBER Session serial number
OPNAME VARCHAR2(64) Brief description of the operation
TARGET VARCHAR2(64) The object on which the operation is carried out
TARGET_DESC VARCHAR2(32) Description of the target
SOFAR NUMBER The units of work done so far
TOTALWORK NUMBER The total units of work
UNITS VARCHAR2(32) The units of measurement
START_TIME DATE The starting time of operation
LAST_UPDATE_TIME DATE Time when statistics last updated
TIMESTAMP DATE Timestamp
TIME_REMAINING NUMBER Estimate (in seconds) of time remaining for theoperation to complete
ELAPSED_SECONDS NUMBER The number of elapsed seconds from the start of operations
CONTEXT NUMBER Context
MESSAGE VARCHAR2(512) Statistics summary message
USERNAME VARCHAR2(30) User ID of the user performing the operation
SQL_ADDRESS RAW(4  |  8) Used with the value of the SQL_HASH_VALUE column to identify the SQL statement associated with the operation
SQL_HASH_VALUE NUMBER Used with the value of the SQL_ADDRESS column to identify the SQL statement associated with the operation
SQL_ID VARCHAR2(13) SQL identifier of the SQL statement associated withthe operation QCSID NUMBER Session identifier of the parallel coordinator

This view displays the status of various operations that run for longer than 6seconds (in absolute time). These operations currentlyinclude many backup and recovery functions, statistics gathering, and queryexecution, and more operations are added for every Oracle release.

Activity Session History (ASH) represents the history of the activities of all recent active sessions captured efficiently through a circular buffer in memory and efficiently written to AWR to incur minimal overhead. The ASH data can be rolled up by different dimensions: TOP SQL, object, file, session, module, action, and so on.

The ASH report can be run through command line, by running the Oracle supplied SQL script located in $OH/rdbms/admin/ashrpt.sql.

Other way to access the ASH report is from the Database page. Choosing the Performance tab will generate a screen similar to the following.

Clicking “Run ASH Report.” brings up the Active Session History report:

This screen allows you to put the date and time of the start and finish times of the period in which you’re interested. Enter the date and time as needed and press the “Generate Report” button on the upper right. By default the date and time shows a 5-minute interval.

After you click the button, you will see the ASH report on the screen for that period. If you look carefully, you will see that the report resembles the STASPACK report; but since it comes from AWR data, the metrics in them are much more useful. A small portion of the screen is shown below:

V$SESSION has three new columns now show the status of tracing:

sql_trace—Shows (TRUE/FALSE) if SQL tracing has been enabled in the session
sql_trace_waits—If session tracing is enabled, you can have the trace write wait information to the trace file; very useful in diagnosing performance issues.
sql_trace_binds—If the session uses bind variables, you can have the trace write the bind variable values to the trace file. This column shows TRUE/FALSE.

When tracing in the session is not turned on

select sid, serial#, sql_trace, sql_trace_waits, sql_trace_binds
from v$session where username = ‘USER1′

The output is:

       SID    SERIAL# SQL_TRAC SQL_T SQL_T
       ———- ———- ——– —– —–
       196      60946 DISABLED FALSE FALSE

From the query we notice that tracing is not enabled in the session with SID 196 and Serial# 60946.

Now, enable tracing of wait events, but not of bind variables.

For that use the package dbms_monitor to enable tracing.

begin
   dbms_monitor.session_trace_enable (
      session_id   => 196,
      serial_num   => 60946,
      waits        => true,
      binds        => false
   );
end;
/

Now if you want to see the session information:
select sid, serial#, sql_trace, sql_trace_waits, sql_trace_binds
from v$session where username = ‘USER1′

The output is:
       SID    SERIAL# SQL_TRAC SQL_T SQL_T
       ———- ———- ——– —– —–
       196      60946 ENABLED  TRUE  FALSE

Note:

View V$SESSION is populated only if the procedure session_trace_enable in the package dbms_monitor is used to enable tracing, not by alter session set sql_trace = true or setting the event 10046

Step 1 – Create snapshot manually
exec DBMS_WORKLOAD_REPOSITORY.create_snapshot();
Step 2 – Create AWR Report
$cd $ORACLE_HOME
$cd rdbms
$cd admin
$sqlplus /nolog
SQL>connect / as sysdba
SQL>@awrrpt.sql
.
.
Enter value for begin_snap: 1405
.
.
Enter value for end_snap: 1406
.
.
Enter value for report_name:awrrpt_1_10405_10406.html

View v$sql_bind_capture, has been introduced to report information on bind variables used by SQL cursors. This view allows  the retrieval of the actual values of bind variables for a given SQL cursor.

The script below can be used to retrieve list of bind variables and the corresponding actual values used for a particular SQL statement. This query uses the sql_id address that should be specified for each unique SQL statement (you can get the SQL ID from the v$sql view):

select  sql_id,  t.sql_text SQL_TEXT,  b.name BIND_NAME,  b.value_string BIND_STRING
from  v$sql t  join v$sql_bind_capture b
using (sql_id)
where 
b.value_string is not null
and sql_id=’<sql id of the sql stmt>’

Use the built-in function VERIFY_SHRINK_CANDIDATE in the package DBMS_SPACE.

DBMS_SPACE.VERIFY_SHRINK_CANDIDATE takes 5 arguments and returns Boolean. Following is the signature of the function.

dbms_space.verify_shrink_candidate
(
segment_owner IN VARCHAR2, — Schema name in which segment resides.
segment_name IN VARCHAR2, — Name of the segment.
segment_type IN VARCHAR2, — Type of the segment(Table, Index etc.)
shrink_target_bytes IN NUMBER, — Size in bytes
partition_name IN VARCHAR2 DEFAULT NULL
)
RETURN BOOLEAN;

If the function returns true, then the specified segment is shrinkable otherwise it is not.

Example:

Below is the PL/SQL code to test if the segment can be shrunk to 300,000 bytes:

begin
if (dbms_space.verify_shrink_candidate
(‘USER1′,’TESTTABLE’,'TABLE’,300000)
) then
:X := ‘T’;
else
:X := ‘F’;
end if;
end;
/

PL/SQL procedure successfully completed.

SQL> print X

X
——————————–
T

Since the output is true the table ‘TESTTABLE’ can be shrunk to 300,000 bytes

If we use a low number for the target shrinkage, say 30,000:

begin
if (dbms_space.verify_shrink_candidate
(‘USER1′,’TESTTABLE’,'TABLE’,30000)
) then
:X := ‘T’;
else
:X := ‘F’;
end if;
end;

the value of the variable X is set to ‘F’, meaning the table cannot be shrunk to 30,000 bytes.