Locks in Oracle Database

Locks are held by Oracle Database to maintain the integrity during the concurrent updates. Locks are held until the transaction in a session is committed or rollbacked. Oracle maintains row level lock in the tables that are recently being updated.

In the distributed transaction, the network connection also plays vital role during locking. Once locked transaction in this mode never released if the failure in completion of processing on the remote side. This may also result in the object lock so that complete system may be affected.

So, it is the database administrator job to look after the database as well as to monitor the network passage side by side. Usually Oracle Database administrator use
v$locked_object to monitor the lock objects in the oracle database. One can get the session information and object held in the lock by linking v$locked_object to
v$session and dba_objects. Use folowing query to check the lock held in Oracle Database.

select c.owner, c.object_name, c.object_type, b.sid, b.serial#, b.status, b.osuser, b.machine
from v$locked_object a , v$session b, dba_objects c
where b.sid = a.session_id
and a.object_id = c.object_id;

You may kill the session to release the lock by

alter system kill session 'SID,SERIAL#';

Oracle releases locks during the restart of the database.

How to decrease the size of the datafiles

The dba_data_files view gives information about the physical datafiles in the database whereas dba_free_space view gives information about the free space in the datafiles with their related block id. The query below gives the datafiles free space and total space allocated for the datafiles.

select a.TABLESPACE_NAME, a.FILE_NAME, free_space/1024/1024 free_space_in_mb, a.BYTES/1024/1024 total_space_allocated_in_mb
from dba_data_files a, (select TABLESPACE_NAME,FILE_ID,sum(BYTES) FREE_SPACE from dba_free_space group by TABLESPACE_NAME,FILE_ID) b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME and
a.FILE_ID=b.FILE_ID
order by a.TABLESPACE_NAME,a.FILE_NAME;

With this information one can decrease the size of the datafile by the free space with the command given below:

alter database datafile 'filepath\filename' resize filesize;

Free Space in Percentage Tablespace Wise

select z.TABLESPACE_NAME,sum(z.free_space_in_mb) free_space_in_mb,
sum(z.total_space_allocated_in_mb) total_space_allocated_in_mb,
round(sum(z.free_space_in_mb)/sum(z.total_space_allocated_in_mb)*100,2) free_pct
from (
select a.TABLESPACE_NAME, a.FILE_NAME, free_space/1024/1024 free_space_in_mb, a.BYTES/1024/1024 total_space_allocated_in_mb
from dba_data_files a, (select TABLESPACE_NAME,FILE_ID,sum(BYTES) FREE_SPACE from dba_free_space group by TABLESPACE_NAME,FILE_ID) b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME and
a.FILE_ID=b.FILE_ID
) z
group by z.tablespace_name
order by z.TABLESPACE_NAME;

Free Space in Percentage At Data File Level

select a.TABLESPACE_NAME, a.FILE_NAME, free_space/1024/1024 free_space_in_mb, a.BYTES/1024/1024 total_space_allocated_in_mb, round((free_space/a.BYTES),2)*100 free_pct
from dba_data_files a, (select TABLESPACE_NAME,FILE_ID,sum(BYTES) FREE_SPACE from dba_free_space group by TABLESPACE_NAME,FILE_ID) b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME and
a.FILE_ID=b.FILE_ID
order by a.TABLESPACE_NAME,a.FILE_NAME;

Export Oracle Dump file from command prompt or from Window Run Mode

The oracle Export Utility is used to export entire database or users or list of tables to the path given by the FILE parameter. The DIRECT path method is much faster than conventional method. The direct path method reads data directly from the disk and places in the buffer and then sent to the export client , writes it to the disk. Whereas conventional path follow SQL SELECT statement to fetch the data thus have to evaluate expression in the memory before transporting the file to the client to write to the disk. The COMPRESS parameter specifies the extent management during import of the table data. If it is set to Y then the table data is consolidated into single extent during import otherwise it uses current storage parameter of the tables during import. It is good practise to verify the log file after the export of the file have been completed to verify the successful export.

EXP username/password FILE=file_path\filename.dmp DIRECT=Y COMPRESS=Y ROWS= Y OWNER=user_to_export LOG=log_path_name\logfilename.txt

You can export the selected tables from the file with the TABLES option

EXP username/password FILE=file_path\filename.dmp DIRECT=Y COMPRESS=Y ROWS= Y OWNER=user_to_export TABLES=TABLE_NAME LOG=log_path_name\logfilename.txt