Category Archives: Oracle

In this we are handling Oracle Database Administration and development task. If provide solution of ORA Errors and configuration Steps for setup in Oracle.

Check the Default data format in Oracle

Check the Default data format in Oracle

NLS DATE FORMAT is describe the default format in Oracle
You can change it at session level or system level.

Check the default date format

SQL> SELECT VALUE FROM NLS_SESSION_PARAMETERS WHERE PARAMETER = 'NLS_DATE_FORMAT';

VALUE
------------------------
DD-MON-RR

Change DATE format at session level

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

Check all the Default NLS setting in Oracle

show parameter NLS

NAME                    TYPE        VALUE
----------------------- ----------- ------------
nls_calendar            string      GREGORIAN
nls_comp                string      BINARY
nls_currency            string      $
nls_date_format         string      DD-MON-RR
nls_date_language       string      AMERICAN
nls_dual_currency       string      $
nls_iso_currency        string      AMERICA
nls_language            string      AMERICAN
nls_length_semantics    string      BYTE
nls_nchar_conv_excp     string      FALSE
nls_numeric_characters  string      .,
nls_sort                string      BINARY
nls_territory           string      AMERICA
nls_time_format         string      HH.MI.SSXFF AM
nls_time_tz_format      string      HH.MI.SSXFF AM TZR
nls_timestamp_format    string      DD-MON-RR HH.MI.SSXFF AM
nls_timestamp_tz_format string      DD-MON-RR HH.MI.SSXFF AM TZR

Advertisements

Script for Segment Advisory for partition tables in Oracle

Script for Segment Advisory for partition tables in Oracle

Script is used for reclaim activity which object has free space. Need to reclaim space to Operating system.

Copy paste the following script in command shell windows for segment advisory or reclaim the space from the object like table or index.
It asked for Object_type, Owner name and object name which you need to check for space reclaim activity.
Following script is consider for Partition table, it will provide you partition name with table name for help in reclaim acitivities.


--Segment Advisory Script:

SET SERVEROUTPUT ON SIZE 1000000
SET LINESIZE 200
SET VERIFY OFF

DECLARE
l_object_id NUMBER;
l_task_name VARCHAR2(32767) := 'SEGMENT_ADVISOR_TASK2';
l_object_type VARCHAR2(32767) := UPPER('&Object_type');
l_attr1 VARCHAR2(32767) := UPPER('&Owner_name');
l_attr2 VARCHAR2(32767) := UPPER('&object_name');
BEGIN
IF l_attr2 = 'NULL' THEN
l_attr2 := NULL;
END IF;

DBMS_ADVISOR.create_task (
advisor_name => 'Segment Advisor',
task_name => l_task_name);

DBMS_ADVISOR.create_object (
task_name => l_task_name,
object_type => l_object_type,
attr1 => l_attr1,
attr2 => l_attr2,
attr3 => NULL,
attr4 => 'null',
attr5 => NULL,
object_id => l_object_id);

DBMS_ADVISOR.set_task_parameter (
task_name => l_task_name,
parameter => 'RECOMMEND_ALL',
value => 'TRUE');

DBMS_ADVISOR.execute_task(task_name => l_task_name);
FOR cur_rec IN (SELECT f.impact,
o.type,
o.attr1,
o.attr2,
o.attr3,
f.message,
f.more_info
FROM dba_advisor_findings f
JOIN dba_advisor_objects o ON f.object_id = o.object_id AND f.task_name = o.task_name
WHERE f.task_name = l_task_name
ORDER BY f.impact DESC)
LOOP
DBMS_OUTPUT.put_line('..');
DBMS_OUTPUT.put_line('Type : ' || cur_rec.type);
DBMS_OUTPUT.put_line('Attr1 : ' || cur_rec.attr1);
DBMS_OUTPUT.put_line('Attr2 : ' || cur_rec.attr2);
DBMS_OUTPUT.put_line('Attr3 : ' || cur_rec.attr3);
DBMS_OUTPUT.put_line('Message : ' || cur_rec.message);
DBMS_OUTPUT.put_line('More info : ' || cur_rec.more_info);
END LOOP;

DBMS_ADVISOR.delete_task(task_name => l_task_name);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('Error : ' || DBMS_UTILITY.format_error_backtrace);
DBMS_ADVISOR.delete_task(task_name => l_task_name);
END;
/

Triggers in Oracle

Triggers in Oracle

Triggers are special kind of procedure which is fired automatically don’t need to call by user explicitly. It automatically fired on some event happen insert update delete operation on which it built or code.

Syntax:
CREATE [OR REPLACE] TRIGGER schema.trigger-name
{BEFORE | AFTER} dmlevent ON tablename
[FOR EACH ROW]
[DECLARE ...]
BEGIN
-- PL/SQL code goes here.
[EXCEPTION ...]
END;
/
BEFORE: defines the trigger is executed before the statement executed on table.
AFTER: defines the trigger is executed after the statement executed on table.
ROW LEVEL TRIGGER:FOR EACH ROW Clause is used for defined, trigger is executed at each row modification.
STATEMENT LEVEL TRIGGER: without clause FOR EACH ROW, executed at start and end at table level.
Example of ROW-LEVEL or STATEMENT-LEVEL trigger:
Before statement-level trigger executes
Before row-level trigger executes
One row is updated
After row-level trigger executes
...
Before row-level trigger executes
Millionth row is updated
After row-level trigger executes
After statement-level trigger executes
Note: Basically Statement trigger is used data is in bulk inserted or updated or deleted operation is started.

Check trigger information in database

SELECT TRIGGER_NAME FROM ALL_TRIGGERS WHERE TABLE_NAME = 'SALES'
SELECT * FROM DBA_TRIGGERS WHERE TRIGGER_NAME = 'AW_DROP_TRG';
SELECT OBJECT_NAME FROM USER_OBJECTS WHERE OBJECT_TYPE='TRIGGER';
SELECT TRIGGER_NAME,STATUS FROM USER_TRIGGERS WHERE TRIGGER_NAME = 'TRIGGER_NAME';

Get source or code for trigger

set heading off;
set echo off;
Set pages 999;
set long 20000;
select dbms_metadata.get_dependent_ddl('TRIGGER','EMP','SCOTT') output from dual;

Enable or Disable the Triggers

--Enable or disable single trigger
ALTER TRIGGER triggername DISABLE;
ALTER TRIGGER triggername ENABLE;

--Enable or disable all trigger at table level
ALTER TABLE tablename DISABLE ALL TRIGGERS;
ALTER TABLE tablename ENABLE ALL TRIGGERS;

--Enable or disable all trigger at schema level
select 'ALTER TABLE '||table_name||' DISABLE ALL TRIGGERS;' from user_triggers;
select 'ALTER TABLE '||table_name||' ENABLE ALL TRIGGERS;' from user_triggers;

Errors:
ORA-04082: NEW or OLD references not allowed in table level triggers
Solutions:
1. Eliminate the use of NEW OR OLD your statement in trigger.
2. Another option make add FOR EACH ROW clause in trigger.

Example of ROW LEVEL Trigger with OLD OR NEW keyword
We have table hr.sales for keep current value of sales data and hr.sales_bkp for taking backup or modification going in sales for keep track. We created trigger on sales table for taking backup.
OLD : old is pointing to UPDATE or DELETE old records.
NEW : New is pointing to INSERT or UPDATE new records.
Note: Update statement is having both OLD or NEW data pointer.

CREATE OR REPLACE TRIGGER t_test
BEFORE INSERT OR UPDATE OR DELETE ON hr.sales
FOR EACH ROW
BEGIN
IF inserting THEN
insert into hr.sales_bkp values ( :new.id, :new.name, :new.sales, sysdate ,'I');
END IF;
-- always set the modified date to now
IF updating THEN
insert into hr.sales_bkp values ( :old.id, :old.name, :old.sales, sysdate ,'U');
END IF;
IF DELETING THEN
insert into hr.sales_bkp values( :old.id, :old.name, :old.sales, sysdate ,'D');
END IF;
END;
/
Trigger created.

SQL> insert into sales values (1,'LG WM',25000);
1 row created.

SQL> update sales set name='SONY WM' where id = '1';
1 row updated.

SQL> update sales set sales='35000' where id = '1';
1 row updated.

SQL> delete from sales;
1 row deleted.

SQL> select * from sales_bkp;

  ID NAME            SALES CREATED_D F
---- ---------- ---------- --------- -
   1 LG WM           25000 11-SEP-18 I
   1 LG WM           25000 11-SEP-18 U
   1 SONY WM         25000 11-SEP-18 U
   1 SONY WM         35000 11-SEP-18 D

Example of STATMENT LEVEL TRIGGERS

--Create Statment Level Trigger
CREATE OR REPLACE TRIGGER trigger_Statement_test
BEFORE INSERT OR UPDATE OR DELETE ON hr.sales
BEGIN
IF inserting THEN
insert into hr.sales_bkp values ( '10','INSERTED',0,sysdate,'I');
END IF;
-- always set the modified date to now
IF updating THEN
insert into hr.sales_bkp values ( '10','UPDATED',0,sysdate,'U');
END IF;
IF DELETING THEN
insert into hr.sales_bkp values ( '10','DELETED',0,sysdate,'D');
END IF;
END;
/
--In this bulk operation of insert statement, Only one entry due to statement trigger.
insert into sales select * from sales_history;
4 rows inserted

--Check SALES_BKP table having STATEMENT LEVEL trigger entry and also have entries for ROW LEVEL trigger created in above example.
select * from sales_bkp;

   ID NAME            SALES CREATED_D F
----- ---------- ---------- --------- -
   10 INSERTED            0 11-SEP-18 I
    1 LG              20000 11-SEP-18 I
    2 SONY            20000 11-SEP-18 I
    3 DELL            25000 11-SEP-18 I
    4 LENOVO          40000 11-SEP-18 I

--Now Sales table has two triggers
SQL> select trigger_name from all_triggers where table_name = 'SALES';
TRIGGER_NAME
-----------------------
T_TEST
TRIGGER_STATEMENT_TEST

Script for tablespace space monitoring for Windows Platform in Oracle

Script for tablespace space monitoring for Windows Platform in Oracle

Script as PL/SQL anonymous block:


set feedback off
set serveroutput on
DECLARE
v_flag VARCHAR2(1) := 0;
v_date date;
BEGIN
Select sysdate into v_date from dual;
dbms_output.put_line('OUTPUT REPORT ON '||v_Date );
FOR loop_emp IN
(
select a.tablespace_name,
a.bytes_alloc/(1024*1024) "TOTALALLOC-MB",
a.physical_bytes/(1024*1024) "TOTALPHYSALLOC-MB",
nvl(b.tot_used,0)/(1024*1024) "USED-MB",
round((nvl(b.tot_used,0)/a.bytes_alloc)*100) "PERCENTUSED"
from
(select tablespace_name,
sum(bytes) physical_bytes,
sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)) bytes_alloc
from dba_data_files
group by tablespace_name ) a,
(select
tablespace_name,
sum(bytes) tot_used
from dba_segments
group by tablespace_name ) b
where a.tablespace_name = b.tablespace_name (+)
and a.tablespace_name not in
(select distinct tablespace_name from dba_temp_files)
and a.tablespace_name not like 'UNDO%' order by 1
)

LOOP

If loop_emp.percentused > 70 then

dbms_output.put_line('Tablespace '||loop_emp.tablespace_name||' need to add more space.');
dbms_output.put_line('Space utilized in percentage: '||loop_emp.PERCENTUSED||'%');

v_flag := 1;
end if;
END LOOP loop_emp;

if v_flag = 0 then
dbms_output.put_line('Everything seems fine. Have Sufficient Space !!');
else
dbms_output.put_line('Please contact DBA team !!');
end if;
END;
/

Data function and format in oracle

Date function and format in Oracle

NUMTODSINTERVAL: Difference in dates with result in DAYS, HOURS, MINUTE and SECONDS

-- You can do it by callculaation
SELECT TRUNC( difference ) AS days,
TRUNC( MOD( difference * 24, 24 ) ) AS hours,
TRUNC( MOD( difference * 24*60, 60 ) ) AS minutes,
TRUNC( MOD( difference * 24*60*60, 60 ) ) AS seconds
FROM (
SELECT TO_DATE( '2018-01-02 01:01:12', 'YYYY-MM-DD HH24:MI:SS' )
- TO_DATE( '2018-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS' )
AS difference
FROM DUAL
);

DAYS HOURS MINUTES SECONDS
---- ----- ------- ----------
1    1     1       12

-- You can use numtodsinterval function to do for you.
SELECT EXTRACT( DAY FROM difference ) AS days,
EXTRACT( HOUR FROM difference ) AS hours,
EXTRACT( MINUTE FROM difference ) AS minutes,
EXTRACT( SECOND FROM difference ) AS seconds
FROM (
SELECT NUMTODSINTERVAL(
TO_DATE( '2018-01-02 01:01:12', 'YYYY-MM-DD HH24:MI:SS' )
- TO_DATE( '2017-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS' ),
'DAY'
) AS difference
FROM DUAL
);

DAYS HOURS MINUTES SECONDS
---- ----- ------- --------
366  1     1       12

NUMTODSINTERVAL direct output

SELECT NUMTODSINTERVAL(
TO_DATE( '2018-01-02 01:01:12', 'YYYY-MM-DD HH24:MI:SS' )
- TO_DATE( '2016-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS' ),
'DAY'
) AS difference
FROM DUAL;

DIFFERENCE
-------------------------------
+000000732 01:01:12.000000000

MONTH_BETWEEN function difference in Month
Note: Month_BETWEEN function calculated the month based of 31 days

SELECT MONTHS_BETWEEN( DATE '2018-03-10', DATE '2017-03-10' ) AS difference FROM DUAL;

DIFFERENCE
----------
12

Extract the Year, Month, Day, Hour, Minute

SELECT EXTRACT (YEAR FROM DATE '2018-03-25') AS YEAR,
EXTRACT (MONTH FROM DATE '2018-03-25') AS MONTH,
EXTRACT (DAY FROM DATE '2018-03-25') AS DAY
FROM DUAL;

YEAR MONTH DAY
---- ----- ----
2018 3     25

Format the Date with TO_CHAR Function as output

SELECT TO_CHAR( sysdate, 'YYYY-MM-DD' ) AS formatted_date FROM dual;
FORMATTED
----------
2018-09-10

SQL> SELECT TO_CHAR( sysdate, 'YYYY-DD-MM' ) AS formatted_date FROM dual;
FORMATTED
----------
2018-10-09

SELECT TO_CHAR( sysdate, 'DD-MON-YYYY' ) AS formatted_date FROM dual;
FORMATTED_DATE
--------------------
10-SEP-2018

SELECT TO_CHAR(sysdate,'FMMonth d yyyy, hh12:mi:ss AM','NLS_DATE_LANGUAGE = French') AS formatted_date FROM dual;
FORMATTED_DATE
----------------------------------
Septembre 2 2018, 2:49:44 PM

ADD_MONTHS function : you can subtract also by – sign
With add month function you can add more month to current date and by using – sign you can subtract also.

SQL> SELECT ADD_MONTHS(DATE'2015-01-12', 2) m FROM dual;
M
---------
12-MAR-15

SQL> SELECT ADD_MONTHS(DATE'2015-01-12', -2) m FROM dual;
M
---------
12-NOV-14

SQL> SELECT TO_CHAR( ADD_MONTHS(DATE'2015-01-31', 1),'YYYY-MM-DD') m FROM dual;
M
----------
2015-02-28

Check version of binaries 32/64 bit and database Oracle

Check version of binaries 32/64 bit and database Oracle

Check version of installed binaries Client or Server
Tnsping command from Command shell also let you know the version of Oracle Binaries
Note: i am taking example of server binaries if you set client binaries then it will let you know client version and its 32/64 bit software.

-- Setting Server Oracle Home and PATH
Set oracle_home= E:\oracle\dbhome_1
set path=%oracle_home%\bin;%PATH%
C:\oracle>tnsping
TNS Ping Utility for 64-bit Windows: Version 11.2.0.2.0 - Production on 07-SEP-2018 14:38:28
Copyright (c) 1997, 2014, Oracle. All rights reserved.
TNS-03502: Insufficient arguments. Usage: tnsping

Check the Version of Oracle Database.

select * from v$version;
BANNER
------------------------------------------------------------------------------
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

Check database version which is connected with 64-bit or 32-bit status

column product for a36
col version for a10
col status for a20
select * from product_component_version ;
PRODUCT VERSION STATUS
------------------------------------ ---------- -----------------
NLSRTL 11.2.0.2.0 Production
Oracle Database 11g Express Edition 11.2.0.2.0 64bit Production
PL/SQL 11.2.0.2.0 Production
TNS for 64-bit Windows: 11.2.0.2.0 Production

Check the database version which connected.

SQL> SELECT version FROM v$instance;
VERSION
-----------------
11.2.0.2.0

Check the version of SQLPLUS (it will give you version of binaries Oracle Client or Server)
Note: it depend upon its path from which sqlplus is running it may be client or Server. If you set client binaries path then it will give you client version.

-- Setting Server Oracle Home and PATH
Set oracle_home= E:\oracle\dbhome_1
set path=%oracle_home%\bin;%PATH%
C:\oracle>sqlplus -version
SQL*Plus: Release 11.2.0.2.0 Production

Flashback restore table from drop command in Oracle

Flashback restore table from drop command in Oracle

Flashback query only help to restore the data from the table. It cannot restore from DDL Operations.

In following example we use HR schema having table TEST which we drop and recover from flash back restore table use or recovered from recycle bin.

Check the table present in HR schema

SQL> set line 999
SQL> set pages 999
SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
TEST TABLE

Drop the table as an example.

SQL> drop table test;

Check the table which dropped move to recycle bin

SQL> SELECT * FROM TAB;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$tyyNltv2QHy8L84OkHttFQ==$0 TABLE

Check the object present in recycle bin

SQL> SHOW RECYCLEBIN
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST BIN$tyyNltv2QHy8L84OkHttFQ==$0 TABLE 2018-09-06:15:04:41

Restore the table from recycle bin

SQL> FLASHBACK TABLE TEST TO BEFORE DROP;

Check the table restored back from recycle bin.

SQL> SELECT * FROM TAB;
TNAME TABTYPE CLUSTERID
--------- ------- ----------
TEST TABLE

For free the utilized space you need to purge the table from recycle bin or drop table permanent:

-- Purge the recycle bin
PURGE RECYCLEBIN;

-- Drop table permanent without move to recycle bin
DROP TABLE EMPLOYEE PURGE;