Oracle

Konfiguration

Tablespaces und Files anzeigen lassen

SELECT file_name, bytes/1024/1024 AS size_mb FROM dba_data_files WHERE tablespace_name = 'PSAPSR3' ORDER BY file_name;

Tablespace anlegen

CREATE TABLESPACE PSAPSR3740X DATAFILE '/oracle/PZ0/sapdata1/sr3740x_1' SIZE 10M;

Tablespace erweitern

alter database datafile '/oracle/PZ0/sapdata1/sr3740x_1' resize 30G;

Tablespace mit neuer Datei erweitern

alter tablespace PSAPSR3740X add datafile '/oracle/PZ0/sapdata1/sr3740x_2' size 30G;

Backupmodus einstellen

Wird benötigt um ggf. die sapdata online kopieren zu können

SQL> ALTER DATABASE BEGIN BACKUP;
To find if database or any tablespace is in backup mode, the status in V$BACKUP is ACTIVE
SQL> select * from v$backup;
SQL> ALTER DATABASE END BACKUP;

SYSTEM Passwort ändern

sqlplus / as sysdba ALTER USER system IDENTIFIED BY "<PASSWORT>";

PSAPUNDO - Undo Tablespace

SELECT file_name,
       tablespace_name,
       autoextensible,
       bytes/1024/1024 AS size_mb,
       maxbytes/1024/1024 AS max_size_mb
FROM dba_data_files
WHERE tablespace_name = 'PSAPUNDO';

ALTER DATABASE DATAFILE '/oracle/R08/sapdata1/undo_1/undo.data1' AUTOEXTEND ON NEXT 100M MAXSIZE 20G;

Test

Freien Speicherplatz anzeigen

select a.tablespace_name, a.free_bytes*100/b.total_bytes as "Free(%)"
from
(select tablespace_name, sum(bytes) free_bytes
from dba_free_space
group by tablespace_name) a,
(select tablespace_name, sum(bytes) total_bytes
from dba_data_files
group by tablespace_name) b
where a.tablespace_name=b.tablespace_name;