1] Tablespace Management
1.0 oracle reverse
engineering on tablespace creation
set long
1000000000 longc 1000000000 lines 500
select
dbms_metadata.get_ddl('TABLESPACE','INDEX_DWH_SMALL') from dual;
1.1 Remove or Resize Empty
tablespaces
|
Goal
|
Drop tablespaces if they do
not contain any segments
or Resize them to a fixed 16
MB size
|
|
Version
|
10gR2
|
|
Views
|
dba_tablespaces dba_segments
dba_data_files
|
|
Log as
|
SYSTEM
|
Get SQL
orders to drop , or resize ( if you still need them ) the tablespaces.
-- drop
select 'drop
tablespace '||tablespace_name||' including
contents and datafiles ; ' from dba_tablespaces a
where not exists ( select '1' from dba_segments b where a.tablespace_name
= b.tablespace_name
) order by 1
-- resize
select -- a.tablespace_name ,
'alter database datafile '''||file_name||''' resize
16M ; ' from dba_tablespaces a , dba_data_files c
where a.tablespace_name
= c.tablespace_name
and not exists ( select '1' from dba_segments b where a.tablespace_name
= b.tablespace_name
)
order by 1
-- modify
sizes
select 'alter database DATAFILE '''||file_name||''' AUTOEXTEND ON MAXSIZE
4096M ; '
from dba_tablespaces a , dba_data_files b where
a.tablespace_name=b.tablespace_name and a.tablespace_name like '%HR%'
select 'alter database DATAFILE '''||file_name||''' AUTOEXTEND ON MAXSIZE
UNLIMITED ; '
from dba_tablespaces a , dba_data_files b where
a.tablespace_name=b.tablespace_name and a.tablespace_name like '%HR%'
A better way
would be as follow : TS without any QUOTAS / DEFAULT_TS and EMPTY
select 'drop
tablespace '||tablespace_name||' including
contents and datafiles ; ' from (
select A.tablespace_name , sum(bytes/1024/1024) Sz_MB from (
-- TS without any quotas
on them
select tablespace_name
from dba_tablespaces a
where not exists ( select '1' from dba_ts_quotas b where a.tablespace_name
= b.tablespace_name )
-- TS without default_ts
on them
and not exists ( select '1' from dba_users b where a.tablespace_name
= b.default_tablespace )
-- without any objects
and not exists ( select '1' from dba_segments b where a.tablespace_name
= b.tablespace_name )
and a.tablespace_name not in ( 'UNDO' , 'TEMP' , 'CTXSYS' )
order by 1 ) A , dba_data_files B
where A.tablespace_name = B.tablespace_name
group by A.tablespace_name
)
order by 1
1.2 Look out for missing tablespaces, only for
schema users
-- according to the
Tablespace_name <schema_name>_DAT
select username
, tablespace_name
from
(
select username
from
(
select username,nvl2(nb_obj,'Y','N') sch from
(
with A as ( select distinct owner , count(1) over ( partition by owner ) nb_obj from dba_objects where
object_type != 'SYNONYM' and object_name not like 'TMP%' )
select
instance_name , username , nb_obj from
dba_users b LEFT OUTER JOIN A on b.username = A.owner , v$instance
)
) where sch = 'Y' and username like 'SCH01_%'-- 8 schemas SCH01
)
LEFT OUTER JOIN
dba_tablespaces b on
tablespace_name = ''||username||'_DAT'
order by 1,2
1.3 Datafile Automatic
Resize to lowest possible limit
|
Goal
|
Datafile Resize Statement
until Used Data
|
|
Version
|
10gR2
|
|
Views
|
dba_tablespaces dba_segments
|
|
Log as
|
SYS
|
Restrictions : Locally managed tablespaces
only & at least one segment must exist in the datafile.
set linesize
500 pagesize 0 feedback off trimspool on
SELECT
'/*
'||to_char(CEIL((f.blocks-e.hwm)*(f.bytes/f.blocks)/1024/1024),99999999)||' M
*/ ' ||
'alter database datafile '''||file_name||'''
resize '||CEIL(e.hwm*(f.bytes/f.blocks)/1024/1024)||'M;' SQL
FROM
DBA_DATA_FILES f,
SYS.TS$ t,
(SELECT ktfbuefno relative_fno,ktfbuesegtsn
ts#,
MAX(ktfbuebno+ktfbueblks) hwm FROM
sys.x$ktfbue GROUP BY ktfbuefno,ktfbuesegtsn) e
WHERE
f.relative_fno=e.relative_fno and
t.name=f.tablespace_name and t.ts#=e.ts#
and f.blocks-e.hwm > 1000
-- and f.tablespace_name = 'PIVOT_ENV_DATA'
ORDER BY
f.blocks-e.hwm DESC
/
/* 1856 M
*/ alter database datafile '/data/database/undo.dbf' resize 28033M;
/* 672 M
*/ alter database datafile '/data/database/app1_dat.dbf' resize 1409M;
/* 644 M
*/ alter database datafile '/data/database/app2_dat.dbf' resize 61M;
/* 624 M
*/ alter database datafile '/data/database/app3_dat.dbf' resize 10513M;
/* 552 M
*/ alter database datafile '/data/database/app_wrk_dat.dbf' resize 1177M;
/* 513 M
*/ alter database datafile '/data/database/app1_dat_02.dbf' resize 10624M;
By the way,
find out which segments are located at the end of the datafiles ( and need
to be moved ).
First 3 last
segments for a tablespace :
select *
from (
select
a.owner,a.segment_name,b.partition_name,b.segment_type,block_id from
dba_extents a , dba_segments b
where
a.owner = b.owner and a.segment_name = b.segment_name and a.tablespace_name = '<TABLESPACE_NAME>'
order by 5
desc
) where
rownum < 4 ;
SPECIAL MULTI TENANT, execute connected to ROOT PDB :
get minimum Datafile size in each PDB, limited to saving more than 500MB.
set linesize 500 pagesize 0 feedback off trimspool on
column name format a10
column file_name format a50 word_wrapped
SELECT
'/* '||name||' '||savings_MB||' MB */ ' ||
'alter database datafile '''||file_name||''' resize '||smallest_MB||'M;' SQL
from (
select p.name,file_name,
ceil( (nvl(hwm,1)*t.block_size)/1024/1024 ) smallest_MB,
ceil( blocks*t.block_size/1024/1024) currsize_MB,
ceil( blocks*t.block_size/1024/1024) - ceil( (nvl(hwm,1)*t.block_size)/1024/1024 ) savings_MB
from cdb_data_files a, cdb_tablespaces t , V$CONTAINERS p,
( select con_id, file_id, max(block_id+blocks-1) hwm from cdb_extents group by con_id,file_id ) b
where a.con_id = b.con_id and a.file_id = b.file_id(+)
and a.tablespace_name = t.tablespace_name and a.con_id = t.con_id
and a.con_id = p.con_id
order by name,savings_MB desc
) where savings_MB > 500
/
Ex:
/* CDB$ROOT 4070 MB */ alter database datafile '/data/<root>/undotbs01.dbf' resize 5865M;
/* PDB1 11424 MB */ alter database datafile '/data/<pdb1>/undo_01.dbf' resize 3344M;
/* PDB2 9094 MB */ alter database datafile '/data/<pdb2>/app_dat.dbf' resize 5472M;
1.4 Reduce the size of
tablespaces when they contain objects
|
Goal
|
Generate move & rebuild
orders
|
|
Version
|
10gR2
|
|
Views
|
dba_tablespaces dba_indexes dba_segments
|
|
Log as
|
SYSTEM
|
Restrictions : LONG and LOBS datatypes are
not taken care of by the following commands ( use exp/imp instead ) [ though LOBS
are seen next ].
-- detect if there are LONG/LOB
select count(1) from dba_tab_columns where owner =
'${USs}' and ( data_type = 'LONG' or data_type = 'RAW' or data_type = 'LONG
RAW' ) and table_name not like 'BIN$%' and table_name != 'TOAD_PLAN_TABLE'
-- 1/ create working tablespace
CREATE TABLESPACE <TEMPO_TBSPCE> DATAFILE '<TEMPO_TBSPCE_PATH>.dbf' SIZE 32M AUTOEXTEND ON NEXT 64M MAXSIZE unlimited ;
-- 2/ move tables
select distinct 'alter user '||owner||' quota unlimited on <TEMPO_TBSPCE> ; ' from dba_segments where tablespace_name = '<TBSPCE>' and segment_type in ('TABLE','INDEX') ;
select 'alter table '||owner||'.'||segment_name||' move tablespace <TEMPO_TBSPCE> ; ' from dba_segments where tablespace_name = '<TBSPCE>' and segment_type = 'TABLE' and
segment_name not like 'BIN$%';
select 'alter index '||owner||'.'||index_name||' rebuild ; ' from dba_indexes where status != 'VALID' and owner not in ('SYS','SYSTEM') ;
-- 3/ move indexes
select 'alter index '||owner||'.'||segment_name||' rebuild tablespace <TEMPO_TBSPCE> ; ' from dba_segments where tablespace_name = '<TBSPCE>' and segment_type = 'INDEX' and
segment_name not like 'BIN$%' order by 1 ;
-- 4/ resize datafile then ( rename or ) move back to
original tablespace
select 'alter table '||owner||'.'||segment_name||' move tablespace <TBSPCE> ; ' from dba_segments where tablespace_name = '<TEMPO_TBSPCE>' and segment_type = 'TABLE' ;
select 'alter index '||owner||'.'||index_name||' rebuild ; ' from dba_indexes where status != 'VALID' and owner not in ('SYS','SYSTEM') ;
select 'alter index '||owner||'.'||segment_name||' rebuild tablespace <TBSPCE> ; ' from dba_segments where tablespace_name = '<TEMPO_TBSPCE>' and segment_type = 'INDEX' ;
-- 5. drop working tablespace
select 'drop tablespace '||tablespace_name||' including contents and datafiles ; ' from dba_tablespaces a
where not exists ( select '1' from dba_segments b where a.tablespace_name = b.tablespace_name ) order by 1
|
Goal
|
Move every type of segments
to new Tablespace
|
|
Version
|
10gR2
|
|
Views
|
dba_tablespaces dba_segments
|
|
Log as
|
SYS SYSTEM
|
select distinct tablespace_name , segment_type from dba_segments where owner = '<OWNER>'
-- quota on <NEW_TABLESPACE>
select distinct 'alter user '||owner||' quota unlimited on <NEW_TABLESPACE> ; ' from dba_segments where tablespace_name = '<OLD_TABLESPACE>' and segment_type in ('TABLE','INDEX') and owner = '<OWNER>'
-- move tables
select 'alter table "'||owner||'".'||segment_name||' move tablespace <NEW_TABLESPACE>
; ' from dba_segments where tablespace_name = '<OLD_TABLESPACE>' and segment_type = 'TABLE' and owner = '<OWNER>' and segment_name not like 'BIN$%'
-- move indexes
select 'alter index "'||owner||'".'||segment_name||' rebuild tablespace <NEW_TABLESPACE>
; ' from dba_segments where tablespace_name = '<OLD_TABLESPACE>' and segment_type = 'INDEX' and owner = '<OWNER>' and segment_name not like 'BIN$%'
-- move LOB objects to new tablespace, lobseg name
generated
-- when LOB segment+index is in <OLD_TABLESPACE> ,
using index location
select distinct 'alter table "'||a.owner||'".'||a.table_name||' move lob("'||column_name||'") store as (tablespace <NEW_TABLESPACE>)
; ' --, a.table_name , column_name , b.data_type
from dba_indexes a , dba_tab_columns b where a.tablespace_name = '<OLD_TABLESPACE>' and a.table_name = b.table_name and a.owner = b.owner and data_type like '%LOB%' and a.owner = '<OWNER>' order by 1 ;
select 'alter index '||owner||'.'||index_name||' rebuild ; ' from dba_indexes where owner = '<OWNER>' and status != 'VALID' ;
-- last check
select distinct tablespace_name from dba_segments where owner = '<OWNER>'
-- remove quota on <OLD_TABLESPACE>
ALTER USER <OWNER> QUOTA 0 ON <OLD_TABLESPACE>;
1.5 Rename a Tablespace from
10g on
select distinct tablespace_name from dba_segments where owner = 'APP3'
select tablespace_name , count(1) from dba_segments where tablespace_name like 'APP%' group by tablespace_name
select count(1) from dba_segments where tablespace_name = 'APP3_DAT' -- 0
select * from dba_ts_quotas where tablespace_name like 'APP%'
TABLESPACE_N USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS DROPPED
APP_DAT UTIL81 0 -1 0 -1 NO
APP_DAT APPM 1807351808 -1 220624 -1 NO
APP_DAT APPV2_INT 1218117632 -1 148696 -1 NO
APP_DAT APP3 2220883968 -1 271104 -1 NO
APP3_DAT APP3 0 -1 0 -1 NO
drop tablespace APP3_DAT including contents and datafiles ;
alter tablespace APP_DAT rename to APP3_DAT ;
1.6 Oracle Check Autoextend
limit
autoextend
check limit ORA-01688
Maximum datafile size for a SmallFile Tablespace based
on 10gR2 documentation would be:
Block Size
Maximum Datafile File Size
-----------
---------------------------
2k
4194303 * 2k = 8 GB
4k
4194303 * 4k = 16 GB
8k
4194303 * 8k = 32 GB
16k
4194303 * 16k = 64 GB
32k
4194303 * 32k = 128 GB
Maximum datafile size for a BigFile Tablespace based
on 10gR2 documentation would be:
Block Size
Maximum Datafile File Size
-----------
---------------------------
2k
4294967295 * 2k = 8 TB
4k
4294967295 * 4k = 16 TB
8k
4294967295 * 8k = 32 TB
16k
4294967295 * 16k = 64 TB
32k
4294967295 * 32k = 128 TB
Monitor the
space left until the Max Datafile Size is reached when Autoextend is ON
-- find the occupied space vs. the max datafile size for
every tablespace and raise an alert
-- we limit to the case wher we have free space left < 3
GB
-- FMP : free_vs_maxsz_pct [0-89] OK ; [90-94] Warning ;
[95-100] Critical
select ts , case when
free_vs_maxsz_MB > 3072 then 1 else free_vs_maxsz_pct end FMP from (
SELECT a.tablespace_name ts,
-- a.file_id , bytes , maxbytes , maxbytes-bytes Free_Pct
-- sum((maxbytes-bytes)/1024/1024) free_left_sz ,
sum(maxbytes/1024/1024) max_data_sz ,
100 - ( CEIL (SUM ((maxbytes - BYTES)) * 100 / SUM (maxbytes)) )
free_vs_maxsz_pct , ceil(SUM(maxbytes-BYTES)/1024/1024)
free_vs_maxsz_MB
FROM dba_data_files a, dba_tablespaces b
WHERE maxbytes
!= 0
AND CONTENTS != 'UNDO'
AND a.tablespace_name = b.tablespace_name
-- and a.tablespace_name
= 'TEST'
GROUP BY a.tablespace_name
ORDER BY 2
)
WHERE ROWNUM < 2;
Example with
Nagios :
/usr/local/nagios/1.4.1/libexec/check_ora_autoextend
DBNAME
OK utilization is
85% on DBNAME for APP_DAT
1.6 Purge Recyclebin Tablespace
SELECT distinct 'purge tablespace '||ts_name||' user "'||owner||'" ;' FROM DBA_RECYCLEBIN where owner not in ('SYS') and ts_name is not null order by 1
2] TEMP Tablespace
|
Goal
|
TEMP tablespace management
|
|
Version
|
10gR2
|
|
Views
|
v$sort_segment v$session s, v$sort_usage
|
|
Log as
|
SYSTEM
|
2.1 Queries
-- raw data
select INST_ID, TABLESPACE_NAME,EXTENT_SIZE, USED_EXTENTS, FREE_EXTENTS,
TOTAL_EXTENTS,MAX_USED_SIZE,MAX_SORT_SIZE from gv$sort_segment ;
-- TEMP Used & Free MB
with block_sz as ( SELECT VALUE FROM v$parameter WHERE NAME = 'db_block_size' )
select TABLESPACE_NAME,ceil(USED_EXTENTS*EXTENT_SIZE*value/1024/1024) Used_MB,
ceil((TOTAL_EXTENTS-USED_EXTENTS)*EXTENT_SIZE*value/1024/1024) Free_MB
from v$sort_segment, block_sz ;
-- who's owning TEMP space ? Active or Not ? which
segment type ?
select distinct s.username,s.sid,s.serial#,s.status , u.tablespace, u.contents,u.extents,
round(u.blocks*(SELECT VALUE FROM v$parameter WHERE NAME = 'db_block_size')/1024/1024,2) "Size (MB)",u.segtype
from v$session s, v$sort_usage u
where s.saddr=u.session_addr -- and tablespace = 'TEMP2'
order by 8 desc ;
|
USERNAME
|
SID
|
SERIAL#
|
STATUS
|
TABLESPACE
|
CONTENTS
|
EXTENTS
|
Size
(MB)
|
SEGTYPE
|
|
SYSTEM
|
1806
|
15764
|
ACTIVE
|
TEMP
|
TEMPORARY
|
345
|
345
|
LOB_DATA
|
|
MPADOX
|
1363
|
8752
|
INACTIVE
|
TEMP
|
TEMPORARY
|
1
|
1
|
DATA
|
|
MPADOX
|
1363
|
8752
|
INACTIVE
|
TEMP
|
TEMPORARY
|
1
|
1
|
INDEX
|
|
MPADOX
|
1363
|
8752
|
INACTIVE
|
TEMP
|
TEMPORARY
|
1
|
1
|
LOB_DATA
|
|
VBOIDRON
|
1367
|
1959
|
INACTIVE
|
TEMP
|
TEMPORARY
|
1
|
1
|
DATA
|
2.2 Creation
Let’s take a
situation I had, when I needed to recreate a controlfile to open an
instance : tempfile was missing !
SQL> select a.tablespace_name , b.file_name from
dba_tablespaces a left outer join dba_temp_files b on a.tablespace_name =
b.tablespace_name where a.tablespace_name like '%TEMP%' ;
TABLESPACE_NAME FILE_NAME
--------------------------------------------------------
TEMP
Create a new
TEMP :
it’s
interesting to pre-create the tempfile using « dd » to allocate every
OS blocks
$ dd if=/dev/zero
of=/temp_01.dbf bs=1024 count=4194304
SQL> CREATE TEMPORARY
TABLESPACE temp_1 tempfile '/data/protr/temp_1_01.dbf' size 2048M EXTENT
MANAGEMENT LOCAL UNIFORM SIZE 1M ;
SQL> alter database
default temporary tablespace temp_1 ;
Database altered.
Check Database Default Temporary Tablespace :
SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE';
Remove old
TEMP ( possibly with a unix rm )
SQL> drop tablespace temp
including contents and datafiles ;
3] Modify Tablespace Status READ
ONLY and WRITE with a nice PL loop
Alter
Tablespace Status with PL Loop, ignoring Oracle errors of already RO, or not RO
Tablespaces.
set serveroutput on
DECLARE
tablespace_is_already_ro
EXCEPTION;
PRAGMA
EXCEPTION_INIT(tablespace_is_already_ro, -1644);
tablespace_is_not_read_only
EXCEPTION;
PRAGMA
EXCEPTION_INIT(tablespace_is_not_read_only, -1646);
begin
-- for i in ( select
distinct tablespace_name from dba_segments where owner = 'OWNER' and
tablespace_name != 'OWNER_DAT' order by 1 )
for i in ( select distinct
tablespace_name from dba_tablespaces where tablespace_name not in
('SYSTEM','SYSAUX','UNDO','TEMP') )
loop
BEGIN
EXECUTE IMMEDIATE 'alter
tablespace '||i.tablespace_name||' READ &action ' ;
-- if errors, we don't want
to stop, if a tablespace is already READ ONLY, no big deal
-- ORA-01644: tablespace 'SCH_DAT'
is already read only
EXCEPTION WHEN
tablespace_is_already_ro THEN NULL;
WHEN
tablespace_is_not_read_only THEN NULL;
END;
end loop;
end;
/
4] UNDO
4.1 Changing UNDO Tablespace
to resize/drop the older UNDOTBS1 -> UNDOTBS2
SQL>
CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '<path>/undotbs02.dbf' SIZE 64M
AUTOEXTEND ON NEXT 32M MAXSIZE UNLIMITED ONLINE RETENTION NOGUARANTEE BLOCKSIZE
8K FLASHBACK ON;
SQL>
alter system set undo_tablespace = 'UNDOTBS2' ;
System
altered.
SQL> shut
immediate
SQL>
startup
Database
opened.
SQL> sho
parameter undo
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
undo_tablespace string UNDOTBS2
select distinct
s.username,s.sid,s.serial#,rn.name,rs.curext,rs.curblk,t.used_ublk,t.used_urec,due.tablespace_name,t.start_time
from v$transaction t
,v$session s ,v$rollname rn
,v$rollstat rs ,dba_undo_extents due
where t.addr
= s.taddr and t.xidusn = rn.usn and rn.usn
= rs.usn and rn.name = due.segment_name and due.status = 'ACTIVE' -- uncommitted
order by 7
desc -- NO ROWS
SQL> drop
tablespace UNDOTBS1 including contents and datafiles ;
Tablespace
dropped.
5] A note on XE, the limits
You
have reached the 4GB user data limit. Oracle Database Express Edition is
designed to provide users with 4 GB of user data storage. Physical storage is
limited to a database size of 5 GB of total overall size. This includes the
system tablespace, but excludes temporary and rollback.
There
are no connection limitations with Oracle XE. The
limitations are on hardware utilization. Regardless of a system's specifications,
an Oracle XE server will only use:
1 CPU
1 Gig
of RAM : SGA+PGA
4 Gigs
of Disk Space (+1 Gig for system data)
Find out
what’s left within Tablespaces :
-- SYSTEM
out of 1 GB, what's left
select 1024-sum(bytes/1024/1024) from dba_data_files a where tablespace_name = 'SYSTEM' -- 474 MB
-- Appli
out of 4 GB
select 4096-sum(bytes/1024/1024) from dba_data_files a where tablespace_name not in ('SYSTEM','UNDO','TEMP') -- 54 MB
-- MAX SYSTEM out of 1
GB
select file_name , autoextensible , 1024-maxbytes/1024/1024 MAX_MB from dba_data_files a where tablespace_name = 'SYSTEM' -- 224 MB
-- MAX Applicative out
of 4 GB
select 4096-sum(maxbytes/1024/1024) MAX_MB from dba_data_files a where tablespace_name not in ('SYSTEM','UNDO','TEMP') -- 0 MB
To avoid ORA-12952:
The request exceeds the maximum allowed database size of 4 GB
select sum(bytes/1024/1024/1024) from dba_data_files -- 4.69 GB
is not what
matters, but this is what’s important to follow :
-- sum up Segments and get what’s left against 4 GB
select 4096-sum(bytes/1024/1024) from
dba_segments a , dba_tablespaces
b where a.tablespace_name = b.tablespace_name
and b.tablespace_name not in ('SYSTEM','UNDO','TEMP')
6] New 12C+ Online move of Datafiles
No need to Offline any Tablespaces prior to move Datafiles, one can now :
ALTER DATABASE MOVE DATAFILE 56 TO '/<new_file_path>/system01.dbf' ;
In case of Dataguard configuration
connect to broker to disable sync to Standby and be able to move datafiles on Primary environment :
$ dgmgrl
connect /
EDIT DATABASE '<standby>' SET STATE='APPLY-OFF';
$ sqlplus / as sysdba
alter session set container = <PDB_NAME> ;
ALTER DATABASE MOVE DATAFILE '/data/<pdb_name>/TS_1.dbf' TO '/<new_path>/TS_1.dbf' ;
$ dgmgrl
EDIT DATABASE '<standby>' SET STATE='APPLY-ON' ;
Check Path on both Primary and Standby CDB
set lines 150 pages 1000
col pdb for a20
col name for a50
select b.NAME PDB , a.name from v$datafile a , v$containers b where a.CON_ID=b.CON_ID order by 1 ;
