How to check locks and kill sessions in Oracle Database?

The below script will tell which session is holding the lock

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
/


The below command is used to kill the session from oracle

ALTER SYSTEM KILL SESSION 'SID,Searial#' IMMEDIATE ;

ALTER SYSTEM KILL SESSION '1626,19894' IMMEDIATE ;

ALTER SYSTEM DISCONNECT SESSION '13, 8' IMMEDIATE ;

How to compile all the invalid objects in Oracle Database?

Below is the script to generate dynamic script to compile all the invalid objects in Oracle Database.

set echo off
set feed off
set sqlp ''
set head off
set pages 0
spool spool_output.sql
select 'ALTER '||
DECODE(object_type, 'VIEW', 'VIEW', 'PACKAGE') || ' '||
LOWER(object_name)|| ' COMPILE '||
DECODE(object_type, 'PACKAGE', 'SPECIFICATION', 'PACKAGE BODY', 'BODY', '')|| ' ; ' from user_objects
where status = 'INVALID'
order by DECODE(object_type, 'VIEW', 1, 'PACKAGE', 2, 'PACKAGE BODY', 3), object_name ;
spool off
set echo on
set feed on
set sqlp 'SQL>'
set head on

select 'alter '||object_type||' '||object_name||' compile;' from user_objects where status='INVALID' order by object_type,object_name;

How to backup control file to trace in Oracle Database?

Below is the script to backup control file to trace on unix to user defined location.

===========================================================
#------------------------------------------------------------
# backup controlfile
#------------------------------------------------------------
#------------------------------------------------------------
# find the user_dump_dest directory through a sqlplus call
# and assign it to an environment variable
#------------------------------------------------------------
ORA_DUMP=`${ORACLE_HOME}/bin/sqlplus -s << EOF
/ as sysdba
set heading off
set feedback off
SELECT value
FROM v\\$parameter
WHERE name='user_dump_dest';
exit
EOF`
#------------------------------------------------------------
# create backup controlfile
#------------------------------------------------------------
${ORACLE_HOME}/bin/sqlplus -s <<> /dev/null 2>&1
/ as sysdba
set heading off
set feedback off
ALTER DATABASE BACKUP CONTROLFILE TO trace;
exit
EOF
#------------------------------------------------------------
# find the trace file that the backup controlfile is in
# this should be the last trace file generated
# validate it is a controlfile and copy to backup_control.sql
#------------------------------------------------------------
DATETIME="`date '+%d%m%y'`"
CONTROL=`ls -t $ORA_DUMP/*.trc | head -1`
if [ ! -z "$CONTROL" ]; then
grep 'CONTROL' ${CONTROL} 1> /dev/null
if test $? -eq 0; then
cp ${CONTROL} /home/oracle/backup_control_file_$DATETIME.sql
fi
fi
#end
=========================================================

How to check database size in Oracle Database?

Below is the script to check the size of the Oracle Database

select (a.data_size+b.temp_size+c.redo_size)/(1024*1024*1024) "total_size GB"
from ( select sum(bytes) data_size
from dba_data_files ) a,
( select nvl(sum(bytes),0) temp_size
from dba_temp_files ) b,
( select sum(bytes) redo_size
from sys.v_$log ) c;

How to check datafile usage in Oracle Database?

Below is the script script which will list datafile wise Allocated size, Used Size and Free Size
Size details are displayed in MB (Mega Bytes)

SELECT SUBSTR (df.NAME, 1, 40) file_name, df.bytes / 1024 / 1024 "Allocated Size(MB)",
((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0)) "Used Size (MB)",
NVL (SUM (dfs.bytes) / 1024 / 1024, 0) "Free Size(MB)"
FROM v$datafile df, dba_free_space dfs
WHERE df.file# = dfs.file_id(+)
GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes
ORDER BY file_name;

How to check the tablespace usage in Oracle Database?

Below is the script which will list tablespace wise free space and used space as well as total space.
The Size details are displayed in MB.

SELECT Total.name "Tablespace Name",
nvl(Free_space, 0) "Free Size(MB)",
nvl(total_space-Free_space, 0) "Used Size(MB)",
total_space "Total Size(MB)"
FROM
(select tablespace_name, sum(bytes/1024/1024) free_space
from sys.dba_free_space
group by tablespace_name
) Free,
(select b.name, sum(bytes/1024/1024) total_space
from sys.v_$datafile a, sys.v_$tablespace B
where a.ts# = b.ts#
group by b.name
) Total
WHERE Free.Tablespace_name(+) = Total.name
ORDER BY Total.name;

How to check which tablespace cannot extent causing tablespace full error?

Below is the script to check which tablespace cannot extent causing "tablespace full error"

select a.owner||'.'||a.segment_name "Segment Name",
a.segment_type "Segment Type",
a.bytes/1024/1024 "Size(MB)",
a.next_extent/1024/1024 "Next Extent",
a.tablespace_name "Tablespace Name"
from sys.dba_segments a
where a.tablespace_name not like 'T%MP%' -- Exclude TEMP tablespaces
and next_extent * 1 > ( -- Cannot extend 1x, can change to 2x...
select max(b.bytes)
from dba_free_space b
where a.tablespace_name = b.tablespace_name)
order by 3 desc;

How to check the locked objects in the Oracle Database?

Below is the script to check the locked objects in the Oracle Database

SELECT oracle_username USERNAME,
owner OBJECT_OWNER,
object_name, object_type, s.osuser,
s.SID SID,
s.SERIAL# SERIAL,
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 gv$locked_object v, dba_objects d,
gv$lock l, gv$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;

Note: Run the above select statement as sys user.

Difference between Physical and Logical Standby Database

Physical Standby Database:
A physical standby database is a exact copy of the primary database. Oracle uses the primary database archive log file to recover the physical standby database.

We can open a physical standby database in read only mode, but at the time of read only mode, the received logs will not be applied.

When the logs are applied, the database is not accessible.

Logical Standby Database:
Logical Standby database are opened in read/write mode, even while they are is applied mode. That is, they can be used to generate reports and the like. It is indeed a fully functional database.

How to Automate the caculation of Database Growth and scheduling it in DBMS JOBS....

1.Create a Table By the Name db_growth as shown below...

Name Null? Type
----------------------------------------- -------- ----------------------------
DAY DATE
DATABASE_SIZE_MB NUMBER
DAILY_GROWTH_MB NUMBER


2.create or replace PROCEDURE database_growth
AS
today_size NUMBER;
yesterday_size NUMBER;
growth_size NUMBER;
cnt NUMBER;
BEGIN
SELECT sum(bytes)/(1024*1024) INTO today_size FROM SM$TS_USED;
SELECT COUNT(1) INTO cnt FROM db_growth ;
IF cnt > 0
THEN
SELECT database_size_mb INTO yesterday_size FROM db_growth WHERE to_date(d
ay,'dd-mon-yy')=to_date(SYSDATE -1,'dd-mon-yy');
ELSE
yesterday_size:=today_size;
END IF;
growth_size := today_size - yesterday_size;
INSERT INTO db_growth VALUES(sysdate,today_size,growth_size);
EXCEPTION
WHEN no_data_found THEN
INSERT INTO db_growth VALUES(sysdate,today_size,0);
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;

3.Submit in DBMS_JOBS

variable jobno number;
begin
dbms_job.submit(
:jobno,
'database_growth ;',
trunc(sysdate+1) + 4/24,
'trunc(sysdate+1) + 4/24'
);
commit;
end;
/
print :jobno

How to calculate the bandwidth required between Primary and Standby Database for Archive log shipping?

Below is the detailed explaination for calculation of bandwith between primary and standby database.

The formula used (assuming a conservative TCP/IP network overhead of 30%) for calculating the network bandwidth is :
Required bandwidth = ((Redo rate bytes per sec. / 0.7) * 8) / 1,000,000 = bandwidth in Mbps

Measuring the Peak Redo Rate
Use the Oracle Statspack utility for an accurate measurement of the redo rate.
Based on your business you should have a good idea as to what your peak periods of normal business activity are. For example, you may be running an online store which historically sees the peak activity for 4 hours every Monday between 10:00 am - 2:00 pm. Or, you may be running a merchandising database which batch-loads a new catalog every Thursday for 2 hours between 1 am - 3 am. Note that we say "normal" business activity - this means that in certain days of the year you may witness much heavier business volume than usual, e.g. the 2-3 days before Mother's Day or Valentine's Day for an online florist business. Just for those days, perhaps you may allocate higher bandwidth than usual, and you may not consider those as "normal" business activity.However, if such periodic surges of traffic are regularly expected as part of your business operations, you must consider them in your redo rate calculation.

During the peak duration of your business, run a Statspack snapshot at periodic intervals. For example, you may run it three times during your peak hours, each time for a five-minute duration. The Statspack snapshot report will include a "Redo size" line under the "Load Profile" section near the beginning of the report. This line includes the "Per Second" and "Per Transaction" measurements for the redo size in bytes during the snapshot interval. Make a note of the "Per Second" value. Take the highest "Redo size" "Per Second" value of these three snapshots, and that is your peak redo generation rate.

Note that if your primary database is a RAC database, you must run the Statspack snapshot on every RAC instance. Then, for each Statspack snapshot, sum the "Redo Size Per Second" value of each instance, to obtain the net peak redo generation rate for the primary database. Remember that for a RAC primary database, each node generates its own redo and independently sends that redo to the standby database - hence the reason to sum up the redo rates for each RAC node, to obtain the net peak redo rate for the database.

Example:
Let us assume the redo rate is a 500 KB/sec.(500*1024=512000)
Required bandwidth = ((Redo rate bytes per sec. / 0.7) * 8) / 1,000,000 = bandwidth in Mbps Required bandwidth = ((512000/0.7) * 8) /1,000,000 Required bandwidth = 4.85 Mbps

Hope this helps...

How to change the location of datafiles in Oracle database?

Below are steps to move the datafiles without shutting down the database.

1. Make the tablespaces offline.
ALTER TABLESPACE tablespace_name offline;

2.copy the files to the new location at the OS level

3.Modify the location of the datafiles
ALTER TABLESPACE tablespace_name RENAME DATAFILE 'datafile_name' to 'datafile_name';

4.Make the tablesapces online
ALTER TABLESPACE tablespace_name online;

How to apply CPU Patches in Oracle Database?

Today I am going to give little insight about Opatch and CPU patch.Opatch is a tool is used to patch all oracle homes for oracle applications. CPU is stands for Critical patch updates, in other way its a security patches for Apps.

How to use Opatch?

Opatch is one of the easiest and safest for patching oracle apps because you can rollback opatch if you find any issues applying the patches

1. From the opatch read me make sure which oracle home you want to patch
2. Shutdown the instance related to particular oracle home.
3. Set the oracle home in the env .Set the opatch in the path.
4. Follow the readme whether opatch needs up gradation.
5. cd to the patch area and opatch apply
6. Proceed the post install steps
7. Run cpu_root.sh to give some permissions to the executables.
8. For rollback use opath rollback –id
9. “opatch lsinventory “ is used to list all the patches
10. “opatch lsinventory –details” is used to find the version belongs to particular oracle home.

CPU patch
CPU patch has to keep uptodate. Use opatch to apply CPU patches. Follow the opatch steps to apply the CPU patch. As part of post installation step run catcpu.sql and utlrp.sql

Issues will face
1. When you are applying CPU patch you will get conflict with some patches .Report to Oracle Corporation they will provide a new merged patch if its actually a conflict.

Script for Datafile usage in Oracle Database

This script will list datafile wise Allocated size, Used Size and Free Size

For running this query you must have SELECT privileges to V$DATAFILE and DBA_FREE_SPACE views or run the sript as sysdba

The Size details are displayed in MB (Mega Bytes)
-----------------------------------------------------------------------

SELECT SUBSTR (df.NAME, 1, 40) file_name, df.bytes / 1024 / 1024 "Allocated Size(MB)",
((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0)) "Used Size (MB)",
NVL (SUM (dfs.bytes) / 1024 / 1024, 0) "Free Size(MB)"
FROM v$datafile df, dba_free_space dfs
WHERE df.file# = dfs.file_id(+)
GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes
ORDER BY file_name;

Script to check tablespace usage in Oracle Database

SELECT Total.name "Tablespace Name",
nvl(Free_space, 0) "Free Size(MB)",
nvl(total_space-Free_space, 0) "Used Size(MB)",
total_space "Total Size(MB)"
FROM
(select tablespace_name, sum(bytes/1024/1024) free_space
from sys.dba_free_space
group by tablespace_name
) Free,
(select b.name, sum(bytes/1024/1024) total_space
from sys.v_$datafile a, sys.v_$tablespace B
where a.ts# = b.ts#
group by b.name
) Total
WHERE Free.Tablespace_name(+) = Total.name
ORDER BY Total.name;

How to enable primary key on a column having duplicate values in oracle database

You may come across a situation where you have to put Primary key on a column in table that is having duplicate rows and you cannot delete the duplicate rows.

Now this is a tricky situation.....below is the solution for the above problem.

for example you have to put primary key on "B column" "on ABC table" that is having duplicate values.

1) create index ABC_B_INX on B tablespace ABC;

2) alter table ABC add constraint ABC_B_PK primary key(B) disable;

3) alter table ABC enable novalidate constraint ABC_B_PK;

Hope this helps.....

How to check last ddl on a table in Oracle Database

drop table test;

CREATE TABLE test (
testcol VARCHAR2(20))
ROWDEPENDENCIES;

SELECT table_name, dependencies
FROM user_tables where table_name='TEST';

SELECT current_scn
FROM v$database;

INSERT INTO test VALUES ('ABC');

COMMIT;

INSERT INTO test VALUES ('ABC');

COMMIT;

INSERT INTO test VALUES ('ABC');

COMMIT;

SELECT ORA_ROWSCN, rowid, testcol FROM test;

SELECT current_scn
FROM v$database;

UPDATE test
SET testcol = 'DEF'
WHERE rownum = 1;

SELECT ORA_ROWSCN, rowid, testcol FROM test;

COMMIT;

SELECT ORA_ROWSCN, rowid, testcol FROM test;

UPDATE test
SET testcol = 'XYZ';

SELECT ORA_ROWSCN, rowid, testcol FROM test;

COMMIT;

SELECT ORA_ROWSCN, rowid, testcol FROM test;

CREATE TABLE test2 AS
SELECT * FROM test;

COMMIT;

SELECT ORA_ROWSCN, rowid, testcol FROM test2;

INSERT INTO test VALUES ('ABC');

UPDATE test SET testcol = 'DEF' WHERE rownum = 1;

UPDATE test2 SET testcol = 'GHI' WHERE rownum = 1;

COMMIT;

SELECT ORA_ROWSCN, rowid, testcol FROM test;

SELECT scn_to_timestamp(ORA_ROWSCN), rowid, testcol FROM test2;

SELECT scn_to_timestamp(ORA_ROWSCN) FROM test2;

SELECT current_scn
FROM v$database;

Keep observing the output of the above select queries.

How to drop Oracle Database

To drop the database follow the below steps:

shutdown abort;

startup mount exclusive restrict;

drop database;

exit;

Hope this helps.....

How to check locks and kill sessions in Oracle Database

The below script will tell which session is holding the lock

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
/


The below command is used to kill the session from oracle

ALTER SYSTEM KILL SESSION '1626,19894' IMMEDIATE

ALTER SYSTEM DISCONNECT SESSION '13, 8' IMMEDIATE

Steps to change character set of Oracle Database

If you are trying to change the character set, do the following:

% svrmgrl
SVRMGR> connect internal
SVRMGR> shutdown immediate
SVRMGR> startup mount
SVRMGR> alter system enable restricted session;
SVRMGR> alter system set job_queue_processes=0;
SVRMGR> alter system set aq_tm_processes=0;
SVRMGR> alter database open;
SVRMGR> alter database character set WE8ISO8859P1;
SVRMGR> alter database national character set WE8ISO8859P1;
SVRMGR> shutdown immediate;
SVRMGR> startup restrict;
SVRMGR> shutdown immediate;
SVRMGR> startup
SVRMGR> shutdown immediate;
SVRMGR> startup

This procedure is outlined in http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=66320.1 and explains why the database has to be shutdown/startup so many times.