Oracle Production Environment Database Change Specification

-- User creation (must bind default tablespace)
CREATE USER ops_user IDENTIFIED BY "S3cureP@ss#2023"
  DEFAULT TABLESPACE ops_data
  QUOTA UNLIMITED ON ops_data
  ACCOUNT UNLOCK;

-- Tablespace creation (must specify bigfile mode)
CREATE BIGFILE TABLESPACE ops_data
  DATAFILE '+DATA' SIZE 10G AUTOEXTEND ON NEXT 1G;

-- Table creation example (must include comments and storage parameters)
CREATE TABLE order_details (
  order_id   NUMBER(12)   PRIMARY KEY,
  product_id VARCHAR2(20) NOT NULL,
  amount     NUMBER(16,2) DEFAULT 0
) TABLESPACE ops_data
  PCTFREE 10 PCTUSED 80
  COMPRESS FOR OLTP;  -- Enable advanced compression

COMMENT ON TABLE order_details IS 'Order details table';
COMMENT ON COLUMN order_details.order_id IS 'Unique order ID';

-- Index naming convention: IDX_[Table Abbreviation]_[Column Name]
CREATE INDEX IDX_ORDETAILS_PRODID ON order_details(product_id)
  TABLESPACE ops_index
  PARALLEL 4 NOLOGGING;  -- Accelerate creation with parallelism

-- Views must include OR REPLACE and FORCE options
CREATE OR REPLACE FORCE VIEW v_active_orders AS
SELECT /*+ INDEX(o IDX_ORD_STATUS) */
       order_id, product_id
FROM orders o
WHERE status = 'ACTIVE'
WITH READ ONLY;  -- Enforce read-only

-- Sequence must specify NOCACHE to prevent gaps
CREATE SEQUENCE seq_order_id
  START WITH 1000000
  INCREMENT BY 1
  NOCACHE NOCYCLE;
CACHE+NOORDER: This is the recommended configuration for RAC environments. When the order attribute is not specified, RAC defaults to this configuration with a default cache value of 20. For frequently used sequences, adjust cache to 1000~2000.

BEGIN
  UPDATE accounts SET balance = balance - :amt WHERE id = :acc_id;
  -- Must include exception handling
  EXCEPTION WHEN OTHERS THEN
    ROLLBACK;
    RAISE;
END;
/
COMMIT;

-- Original operation: Add column
ALTER TABLE orders ADD (discount NUMBER(5,2));

-- Rollback script (must be pre-generated)
ALTER TABLE orders DROP COLUMN discount;

-- Original operation: Data correction
UPDATE employees SET salary = salary * 1.1
WHERE dept_id = 'IT';

-- Rollback plan (using Flashback Query)
UPDATE employees e
SET e.salary = (
  SELECT salary
  FROM employees AS OF TIMESTAMP SYSDATE - 1/24  -- 1 hour ago
  WHERE employee_id = e.employee_id
)
WHERE dept_id = 'IT';

-- Pre-deletion backup (standard operation)
CREATE TABLE orders_bak_20230811 AS
SELECT * FROM orders;

-- Rollback command
RENAME orders_bak_20230811 TO orders;

-- Use Flashback Table (requires row movement enabled)
ALTER TABLE orders ENABLE ROW MOVEMENT;
FLASHBACK TABLE orders TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '15' MINUTE);

-- Rapid index rollback
DROP INDEX new_index_name FORCE;
CREATE INDEX old_index_name ... NOLOGGING PARALLEL 8;  -- Rebuild with parallelism