#2192

TEMP tablespace

List current files :

SELECT file_name, tablespace_name, 
       bytes/1024/1024 MB, blocks
FROM dba_temp_files
ORDER BY tablespace_name, file_name;

Remove file :

ALTER DATABASE TEMPFILE '/opt/oracle/tablespaces/tmp/temp02.dbf' DROP INCLUDING DATAFILES;

Add new file :

ALTER TABLESPACE temp ADD TEMPFILE '/opt/oracle/tablespaces/tmp/temp01.dbf' SIZE 1G;

Create new temp tablespacce

CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE  ‘/path/to/file.dbf′ SIZE 1000M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED; 

Change temp tablespace

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

Drop old temp tablespace

DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

Drop tablespace even if file is missing

drop tablespace <tablespace_name> including contents cascade constraints;

If the file is not present anymore, use this command before DROP statement :

alter database datafile '/path/to/the/tablespace/<tablespace_file>'  offline drop;

Drop missing tablespace file
Shutdown DB :

shutdown abort

Start mounting :

startup mount

Drop all missing files (1 by 1) :

ALTER DATABASE DATAFILE '/path/to/missing/file.dbf' OFFLINE DROP;

Restart DB :

alter database open

UNDO tablespace
Create new TBS :

create undo tablespace UNDO_RBS1 datafile '/path/to/file.dbf' size 1000m AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED;

Set as default undo tablespace :

alter system set undo_tablespace=undo_rbs1;

Retrieve the old one

SELECT tablespace_name, file_name FROM dba_data_files WHERE tablespace_name like 'UNDO%';

Drop it

drop tablespace XXX;

or :

DROP TABLESPACE XXX INCLUDING CONTENTS AND DATAFILES;

Drop multiple tablespaces (with files)

select 'DROP TABLESPACE '|| tablespace_name || ' INCLUDING CONTENTS AND DATAFILES;'  from DBA_TABLESPACES where tablespace_name like 'AAA%';

Update tablespace max size

DECLARE
NB_UNLOCKED PLS_INTEGER := 0;
 
CURSOR C_TBL IS
    SELECT FILE_NAME, TABLESPACE_NAME 
    FROM DBA_DATA_FILES
    WHERE TABLESPACE_NAME LIKE 'AAA%';
 
BEGIN
 
    FOR tbl IN C_TBL LOOP     
        dbms_output.put_line('Update TABLSPACE '||tbl.FILE_NAME ||''||tbl.TABLESPACE_NAME );
        EXECUTE IMMEDIATE 'ALTER DATABASE DATAFILE '''||tbl.FILE_NAME||''' AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED';
        NB_UNLOCKED := NB_UNLOCKED+1;     
    END LOOP;
     
    dbms_output.put_line('Nb tablespace updated : '||NB_UNLOCKED );
 
END;

Add datafile

ALTER TABLESPACE users
    ADD DATAFILE '/path/to/tablespace/users03.dbf' SIZE 100M
      AUTOEXTEND ON
      NEXT 50M
      MAXSIZE UNLIMITED;

List (and resize) empty tablespace

select subQ.*, 'ALTER DATABASE DATAFILE '''||file_name||''' RESIZE '||trunc(usedMB*1.3)||'M;' 
from (
select df.tablespace_name, df.file_name, round(df.bytes/1024/1024) totalSizeMB, nvl(round(usedBytes/1024/1024), 0) usedMB, nvl(round(freeBytes/1024/1024), 0) freeMB,
    nvl(round(freeBytes/df.bytes * 100), 0) freePerc
from dba_data_files df
    left join (
        select file_id, sum(bytes) usedBytes
        from dba_extents
        group by file_id
    ) ext on df.file_id = ext.file_id
    left join (
        select file_id, sum(bytes) freeBytes
        from dba_free_space
        group by file_id
    ) free on df.file_id = free.file_id
where df.tablespace_name like '%ICS%'
) subQ
where 
totalSizeMB > 10000
--and usedMB < 1000
AND freePerc > 70

DB, oracle, tablespace

[Oracle] How-To Oracle RAC