// List the databases
show dbs

-- List the schemas (users)
SELECT username FROM all_users;

// Create / switch to a database
use myFirstDB

-- Create a schema (user)
CREATE USER myfirstdb IDENTIFIED BY "Password#123"
  QUOTA UNLIMITED ON users;
GRANT CREATE SESSION, CREATE TABLE TO myfirstdb;

// Show the current database
db

-- Show the current schema
SELECT SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
FROM dual;

// Create a user
use myFirstDB
db.createUser({
  user: "appuser",
  pwd: passwordPrompt(),
  roles: [ { role: "readWrite", db: "myFirstDB" } ]
})

-- Create a user
CREATE USER appuser IDENTIFIED BY "Password#123";
GRANT CREATE SESSION TO appuser;
GRANT SELECT, INSERT, UPDATE, DELETE
  ON myfirstdb.students TO appuser;

// Create a custom role
db.createRole({
  role: "studentReader",
  privileges: [
    { resource: { db: "myFirstDB", collection: "students" },
      actions: [ "find" ] }
  ],
  roles: []
})

-- Create a custom role
CREATE ROLE student_reader;
GRANT SELECT ON myfirstdb.students TO student_reader;

// Give the role to a user
db.grantRolesToUser("appuser", [ "studentReader" ])

-- Give the role to a user
GRANT student_reader TO appuser;

// List users and roles
show users
show roles

-- List users and roles
SELECT username FROM dba_users;
SELECT role FROM dba_roles;

// Drop a user and a role
db.dropUser("appuser")
db.dropRole("studentReader")

-- Drop a user and a role
DROP USER appuser CASCADE;
DROP ROLE student_reader;

// Drop the current database
db.dropDatabase()

-- Drop the schema and all its objects
DROP USER myfirstdb CASCADE;

// Create a collection
db.createCollection("students")

-- Create a table
CREATE TABLE students (
  name   VARCHAR2(50),
  age    NUMBER,
  course VARCHAR2(20)
);

// Add a new field
// (no command needed, just update a document)
db.students.updateMany(
  {}, { $set: { status: "active" } }
)

-- Add a new column
ALTER TABLE students
  ADD (status VARCHAR2(20));

// Rename a field
db.students.updateMany(
  {}, { $rename: { course: "subject" } }
)

-- Rename a column
ALTER TABLE students
  RENAME COLUMN course TO subject;

// Remove a field from all documents
db.students.updateMany(
  {}, { $unset: { status: "" } }
)

-- Drop a column
ALTER TABLE students DROP COLUMN status;

// Rename a collection
db.students.renameCollection("learners")

-- Rename a table
ALTER TABLE students RENAME TO learners;

// Show collection statistics
db.students.stats()

-- Show table size
SELECT segment_name, bytes/1024/1024 AS size_mb
FROM user_segments
WHERE segment_name = 'STUDENTS';

// Drop a collection
db.students.drop()

-- Drop a table
DROP TABLE students PURGE;

// Create a collection with validation
// (name and age required, age at least 1)
db.createCollection("students", {
  validator: {
    $jsonSchema: {
      bsonType: "object",
      required: [ "name", "age" ],
      properties: {
        name: { bsonType: "string" },
        age:  { bsonType: "number", minimum: 1 }
      }
    }
  }
})

-- Create a table with constraints
CREATE TABLE students (
  name   VARCHAR2(50) NOT NULL,
  age    NUMBER NOT NULL
         CONSTRAINT chk_age CHECK (age >= 1),
  course VARCHAR2(20)
);

// Add validation to an existing collection
db.runCommand({
  collMod: "students",
  validator: {
    $jsonSchema: {
      bsonType: "object",
      required: [ "name" ]
    }
  }
})

-- Add a constraint to an existing table
ALTER TABLE students
  MODIFY (name NOT NULL);
ALTER TABLE students
  ADD CONSTRAINT chk_age CHECK (age >= 1);

// Unique constraint (a unique index)
db.students.createIndex(
  { name: 1 }, { unique: true }
)

-- Unique constraint
ALTER TABLE students
  ADD CONSTRAINT uq_students_name UNIQUE (name);

// Primary key
// _id is the primary key and is created automatically
db.students.find({ _id: ObjectId("6ae9e9564b0e99873596e6f2") })

// Foreign key: not supported.
// Store the related _id and check it in the application.

-- Primary key and foreign key
ALTER TABLE students
  ADD CONSTRAINT pk_students PRIMARY KEY (student_id);
ALTER TABLE students
  ADD CONSTRAINT fk_students_course
  FOREIGN KEY (course_id) REFERENCES courses (course_id);

// Single field index
db.students.createIndex({ name: 1 })

-- Single column index
CREATE INDEX idx_students_name
  ON students (name);

// Compound index
db.students.createIndex(
  { course: 1, age: -1 }
)

-- Composite index
CREATE INDEX idx_students_course_age
  ON students (course, age DESC);

// Unique index
db.students.createIndex(
  { name: 1 }, { unique: true }
)

-- Unique index
CREATE UNIQUE INDEX uq_students_name
  ON students (name);

// List the indexes
db.students.getIndexes()

-- List the indexes
SELECT index_name, uniqueness
FROM user_indexes
WHERE table_name = 'STUDENTS';

// Check whether a query uses an index
db.students.find({ name: "Ravi" }).explain("executionStats")

-- Check whether a query uses an index
EXPLAIN PLAN FOR
SELECT * FROM students WHERE name = 'Ravi';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

// Drop an index
db.students.dropIndex("name_1")

-- Drop an index
DROP INDEX idx_students_name;

// Create the counter
db.counters.insertOne(
  { _id: "student_id", seq: 0 }
)

-- Create a sequence
CREATE SEQUENCE student_seq
  START WITH 1 INCREMENT BY 1;

// Get the next value
db.counters.findOneAndUpdate(
  { _id: "student_id" },
  { $inc: { seq: 1 } },
  { returnDocument: "after" }
)

-- Get the next value
SELECT student_seq.NEXTVAL FROM dual;

// Drop the counter
db.counters.drop()

-- Drop the sequence
DROP SEQUENCE student_seq;

// Create a view
db.createView(
  "cs_students", "students",
  [ { $match: { course: "CS" } } ]
)

-- Create a view
CREATE OR REPLACE VIEW cs_students AS
SELECT * FROM students
WHERE course = 'CS';

// Query the view
db.cs_students.find()

-- Query the view
SELECT * FROM cs_students;

// List the views
db.getCollectionInfos({ type: "view" })

-- List the views
SELECT view_name FROM user_views;

// Drop the view
db.cs_students.drop()

-- Drop the view
DROP VIEW cs_students;

// Create (and refresh) the summary collection
db.students.aggregate([
  { $group: { _id: "$course", total: { $sum: 1 } } },
  { $merge: {
      into: "course_summary",
      whenMatched: "replace",
      whenNotMatched: "insert"
  } }
])

-- Create a materialized view
CREATE MATERIALIZED VIEW course_summary
  BUILD IMMEDIATE
  REFRESH COMPLETE ON DEMAND
AS
SELECT course, COUNT(*) AS total
FROM students
GROUP BY course;

// Query the result
db.course_summary.find()

-- Query the result
SELECT * FROM course_summary;

// Refresh: run the same aggregate again

-- Refresh
EXEC DBMS_MVIEW.REFRESH('COURSE_SUMMARY', 'C');

// Drop
db.course_summary.drop()

-- Drop
DROP MATERIALIZED VIEW course_summary;

// Listen for new documents (change stream)
const cursor = db.students.watch(
  [ { $match: { operationType: "insert" } } ]
)
while (!cursor.isClosed()) {
  const change = cursor.tryNext()
  if (change) printjson(change.fullDocument)
}

-- Create a trigger
CREATE OR REPLACE TRIGGER trg_students_audit
AFTER INSERT ON students
FOR EACH ROW
BEGIN
  INSERT INTO students_audit (name, action_date)
  VALUES (:NEW.name, SYSDATE);
END;
/

No list command. Atlas triggers are managed in the Atlas console or Atlas CLI.

-- List the triggers
SELECT trigger_name, status
FROM user_triggers;

// Stop listening
cursor.close()

-- Disable or drop the trigger
ALTER TRIGGER trg_students_audit DISABLE;
DROP TRIGGER trg_students_audit;

Procedure: no equivalent. The application runs the insert itself:

db.students.insertOne(
  { name: "Ravi", age: 20, course: "CS" }
)

-- Create a procedure
CREATE OR REPLACE PROCEDURE add_student (
  p_name   VARCHAR2,
  p_age    NUMBER,
  p_course VARCHAR2
) AS
BEGIN
  INSERT INTO students (name, age, course)
  VALUES (p_name, p_age, p_course);
  COMMIT;
END;
/

-- Run it
EXEC add_student('Ravi', 20, 'CS');

Function: no equivalent. Use a command or an aggregation from the application:

db.students.countDocuments()

-- Create a function
CREATE OR REPLACE FUNCTION get_student_count
RETURN NUMBER AS
  v_count NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_count FROM students;
  RETURN v_count;
END;
/

-- Run it
SELECT get_student_count FROM dual;

Package: no equivalent. Group the logic in a module or class in your application.

-- Package specification
CREATE OR REPLACE PACKAGE student_pkg AS
  PROCEDURE add_student (
    p_name VARCHAR2, p_age NUMBER, p_course VARCHAR2);
  FUNCTION get_student_count RETURN NUMBER;
END student_pkg;
/

-- Package body
CREATE OR REPLACE PACKAGE BODY student_pkg AS
  PROCEDURE add_student (
    p_name VARCHAR2, p_age NUMBER, p_course VARCHAR2) AS
  BEGIN
    INSERT INTO students (name, age, course)
    VALUES (p_name, p_age, p_course);
    COMMIT;
  END;

  FUNCTION get_student_count RETURN NUMBER AS
    v_count NUMBER;
  BEGIN
    SELECT COUNT(*) INTO v_count FROM students;
    RETURN v_count;
  END;
END student_pkg;
/

Nothing to list or drop in the database.

-- List and drop
SELECT object_name, object_type
FROM user_objects
WHERE object_type IN ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY');

DROP PROCEDURE add_student;
DROP FUNCTION get_student_count;
DROP PACKAGE student_pkg;

// List collections and views
show collections

// With the type (collection, view, timeseries)
db.getCollectionInfos()

-- List all objects with their type
SELECT object_name, object_type, status
FROM user_objects
ORDER BY object_type, object_name;

// Count the collections
db.getCollectionNames().length

-- Count the objects by type
SELECT object_type, COUNT(*)
FROM user_objects
GROUP BY object_type;

// Capped collection (fixed size, the oldest
// documents are removed automatically)
db.createCollection("logs", {
  capped: true, size: 1048576, max: 1000
})

-- No direct equivalent. Delete old rows with a job
DELETE FROM logs
WHERE log_date < SYSDATE - 7;
COMMIT;

// TTL index (documents are deleted automatically
// 3600 seconds after createdAt)
db.sessions.createIndex(
  { createdAt: 1 },
  { expireAfterSeconds: 3600 }
)

-- No direct equivalent. Schedule a cleanup job
BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name        => 'PURGE_SESSIONS',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN
      DELETE FROM sessions
      WHERE created_at < SYSDATE - 1/24;
      COMMIT; END;',
    repeat_interval => 'FREQ=MINUTELY;INTERVAL=10',
    enabled         => TRUE);
END;
/

// Time series collection
db.createCollection("sensor_data", {
  timeseries: {
    timeField: "ts",
    metaField: "sensor",
    granularity: "minutes"
  }
})

-- No direct equivalent. Use a table partitioned by time
CREATE TABLE sensor_data (
  ts     TIMESTAMP,
  sensor VARCHAR2(30),
  value  NUMBER
)
PARTITION BY RANGE (ts)
INTERVAL (NUMTODSINTERVAL(1, 'DAY'))
(PARTITION p0 VALUES LESS THAN
  (TIMESTAMP '2026-01-01 00:00:00'));