ORA-1652: unable to extend temp segment by 128 in tablespace TEMP

ORA-1652: unable to extend temp segment by %s in tablespace TEMP

This error occur when user running the queries doesn't get the enough free space in the temporary tablespace to complete the task.

Usually SMON automatically clean up the unused temporary segments so increasing the temporary tablespace is not a good idea in production environment. If this issue is happening on the regular basis then you need to analyse the temporary tablespace and the sessions along with the queries holding the temp space.

It's better if you can analyse and tune the queries. Below are some of the troubleshooting steps using which you can find the root cause.



Query To Check TEMP Tablespace Usage:

SELECT A.tablespace_name tablespace, D.mb_total,
   SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
   D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
  FROM v$sort_segment A,
   (
  SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
   FROM v$tablespace B, v$tempfile C
    WHERE B.ts#= C.ts#
     GROUP BY B.name, C.block_size) D
   WHERE A.tablespace_name = D.name
   GROUP by A.tablespace_name, D.mb_total;

TABLESPACE                          MB_TOTAL      MB_USED      MB_FREE
------------------------------- ------------ ------------ ----------------------
TEMP                                  13107122       1293301         17411

The following query will display the information about each statement using space in a sort segment.
This will also including information about  the database sessions that issued the statement and the temporary tablespace and amount of sort space being used.

SELECT   S.sid || ',' || S.serial# sid_serial, S.username,T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
T.sqladdr address, Q.hash_value, Q.sql_text
FROM     v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
WHERE    T.session_addr = S.saddr
AND      T.sqladdr = Q.address
AND      T.tablespace = TBS.tablespace_name
ORDER BY S.sid;

Run the above query at regular intervals to know which statement is chewing up temp space.

To add datafiles to the temp tablespace:

select file_name,tablespace_name,bytes/1024/1024/1024, maxbytes/1024/1024/1204,autoextensible from dba_temp_files;

 ALTER TABLESPACE TEMP ADD TEMPFILE 'LOCATION' size 100m reuse autoextend on next 50m maxsize 20G

To avoid the ORA-1652 error, you can configure the temp tablespace usaage alert and monitor the sessions/queries causing the temp issues.

Read more ...

How to stop Materialized view Auto Refresh in Oracle

A materialized view is a table segment or database object that contains the results of a query. A materialized view created with the automatic refresh can not be alter to stop refreshing. In order to disable that you must break the dbms_job that was created in order to refresh the view.

Mview are local copies of data located remotely, or are used to create summary tables based on aggregations of a table's data. Materialized views, which store data based on remote tables are also, know as snapshots.

Steps to Disable Automatic Refresh of Materialized View.

STEP 1. Connect as mview owner and execute the below query.

select * from user_jobs where broken ='N';

STEP 2. In the WHAT column for the mview refresh job you will see:

dbms_refresh.refresh('"[OWNER]"."[MVIEW_NAME]"');

STEP 3. Take the JOB_ID form the job column and execute below procedure and mview will stop refreshing automatically:

begin
dbms_job.broken(JOB_ID,TRUE);
commit;
end;
/

Example:

begin
dbms_job.broken(25,TRUE);
commit;
end;
/

If you want the mview to start refreshing again just run the job.

begin
dbms_job.run(JOB_ID);
commit;
end;
/

Example:

begin
dbms_job.broken(25,FALSE);
commit;
end;
/

That's all about disabling the materialized view. If you want to stop materialized view from auto refresh just run the above procedure and check the status of the job

Read more ...

How to Flush a Single SQL Statement from the Shared Pool

Sometimes it is required to flush the single sql plan from the shared pool and it a good idea rather than flushing all the sql plan from the memory. After baselining or fixing the bad plan, DBA's wanted to kick out the sql plan from the memory to confirm, if optimizer is picking the correct plan.

I have seen that many guys simply use to flush the shared pool using alter system flush shared_pool statement which is not a good idea. Rather if you are struggling for fixing the bad plan of any single query, it is advisable to flush the single sql_id out of the memory.


STEP 1: Find Address and  hash_value of particular sql_id.

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID='251fjyn5fj84q';

ADDRESS HASH_VALUE
---------------- ------------------------

00000005DGEC9DE0 257655674

STEP 2: Purge sql plan from the shared pool by passing the above values.

SQL> exec DBMS_SHARED_POOL.PURGE ('00000005DGEC9DE0, 257655674', 'C');

PL/SQL procedure successfully completed.

Here, ‘C’ (for cursor)

STEP 3: Check if the plan still exist in the memory. If no rows selected then plan has been flushed out from the memory for that sql_id.

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID='251fjyn5fj84q';

no rows selected

In the above example, we have used the V$ view to find cursor information. If you are using RAC environment then change your query to use gv$sqlarea just in case the SQL statement you are looking for was executed on an instance other than the one you are currently logged into.


So this is all about flushing the sql statement from the shared pool. If you are facing such perforamce issues then you can use the above method to flush plan for the particular sql_id from the memory.

Read more ...

Script to Monitor RMAN Backup Status and Timings

Being a DBA, you often asked to check the status of  RMAN backup job details. There are many ways to monitor the progress of the backup but you can use the below scripts to monitor the status of the RMAN job like full, incremental & archivelog backups.

You can use views like v$rman_backup_job_details and V$SESSION_LONGOPS to monitor the current executing RMAN jobs and the status of the previously completed backups.

SQL> desc v$rman_backup_job_details
 Name                                 Null?        Type
 ----------------------------- -------- --------------------
 SESSION_KEY                              NUMBER
 SESSION_RECID                          NUMBER
 SESSION_STAMP                         NUMBER
 COMMAND_ID                             VARCHAR2(33)
 START_TIME                                DATE
 END_TIME                                    DATE
 INPUT_BYTES                             NUMBER
 OUTPUT_BYTES                         NUMBER
 STATUS_WEIGHT                       NUMBER
 OPTIMIZED_WEIGHT                  NUMBER
 OBJECT_TYPE_WEIGHT                    NUMBER
 OUTPUT_DEVICE_TYPE                     VARCHAR2(17)
 AUTOBACKUP_COUNT                       NUMBER
 BACKED_BY_OSB                          VARCHAR2(9)
 AUTOBACKUP_DONE                        VARCHAR2(9)
 STATUS                                 VARCHAR2(69)
 INPUT_TYPE                             VARCHAR2(39)
 OPTIMIZED                              VARCHAR2(9)
 ELAPSED_SECONDS                        NUMBER
 COMPRESSION_RATIO                      NUMBER
 INPUT_BYTES_PER_SEC                    NUMBER
 OUTPUT_BYTES_PER_SEC                   NUMBER
 INPUT_BYTES_DISPLAY                    VARCHAR2(4000)
 OUTPUT_BYTES_DISPLAY                   VARCHAR2(4000)
 INPUT_BYTES_PER_SEC_DISPLAY            VARCHAR2(4000)
 OUTPUT_BYTES_PER_SEC_DISPLAY           VARCHAR2(4000)
 TIME_TAKEN_DISPLAY                     VARCHAR2(4000)


This script will report status of current as well as completed backup details like full, incremental and archivelog backups:

col STATUS format a9
col hrs format 999.99
select
SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi')   end_time,
elapsed_seconds/3600                   hrs
from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;


SESSION_KEY INPUT_TYPE    STATUS    START_TIME     END_TIME           HRS
----------- ------------- --------- -------------- -------------- -------
        585 ARCHIVELOG    COMPLETED 01/08/15 06:00 01/08/15 06:02     .03
        591 ARCHIVELOG    COMPLETED 01/08/15 12:00 01/08/15 12:01     .02
        596 ARCHIVELOG    COMPLETED 01/08/15 18:01 01/08/15 18:02     .03
        601 DB INCR       FAILED    01/08/15 20:00 01/09/15 01:47    5.79
        603 ARCHIVELOG    COMPLETED 01/09/15 06:00 01/09/15 06:07     .12
        608 ARCHIVELOG    COMPLETED 01/09/15 12:00 01/09/15 12:09     .16
        613 ARCHIVELOG    COMPLETED 01/09/15 15:07 01/09/15 15:25     .29

Below script will report you the percentage of completion along with sid and serial#.

SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,
ROUND (SOFAR/TOTALWORK*100, 2) "% COMPLETE"
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%'
AND TOTALWORK! = 0 AND SOFAR <> TOTALWORK;


 SID       SERIAL#    CONTEXT    SOFAR      TOTALWORK  %COMPLETE
---------- ---------- ---------- ---------- ---------- ----------
 22        31         1          8225460    18357770   45.21

Read more ...

Script to Start Oracle Database Automatically on Linux

From Oracle 10gR2 onward RAC clusterware automatically start and stop the ASM and Oracle database instances and listeners, so the following procedures are not necessary. But for the single instance where the RAC is not being used, this script will allow you to automate the startup and shutdown of oracle databases on Linux automatically after server reboot.

Already both scripts are installed in $ORACLE_HOME/bin and are called dbstart and dbshut. However, these scripts are not executed automatically after you reboot your server. I will explain you how to configure this script so that Oracle Services can start automatically after Linux server reboot.

How to Configure Auto Startup Script?

Below are the changes you need to perform in order to automate this script.

STEP 1: First, you need to make sure that any database instances you want to autostart need to be set to “Y” in /etc/oratab file as shown below.

# This file is used by ORACLE utilities.  It is created by root.sh
# and updated by either Database Configuration Assistant while creating
# a database or ASM Configuration Assistant while creating ASM instance.
#
# Entries are of the form:
#   $ORACLE_SID:$ORACLE_HOME:<N|Y>:
#
# The first and second fields are the system identifier and home
# directory of the database respectively.  The third filed indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
ora11g:/u01/app/oracle/product/11.2.0/dbhome_1:Y
ora12c:/u01/app/oracle/product/11.2.0/dbhome_1:Y

The /etc/oratab file is normally created by running the root.sh script at the end of the installation. If you don’t have the file, you can always add it to your system by creating it manually (with user root!).

STEP 2: Make entry of ORACLE_HOME and PATH in your . ~/.bash_profile


STEP 3: Next, we are going to create 2 scripts under home path as /home/oracle/scripts: ora_start.sh and ora_stop.sh.

These scripts will call dbstart and dbshut and it will also allow us to add some more actions, for example the start of the Enterprise Manager Database control or any other services you might have.
You can also create separate directory for this script.

$ su – oracle
$ vi /home/oracle/scripts/ora_start.sh

#!/bin/bash

# script to start the Oracle database, listener and dbconsole

. ~/.bash_profile

# start the listener and the database
$ORACLE_HOME/bin/dbstart $ORACLE_HOME

# start the Enterprise Manager db console
#$ORACLE_HOME/bin/emctl start dbconsole

exit 0

$ vi /home/oracle/scripts/ora_stop.sh
#!/bin/bash

# script to stop the Oracle database, listener and dbconsole

. ~/.bash_profile

# stop the Enterprise Manager db console
#$ORACLE_HOME/bin/emctl stop dbconsole

# stop the listener and the database
$ORACLE_HOME/bin/dbshut $ORACLE_HOME

exit 0

You can see that inside the scripts, we are calling the .bash_profile file of the user “oracle”. This is needed to set the ORACLE_HOME, PATH environment variable.



STEP 4: Give execute permission to the scripts:
$  chmod u+x ora_start.sh ora_stop.sh



STEP 5: We will now create a wrapper script that can be used to schedule as a service.
With user root, create a file called “oracle” under /etc/init.d.

$ vi /etc/init.d/oracle
#!/bin/bash
# chkconfig: 345 99 10
# description: Oracle auto start-stop script.

# Set ORA_OWNER to the user id of the owner of the
# Oracle database in ORA_HOME.

ORA_OWNER=oracle
RETVAL=0

case "$1" in
    'start')
        # Start the Oracle databases:
        # The following command assumes that the oracle login
        # will not prompt the user for any values
        su - $ORA_OWNER -c "/home/oracle/scripts/ora_start.sh"
        touch /var/lock/subsys/oracle
        ;;
    'stop')
        # Stop the Oracle databases:
        # The following command assumes that the oracle login
        # will not prompt the user for any values
        su - $ORA_OWNER -c "/home/oracle/scripts/ora_stop.sh"
        rm -f /var/lock/subsys/oracle
        ;;
    *)
        echo $"Usage: $0 {start|stop}"
        RETVAL=1
esac
exit $RETVAL



STEP 6: Grant below permission for this script.
$ chmod 750 /etc/init.d/oracle

Note: Add the oracle home paths in .bash_profile



STEP 7: To create a service of this script, run the following command:
$ chkconfig --add oracle



STEP 8: All done, check the script and database status by running “service oracle stop” or “service oracle start” from the command line.

$ service oracle stop
Oracle Enterprise Manager 11g Database Control Release 11.2.0.3.0
Copyright (c) 1996, 2011 Oracle Corporation. All rights reserved.
Stopping Oracle Enterprise Manager 11g Database Control …
… Stopped.
Processing Database instance “oratst”: log file /u01/app/oracle/product/11.2.0/db_1/shutdown.log

$ service oracle start
Processing Database instance “oratst”: log file /u01/app/oracle/product/11.2.0/db_1/startup.log
Oracle Enterprise Manager 11g Database Control Release 11.2.0.3.0
Copyright (c) 1996, 2011 Oracle Corporation. All rights reserved.
Starting Oracle Enterprise Manager 11g Database Control …… started.
After this, it’s time for the final test: reboot your server and check if your Oracle database is automatically started after the reboot.


Whenever database server will reboot, you will observe that your configured databases has been started automatically. There is no need to start and stop the database manually. If you are getting any issue you can contact for support. We will be pleased to assist you.

Read more ...

How to Kill All Sessions of a Specific User in Oracle

Any time request may come like "Can you please kill all the sessions that is on ORAHOW database for user name Tiwary. Killing sessions can be very destructive if you kill the wrong session, so be very careful when identifying the session to be killed. If you kill a session belonging to a background process you will cause an instance crash.

Before killing session gather session information for that user from Oracle.

• Before killing check which sql being executed by that user.
• Check for any blocking locks, long running query etc.

Best Way to kill Blocking Sessions in Oracle

• Find inst_id, sid, serial#, machine, sql_id for that user.

To Find inst_id, sid, serial# for a user
select inst_id, sid, serial#, machine, username status, sql_id from gv$session where username='Tiwary';


To get more detailed information for a particular user
set lines 1234 pages 9999
 col inst_id for a10
col serial# for a10
 col machine for a30
col username for a10
col event for a20
col blocking_session for 999999
col blocking_instance for 999999
 col status for a10
 col INST_ID for 9999
 col SERIAL# for 999999

select inst_id,sid,serial#,machine,username,event,blocking_session,blocking_instance,status,sql_id from gv$session where username='TIWARY';


In RAC @ sign is now required to kill a session or when using an inst_id.
SQL>  alter system kill session '125,640,1';
 alter system kill session '125,640,1'
*
ERROR at line 1:
ORA-00026: missing or invalid session ID

After specifying @inst_id, it works

alter system kill session '125,640,@1';

System altered.

NOTE: If you don't want to put inst_id, then goto the instance from where user is connected and then kill it.


Best way to kill a session is using ALTER SYSTEM DISCONNECT SESSION command.
ALTER SYSTEM DISCONNECT SESSION 'SID,SERIAL#' immediate;
ALTER SYSTEM DISCONNECT SESSION '125,640'immediate;


Finally check once to verify it.
select inst_id, sid, serial#, machine, username status, sql_id from gv$session where username='Tiwary';

no rows selected


Finally we have killed all the sessions for a special user connected to the oracle database. Now there is no active sessions for that user.

Read more ...

How to Find Out the size of your Oracle Database

As an Oracle DBA this is the most important interview question and at any time you may face an issue where you need to find out the size of the Oracle Database. In detail, here you will get to know the actual size of the database that comprises of only data files when no redo and temp are generated. In second case, you may asked to find out the overall database size that contains all the data files, temp files and the redo logs (free+used space). Third, is the size occupied by data in this database or you can say  Database usage details which you can get using dba_segments. 

To Find the Actual size of the Database in GB

SELECT SUM (bytes) / 1024 / 1024 / 1024 AS GB FROM dba_data_files;

To Find the Size Occupied by Data in the Database 

Gives the size occupied by data in this database or Database usage details.

SELECT SUM (bytes)/1024/1024/1024 AS GB FROM dba_segments;

To Find the Overall/Total Database Size

Overall database size is the sum of used space plus free space i.e. the size of the data files, temp files, log files and the control files. You can find out the total database size using simple query. This sql gives the total size in GB.  

select
( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) +
( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) +
( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) +
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) "Size in GB"
from
dual

Read more ...