Oracle Temporary Tablespace

 Temporary Tablespace Usage

Oracle uses Temp tablespace for operations like sorting and also used in join operations. A database can have multiple Temp tablespaces but only one of them can be assigned as the default tablespace.

So before getting into Temp tablespace monitoring stuff, remember you wont get that information from DBA_FREE_SPACE view. Instead use V$TEMP_SPACE_HEADER as shown below

Finding the default temporary tablespace:

select property_name,property_value from database_properties where property_name='default_temp_tablespace';

List all temporary tablespaces in the instance:

select tablespace_name, status,contents from dba_tablespaces where contents='TEMPORARY';

Create temporary tablespace:

create temporary tablespace <TEMP NAME> tempfile '/oracle/datafile/tempfile01.dbf' size 1G;

Change the default temporary tablespace of the database:

alter database default temporary tablespace <NEW TEMP NAME>;

Change the default temporary tablespace of the user:

alter user <USER NAME> default temporary tablespace <TEMP NAME>;

Find the Allocated, Used and Free MB in temporary tablespace:

      select * from (select a.tablespace_name, 
      sum(a.bytes/1024/1024) allocated_mb 
      from dba_temp_files a 
      where a.tablespace_name = upper('&&temp_tsname') group by a.tablespace_name) x, 
      (select sum(b.bytes_used/1024/1024) used_mb, 
      sum(b.bytes_free/1024/1024) free_mb 
      from v$temp_space_header b 
      where b.tablespace_name=upper('&&temp_tsname') group by b.tablespace_name); 

Enter value for temp_tsname: TEMP 

                                            (OR)

select tablespace_name,bytes_used/1024/1024 used_mb,bytes_free/1024/1024 free_mb from v$temp_space_header;

List the datafiles under the temporary tablespace:

select file_name, tablespace_name, bytes/1024/1024 size_mb,autoextensible from dba_temp_files;

Increase temporary tablespace:

             To Add: alter tablespace TEMP add tempfile '/oracle/datafile/tempfile02.dbf' size 1G;

             To Resize: alter database tempfile '/oracle/datafile/tempfile01.dbf' resize 1G;

Drop the temporary tablespace:

drop tablespace <TEMP NAME> including contents and datafiles;

 Identify who is filling up the Temporary Tablespace (ORA-1652)

 You have just noticed that the TEMP tablespace is filling up fast.

Now you want to know which user and SQL statement is causing that then use the below mentioned script

select s.sid || ',' || s.serial# sid_serial, s.username,

o.blocks * t.block_size / 1024 / 1024 mb_used, o.tablespace, s.status,

o.sqladdr address, h.hash_value, h.sql_text

from v$sort_usage o, v$session s, v$sqlarea h, dba_tablespaces t

where o.session_addr = s.saddr

and o.sqladdr = h.address (+)

and o.tablespace = t.tablespace_name

order by 3 desc;

Actually, Oracle performs the sort/hash operations in PGA memory. However, if that sort operation is too large to fit into PGA memory area then it starts using temporary tablespace. If even the temporary tablespace gets all filled up then oracle user will get this error 'ORA-1652: unable to extend temp segment error'. No new sessions or queries would be possible in that case.

Thus, one of the first things you must do is to review the current value set for the PGA_AGGREGATE_TARGET initialization parameter and see if bumping it up will help. 

Query to find the duplicate/redundant columns from all tables in a schema in Oracle DB

 Query to find the duplicate/redundant columns from all tables in a schema in Oracle DB

We can find the same columns appearing in multiple tables of a particular schema

 SELECT column_name,data_type,data_length,nullable,table_name
FROM dba_tab_columns
WHERE column_name IN
(
    SELECT column_name
    FROM dba_tab_columns
    GROUP BY
        column_name
    HAVING COUNT(1) > 1  -- more than one value
)
and owner = 'USER' -- enter the schema name
AND COLUMN_NAME LIKE '%TIME%'
ORDER BY column_name

Monitor progress of currently running RMAN backup

 Monitor progress of currently running RMAN backup

select SID,username, opname,to_char(START_TIME,'dd mm yyyy hh:mm:ss'),TOTALWORK, sofar, (sofar/totalwork) * 100 done,TIME_REMAINING/3600/24,
to_char(sysdate + TIME_REMAINING/3600/24,'dd mm yyyy hh:mm:ss') end_at
from v$session_longops
where totalwork > sofar
AND opname NOT LIKE '%aggregate%'
AND opname like 'RMAN%'

To check whether the backup has been completed or not?

SELECT end_time, status, session_key, session_recid, session_stamp,command_id, start_time, time_taken_display, input_type,

output_device_type, input_bytes_display, output_bytes_display,

output_bytes_per_sec_display

FROM v$rman_backup_job_details WHERE end_time = (select max(end_time) from v$rman_backup_job_details)  

To stop/kill the Rman Backup

Find out the sid, serial# from the below query

SELECT p.SPID, s.sid, s.serial#, sw.EVENT, sw.SECONDS_IN_WAIT AS SEC_WAIT, sw.STATE, CLIENT_INFO

FROM V$SESSION_WAIT sw, V$SESSION s, V$PROCESS p

WHERE s.client_info LIKE 'rman%'

AND s.SID=sw.SID

AND s.PADDR=p.ADDR;

once we find the sid and serial#, issue the command on sql prompt

alter system kill session 'sid,serail#'

Finding size of the table

 Finding size of the table

select segment_name, sum(bytes)/(1024*1024) as SIZE_IN_MB from dba_extents
where segment_type = 'TABLE'
and
segment_name = 'MY_TABLE'
group by segment_name;

Note : If there are mutliple users, then also user the owner col in the query

Query to find if the Datafiles have been backed up or not in the last 24 hours 

 SELECT dbfiles||' out of '||numfiles||' datafiles backed up' "Datafiles backed up",
                          cfiles "Control Files backed up", spfiles "SPFiles backed up"
          FROM    (select count(*) numfiles from v$datafile),
                         (select count(*) dbfiles  from v$backup_datafile a, v$datafile b
                          where a.file# = b.file#   and a.completion_time > sysdate - 1),
                         (select count(*) cfiles from v$backup_datafile
                          where file# = 0 and completion_time > sysdate - 1),
                         (select count(*) spfiles from v$backup_spfile
                         where completion_time > sysdate - 1);

Query to find the registered and scheduled Jobs in Oracle DB

 Query to find the registered and scheduled Jobs in Oracle DB

SELECT  job, log_user username, what, 

 TO_CHAR(next_date, 'DD-MON-YYYY HH24:MI:SS') Next_Run, interval,

 TO_CHAR(last_date, 'DD-MON-YYYY HH24:MI:SS') last_Run, failures, broken

FROM dba_jobs;

Query to find Tablespace Fragmentation

 Query to find Tablespace Fragmentation

Use the below query to find out the level of tablespace fragmentation.

 select tablespace_name, count(*) free_chunks, decode (round((max(bytes) / 1024000), 2), null, 0,
 round((max(bytes) / 1024000), 2)) largest_chunk, 

 nvl (round(sqrt(max(blocks)/sum(blocks))*(100/sqrt(sqrt(count(blocks)) )), 2), 0) fragmentation_index
from sys.dba_free_space 

group by tablespace_name
order by 2 desc, 1

Query to find the Outstanding Database Alerts in Oracle DB

Query to find the Outstanding Database Alerts in Oracle DB

TO know what are the top database Alerts, use the below query

SELECT To_Char(Creation_Time, 'DD-MM-YYYY HH24:MI') Creation_Time,instance_name,object_type,message_type,message_level,reason,suggested_action FROM

dba_outstanding_alerts ORDER BY Creation_Time;

Queries to find the size of an Oracle database

Queries to find the size of an Oracle database

To find how much space has been allocated to ALL datafiles:
select sum(bytes)/1024/1024 "MB" from dba_data_files;

Size of the online redo logs:
select sum(bytes)/1024/1024 "Meg" from sys.v_$log;

Size of all TEMP files:
select nvl(sum(bytes),0)/1024/1024 "MB" from dba_temp_files;

Size of the control files usage,
select sum(BLOCK_SIZE*FILE_SIZE_BLKS/1024/1024) "MB" from v$controlfile;

Find all the details at a time

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;

Heavy load on Server... Find the session details of the user

If the CPU utilization is too high on the Server, then run the below command at OS level to find the Process Id and then use the same PID in the next query to find the session details which is consuming the resources

To find the PID of the top most resource consuming process

ps -eo pcpu,pid,user,args | sort -k 1 -r | head -10

%CPU   PID USER     COMMAND
 9.8 22834 oraprod  oraclePROD (LOCAL=NO)
 9.7 22832 oraprod  oraclePROD (LOCAL=NO)
 8.9 21278 oraprod  oraclePROD (LOCAL=NO)
 8.8 21953 oraprod  oraclePROD (LOCAL=NO)
 8.5 20334 oraprod  oraclePROD (LOCAL=NO)
 6.7 22610 oraprod  oraclePROD (LOCAL=NO)
 4.7 18182 oraprod  oraclePROD (LOCAL=NO)
43.6 10136 oraprod  oraclePROD (LOCAL=NO)
40.4 20247 oraprod  oraclePROD (LOCAL=NO)



To find the session details of the user, use the PID from the above command as the input for the below statement


SELECT s.sid, p.spid "OS Pid", s.module, s.process, s.schemaname "Schema", s.username "Username",
s.osuser "OS User", s.program "Program", a.sql_id, substr(a.sql_text,1,550) "SQL Text"
FROM v$session s, v$sqlarea a, v$process p
WHERE s.sql_hash_value = a.hash_value (+)
AND s.sql_address = a.address (+)
AND s.paddr = p.addr
and s.sid = (select s.sid from v$session s, v$process p where s.paddr = p.addr and p.spid = &p);