1.What is your Oracle Database Version?

**select banner
from sys.v_$version;

2.Find out which parameter hasn’t installed in your Database?

**select ‘ The ‘||parameter||’ option is not installed’
from sys.v_$option
where value <> ‘TRUE’;

3.Find out duration of Database?

**select SYSDATE-logon_time “Days”, (SYSDATE-logon_time)24 “Hours”
from sys.v_$session
where sid=1 /
this is PMON */;

4..Delete all duplicate row from table

**DELETE FROM JOB_HISTORY
WHERE ROWID NOT IN (SELECT MIN(ROWID) FROM JOB_HISTORY GROUP BY EMPLOYEE_ID);

5.How to alter client who are connection to Database?

select username, osuser, machine, terminal, sid, serial#
from v$session where username=’SPINNING’

alter system kill session ‘150,127’ immediate;

alter system kill session ‘158,185’ immediate;

6.On error trigger for oracle developer .

DECLARE
lv_errtype VARCHAR2(3) := MESSAGE_TYPE;
lv_errcod NUMBER := MESSAGE_CODE;
lv_errtxt VARCHAR2(80) := MESSAGE_TEXT;
BEGIN
if lv_errcod = 40735 then
message(‘Duplicate Party_id. Found’||’ ….”‘||:YS_TRAN_MST.PARTY_NAME||'”‘);
message(‘Duplicate Party_id. Found’||’ ….”‘||:YS_TRAN_MST.PARTY_NAME||'”‘);
elsif      lv_errcod = 41800 then
message(‘No Value any value here’);
message(‘No Value any value here’);
elsif lv_errcod = 41830 then
message(‘Incomplete DO….”Item/Count Missing..!!”‘);
message(‘Incomplete DO….”Item/Count Missing..!!”‘);
else
Message(lv_errtype||’-‘||to_char(lv_errcod)||’:’||lv_errtxt);
end if;
exception
when     too_many_rows then
message(‘Duplicate Row found’);
message(‘Duplicate Row found’);
when NO_DATA_FOUND then
message(‘No Rows found’);
message(‘No Rows found’);
WHEN DUP_VAL_ON_INDEX THEN
message(‘You have tried to insert a duplicate supplier_id.’);
message(‘You have tried to insert a duplicate supplier_id.’);
when others then
message(‘Please Contact our office’);
message(‘Please Contact our office’);
END;

7.Select Nth Number of value from SQL.

select FINC_YEAR_ID INTO FINC_YEAR_NEXT
from (select rownum n,a.* from
( select distinct FINC_YEAR_ID from FINC_YEAR  order by FINC_YEAR_ID asc) a)
where n = FINC_YEAR+1;

8.Find out any kind of date/year/month from SQL.

–First day of current week(sunday)
select TRUNC(SYSDATE, ‘Day’) from dual;
–First day of next week(sunday)
select TRUNC(SYSDATE+7 , ‘Day’) from dual;
–First day of previous week(sunday)
select TRUNC(SYSDATE-7 , ‘Day’) from dual;
–First day of current month
select TRUNC(SYSDATE , ‘Month’) from dual;
–First day of previous month
select TRUNC(TRUNC(SYSDATE , ‘Month’)-1 , ‘Month’) from dual;
–First day of next month
select TRUNC(LAST_DAY(SYSDATE)+1 , ‘Month’) from dual;
–First day of current year
select TRUNC(SYSDATE , ‘Year’) from dual;
–First day of previous year
select TRUNC(TRUNC(SYSDATE , ‘Year’)-1 , ‘Year’) from dual;
–First day of next year
select ADD_MONTHS(TRUNC(SYSDATE , ‘Year’),12) from dual;
– First Day of Current quater
select TRUNC(SYSDATE , ‘Q’) from dual;
–  First Day of Previous Quarter
select ADD_MONTHS(TRUNC(SYSDATE , ‘Q’),-3) from dual;
–  First Day of Next Quarter
select ADD_MONTHS(TRUNC(SYSDATE , ‘Q’),3) from dual;

–Last day of current week(sunday)
select TRUNC(SYSDATE, ‘Day’)+6 from dual;
–Last day of next week(sunday)
select TRUNC(SYSDATE+7 , ‘Day’)+6 from dual;
–Last day of previous week(sunday)
select TRUNC(SYSDATE-7 , ‘Day’)+6 from dual;
–Last day of current month
select LAST_DAY(TRUNC(SYSDATE , ‘Month’)) from dual;
–Last day of previous month
select LAST_DAY(TRUNC(TRUNC(SYSDATE , ‘Month’)-1 , ‘Month’)) from dual;
–Last day of next month
select LAST_DAY(TRUNC(LAST_DAY(SYSDATE)+1 , ‘Month’)) from dual;
–Last day of current year
select LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE , ‘Year’),11)) from dual;
–Last day of previous year
select LAST_DAY(ADD_MONTHS(TRUNC(TRUNC(SYSDATE , ‘Year’)-1 , ‘Year’),11)) from dual;
–Last day of next year
select LAST_DAY(ADD_MONTHS(TRUNC(TRUNC(SYSDATE , ‘Year’)-1 , ‘Year’),-13)) from dual;
– Last Day of Current quater
select LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE , ‘Q’),2)) from dual;
–  Last Day of Previous Quarter
select TRUNC(SYSDATE , ‘Q’)-1 from dual;
–  Last Day of Next Quarter
select LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE , ‘Q’),5)) from dual;

9.Select January To December By SQL.

select to_char(add_months(to_date(‘

01/01/1000′, ‘DD/MM/RRRR’), ind.l-1), ‘MONTH’) as month_descr,
ind.l as month_ind
from dual descr,
(select l
from (select level l
from dual
connect by level <= 12
)
) ind
10. Number Value Show different type ways:
select TO_CHAR(12345.67, ‘99999V99’) from dual –: shift specified number of digits
select TO_CHAR(12345.67, ‘U99,999.99’) from dual
select TO_CHAR(12345.67, ‘99999.99EEEE’) from dual –: scientific notation
select TO_CHAR(12345.67, ‘99999D99’) from dual –: decimal point
select TO_CHAR(12345.67, ‘C99,999.99’) from dual —
select TO_CHAR(0.67, ‘B9.99’) from dual —
select TO_CHAR(12345.67, ‘$99,999.99’) from dual —
select TO_CHAR(12345.67, ‘99,999.9900’) from dual —
select TO_CHAR(12345.67, ‘099,999.99’) from dual —
select TO_CHAR(-12345.67, ‘99,999.99’) (2) from dual —
select TO_CHAR(12345.67, ‘99999G99’) from dual –: group separator
select TO_CHAR(12345.67, ‘TM’) from dual —
select TO_CHAR(2007, ‘RN’) from dual —
select TO_CHAR(-12345.67, ‘99,999.99PR’) from dual –: negative value in angle brackets
select TO_CHAR(12345.67, ‘L99,999.99’ ) from dual –: currency symbol
select TO_CHAR(0012345.6700, ‘FM99999.99’) from dual —
select TO_CHAR(-12345.67, ‘99,999.99MI’) from dual —
select TO_CHAR(10000, ‘$9,999.00’) from dual —
select decode(sign(-5000), -1,'(‘||abs(5000)||’)’,5000) from dual –negative value as ().

11.How to flashback drop table from recycle bin?

SELECT * FROM RECYCLEBIN;
/
FLASHBACK TABLE persons TO BEFORE DROP
/
FLASHBACK TABLE
“BIN$+Mf6bWc70G/gQAB/AQA+Dg==$0”
TO BEFORE DROP RENAME TO “PubEvent”

12.Like Operator use in decode function :

select DECODE(REGEXP_SUBSTR(:dt,’123′),NULL,’Not Match’,’Match’)  from dual
select DECODE(INSTR(:dt,’123′),0,’Not Match’,’Match’)  from dual
select DECODE(REPLACE(:dt,’123′),:dt,’Not Match’,’Match’)  from dual
select decode ( (select 1
from dual
where 1234 like ‘%12%4’),
1, ‘is a match’, ‘no match’) col
from dual;

SELECT SUM (DECODE (GREATEST (e.sal, 10000), 10000, e.sal + 1000, e.sal))
projected_salary_bill,
SUM (DECODE (LEAST (e.sal, 10000), 10000, e.sal + 1000, e.sal))
projected_salary
FROM emp e;

13.Data Edit from sql query :

select employee_id,salary,first_name, rowid from hr.employee;

14.Check and Uncheck multiple item:

DECLARE
v_last   NUMBER;
BEGIN
IF :crq2.s_chkall = ‘Y’
THEN
GO_BLOCK (‘DATA1’);
FIRST_RECORD;
LOOP
:data1.s_chk := ‘Y’;
exit when :system.last_record = ‘TRUE’;
NEXT_RECORD;
END LOOP;
ELSE
GO_BLOCK (‘DATA1’);
FIRST_RECORD;
LOOP
:data1.s_chk := ‘N’;
exit when :system.last_record = ‘TRUE’;
NEXT_RECORD;
END LOOP;
END IF;
END;

15.Enable and Disable On/Off for definite data Block:

PROCEDURE FPR_ENABLE_DISABLE (blk_name IN char, item_on_off IN Number) IS
NXT_ITEMNAME VARCHAR2(70);
A VARCHAR2(70);
BEGIN
Go_block(blk_name);
first_record;
NXT_ITEMNAME := blk_name||’.’||get_block_property(blk_name, first_item);
A:= NXT_ITEMNAME ;
loop
IF get_item_property(nxt_itemname, item_canvas) is not null AND
get_item_property(nxt_itemname, item_type)<> ‘DISPLAY ITEM’ AND
get_item_property(nxt_itemname, item_type)<> ‘RADIO GROUP’ then
set_item_property(nxt_itemname, ENABLED, item_on_off);
set_item_property(nxt_itemname, insert_allowed, item_on_off);
set_item_property(nxt_itemname, update_allowed, item_on_off);
end if;
NXT_ITEMNAME := blk_name||’.’||get_item_property(NXT_ITEMNAME, next_navigation_item);
if (NXT_ITEMNAME= blk_name||’.ROWID’) then
exit;
end if;
end loop;
END;

16.Negative Value show with Bracket:

select to_char(-133133,’99999999999PR’) from dual;
select TO_CHAR(12345.67, ‘99999V99’) from dual –: shift specified number of digits
select    TO_CHAR(12345.67, ‘U99,999.99’) from dual
select   TO_CHAR(12345.67, ‘99999.99EEEE’) from dual –: scientific notation
select   TO_CHAR(12345.67, ‘99999D99’) from dual –: decimal point
select   TO_CHAR(12345.67, ‘C99,999.99’)  from dual —
select  TO_CHAR(0.67, ‘B9.99’)   from dual —
select  TO_CHAR(12345.67, ‘$99,999.99’)   from dual —
select    TO_CHAR(12345.67, ‘99,999.9900’)  from dual —
select   TO_CHAR(12345.67, ‘099,999.99’)   from dual —
select    TO_CHAR(-12345.67, ‘99,999.99’) (2)  from dual —
select   TO_CHAR(12345.67, ‘99999G99’) from dual –: group separator
select    TO_CHAR(12345.67, ‘TM’) from dual —
select   TO_CHAR(2007, ‘RN’)    from dual —
select    TO_CHAR(-12345.67, ‘99,999.99PR’) from dual –: negative value in angle brackets
select    TO_CHAR(12345.67, ‘L99,999.99’ ) from dual –: currency symbol
select   TO_CHAR(0012345.6700, ‘FM99999.99’) from dual —
select  TO_CHAR(-12345.67, ‘99,999.99MI’) from dual —
select   TO_CHAR(10000, ‘$9,999.00’) from dual —
select decode(sign(-5000), -1,'(‘||abs(5000)||’)’,5000) from dual –negative value as ().