ORA-01652: unable to extend temp segment by 128

ora-01652: unable to extend temp segment by 128

Error occurred due to temp table-space is full. You need to add new temp file in your tablespace.

Solution

Following are the steps to add temp file in the TEMPORARY tablespace.

1. Check the temp file location and tablespace name for temporary tablespace.

col tablespace_name for a10
col file_name for a50
select tablespace_name,file_name from dba_temp_files;

TABLESPACE FILE_NAME
---------- ----------------------------------
TEMP D:\ORACLEXE\ORADATA\XE\TEMP01.DBF

2. Check the space on your operating system

3. Add a new temp file to the location.

alter tablespace TEMP add tempfile 'D:\ORACLEXE\ORADATA\XE\TEMP02.DBF' size 100m autoextend on next 100m;

Note: Check the tablespace name and datafile location before adding it in database.

Advertisements

Change value of OPTIMIZER MODE parameter in Oracle

Optimizer Mode Parameter in Oracle

Optimizer choose the execution plan according to the value of OPTIMIZER MODE parameter  for all rows it check all rows for session to choose better execution plan . It has different values you can set as you need.

Default value is ALL_ROWS

You can set is at session or system level for testing or permanent changes at database level.

First_rows_n Optimizer used a cost-based approach according to response time of the return the first n rows

--n stands for no of rows
Alter system set OPTIMIZER_MODE = first_rows_n scope=both;

--For first 1000 rows
Alter system set OPTIMIZER_MODE = first_rows_1000 scope=both;
-- For first 100 rows
Alter system set OPTIMIZER_MODE=first_rows_100 scope=both;
--For first one rows
Alter system set OPTIMIZER_MODE=first_rows_1 scope=both;

FIRST_ROWS: Optimizer choose mix of Cost and heuristics plan for better execution plan of first few rows

Alter system set OPTIMIZER_MODE = first_rows scope=both;

ALL_ROWS Optimizer used a cost-based approach for all SQL statements in the session and try best execution plan.

-- Default setting in Oracle
Alter system set OPTIMIZER_MODE=ALL_ROWS scope=both;

 

 

 

 

 

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

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