Dit document biedt een snelle referentie voor veelvoorkomende Oracle Database Administration (DBA)-taken, inclusief queries, voorbeelden en veelgebruikte SQL-commando's.
Een Container Database (CDB) is een architectuur in Oracle 12c en hoger die meerdere Pluggable Databases (PDB) kan bevatten. Een CDB bevat gedeelde metadata en resources (zoals redo logs en undo tablespaces), terwijl elke PDB een geïsoleerde database is met eigen schema's, gebruikers en data.
SELECT name, con_id, dbid, open_mode FROM v$database;
SHOW PARAMETER db_name;
-- Alle containers (CDB en PDB's)
SELECT name, con_id, open_mode FROM v$containers;
-- Alleen PDB's
SELECT name, con_id, open_mode FROM v$pdbs;
ALTER SESSION SET container = pdb_name;
CREATE PLUGGABLE DATABASE my_pdb
ADMIN USER pdb_admin IDENTIFIED BY password
FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/cdb/pdbseed', '/u01/app/oracle/oradata/cdb/my_pdb');
ALTER PLUGGABLE DATABASE my_pdb OPEN;
ALTER PLUGGABLE DATABASE my_pdb CLOSE IMMEDIATE;
DROP PLUGGABLE DATABASE my_pdb INCLUDING DATAFILES;
CREATE PLUGGABLE DATABASE new_pdb FROM my_pdb
FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/cdb/my_pdb', '/u01/app/oracle/oradata/cdb/new_pdb');
RMAN> BACKUP PLUGGABLE DATABASE my_pdb;
RMAN> RESTORE PLUGGABLE DATABASE my_pdb;
RMAN> RECOVER PLUGGABLE DATABASE my_pdb;
ALTER SESSION SET container = my_pdb;
CREATE USER pdb_user IDENTIFIED BY password;
GRANT CREATE SESSION, CREATE TABLE TO pdb_user;
-- Parameters in huidige container
SHOW PARAMETER;
-- Parameter wijzigen voor een specifieke PDB
ALTER SYSTEM SET open_cursors = 500 SCOPE = SPFILE CONTAINER = my_pdb;
SELECT tablespace_name, status, contents
FROM dba_tablespaces;
SELECT t.tablespace_name, t.status, t.contents, f.file_name, f.bytes/1024/1024 AS size_mb
FROM dba_tablespaces t
JOIN dba_data_files f ON t.tablespace_name = f.tablespace_name;
SELECT
t.tablespace_name,
t.total_mb,
t.total_mb - f.free_mb AS "Used Space (MB)",
f.free_mb AS "Free Space (MB)",
ROUND((t.total_mb - f.free_mb) * 100 / t.total_mb) AS "% Used"
FROM
( -- Total allocated size
SELECT tablespace_name, SUM(bytes) / 1024 / 1024 total_mb
FROM dba_data_files
GROUP BY tablespace_name
) t,
( -- Total free space within allocated size
SELECT tablespace_name, SUM(bytes) / 1024 / 1024 free_mb
FROM dba_free_space
GROUP BY tablespace_name
) f
WHERE
t.tablespace_name = f.tablespace_name(+)
ORDER BY
"% Used" DESC;
SELECT
d.tablespace_name,
SUM(d.bytes) / 1024 / 1024 AS "Current Size (MB)",
SUM(d.maxbytes) / 1024 / 1024 AS "Max Possible Size (MB)",
SUM(d.bytes - NVL(f.bytes, 0)) / 1024 / 1024 AS "Currently Used (MB)"
FROM
dba_data_files d
LEFT JOIN
(SELECT tablespace_name, SUM(bytes) bytes FROM dba_free_space GROUP BY tablespace_name) f
ON
d.tablespace_name = f.tablespace_name
GROUP BY
d.tablespace_name
ORDER BY
"Currently Used (MB)" DESC;
CREATE TABLESPACE my_tbs
DATAFILE '/u01/app/oracle/oradata/orcl/my_tbs01.dbf'
SIZE 100M
AUTOEXTEND ON
NEXT 10M
MAXSIZE 500M;
ALTER DATABASE
DATAFILE '/u01/app/oracle/oradata/orcl/my_tbs01.dbf'
RESIZE 200M;
ALTER TABLESPACE my_tbs
ADD DATAFILE '/u01/app/oracle/oradata/orcl/my_tbs02.dbf'
SIZE 100M
AUTOEXTEND ON
NEXT 10M
MAXSIZE 500M;
ALTER TABLESPACE my_tbs OFFLINE;
ALTER TABLESPACE my_tbs ONLINE;
DROP TABLESPACE my_tbs INCLUDING CONTENTS AND DATAFILES;
SELECT username, account_status, created
FROM dba_users;
SELECT * FROM dba_sys_privs
WHERE grantee = 'GEBRUIKERSNAAM';
CREATE USER my_user IDENTIFIED BY my_password;
GRANT CREATE SESSION, CREATE TABLE TO my_user;
ALTER USER my_user ACCOUNT UNLOCK;
ALTER USER my_user IDENTIFIED BY new_password;
ALTER USER my_user PROFILE my_profile;
SELECT sid, serial#, username, status, machine, program
FROM v$session;
select
c.owner,
c.object_name,
c.object_type,
b.sid,
b.serial#,
b.status,
b.osuser,
b.machine
from
v$locked_object a,
v$session b,
dba_objects c
Op tabel
select
c.owner,
c.object_name,
c.object_type,
b.sid,
b.serial#,
b.status,
b.osuser,
b.machine
from
v$locked_object a,
v$session b,
dba_objects c
where
b.sid = a.session_id and
a.object_id = c.object_id and
object_name = '<TABLE_NAME>';
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
SELECT name, open_mode, log_mode
FROM v$database;
CREATE OR REPLACE DIRECTORY my_dir AS '/u01/app/oracle/export';
GRANT READ, WRITE ON DIRECTORY my_dir TO my_user;
expdp gebruikersnaam/wachtwoord@database SCHEMAS=schema_naam DIRECTORY=my_dir DUMPFILE=export.dmp LOGFILE=export.log
Maak een bestand genaamd export.par met de volgende inhoud:
USERID=gebruikersnaam/wachtwoord
SCHEMAS=schema_naam
DIRECTORY=my_dir
DUMPFILE=export.dmp
LOGFILE=export.log
EXCLUDE=TABLE:"IN ('TEMP_TABELLEN')"
Voer vervolgens uit:
expdp PARFILE=export.par
impdp gebruikersnaam/wachtwoord@database SCHEMAS=schema_naam DIRECTORY=my_dir DUMPFILE=import.dmp LOGFILE=import.log
Maak een bestand genaamd import.par met de volgende inhoud:
USERID=gebruikersnaam/wachtwoord
SCHEMAS=schema_naam
DIRECTORY=my_dir
DUMPFILE=import.dmp
LOGFILE=import.log
EXCLUDE=TABLE:"IN ('OUDE_TABELLEN')"
Voer vervolgens uit:
impdp PARFILE=import.par
rman target /
BACKUP DATABASE PLUS ARCHIVELOG;
rman target /
BACKUP DATABASE PLUS ARCHIVELOG;
BACKUP INCREMENTAL LEVEL 1 DATABASE;
BACKUP CURRENT CONTROLFILE;
BACKUP SPFILE;
LIST BACKUP SUMMARY;
LIST BACKUP OF DATABASE;
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN;
RESTORE TABLESPACE users;
RECOVER TABLESPACE users;
ALTER DATABASE OPEN;
RUN {
SET UNTIL TIME "TO_DATE('2025-10-10 10:00:00','YYYY-MM-DD HH24:MI:SS')";
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS;
}
Voorbeeld van een dagelijks schema met Oracle Scheduler:
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'DAILY_RMAN_BACKUP',
job_type => 'EXECUTABLE',
job_action => '/u01/app/oracle/scripts/rman_backup.sh',
repeat_interval => 'FREQ=DAILY;BYHOUR=2',
enabled => TRUE
);
END;
/
SELECT sql_id, sql_text, elapsed_time/1000000 AS elapsed_seconds
FROM v$sql
WHERE elapsed_time/1000000 > 60
ORDER BY elapsed_time DESC;
SELECT sql_id, cpu_time/1000000 AS cpu_seconds, sql_text
FROM v$sql
ORDER BY cpu_time DESC
FETCH FIRST 10 ROWS ONLY;
SELECT column1, column2 FROM table_name WHERE condition;
INSERT INTO table_name (column1, column2) VALUES (value1, value2);
UPDATE table_name SET column1 = value1 WHERE condition;
DELETE FROM table_name WHERE condition;
CREATE TABLE table_name (
column1 VARCHAR2(50),
column2 NUMBER(10,2),
column3 DATE,
column4 CHAR(10),
column5 CLOB,
column6 BLOB,
column7 TIMESTAMP,
column8 INTERVAL DAY TO SECOND
);
ALTER TABLE table_name ADD column_name datatype;
CREATE INDEX idx_name ON table_name(column_name);
DROP INDEX idx_name;
ALTER TABLE table_name ADD CONSTRAINT pk_name PRIMARY KEY (column_name);
ALTER TABLE table_name ADD CONSTRAINT fk_name FOREIGN KEY (column_name) REFERENCES other_table(column_name);
CREATE ROLE rol_naam;
GRANT privilege_naam TO rol_naam;
GRANT rol_naam TO gebruikers_naam;
ALTER USER gebruikers_naam QUOTA 100M ON tablespace_naam;
SELECT name, tablespace_name FROM v$rollname;
ALTER SYSTEM SET undo_tablespace = undo_tbs_naam;
SELECT group#, bytes/1024/1024 AS size_mb, members, status FROM v$log;
ALTER SYSTEM SWITCH LOGFILE;
CREATE DATABASE LINK link_naam CONNECT TO gebruikers_naam IDENTIFIED BY wachtwoord USING 'service_naam';
ALTER DATABASE FLASHBACK ON;
FLASHBACK TABLE table_name TO TIMESTAMP TO_TIMESTAMP('YYYY-MM-DD HH24:MI:SS', 'YYYY-MM-DD HH24:MI:SS');
CREATE TABLE table_name (column1 datatype, column2 datatype)
PARTITION BY RANGE (column1) (
PARTITION part1 VALUES LESS THAN (value1),
PARTITION part2 VALUES LESS THAN (value2)
);
AUDIT SELECT, INSERT, UPDATE, DELETE ON table_name;
SELECT * FROM dba_audit_trail;
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'taak_naam',
job_type => 'SQL_SCRIPT',
job_action => 'script.sql',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=DAILY; BYHOUR=2',
enabled => TRUE
);
END;
/
Een join wordt gebruikt om rijen uit meerdere tabellen te combineren op basis van een gerelateerde kolom. Er zijn verschillende soorten joins in Oracle SQL.
Geeft alleen rijen terug die in beide tabellen overeenkomen.
SELECT e.employee_id, e.last_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;
Geeft alle rijen van de linkertabel terug, en de overeenkomende rijen van de rechtertabel (of NULL als er geen match is).
SELECT e.employee_id, e.last_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;
Geeft alle rijen van de rechtertabel terug, en de overeenkomende rijen van de linkertabel (of NULL als er geen match is).
SELECT e.employee_id, e.last_name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id;
Geeft alle rijen uit beide tabellen, met NULL waar geen overeenkomst bestaat.
SELECT e.employee_id, e.last_name, d.department_name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.department_id;
Combineert elke rij van de ene tabel met elke rij van de andere tabel (cartesisch product).
SELECT e.employee_id, d.department_name
FROM employees e
CROSS JOIN departments d;
Een tabel joinen met zichzelf.
SELECT e.employee_id, e.last_name, m.last_name AS manager_name
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id;
Het alert log bevat belangrijke meldingen van de database (crashes, startups, errors).
Standaardlocatie (vanaf Oracle 11g):
$ORACLE_BASE/diag/rdbms/<db_naam>/<instance_naam>/trace/alert_<instance_naam>.log
Tracebestanden worden automatisch aangemaakt bij fouten of kunnen manueel worden geactiveerd:
ALTER SESSION SET sql_trace = TRUE;
-- voer hier je SQL-statements uit
ALTER SESSION SET sql_trace = FALSE;
SHOW PARAMETER diagnostic_dest;
SHOW PARAMETER user_dump_dest;
TKPROF formatteert tracebestanden tot leesbare performance-analyse:
tkprof input.trc output.txt sys=no sort=exeela,fchela
BEGIN
DBMS_MONITOR.SESSION_TRACE_ENABLE(session_id => 123, serial_num => 456, waits => TRUE, binds => TRUE);
END;
/
Trace weer uitschakelen:
BEGIN
DBMS_MONITOR.SESSION_TRACE_DISABLE(session_id => 123, serial_num => 456);
END;
/
-- Lopende transacties
SELECT * FROM v$transaction;
-- Lock informatie
SELECT * FROM v$locked_object;
-- Objectgrootte
SELECT segment_name, bytes/1024/1024 AS size_mb
FROM dba_segments
WHERE owner = 'SCHEMA_NAAM';
-- Lijst van tabellen in een schema
SELECT table_name FROM all_tables WHERE owner = 'SCHEMA_NAAM';
-- Controleer welke gebruikers DBA zijn
SELECT grantee FROM dba_role_privs WHERE granted_role = 'DBA';
-- Controleer privileges van een gebruiker
SELECT privilege FROM dba_sys_privs WHERE grantee = 'GEBRUIKERSNAAM';
-- ASM diskgroups bekijken
SELECT name, total_mb, free_mb FROM v$asm_diskgroup;
-- ASM disks
SELECT disk_number, name, path, total_mb, free_mb FROM v$asm_disk;
-- Crosscheck om ontbrekende backups te valideren
CROSSCHECK BACKUP;
-- Oude backups verwijderen
DELETE NOPROMPT OBSOLETE;
-- Huidige sessie prestaties
SELECT * FROM v$active_session_history WHERE session_id = 123;
-- Recent CPU-gebruik per sessie
SELECT sid, value/100 AS cpu_seconds
FROM v$sesstat JOIN v$statname USING(statistic#)
WHERE name LIKE '%CPU used by this session%';
SELECT * FROM v$version;
SELECT * FROM dba_registry;
-- Dynamische parameters bekijken
SHOW PARAMETER memory;
-- Parameter wijzigen
ALTER SYSTEM SET processes = 300 SCOPE=SPFILE;
Verhoog undo retentie of grootte.
Identificeer blokkerende sessies en los locks op.
set linesize 160;
set wrap off;