* [[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)]]
==== Check the system-wide tablespace warning and critical alerting thresholds ====
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
/
METRICS_NAME OBJECT_NAME WARNING_OPER WARNING_VALUE CRITICAL_OPE CRITICAL_VALUE
---------------------------------------------------------------- ------------------------------ ------------ ------------------------------ ------------ ------------------------------
Tablespace Space Usage TEMP DO NOT CHECK DO_NOT_CHECK 0
Tablespace Space Usage UNDOTBS1 DO NOT CHECK DO_NOT_CHECK 0
Tablespace Space Usage GE 85 GE 97
==== Reset the tablespace thresholds to their system-wide default ====
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;
/
==== Override tablespace warning and critical thresholds for a tablespace ====
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;
/
==== Quick check of free space in a tablespace ====
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)
/
==== 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.
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
==== 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.
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
alter tablespace temp_system shrink space;
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
==== User quotas on all tablespaces ====
col quota format a10
select username
, tablespace_name
, decode ( max_bytes
, -1, 'unlimited'
, ceil(max_bytes / 1024 / 1024)||'M'
) quota
from dba_ts_quotas
where 1=1
and tablespace_name not in ('TEMP');
==== Tablespace Growth Report 1 ====
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;
==== Tablespace Growth Report 2 ====
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]]
set linesize 120
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;
==== 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!
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
/
If you are stuck on software written last century:
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 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
select (select decode(extent_management,'LOCAL','*',' ')
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
/
==== Use snapshot history tables used by AWR to track tablespace growth ====
select * from dba_hist_tbspc_space_usage order by to_date(substr(rtime,1,10),'MM/DD/YYYY')
==== Segment Growth Report ====
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
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')
/
==== What is the size of the segments in the SYSAUX tablespace? ====
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;
==== What are the components that make up the SYSAUX tablespace? ====
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
/
==== Show actual space taken up by objects in TEMP tablespaces ====
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
/
==== Alert tablespace with least space available ====
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
/
==== 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
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;
==== Which sessions are using TEMP tablespace? ====
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
/
or
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
/
...and what SQL statement is running there?
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
/
==== Show temp tablespace usage with os user (and killer) ====
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
/
==== How much space is being used by inactive sessions using temp storage (with killer)? ====
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'
/
and that wrapped up in PL/SQL
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;
/
==== Current TEMP tablespace usage ====
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
/
==== Find the SQL that is taking up all the TEMP space ====
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
/
==== What is the currently assigned default temporary tablespace? ====
See DEFAULT_TEMP_TABLESPACE to see which TEMP table space is defaulted at database level
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
/
==== Reduce (or recreate) size of temporary tablespace ====
Sometimes the TEMP tablespace grows too big - out of all proportion to the size of the database.\\
Create a //temporary// temp tablespace
create temporary tablespace tempx tempfile '/oracle/RMANV12/oradata1/temp1.dbf' size 1g autoextend on next 1g maxsize unlimited;
Assign this new //temporary// temp tablespace to the database
alter database default temporary tablespace tempx;
Check for any sessions still holding on to the TEMP tablespace
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'
/
Drop the original temp tablespace
drop tablespace temp including contents and datafiles;
Recreate the original temp tablespace (smaller, or with fewer datafiles)
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;
Assign the new temp tablespace to the database
alter database default temporary tablespace temp;
Drop the //temporary// temp tablespace
drop tablespace tempx including contents and datafiles;
==== Recreate the temporary tablespace when it belongs to a tablespace group ====
Verify the tablespace groups and their members
select * from dba_tablespace_groups;
GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TEMP TEMP1
TEMP TEMP2
Check the filenames for these tablespaces
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
Create new tablespaces and assign them to a new group
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;
Recheck the groups
select * from dba_tablespace_groups;
GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TEMP TEMP1
TEMP TEMP2
TEMP_G TEMP_G1
TEMP_G TEMP_G2
Make the new tablespace group the default at database level
alter database default temporary tablespace temp_g;
Check for any sessions still using the old default temporary tablespace (group)
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
Kill any sessions that may be killed
alter system disconnect session '467,15748' immediate;
Remove the old tablespaces from the tablespace group and reassign the new tablespaces to it
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;
Drop the old tablespaces
drop tablespace temp1 including contents and datafiles;
drop tablespace temp2 including contents and datafiles;
Check the final situation
select * from dba_tablespace_groups;
GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TEMP TEMP_G1
TEMP TEMP_G2
=== 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)
select tablespace_name, encrypted from dba_tablespaces;
==== Check encryption algorithm ====
select t.name
, e.encryptionalg
, e.encryptedts
from v$tablespace t
, v$encrypted_tablespaces e
where t.ts# = e.ts#;