Oracle Database: Administration

Users & Privileges

-- Create user
CREATE USER myapp IDENTIFIED BY "SecurePass123"
    DEFAULT TABLESPACE users
    TEMPORARY TABLESPACE temp
    QUOTA 500M ON users;

-- Grant privileges
GRANT CONNECT, RESOURCE TO myapp;
GRANT CREATE SESSION TO myapp;
GRANT SELECT, INSERT, UPDATE, DELETE ON hr.employees TO myapp;
GRANT ALL ON hr.departments TO myapp;
GRANT CREATE VIEW, CREATE PROCEDURE TO myapp;

-- Roles
CREATE ROLE app_read_only;
GRANT SELECT ON hr.employees TO app_read_only;
GRANT SELECT ON hr.departments TO app_read_only;
GRANT app_read_only TO myapp;

-- Revoke
REVOKE DELETE ON hr.employees FROM myapp;

-- View user privileges
SELECT * FROM dba_sys_privs WHERE grantee = 'MYAPP';
SELECT * FROM dba_tab_privs WHERE grantee = 'MYAPP';
SELECT * FROM dba_role_privs WHERE grantee = 'MYAPP';

-- Switch user (DBA)
ALTER SESSION SET CURRENT_SCHEMA = hr;

-- Lock/unlock user
ALTER USER myapp ACCOUNT LOCK;
ALTER USER myapp ACCOUNT UNLOCK;

-- Change password
ALTER USER myapp IDENTIFIED BY "NewPass456";

Tablespaces

-- Create tablespace
CREATE TABLESPACE myapp_data
    DATAFILE '/u01/oradata/mydb/myapp_data01.dbf' SIZE 1G
    AUTOEXTEND ON NEXT 100M MAXSIZE 10G
    EXTENT MANAGEMENT LOCAL
    SEGMENT SPACE MANAGEMENT AUTO;

-- Add datafile to existing tablespace
ALTER TABLESPACE myapp_data
    ADD DATAFILE '/u01/oradata/mydb/myapp_data02.dbf' SIZE 1G AUTOEXTEND ON;

-- Resize datafile
ALTER DATABASE DATAFILE '/u01/oradata/mydb/myapp_data01.dbf' RESIZE 2G;

-- Check tablespace usage
SELECT
    tablespace_name,
    ROUND(used_space * 8192 / 1024 / 1024, 2) AS used_mb,
    ROUND(tablespace_size * 8192 / 1024 / 1024, 2) AS total_mb,
    ROUND(used_percent, 2) AS pct_used
FROM dba_tablespace_usage_metrics
ORDER BY used_percent DESC;

-- Drop tablespace (with data!)
DROP TABLESPACE myapp_data INCLUDING CONTENTS AND DATAFILES;

RMAN Backup

# RMAN (Recovery Manager) — Oracle's built-in backup tool

# Connect to RMAN
rman target /
rman target sys/password@mydb

# Full database backup
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

# Incremental backup (level 0 = full, level 1 = changes since level 0)
RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE;
RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

# Configure backup retention
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
RMAN> CONFIGURE BACKUP OPTIMIZATION ON;

# List backups
RMAN> LIST BACKUP SUMMARY;
RMAN> LIST BACKUP OF DATABASE;

# Validate backup (check for corruption without restoring)
RMAN> VALIDATE DATABASE;

# Restore and recover
RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE;
RMAN> ALTER DATABASE OPEN;

# Point-in-time recovery
RMAN> RUN {
    SET UNTIL TIME "TO_DATE('2024-03-15 14:00:00', 'YYYY-MM-DD HH24:MI:SS')";
    RESTORE DATABASE;
    RECOVER DATABASE;
    ALTER DATABASE OPEN RESETLOGS;
}

Key Data Dictionary Views

• DBA_TABLES, DBA_INDEXES, DBA_COLUMNS — all objects in database (DBA access required).

• ALL_TABLES, ALL_INDEXES — objects accessible to current user.

• USER_TABLES, USER_INDEXES — objects owned by current user.

• V$SESSION — active sessions; V$SQL — cached SQL statements.

• V$LOCK, V$LOCKED_OBJECT — lock contention debugging.

• V$DATABASE — database info; V$INSTANCE — instance info.

• DBA_SEGMENTS — storage used per segment (table, index).

• DBA_JOBS / DBA_SCHEDULER_JOBS — scheduled jobs.