oracle admin datafiles tablespace - ghdrako/doc_snipets GitHub Wiki

• https://support.dbagenesis.com/oracle-database/tablespace-administration
• https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-tablespaces.html#GUID-1C162C60-6698-44F2-B2A9-F3E2D2958D88

SELECT tablespace_name,
       contents,
       extent_management,
       segment_space_management
FROM dba_tablespaces;

Kolumna	Co oznacza
CONTENTS	Typ przestrzeni: 🔹 PERMANENT – zwykła przestrzeń dla danych 🔹 TEMPORARY – dla sortowania, operacji tymczasowych 🔹 UNDO – do zarządzania transakcjami (rollback, undo)
EXTENT_MANAGEMENT	Zarządzanie extentami: 🔹 LOCAL – lokalnie zarządzana (zalecana) 🔹 DICTIONARY – starszy typ, zarządzany przez katalog (niezalecany)
SEGMENT_SPACE_MANAGEMENT	Zarządzanie segmentami:🔹 AUTO – automatyczne (zalecane)🔹 MANUAL – ręczne zarządzanie (starsze wersje)

Przykład wyniku:

TABLESPACE_NAME	CONTENTS	EXTENT_MANAGEMENT	SEGMENT_SPACE_MANAGEMENT
USERS	PERMANENT	LOCAL	AUTO
TEMP	TEMPORARY	LOCAL	N/A
UNDO	UNDO	LOCAL	N/A

SELECT file#, name FROM v$datafile;
     FILE# NAME
---------- -------------------------------------
         1 /u01/oracle/oradata/MITP/system01.dbf
         3 /u01/oracle/oradata/MITP/sysaux01.dbf
         4 /u01/oracle/oradata/MITP/undotbs01.dbf
         5 /u01/oracle/oradata/MITP/tools01.dbf
         7 /u01/oracle/oradata/MITP/users01.dbf

Lista plikow danej przestrzeni tabel

SELECT tablespace_name, file_name
FROM dba_data_files
WHERE tablespace_name = 'USERS';  -- wpisz nazwę swojej przestrzeni

Przeniesienie pliku do innej lokalizacji Od Oracle 12c (12.1 i nowsze) możesz użyć polecenia:

ALTER DATABASE MOVE DATAFILE 5 TO '/u02/oracle/oradata/MITP/tools01.dbf';

i wykonać przeniesienie online, bez odmontowywa i bez restartu bazy, o ile plik nie należy do systemowych przestrzeni tabel (np. SYSTEM, SYSAUX) i nie jest w użyciu w sposób, który to blokuje.

SQL> ALTER DATABASE MOVE DATAFILE 5 TO
  2  '/u02/oracle/oradata/MITP/tools01.dbf';

SQL> SELECT file#, name FROM v$datafile;
     FILE# NAME
---------- -------------------------------------
         5 /u02/oracle/oradata/MITP/tools01.dbf

Po wykonaniu polecenia FLASHBACKDATABASE plik danych nie jest przenoszony do swojej pierwotnej lokalizacji. Dane zostaną jednak zaktualizowane do starego statusu

SELECT tablespace_name,file_name
  2  FROM dba_data_files
  3  ORDER BY 1,2;

Istnieją dwa podstawowe typy przestrzeni tabel:

• Locally Managed Tablespaces
• Dictionary Managed Tablespaces

Domyślnym typem jest Lokalnie zarządzana przestrzeń tabelowa. Wolną przestrzenią zarządza się za pośrednictwem mapy bitowej w nagłówku pliku danych. Segmentami w przestrzeni tabel można zarządzać automatycznie lub ręcznie. Standardem jest automatyczne zarządzanie przestrzenią segmentów (ASSM), z wyjątkiem przestrzeni tabel SYSTEM, UNDO i TEMP. Oprócz lepszej wydajności, ASSM oferuje zaletę wyeliminowania potrzeby ręcznego zarządzania parametrami pamięci masowej, co upraszcza administrację. Przestrzeń tabel zarządzana za pomocą słownika wykorzystuje katalog bazy danych do zarządzania zakresami. Był to domyślny typ w poprzednich wersjach i został zastąpiony lokalnie zarządzaną przestrzenią tabel w celu uzyskania lepszej wydajności i uniknięcia konfliktów w katalogu.

Przestrzenie tabel zarządzane przez słownik nadal mogą być używane ze względów zgodności.

W wersji 10g wprowadzono kolejny typ przestrzeni tabel:Bigfile Tablespace . Było to spowodowane głównie rosnącym rozmiarem baz danych. Plik danych małej przestrzeni tabelowej plików (domyślnie) może składać się maksymalnie z 4 milionów bloków danych. Przy rozmiarze bloku 8 KB oznacza to, że maksymalny rozmiar pliku danych nie może przekroczyć 32 GB. Plik danych dużej przestrzeni tabelowej plików może osiągnąć rozmiar 32 TB przy rozmiarze bloku 8 KB. Jednak w przestrzeni tabel dużego pliku nie może znajdować się więcej niż jeden plik danych.

Pliki dziennika redo tworzą dziennik transakcji bazy danych. Zmiany w bazie danych zwykle nie są zapisywane na dysku po zakończeniu transakcji (COMMIT), lecz pozostają w pamięci podręcznej bufora bazy danych aż do następnego punktu kontrolnego. Są one jednak zapisywane w plikach dziennika redo, tak aby w przypadku awarii bazy danych możliwe było odzyskanie wszystkich zakończonych transakcji. Pliki dziennika powtórzeń online mają zatem kluczowe znaczenie dla możliwości odzyskania bazy danych. Pliki dziennika powtórzeń online składają się z kilku grup. Każda grupa może zawierać wielu członków (plików). Pliki członków grupy są identycznymi lustrzanymi odbiciami, co zapewnia niezawodność. Jeśli członek grupy ulegnie uszkodzeniu lub zostanie przypadkowo usunięty, dostęp do pozostałych członków będzie nadal możliwy. Zazwyczaj w skład grupy wchodzą dwie osoby

Log Writer zawsze zapisuje do dokładnie jednej grupy o statusie CURRENT,Jeśli grupa jest pełna, ma miejsce przełączenie dziennika. Logwriter zmienia się w następną grupę i oznacza je jako CURRENT. Gdy dziennik dotrze do ostatniej grupy, zaczyna się ponownie od pierwszego i zastępuje go. Jeśli baza danych działa w trybie Archivelog, grupa jest zarchiwizowana przed nadpisaniem. Kopia jest tworzona w postaci zarchiwizowanego pliku dziennika ponownego. Oracle gwarantuje zatem przywrócenie w dowolnym momencie z powodu poprzedniej kopii zapasowej. Nowy numer sekwencji jest tworzony z każdym przełącznikiem dziennika.

SQL> SELECT group#,sequence#,status,first_time
  2  FROM v$log;
    GROUP#  SEQUENCE# STATUS           FIRST_TIME
---------- ---------- ---------------- -------------------
         1         73 CURRENT          23.06.2019 20:00:51
         2         71 INACTIVE         20.06.2019 21:00:16
         3         72 INACTIVE         23.06.2019 18:49:32

Utworzenie przestrzeni tabel z autoextend

CREATE TABLESPACE moja_przestrzen
DATAFILE '/u01/app/oracle/oradata/moja_przestrzen01.dbf'
SIZE 500M
AUTOEXTEND ON
NEXT 100M
MAXSIZE 2G;

Ustawienie pliku jako autoextend

ALTER DATABASE DATAFILE '/ścieżka/do/plik_danych.dbf'
AUTOEXTEND ON
NEXT 100M
MAXSIZE 2G;

• AUTOEXTEND ON – włącza automatyczne zwiększanie pliku danych, gdy kończy się miejsce.
• NEXT 100M – ustala krok powiększania, czyli o ile ma rosnąć plik danych za każdym razem.
• MAXSIZE 2G – ustala maksymalny rozmiar, do jakiego plik może urosnąć.

Gdy mamy ustawiony data guard to na standby:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

-- Wykonaj zmianę, np. AUTOEXTEND:
ALTER DATABASE DATAFILE '/u02/oracle/oradata/xyz01.dbf' AUTOEXTEND ON NEXT 100M MAXSIZE 2G;

-- Wznów recovery w tle:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Utworzenie przestrzeni tabel z autoextend

CREATE TABLESPACE moja_przestrzen
DATAFILE '/u01/app/oracle/oradata/moja_przestrzen01.dbf'
SIZE 500M
AUTOEXTEND ON
NEXT 100M
MAXSIZE 2G;

Włączyć auto-rozszerzanie (autoextend) na poziomie datafile’a

SELECT tablespace_name, file_name, autoextensible, bytes/1024/1024 AS size_mb
FROM dba_data_files;

Jeśli kolumna AUTOEXTENSIBLE ma wartość NO, to autoextend jest wyłączony.

ALTER DATABASE DATAFILE '/ścieżka/do/pliku.dbf'
AUTOEXTEND ON
NEXT 100M
MAXSIZE UNLIMITED;

ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/ORCL/users01.dbf'
AUTOEXTEND ON
NEXT 50M
MAXSIZE 5G;

Opcje:

• AUTOEXTEND ON – włącza auto-rozszerzanie
• NEXT 100M – przyrost o 100 MB, gdy plik się zapełni
• MAXSIZE UNLIMITED – brak ograniczenia rozmiaru (możesz też ustawić limit, np. MAXSIZE 10G)

Wyłaczenie autoextend

ALTER DATABASE DATAFILE '/ścieżka/do/pliku.dbf'
AUTOEXTEND OFF;

Ustawienie pliku jako autoextend

ALTER DATABASE DATAFILE '/ścieżka/do/plik_danych.dbf'
AUTOEXTEND ON
NEXT 100M
MAXSIZE 2G;

• AUTOEXTEND ON – włącza automatyczne zwiększanie pliku danych, gdy kończy się miejsce.
• NEXT 100M – ustala krok powiększania, czyli o ile ma rosnąć plik danych za każdym razem.
• MAXSIZE 2G – ustala maksymalny rozmiar, do jakiego plik może urosnąć.

Gdy mamy ustawiony data guard to na standby:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

-- Wykonaj zmianę, np. AUTOEXTEND:
ALTER DATABASE DATAFILE '/u02/oracle/oradata/xyz01.dbf' AUTOEXTEND ON NEXT 100M MAXSIZE 2G;

-- Wznów recovery w tle:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Create Tablespace

Create tablespace test_tbs datafile '/u01/test_tbs_01.dbf' size 50m;

Add Space to Tablespace

!!! Make sure you have space at OS level before resizing or adding new datafile

Alter database datafile '/u01/test_tbs_01.dbf' resize 10g;

Alter tablespace test_tbs add datafile '/u01/test_tbs_02.dbf' size 5g;

Powieksz limit w autoextend

ALTER DATABASE DATAFILE '/ścieżka/do/pliku.dbf' AUTOEXTEND ON MAXSIZE 10G;

Dodanie pliku do tablespace z autoextend

alter tablespace sysaux add datafile '/ora/oradata/ora/sysaux02.dbf' size 64m autoextend on next 64m maxsize 32765m;

Drop Tablespace

drop tablespace test_tbs including contents and datafiles;

Tablespace Coalesce

• Tablespace Coalesce combines all contiguous free extents into larger contiguous extents inside all datafiles
• It takes any free extents that are right next to some other free extent and make one bigger free extent
• SMON will perform this coalescing in the background but if you need it to happen right now, coalesce will do it

SQL> ALTER TABLESPACE USERS COALESCE;

Find Tablespace Utilization

set colsep |
set linesize 100 pages 100 trimspool on numwidth 14 
col name format a25
col owner format a15 
col "Used (GB)" format a15
col "Free (GB)" format a15 
col "(Used) %" format a15 
col "Size (M)" format a15 
SELECT d.status "Status", d.tablespace_name "Name", 
       TO_CHAR(NVL(a.bytes / 1024 / 1024 /1024, 0),'99,999,990.90') "Size (GB)", 
       TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024 /1024,'99999999.99') "Used (GB)", 
       TO_CHAR(NVL(f.bytes / 1024 / 1024 /1024, 0),'99,999,990.90') "Free (GB)", 
       TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), '990.00') "(Used) %"
 FROM sys.dba_tablespaces d, 
      (select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) a, 
      (select tablespace_name, sum(bytes) bytes 
         from dba_free_space group by tablespace_name) f 
        WHERE d.tablespace_name = a.tablespace_name(+) 
          AND d.tablespace_name = f.tablespace_name(+) 
          AND NOT (d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY') 
UNION ALL 
SELECT d.status 
       "Status", d.tablespace_name "Name", 
       TO_CHAR(NVL(a.bytes / 1024 / 1024 /1024, 0),'99,999,990.90') "Size (GB)", 
       TO_CHAR(NVL(t.bytes,0)/1024/1024 /1024,'99999999.99') "Used (GB)",
       TO_CHAR(NVL((a.bytes -NVL(t.bytes, 0)) / 1024 / 1024 /1024, 0),'99,999,990.90') "Free (GB)", 
       TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "(Used) %" 
 FROM sys.dba_tablespaces d, 
      (select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a, 
      (select tablespace_name, sum(bytes_cached) bytes from v$temp_extent_pool group by tablespace_name) t 
 WHERE d.tablespace_name = a.tablespace_name(+) 
   AND d.tablespace_name = t.tablespace_name(+) 
   AND d.extent_management like 'LOCAL' 
   AND d.contents like 'TEMPORARY';

Uzycie tablespace (dla danych, nie undo/temp)

SELECT
    df.tablespace_name,
    ROUND(df.total_mb, 2) AS total_mb,
    ROUND(df.total_mb - NVL(fs.free_mb, 0), 2) AS used_mb,
    ROUND(NVL(fs.free_mb, 0), 2) AS free_mb,
    ROUND((df.total_mb - NVL(fs.free_mb, 0)) / df.total_mb * 100, 2) AS pct_used
FROM
    (SELECT tablespace_name, SUM(bytes) / 1024 / 1024 AS total_mb
     FROM dba_data_files
     GROUP BY tablespace_name) df
LEFT JOIN
    (SELECT tablespace_name, SUM(bytes) / 1024 / 1024 AS free_mb
     FROM dba_free_space
     GROUP BY tablespace_name) fs
ON df.tablespace_name = fs.tablespace_name
ORDER BY pct_used DESC;

Uzycie tablespace uwzgledniajac AUTOEXTEND (czyli potencjalny wzrost)

SELECT
    a.tablespace_name,
    ROUND(a.used_mb, 2) AS used_mb,
    ROUND(a.free_mb, 2) AS free_mb,
    ROUND(a.max_mb, 2) AS max_mb,
    ROUND((a.used_mb / a.max_mb) * 100, 2) AS pct_used
FROM (
    SELECT
        df.tablespace_name,
        SUM(df.bytes) / 1024 / 1024 AS max_mb,
        SUM(df.bytes - NVL(fs.bytes, 0)) / 1024 / 1024 AS used_mb,
        SUM(NVL(fs.bytes, 0)) / 1024 / 1024 AS free_mb
    FROM dba_data_files df
    LEFT JOIN dba_free_space fs
        ON df.file_id = fs.file_id
    GROUP BY df.tablespace_name
) a
ORDER BY pct_used DESC;

TEMP tablespace (bo tam dba_free_space nie działa)

SELECT
    tablespace_name,
    ROUND(SUM(bytes_used)/1024/1024, 2) AS used_mb,
    ROUND(SUM(bytes_free)/1024/1024, 2) AS free_mb,
    ROUND(SUM(bytes_used + bytes_free)/1024/1024, 2) AS total_mb,
    ROUND(SUM(bytes_used) / SUM(bytes_used + bytes_free) * 100, 2) AS pct_used
FROM v$temp_space_header
GROUP BY tablespace_name;

Monitor tablespace usage

cat sql/ts.sql
select df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
"Pct. Free"
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name
/

$cat to.sql
prompt
prompt Tablespace: &&1
prompt Object size > &&2 MB
set pagesize 999
set verify off
col owner for a16
col segment_name for a30
col segment_type for a20
select owner, segment_name, segment_type, round(bytes/1048576) MB
from dba_segments
where tablespace_name=upper('&&1')
  and bytes > nvl(to_number('&&2'),0) * 1024 * 1204
order by bytes desc
/