Archive for the ‘Oracle Queries’ Category

« Older Entries
Newer Entries »

Locking And Unlocking Tables In Oracle

August 4th, 2015, posted in Oracle Queries
Share

Locking And Unlocking Tables In Oracle,Locking Tables In Oracle,Unlocking Tables In Oracle,Tables In Oracle,Oracle DBA,DBA,APPS DBA,ORA DBA,Application DBA,Database DBA,Oracle Tricks,Locking Query in Oracle,Unlocking Query in DBA,Oracle Forms Error,Oracle Application Forms Error,Forms : Could not reserve record (2 tries). Keep trying,FRM-40501,FRM-40501: COULD NOT RESERVE RECORD [2 TRIES]; ORACLE APPS RECORD-LOCKING

One Way : 

Oracle puts locks while performing any DDL or DML operation on oracle tables.When table locks is present on any tables in Oracle we cannot run DDL on those tables.

Some of the locks automatically set by oracle are RS and RX Locks.

SELECT … FOR UPDATE execution results in RS (row share) table lock. When you execute an INSERT, UPDATE or DELETE Oracle puts RX (row exclusive) table lock.

We have to kill the session which holds the lock in order to execute further operations. Follow the below steps to kill the session and forcibly unlock the table.

Let’s assume that ‘EMP’ table is locked,

 

SELECT object_id FROM dba_objects WHERE object_name='EMP';
 OBJECT_ID
----------
   7401242

If there are no locks present for the table ‘EMP’ this query won’t return any values.

SELECT sid FROM v$lock WHERE id1=7401242
SID
----------
3434

 

SELECT sid, serial# from v$session where sid=3434
SID        SERIAL#
---------- ----------
3434       92193

 

ALTER SYSTEM KILL SESSION '3434,92193' ;

 

Once the session is killed you will be able to carry out any DDL activities on EMP table. Also you can check in TOAD if there are any active sessions associated to the SID that we killed, to make sure that the session has been killed.

 

 

*********************************************************************************************************************
Also Check : http://aliimmam.com/forms-could-not-reserve-record-2-tries-keep-trying/
Also Check : http://aliimmam.com/lock-tables-and-unlock-tables-syntax/
Note : Please not do make backups before using these queries and also confirm them yourself or by aother means as well. Source : http://www.bluegecko.net/oracle/frm-40501-could-not-reserve-record-2-tries-oracle-apps-record-locking/
*********************************************************************************************************************
Share
read more 2 comments

Connect Oracle SQL by Dos And Run Query

June 27th, 2015, posted in Microsoft, Oracle Queries
Share

 

1 – Make a bat file and connect it with Oracle Sql, let be file name connect.bat :

 

@echo off
sqlplus username/password@connect_identifier @C:UsersrizDesktopmYmy.sql

 

2 – Now make a sql file and write query in it !!

 

spool C:UsersmeDesktopmYmy_log.txt
set linesize 50
set pagesize 50
prompt Purpose:
prompt To run Daily
SELECT TO_CHAR (SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "NOW" FROM DUAL;
select * from v$instance;
SELECT TO_CHAR (SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "NOW" FROM DUAL;
exit
spool off;

 

And you are all done.
The first select will give you time so that you may know when it started, the second seelct will give you the instance details and the third select will give you time again so that you can check when it ended.

Share
read more no comments

Check Oracle Archived Redo Log

May 14th, 2015, posted in Oracle Queries
Share

Archive logging is essential for production databases where the loss of a transaction might be fatal. It is generally considered unnecessary in development and test environments.

To check if the ARCHIVELOG mode is enabled

  1. Log into Oracle server as an Oracle user with SYSDBA equivalent privileges.
  2. Enter the following command at the SQL*Plus prompt: 
    ARCHIVE LOG LIST;

 

Check Oracle Archived Redo Log,Check Oracle Archived ,Redo Log,Check Oracle Archiving,Check Oracle Redo Log,Redo Lob,Oracle Archived Redo,Oracle,Oracle DBA,Oracle DBA Task,Oracle Archiving,Oracle Archiving Log

To see the current archiving mode, query the V$DATABASE view:

SELECT LOG_MODE FROM SYS.V$DATABASE;

LOG_MODE
------------
ARCHIVELOG

The SQL*Plus command ARCHIVE LOG LIST displays archiving information for the connected instance. For example:

SQL> ARCHIVE LOG LIST

Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            D:oracleoradataIDDB2archive
Oldest online log sequence     11160
Next log sequence to archive   11163
Current log sequence           11163

This display tells you all the necessary information regarding the archived redo log settings for the current instance:

  • The database is currently operating in ARCHIVELOG mode.
  • Automatic archiving is enabled.
  • The archived redo log destination is D:oracleoradataIDDB2archive.
  • The oldest filled redo log group has a sequence number of 11160.
  • The next filled redo log group to archive has a sequence number of 11163.
  • The current redo log file has a sequence number of 11163.

 

You can also run this UNIX/Linux/Solairs command to see if you are running in archivelog mode.  If you are in ARCHIVELOG mode, you will see rows returned by this ps command:

$ ps -ef|grep -i _arc

——————————————————————-

Links :
http://www.dba-oracle.com/t_check_if_running_in_archivelog_mode.htm
http://docs.oracle.com/cd/B28359_01/server.111/b28310/archredo008.htm#ADMIN11355
http://psoug.org/reference/archivelog.html
https://support.ca.com/cadocs/0/CA%20ARCserve%20%20Backup%20r16-ENU/Bookshelf_Files/HTML/oraclewn/index.htm?toc.htm?ow_check_archivelog_mode.htm

Share
read more no comments

Query to find sessions generating more Archives

April 27th, 2015, posted in Oracle Queries
Share

This Query is to find the session that is generating more Archives.

 

select to_char(sysdate,'hh24:mi'), username, program , a.sid, a.serial#, b.name, c.value
from v$session a, v$statname b, v$sesstat c
where b.STATISTIC# =c.STATISTIC#
and c.sid=a.sid and b.name like 'redo%'
order by value;
Share
read more no comments

Oracle : Size Of Database

March 31st, 2015, posted in Oracle Queries
Share

An Oracle Database consists of data files, redo log files, control files, temporary files.Oracle : Size Of Database,Oracle,Size Of Database,Database,data files, redo log files, control files, temporary files,Oracle data files,Oracle redo log files,Oracle control files,Oracle temporary files
Whenever you say the size of the database this actually means the summation of these files.

 

 

select a.data_size+b.temp_size+c.redo_size+d.controlfile_size "total_size in MB"
from 
( select sum(bytes)/1024/1024 data_size from dba_data_files ) a,
( select nvl(sum(bytes),0)/1024/1024 temp_size from dba_temp_files ) b,
( select sum(bytes)/1024/1024 redo_size from sys.v_$log ) c,
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024 controlfile_size from v$controlfile) d;

-------------------------------------------------------------
For Round Figure For MB :

select a.data_size+b.temp_size+c.redo_size+d.controlfile_size "total_size in MB"
from 
( select round( sum(bytes)/1024/1024 ) data_size from dba_data_files ) a,
( select round( nvl(sum(bytes),0)/1024/1024 ) temp_size from dba_temp_files ) b,
( select round( sum(bytes)/1024/1024 ) redo_size from sys.v_$log ) c,
( select round( sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024 ) controlfile_size from v$controlfile) d;

-------------------------------------------------------------

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

-------------------------------------------------------------
For Round Figure GB :


select a.data_size+b.temp_size+c.redo_size+d.controlfile_size "total_size in GB"
from 
( select round( sum(bytes/1024/1024/1024) ) data_size from dba_data_files ) a,
( select round( nvl(sum(bytes),0)/1024/1024/1024 ) temp_size from dba_temp_files ) b,
( select round( sum(bytes/1024/1024/1024) ) redo_size from sys.v_$log ) c,
( select round( sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 ) controlfile_size from v$controlfile) d;



----------------------------------------------------------

Get database size from v$datafile:

select round((sum(bytes)/1048576/1024),2) from v$datafile;

------------------------------------------------------------
Get Oracle Database size from dba_data_files:

select "Reserved_Space(MB)", "Reserved_Space(MB)" - "Free_Space(MB)" "Used_Space(MB)","Free_Space(MB)"
from(
select 
(select sum(bytes/(1014*1024)) from dba_data_files) "Reserved_Space(MB)",
(select sum(bytes/(1024*1024)) from dba_free_space) "Free_Space(MB)"
from dual
);

-----------------------------------------------------------------
-

 

 

here
a is megabytes allocated to ALL datafiles
b is megabytes allocated to ALL TEMP files
c is megabytes allocated to ALL redo-logs
d is megabytes allocated to ALL control files

Share
read more no comments
« Older Entries
Newer Entries »