Differences

This shows you the differences between two versions of the page.

--- tablespaces [2018/12/08 12:49] – created 0.0.0.0
+++ tablespaces [2021/11/13 11:41] (current) – external edit 127.0.0.1
@@ Line 1: / Line 1: @@
-====== Tablespaces ======
+  *  [[https://oracle-base.com/articles/misc/reclaiming-unused-space|Reclaiming Unused Space in Datafiles - ORACLE-BASE]]
+  *  [[http://oracle-base.com/articles/10g/space-object-transaction-management-10g.php|Space management for 10g (oracle-base.com)]]
-  * [[http://oracle-base.com/articles/10g/space-object-transaction-management-10g.php|Space management for 10g (oracle-base.com)]]
-=====Update all datafiles in the database to be autoextend on and maxsize unlimited=====
+==== Check the system-wide tablespace warning and critical alerting thresholds ====
+<code>
+set lines 1000 pages 100
+col object_name for a30
+col warning_value for a30
+col critical_value for a30
+select metrics_name
+,      object_name
+,      warning_operator
+,      warning_value
+,      critical_operator
+,      critical_value
+from   dba_thresholds
+/
+</code>
+<code>
+METRICS_NAME                                                     OBJECT_NAME                    WARNING_OPER WARNING_VALUE                  CRITICAL_OPE CRITICAL_VALUE
+---------------------------------------------------------------- ------------------------------ ------------ ------------------------------ ------------ ------------------------------
+Tablespace Space Usage                                           TEMP                           DO NOT CHECK <SYSTEM-GENERATED THRESHOLD>   DO_NOT_CHECK 0
+Tablespace Space Usage                                           UNDOTBS1                       DO NOT CHECK <SYSTEM-GENERATED THRESHOLD>   DO_NOT_CHECK 0
+Tablespace Space Usage                                                                          GE           85                             GE           97
+</code>
+==== Reset the tablespace thresholds to their system-wide default ====
+<code>
+begin
+    dbms_server_alert.set_threshold ( dbms_server_alert.tablespace_pct_full
+                                    , null
+                                    , null
+                                    , null
+                                    , null
+                                    , 1
+                                    , 1
+                                    , null
+                                    , dbms_server_alert.object_type_tablespace
+                                    , null
+                                    );
+end;
+/
+</code>
+==== Override tablespace warning and critical thresholds for a tablespace ====
+<code>
+begin
+    dbms_server_alert.set_threshold ( metrics_id         => dbms_server_alert.tablespace_pct_full
+                                    , warning_operator   => dbms_server_alert.operator_ge
+                                    , warning_value      => '55'
+                                    , critical_operator  => dbms_server_alert.operator_ge
+                                    , critical_value     => '70'
+                                    , observation_period => 1
+                                    , instance_name      => 'upg'
+                                    , object_type        => dbms_server_alert.object_type_tablespace
+                                    , object_name        => 'USERS'
+                                    ,
+                                    );
+end;
+/
+</code>
+==== Quick check of free space in a tablespace ====
+<code>
+select df.tablespace_name
+,      trunc(fs.bytes/1024/1024/1024)         gigsfree
+,      trunc(df.bytes/1024/1024/1024)         gigsused
+,      trunc( ( (fs.bytes/1024/1024/1024) / (df.bytes/1024/1024/1024) *100), 3 )  freepct
+from   dba_free_space fs
+,      dba_data_files df
+where  fs.tablespace_name = df.tablespace_name
+and    df.tablespace_name = nvl(upper('&tablespace_name'),df.tablespace_name)
+/
+</code>
+==== Update all datafiles in the database to be autoextend on and maxsize unlimited ====
 A quick fix to ensure all tablespaces can be extended to the max. Have to keep an eye on the filesystem growth now.
-<code>0@@</code>
+<code>
+set lines 1000 pages 0 newpa none feedb off trims on echo off termo off
+spool fix_ts.sql
+select 'alter database datafile '||''''||file_name||''''||' autoextend on maxsize unlimited;' from dba_data_files;
+spool off
+@fix_ts
+</code>
+==== Shrink tablespace to reclaim space on filesystem ====
+For temporary tablespaces, it is a non-issue. Just shrink them. It will do what you expect it to do.
+<code>
+ls -l /cln/prg/ora_data2/clnp/temp_system
+-rw-r-----    1 oracle   oinstall 34358697984 Aug 24 12:12 temp_system01.dbf
+-rw-r-----    1 oracle   oinstall 5368717312 Aug 24 11:45 temp_system02.dbf
+</code>
+<code>
+alter tablespace temp_system shrink space;
+</code>
+<code>
+ls -l /cln/prg/ora_data2/clnp/temp_system
+-rw-r-----    1 oracle   oinstall    2097152 Aug 24 12:20 temp_system01.dbf
+-rw-r-----    1 oracle   oinstall    2097152 Aug 24 12:20 temp_system02.dbf
+</code>
-=====User quotas on all tablespaces=====
+==== User quotas on all tablespaces ====
+<code>
  col quota format a10
  select username
@@ Line 17: / Line 117: @@
  where  1=1
  and    tablespace_name not in ('TEMP');
+</code>
-=====Tablespace Growth Report 1=====
+==== Tablespace Growth Report 1 ====
-<code>1@@</code>
+<code>
+select b.tsname tablespace_name
+,      MAX(b.used_size_mb) cur_used_size_mb
+,      round(AVG(inc_used_size_mb),2) avg_increas_mb
+from   ( SELECT a.days,a.tsname , used_size_mb , used_size_mb - LAG (used_size_mb,1) OVER ( PARTITION BY a.tsname ORDER BY a.tsname,a.days) inc_used_size_mb
+from   ( SELECT TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY') days  ,ts.tsname ,MAX(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) used_size_mb
+from   dba_hist_tbspc_space_usage  tsu , dba_hist_tablespace_stat  ts ,dba_hist_snapshot  sp, dba_tablespaces  dt   where tsu.tablespace_id= ts.ts# AND tsu.snap_id = sp.snap_id
+AND    ts.tsname = dt.tablespace_name AND sp.begin_interval_time > sysdate-7
+GROUP  BY TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY'), ts.tsname
+ORDER  BY ts.tsname, days ) a
+       ) b
+GROUP BY b.tsname ORDER BY b.tsname;
+</code>
-=====Tablespace Growth Report 2=====
+==== Tablespace Growth Report 2 ====
-The period range for this report is dependent on the snapshot retention period set for the db<br />
+The period range for this report is dependent on the snapshot retention period set for the db\\
-[[http://www.toadworld.com/platforms/oracle/w/wiki/10837.tablespace-growth-report.aspx Original page]]
+[[http://www.toadworld.com/platforms/oracle/w/wiki/10837.tablespace-growth-report.aspx|Original page]]
-<code>2@@</code>
+<code>
-=====Tablespace Growth Report 3=====
+set linesize 120
-[[http://asktom.oracle.com/pls/asktom/ASKTOM.download_file?p_file=6551402703363001567|Original page]]<br />
+column name format a15
+column variance format 9G990D00
+alter session set nls_date_format='yyyy-mm-dd';
+with t as (
+select ss.run_time,ts.name,round(su.tablespace_size*dt.block_size/1024/1024/1024,2) alloc_size_gb,
+round(su.tablespace_usedsize*dt.block_size/1024/1024/1024,2) used_size_gb
+from
+dba_hist_tbspc_space_usage su,
+(select trunc(BEGIN_INTERVAL_TIME) run_time,max(snap_id) snap_id from dba_hist_snapshot
+group by trunc(BEGIN_INTERVAL_TIME) ) ss,
+v$tablespace ts,
+dba_tablespaces dt
+where su.snap_id = ss.snap_id
+and   su.tablespace_id = ts.ts#
+and   ts.name          =upper('&TABLESPACE_NAME')
+and   ts.name          = dt.tablespace_name )
+select e.run_time,e.name,e.alloc_size_gb,e.used_size_gb curr_used_size_gb,b.used_size_gb prev_used_size_gb,(e.used_size_gb - b.used_size_gb) variance
+from t e, t b
+where e.run_time = b.run_time + 1
+order by 1;
+</code>
+==== Tablespace Growth Report 3 ====
+[[http://asktom.oracle.com/pls/asktom/ASKTOM.download_file?p_file=6551402703363001567|Original page]]\\
 You probably have a problem with the autoextend max_size if column 5 is less than column 8!
-<code>3@@</code>
+<code>
+set pages 400 lines 132
+-------------------------------------------------------
+-- free.sql
+--
+-- This SQL Plus script lists freespace by tablespace
+--------------------------------------------------------
+column  dummy noprint
+column  pct_used     format 999.9             heading "%|Used"
+column  name         format a25               heading "Tablespace Name"
+column  Kbytes       format 999,999,999,999   heading "KBytes"
+column  used         format 999,999,999,999   heading "Used"
+column  free         format 999,999,999,999   heading "Free"
+column  largest      format 999,999,999       heading "Largest"
+column  max_size     format 9,999,999,999,999 heading "MaxPoss|Kbytes"
+column  pct_max_used format 999.9             heading "%|Max|Used"
+break   on report
+compute sum of kbytes on report
+compute sum of used   on report
+compute sum of free   on report
+select (
+       select decode(extent_management,'LOCAL','*',' ') ||
+              decode(segment_space_management,'AUTO','a ','m ')
+       from   dba_tablespaces where tablespace_name = b.tablespace_name
+       )    ||
+       nvl(b.tablespace_name
+,      nvl(a.tablespace_name,'UNKOWN')) name
+,      kbytes_alloc kbytes
+,      kbytes_alloc-nvl(kbytes_free,0) used
+,      nvl(kbytes_free,0) free
+,      ((kbytes_alloc-nvl(kbytes_free,0))/kbytes_alloc)*100 pct_used
+,      nvl(largest,0) largest
+,      nvl(kbytes_max,kbytes_alloc) Max_Size
+,      decode( kbytes_max, 0, 0, (kbytes_alloc/kbytes_max)*100) pct_max_used
+from   (
+       select sum(bytes)/1024      kbytes_free
+       ,      max(bytes)/1024      largest
+       ,      tablespace_name
+       from   sys.dba_free_space
+       group  by tablespace_name
+       ) a
+,
+       (
+       select sum(bytes)/1024      kbytes_alloc
+       ,      sum(maxbytes)/1024   kbytes_max
+       ,      tablespace_name
+       from   sys.dba_data_files
+       group  by tablespace_name
+       union all
+       select sum(bytes)/1024      kbytes_alloc
+       ,      sum(maxbytes)/1024   kbytes_max
+       ,      tablespace_name
+       from   sys.dba_temp_files
+       group  by tablespace_name
+       ) b
+where  a.tablespace_name (+) = b.tablespace_name
+order  by &1
+/
+</code>
 If you are stuck on software written last century:
-<code>4@@</code>
+<code>
+set pages 400 lines 132
+-------------------------------------------------------
+-- free.sql
+--
+-- This SQL Plus script lists freespace by tablespace
+--------------------------------------------------------
-=====Segment Growth Report=====
+column  dummy noprint
-<code>5@@</code>
+column  pct_used     format 999.9       heading "%|Used"
+column  name         format a19         heading "Tablespace Name"
+column  Kbytes       format 999,999,999 heading "KBytes"
+column  used         format 999,999,999 heading "Used"
+column  free         format 999,999,999 heading "Free"
+column  largest      format 999,999,999 heading "Largest"
+column  max_size     format 999,999,999 heading "MaxPoss|Kbytes"
+column  pct_max_used format 999.9       heading "%|Max|Used"
+break   on report
+compute sum of kbytes on report
+compute sum of free   on report
+compute sum of used   on report
-=====What is the size of the segments in the SYSAUX tablespace?=====
+select (select decode(extent_management,'LOCAL','*',' ')
-<code>6@@</code>
+          from dba_tablespaces where tablespace_name = b.tablespace_name) ||
-=====What are the components that make up the SYSAUX tablespace?=====
+nvl(b.tablespace_name,
-<code>7@@</code>
+             nvl(a.tablespace_name,'UNKOWN')) name,
+       kbytes_alloc kbytes,
+       kbytes_alloc-nvl(kbytes_free,0) used,
+       nvl(kbytes_free,0) free,
+       ((kbytes_alloc-nvl(kbytes_free,0))/
+                          kbytes_alloc)*100 pct_used,
+       nvl(largest,0) largest,
+       nvl(kbytes_max,kbytes_alloc) Max_Size,
+       decode( kbytes_max, 0, 0, (kbytes_alloc/kbytes_max)*100) pct_max_used
+from ( select sum(bytes)/1024 Kbytes_free,
+              max(bytes)/1024 largest,
+              tablespace_name
+       from  sys.dba_free_space
+       group by tablespace_name ) a,
+     ( select sum(bytes)/1024 Kbytes_alloc,
+              sum(maxbytes)/1024 Kbytes_max,
+              tablespace_name
+       from sys.dba_data_files
+       group by tablespace_name
+       union all
+      select sum(bytes)/1024 Kbytes_alloc,
+              sum(maxbytes)/1024 Kbytes_max,
+              tablespace_name
+       from sys.dba_temp_files
+       group by tablespace_name )b
+where a.tablespace_name (+) = b.tablespace_name
+order by &1
+/
+</code>
-=====Alert tablespace with least space available=====
+==== Use snapshot history tables used by AWR to track tablespace growth ====
-<code>8@@</code>
+<code>
+select * from dba_hist_tbspc_space_usage order by to_date(substr(rtime,1,10),'MM/DD/YYYY')
+</code>
-=====Which sessions are using TEMP tablespace?=====
+==== Segment Growth Report ====
-<code>9@@</code>
+<code>
+column "Percent of Total Disk Usage" justify right format 999.99
+column "Space Used (MB)" justify right format 9,999,999.99
+column "Total Object Size (MB)" justify right format 9,999,999.99
+set linesize 150
+set pages 80
+set feedback off
-=====What is the currently assigned default temporary tablespace?=====
+select * from (select to_char(end_interval_time, 'DD-MON-YY') mydate, sum(a.space_used_delta) / 1024 / 1024 "Space used (MB)", avg(c.bytes) / 1024 / 1024 "Total Object Size (MB)",
+round(sum(space_used_delta) / sum(c.bytes) * 100, 2) "Percent of Total Disk Usage"
+from
+   dba_hist_snapshot sn,
+   dba_hist_seg_stat a,
+   dba_objects b,
+   dba_segments c
+where begin_interval_time > trunc(sysdate) - &days_back
+and sn.snap_id = a.snap_id
+and b.object_id = a.obj#
+and b.owner = c.owner
+and b.object_name = c.segment_name
+and c.segment_name = '&segment_name'
+group by to_char(end_interval_time, 'DD-MON-YY'))
+order by to_date(mydate, 'DD-MON-YY')
+/
+</code>
+==== What is the size of the segments in the SYSAUX tablespace? ====
+<code>
+set lines 2000
+col owner for a30
+col segment_name for a40
+select owner
+,      segment_name
+,      (bytes/1024/1024) mb
+from   dba_segments
+where  tablespace_name = 'SYSAUX'
+order  by bytes;
+</code>
+==== What are the components that make up the SYSAUX tablespace? ====
+<code>
+set lines 2000
+col occupant_name  for a30 heading "Item"
+col schema_name    for a20 heading "Schema"
+col move_procedure for a50 heading "Move Procedure"
+col used                   heading "Space Used (GB)"
+select occupant_name
+,      round(space_usage_kbytes/1048576,3) used
+,      schema_name
+,      move_procedure
+from   v$sysaux_occupants
+order  by 2
+/
+</code>
+==== Show actual space taken up by objects in TEMP tablespaces ====
+<code>
+select vss.tablespace_name                                                 tablespace
+,      tot.mb_total                                                        mb_total
+,      sum (vss.used_blocks * tot.block_size) / 1024 / 1024                mb_used
+,      tot.mb_total - sum (vss.used_blocks * tot.block_size) / 1024 / 1024 mb_free
+from   v$sort_segment vss
+,      (
+       select vt.name
+       ,      vtmp.block_size
+       ,      sum (vtmp.bytes) / 1024 / 1024 mb_total
+       from   v$tablespace vt
+       ,      v$tempfile   vtmp
+       where  vt.ts# = vtmp.ts#
+       group  by vt.name
+       ,      vtmp.block_size
+       ) tot
+where  vss.tablespace_name = tot.name
+group  by vss.tablespace_name
+,      tot.mb_total
+/
+</code>
+==== Alert tablespace with least space available ====
+<code>
+select t1.naam                                                                   naam
+,      round (t1.df_grootte/1024/1024, 0)                                        df_grootte_m
+,      round (t2.vrij/1024/1024, 0)                                              vrij_m
+,      round (t1.ruimte/1024/1024, 0)                                            ruimte_m
+,      round ( (t1.df_grootte - t2.vrij) / (t1.df_grootte + t1.ruimte) * 100, 0) vullingsgraad
+,      (
+       case when round ( (df_grootte - vrij) / (df_grootte + ruimte) * 100, 2) > 95 then '0'
+            when round ( (df_grootte - vrij) / (df_grootte + ruimte) * 100, 2) > 85 then '1'
+                                                                                    else '2'
+       end
+       ) alert_lvl
+from   (
+       select tablespace_name                         naam
+       ,      sum (greatest ( (maxbytes - bytes), 0)) ruimte
+       ,      sum (bytes)                             df_grootte
+       ,      sum (maxbytes)                          max
+       from   dba_data_files df
+       group  by df.tablespace_name
+       ) t1
+,      (
+       select tablespace_name naam
+       ,      sum (bytes)     vrij
+       from   dba_free_space
+       group  by tablespace_name
+       ) t2
+,      (
+       select tablespace_name naam
+       ,      contents        contents
+       from   dba_tablespaces
+       where  contents not in ('TEMPORARY', 'UNDO')
+       ) t3
+where  t1.naam = t2.naam
+and    t1.naam = t3.naam
+order  by vullingsgraad desc
+/
+</code>
+==== Slow or hanging drop tablespace including contents and datafiles ====
+Maybe a bug if there are no active sessions on the tablespace being dropped. See Metalink Note 438461.1
+<code>
+exec dbms_stats.gather_fixed_objects_stats;
+exec dbms_stats.gather_dictionary_stats;
+execute dbms_stats.gather_schema_stats('SYS');
+alter system flush shared_pool;
+alter system flush shared_pool;
+alter system flush shared_pool;
+</code>
+==== Which sessions are using TEMP tablespace? ====
+<code>
+set lines 1000
+col killer     for a15
+col status     for a8
+col username   for a15
+col osuser     for a15
+col spid       for a10
+col mb_used    for 99999
+col module for a20
+col program    for a25
+col tablespace for a10
+col statements for 999
+select s.sid||','||s.serial#                  killer
+,      s.status                               status
+,      s.username                             username
+,      s.osuser                               osuser
+,      p.spid                                 spid
+,      p.pid                                  pid
+,      s.module                               module
+,      p.program                              program
+,      sum(t.blocks)*tbs.block_size/1024/1024 mb_used
+,      t.tablespace                           tablespace
+,      count(*)                               statements
+from   v$sort_usage    t
+,      v$session       s
+,      dba_tablespaces tbs
+,      v$process       p
+where  1=1
+and    t.session_addr = s.saddr
+and    s.paddr        = p.addr
+and    t.tablespace   = tbs.tablespace_name
+group  by s.sid
+,      s.status
+,      s.serial#
+,      s.username
+,      s.osuser
+,      p.spid
+,      p.pid
+,      s.module
+,      p.program
+,      tbs.block_size
+,      t.tablespace
+order  by killer
+/
+</code>
+or
+<code>
+set lines 1000 pages 100
+col hash_value for a40
+col tablespace for a10
+col username for a15
+select s.sid
+,      s.username
+,      u.tablespace
+,      s.sql_hash_value||'/'||u.sqlhash hash_value
+,      u.segtype
+,      u.blocks
+from   v$session       s
+,      v$tempseg_usage u
+where  s.saddr = u.session_addr
+order  by u.blocks
+/
+</code>
+...and what SQL statement is running there?
+<code>
+col stmt for a70 wrap
+col piece noprint
+select sql.piece
+,      replace(sql.sql_text,chr(10),'') stmt
+from   v$session ses
+,      v$sqltext_with_newlines sql
+where  ses.status         = 'ACTIVE'
+and    ses.username       is not null
+and    ses.sql_address    = sql.address
+and    ses.sql_hash_value = sql.hash_value
+and    ses.audsid        != userenv('SESSIONID')
+and    ses.sid            = &sid
+order  by sql.piece
+/
+</code>
+==== Show temp tablespace usage with os user (and killer) ====
+<code>
+col killer for a60
+col osuser for a15
+set lines 1000 pages 100
+select 'alter system disconnect session '''||s.sid||','||s.serial#||''' immediate;' killer
+,      to_char(s.logon_time,'DD-MON-YY HH24:MI:SS') logon_time
+,      s.type
+,      sci.osuser
+,      s.status
+,      su.tablespace
+from   gv$session s
+,      gv$session_connect_info sci
+,      gv$process p
+,      gv$sort_usage su
+where  1=1
+and    p.addr     = s.paddr
+and    p.inst_id  = s.inst_id
+and    s.sid      = su.session_num
+and    s.sid      = sci.sid
+and    s.serial#  = sci.serial#
+order  by s.logon_time
+/
+</code>
+==== How much space is being used by inactive sessions using temp storage (with killer)? ====
+<code>
+set lines 1000 pages 100
+col killer     for a60
+col inst_id nopri
+col logon      for a21
+col osuser     for a10
+col spid       for a10
+col username   for a15
+col program    for a25
+col tablespace for a10
+col temp_size  for a20
+select 'alter system disconnect session '''||a.sid||','||a.serial#||''' immediate;' killer
+,      round(((b.blocks*p.value)/1024/1024),2)||'M' temp_size
+,      a.inst_id instance
+,      nvl(a.username, '(oracle)') username
+,      a.program
+,      a.status
+,      a.sql_id
+from   gv$session a
+,      gv$sort_usage b
+,      gv$parameter p
+where  p.name  = 'db_block_size'
+and    a.saddr = b.session_addr
+and    a.inst_id=b.inst_id
+and    a.inst_id=p.inst_id
+and    a.status = 'INACTIVE'
+/
+</code>
+and that wrapped up in PL/SQL
+<code>
+declare
+cursor c1 is
+select 'alter system disconnect session '''||s.killer||''' immediate' stmt
+from
+(
+select s.sid||','||s.serial#                  killer
+,      s.status                               status
+from   v$sort_usage    t
+,      v$session       s
+,      dba_tablespaces tbs
+,      v$process       p
+where  1=1
+and    t.session_addr = s.saddr
+and    s.paddr        = p.addr
+and    t.tablespace   = tbs.tablespace_name
+) s
+where  s.status = 'INACTIVE';
+stmt varchar2(4000);
+begin
+    for r1 in c1
+    loop
+        begin
+            dbms_output.put_line(r1.stmt);
+            execute immediate r1.stmt;
+        exception
+        when others then
+            null;
+        end;
+    end loop;
+end;
+/
+</code>
+==== Current TEMP tablespace usage ====
+<code>
+select vss.tablespace_name
+,      dtf.total_mb
+,      dtf.total_mb-round(vss.used_blocks*8/1024) current_free_mb
+,      round(vss.used_blocks*8/1024) current_used_mb
+,      round(vss.max_used_blocks*8/1024) max_used_mb
+from   v$sort_segment  vss
+,      (
+       select tablespace_name
+       ,      round(sum(bytes)/1024/1024) total_mb
+       from   dba_temp_files
+       group  by tablespace_name
+       ) dtf
+where  1=1
+and    vss.tablespace_name = dtf.tablespace_name
+/
+</code>
+==== Find the SQL that is taking up all the TEMP space ====
+<code>
+select hash_value
+,      sorts
+,      rows_processed/executions
+from   v$sql
+where  hash_value in (
+                     select hash_value
+                     from   v$open_cursor
+                     where  sid = &sid
+                     )
+and    sorts > 0
+and    parsing_schema_name like '&schema_name'
+order  by rows_processed/executions
+/
+</code>
+==== What is the currently assigned default temporary tablespace? ====
 See DEFAULT_TEMP_TABLESPACE to see which TEMP table space is defaulted at database level
-<code>10@@</code>
+<code>
+set lines 200 pages 100 newpa none
+col description for a60 noprint
+col property_name  for a40
+col property_value for a120
+select *
+from database_properties
+/
+</code>
-=====Reduce (or recreate) size of temporary tablespace=====
+==== Reduce (or recreate) size of temporary tablespace ====
-Sometimes the TEMP tablespace grows too big - out of all proportion to the size of the database.<br />
+Sometimes the TEMP tablespace grows too big - out of all proportion to the size of the database.\\
 Create a //temporary// temp tablespace
-<code>11@@</code>
+<code>
+create temporary tablespace tempx tempfile '/oracle/RMANV12/oradata1/temp1.dbf' size 1g autoextend on next 1g maxsize unlimited;
+</code>
 Assign this new //temporary// temp tablespace to the database
-<code>12@@</code>
+<code>
+alter database default temporary tablespace tempx;
+</code>
 Check for any sessions still holding on to the TEMP tablespace
-<code>13@@</code>
+<code>
+set lines 1000 pages 0 newpa none
+col stmt for a300
+select 'alter system disconnect session '''||s.killer||''' immediate;' stmt
+from
+(
+select s.sid||','||s.serial#                  killer
+,      s.status                               status
+from   v$sort_usage    t
+,      v$session       s
+,      dba_tablespaces tbs
+,      v$process       p
+where  1=1
+and    t.session_addr = s.saddr
+and    s.paddr        = p.addr
+and    t.tablespace   = tbs.tablespace_name
+) s
+where  s.status = 'INACTIVE'
+/
+</code>
 Drop the original temp tablespace
-<code>14@@</code>
+<code>
+drop tablespace temp including contents and datafiles;
+</code>
 Recreate the original temp tablespace (smaller, or with fewer datafiles)
-<code>15@@</code>
+<code>
+create temporary tablespace temp tempfile '/oracle/RMANV12/oradata2/temp01.dbf' size 2g autoextend on next 1g maxsize unlimited
+                                 ,        '/oracle/RMANV12/oradata2/temp02.dbf' size 2g autoextend on next 1g maxsize unlimited
+                                 ,        '/oracle/RMANV12/oradata2/temp03.dbf' size 2g autoextend on next 1g maxsize unlimited
+                                 ,        '/oracle/RMANV12/oradata2/temp04.dbf' size 2g autoextend on next 1g maxsize unlimited;
+</code>
 Assign the new temp tablespace to the database
-<code>16@@</code>
+<code>
+alter database default temporary tablespace temp;
+</code>
 Drop the //temporary// temp tablespace
-<code>17@@</code>
+<code>
+drop tablespace tempx including contents and datafiles;
+</code>
-=====Recreate the temporary tablespace when it belongs to a tablespace group=====
+==== Recreate the temporary tablespace when it belongs to a tablespace group ====
 Verify the tablespace groups and their members
-<code>18@@</code>
+<code>
+select * from dba_tablespace_groups;
+GROUP_NAME                     TABLESPACE_NAME
+------------------------------ ------------------------------
+TEMP                           TEMP1
+TEMP                           TEMP2
+</code>
 Check the filenames for these tablespaces
-<code>19@@</code>
+<code>
+select file_name, tablespace_name from dba_temp_files order by 2,1;
+FILE_NAME                                   TABLESPACE_NAME
+------------------------------------------- -------------------
+/CLN/prg/ora_ebs/db/apps_st/data/temp01.dbf TEMP1
+/CLN/prg/ora_ebs/db/apps_st/data/temp12.dbf TEMP1
+/CLN/prg/ora_ebs/db/apps_st/data/temp02.dbf TEMP2
+/CLN/prg/ora_ebs/db/apps_st/data/temp22.dbf TEMP2
+</code>
 Create new tablespaces and assign them to a new group
-<code>20@@</code>
+<code>
+create temporary tablespace temp_g1 tempfile '/CLN/prg/ora_ebs/db/apps_st/data/temp_g01.dbf' size 1g autoextend on next 1g maxsize 10g tablespace group temp_g;
+create temporary tablespace temp_g2 tempfile '/CLN/prg/ora_ebs/db/apps_st/data/temp_g02.dbf' size 1g autoextend on next 1g maxsize 10g tablespace group temp_g;
+</code>
 Recheck the groups
-<code>21@@</code>
+<code>
+select * from dba_tablespace_groups;
+GROUP_NAME                     TABLESPACE_NAME
+------------------------------ ------------------------------
+TEMP                           TEMP1
+TEMP                           TEMP2
+TEMP_G                         TEMP_G1
+TEMP_G                         TEMP_G2
+</code>
 Make the new tablespace group the default at database level
-<code>22@@</code>
+<code>
+alter database default temporary tablespace temp_g;
+</code>
 Check for any sessions still using the old default temporary tablespace (group)
-<code>23@@</code>
+<code>
+SET PAGESIZE 50
+SET LINESIZE 300
+COLUMN tablespace FORMAT A20
+COLUMN temp_size FORMAT A20
+COLUMN sid_serial FORMAT A20
+COLUMN username FORMAT A20
+COLUMN program FORMAT A50
+SELECT b.tablespace,
+       ROUND(((b.blocks*p.value)/1024/1024),2)||'M' AS temp_size,
+       a.inst_id as Instance,
+       a.sid||','||a.serial# AS sid_serial,
+       NVL(a.username, '(oracle)') AS username,
+       a.program,
+       a.status,
+       a.sql_id
+FROM   gv$session a,
+       gv$sort_usage b,
+       gv$parameter p
+WHERE  p.name  = 'db_block_size'
+AND    a.saddr = b.session_addr
+AND    a.inst_id=b.inst_id
+AND    a.inst_id=p.inst_id
+ORDER BY b.tablespace, b.blocks
+/
+TABLESPACE           TEMP_SIZE              INSTANCE SID_SERIAL           USERNAME             PROGRAM                                            STATUS   SQL_ID
+-------------------- -------------------- ---------- -------------------- -------------------- -------------------------------------------------- -------- -------------
+TEMP2                1M                            1 467,15748            DBSNMP               JDBC Thin Client                                   INACTIVE
+</code>
 Kill any sessions that may be killed
-<code>24@@</code>
+<code>
+alter system disconnect session '467,15748' immediate;
+</code>
 Remove the old tablespaces from the tablespace group and reassign the new tablespaces to it
-<code>25@@</code>
+<code>
+alter tablespace temp1 tablespace group '';
+alter tablespace temp2 tablespace group '';
+alter tablespace temp_g1 tablespace group temp;
+alter database default temporary tablespace temp;
+alter tablespace temp_g2 tablespace group temp;
+</code>
 Drop the old tablespaces
-<code>26@@</code>
+<code>
+drop tablespace temp1 including contents and datafiles;
+drop tablespace temp2 including contents and datafiles;
+</code>
 Check the final situation
-<code>27@@</code>
+<code>
+select * from dba_tablespace_groups;
-=====Which tablespaces are encrypted=====
+GROUP_NAME                     TABLESPACE_NAME
+------------------------------ ------------------------------
+TEMP                           TEMP_G1
+TEMP                           TEMP_G2
+</code>
+=== TDE Transparent Data Encryption ===
+==== Make a wallet keystore ====
+mkdir -p "${ORACLE_BASE}/admin/${ORACLE_SID}/wallet/tde"
+sqlplus / as sysdba
+alter system set wallet_root="${ORACLE_BASE}/admin/${ORACLE_SID}/wallet" scope = spfile;
+shu immediate
+startup
+alter system set tde_configuration="keystore_configuration=file";
+administer key management create keystore '${ORACLE_BASE}/admin/${ORACLE_SID}/wallet/tde' identified by some_password;
+set lines 1000 pages 100
+col wrl_parameter for a40
+select con_id, wrl_type, wrl_parameter, status from v$encryption_wallet;
+administer key management set keystore open force keystore identified by some_password;
+administer key management set key identified by some_password with backup;
+administer key management create auto_login keystore from keystore '${ORACLE_BASE}/admin/${ORACLE_SID}/wallet/tde' identified by some_password;
+select tablespace_name, status, encrypted from dba_tablespaces;
+alter tablespace users encryption online encrypt;
+alter system set encrypt_new_tablespaces=always;
+==== Which tablespaces are encrypted ====
 Encrypt tablespaces to protect data (after having created a wallet)
-<code>28@@</code>
+<code>
-=====Check encryption algorithm=====
+select tablespace_name, encrypted from dba_tablespaces;
-<code>29@@</code>
+</code>
+==== Check encryption algorithm ====
+<code>
+select t.name
+,      e.encryptionalg
+,      e.encryptedts
+from   v$tablespace t
+,      v$encrypted_tablespaces e
+where  t.ts# = e.ts#;
+</code>