Miscellanous

 First, Miscellanous stuff :

1] Put RMAN backups on NFS

It’s very annoying to hit this error, even when following the given OS mount arguments …

ORA-27054: NFS file system where the file is created or resides is not mounted with correct options See [ID 781349.1]

If you’re sick of dealing with NFS, it’s now possible to remove the NFS options checking ( 10gR2+ ) :

SQL> alter system set event="10298 trace name context forever, level 32" scope = spfile ;

Side effects:

It will generate some trace files which you can ignore Bug:4998064 'Ksfq 10298 Trace Enabled For Levels Other Than Bit Level 0x2'  (unpublished)- It can have a side effect of disabling direct IO on  RAC using regular
files on NFS mounts

2] List commands

list backup of archivelog sequence between 252000 and 252051 ;

list backup of archivelog sequence 359490 ;

list backup of archivelog from sequence 889818 until sequence 889873 ;

3] Is it possible to restore an RMAN dump coming from another OS platform

?

Is RMAN portable from an OS to another : NO

What’s possible in 10g :

- Use RMAN to copy a source DB in READ-ONLY state to a different OS platform but with the same ENDIAN

RMAN> CONVERT DATABASE NEW DATABASE 'LinDB10g' ... TO PLATFORM 'Linux IA (32-bit)'

- Copy the tablespaces to a target DB on a different OS and ENDIAN platform

Transportable Tablespaces :

RMAN> CONVERT TABLESPACE TBS1 TO PLATFORM 'HP-UX (64-bit)' FORMAT '/tmp/%U';

Note :

SELECT * FROM V$TRANSPORTABLE_PLATFORM order by 1 ;

PLATFORM_ID PLATFORM_NAME                       ENDIAN_FORMAT

----------- ----------------------------------- --------------

          1 Solaris[tm] OE (32-bit)             Big

          2 Solaris[tm] OE (64-bit)             Big

          3 HP-UX (64-bit)                      Big

          4 HP-UX IA (64-bit)                   Big

          5 HP Tru64 UNIX                       Little

          6 AIX-Based Systems (64-bit)          Big

          7 Microsoft Windows IA (32-bit)       Little

          8 Microsoft Windows IA (64-bit)       Little

          9 IBM zSeries Based Linux             Big

         10 Linux IA (32-bit)                   Little

         11 Linux IA (64-bit)                   Little

         12 Microsoft Windows x86 64-bit        Little

         13 Linux x86 64-bit                    Little

         15 HP Open VMS                         Little

         16 Apple Mac OS                        Big

         17 Solaris Operating System (x86)      Little

         18 IBM Power Based Linux               Big

         19 HP IA Open VMS                      Little

         20 Solaris Operating System (x86-64)   Little

4] RMAN & Split-Mirror Backup

I used two technologies to do an RMAN Backup on a Clone DB to eliminate the overhead of a Backup on the Production Database :

EMC BCVs Business Continuance Volumes : Split-Mirror Disks

IBM Flash Copy : more like a Snapshot of Unix Inodes, then Copy those Blocks as a Clone

Those tools are different but have the same result : RMAN may be used to backup a copy either done online or offline of a Production DB. This way, RMAN capabilities remain available while we offload the Database Server.

RMAN Catalog is required.

Those backups are very Complex and should be run with DBA expert knowledge …

4.1 Best practices

·         on the fc_mirror, it may be necessary to recover the database in mount mode to get a consistent state in case of non-clean shutdown.

·         for archivelog db, instance fc_mirror shouldn’t never be open because it would rollback transactions that may be commited on PRD db.

·         controlfile autobackup ON may still be used

4.2 RMAN-20035: invalid high recid

Note:302615.1 RMAN is allowed to backup a mounted database on an intermediate server that hosts mirror filesystems only if the controlfile is converted before backup from ‘current’ to ‘backup’ state.

Therefore, RMAN configuration and Current Controfile backup will take place on the Production Database.

If 2 ‘current’ controlfiles connect to RCAT, error RMAN-20035 occurs.

Explanation

Every Backup records in Controlfile and RMAN Catalog have a RECID generated by the Instance.

After a successful backup on a Clone DB, the RECID is incremented in the Controlfile, then is logged in the RMAN Catalog. RECID in Controlfile hasn’t been modified.

On the following communication between Production DB and RMAN Catalog, if the highest RECID in Catalog is superior to the RECID of Controlfile, the RMAN-20035 id triggered.

Oracle recommends to convert the Controlfile on the Copy into a ‘backup controlfile’.

Hence, when RMAN detects a non-current Controlfile ( ‘backup’ or ‘standby’ ), it won’t increment the RECID in the Catalog.

5] RMAN Operations Running Status

set pages 5000 lines 150

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;

6] 10G+ Block Change Tracking

6.1 Enable / Check for

alter database enable block change tracking using file '/work/oracle/<base>/flash_area/<base>_bct.trk' ;

col FILENAME for a40

select * from v$block_change_tracking ;

Tablespaces

1] 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

2] TEMP

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.

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')