-- Create table with JSON column
CREATE TABLE products (
  id NUMBER GENERATED ALWAYS AS IDENTITY,
  product_data JSON,
  CONSTRAINT products_pk PRIMARY KEY (id)
);

-- Insert JSON data
INSERT INTO products (product_data) VALUES (
  JSON_OBJECT(
    'name' VALUE 'Laptop',
    'price' VALUE 1299.99,
    'specs' VALUE JSON_OBJECT(
      'cpu' VALUE 'Intel i7',
      'ram' VALUE '16GB',
      'storage' VALUE '512GB SSD'
    ),
    'tags' VALUE JSON_ARRAY('electronics', 'computers', 'laptops')
  )
);

-- Query JSON data
SELECT
  id,
  JSON_VALUE(product_data, '$.name') as product_name,
  JSON_VALUE(product_data, '$.price' RETURNING NUMBER) as price,
  JSON_VALUE(product_data, '$.specs.cpu') as cpu
FROM products;

-- JSON search
SELECT * FROM products
WHERE JSON_EXISTS(product_data, '$.tags[*]?(@ == "laptops")');

-- Create ML model for prediction
BEGIN
  DBMS_DATA_MINING.CREATE_MODEL(
    model_name          => 'CUSTOMER_CHURN_MODEL',
    mining_function     => DBMS_DATA_MINING.CLASSIFICATION,
    data_table_name     => 'CUSTOMER_DATA',
    case_id_column_name => 'CUSTOMER_ID',
    target_column_name  => 'CHURNED',
    settings_table_name => 'MODEL_SETTINGS'
  );
END;
/

-- Use model for predictions
SELECT
  customer_id,
  PREDICTION(CUSTOMER_CHURN_MODEL USING *) as predicted_churn,
  PREDICTION_PROBABILITY(CUSTOMER_CHURN_MODEL USING *) as confidence
FROM customer_data_new;

-- Enable REST on table
BEGIN
  ORDS.ENABLE_OBJECT(
    p_enabled      => TRUE,
    p_schema       => 'ADMIN',
    p_object       => 'PRODUCTS',
    p_object_type  => 'TABLE',
    p_object_alias => 'products'
  );
END;
/

-- Create custom REST endpoint
BEGIN
  ORDS.DEFINE_SERVICE(
    p_module_name    => 'api.v1',
    p_base_path      => '/api/v1/',
    p_items_per_page => 25,
    p_status         => 'PUBLISHED'
  );

  ORDS.DEFINE_TEMPLATE(
    p_module_name    => 'api.v1',
    p_pattern        => 'products/:id'
  );

  ORDS.DEFINE_HANDLER(
    p_module_name    => 'api.v1',
    p_pattern        => 'products/:id',
    p_method         => 'GET',
    p_source_type    => ORDS.source_type_collection_feed,
    p_source         => 'SELECT * FROM products WHERE id = :id'
  );
END;
/

-- Enable automatic indexing (enabled by default)
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE', 'IMPLEMENT');

-- Monitor automatic indexes
SELECT
  index_name,
  auto_index_type,
  auto_index_status,
  last_used
FROM dba_auto_index_statistics
WHERE owner = 'ADMIN';

-- View automatic indexing recommendations
SELECT
  sql_text,
  improvement_percent,
  recommendation
FROM dba_auto_index_sql_recommendations
WHERE owner = 'ADMIN';

-- Enable parallel execution
ALTER SESSION ENABLE PARALLEL DML;

-- Parallel query hint
SELECT /*+ PARALLEL(t, 8) */
  region,
  SUM(sales_amount) as total_sales
FROM sales_fact t
GROUP BY region;

-- Check parallel execution
SELECT
  sql_id,
  px_servers_requested,
  px_servers_allocated
FROM v$sql
WHERE sql_text LIKE 'SELECT%sales_fact%';

-- Enable in-memory for table (ADW only)
ALTER TABLE sales_fact INMEMORY;

-- Set inmemory priority
ALTER TABLE sales_fact INMEMORY PRIORITY HIGH;

-- Check in-memory status
SELECT
  segment_name,
  inmemory_size,
  bytes_not_populated,
  populate_status
FROM v$im_segments
WHERE segment_name = 'SALES_FACT';

-- Enable result cache for query
SELECT /*+ RESULT_CACHE */
  product_category,
  COUNT(*) as product_count,
  AVG(price) as avg_price
FROM products
GROUP BY product_category;

-- Monitor result cache
SELECT
  cache_id,
  name,
  status,
  block_count,
  scan_count
FROM v$result_cache_objects
WHERE type = 'Result';

-- Create directory
CREATE DIRECTORY data_pump_dir AS 'DATA_PUMP_DIR';

-- Export
BEGIN
  DBMS_DATAPUMP.CREATE_JOB(
    job_name => 'EXPORT_JOB',
    operation => 'EXPORT',
    job_mode => 'SCHEMA'
  );
END;
/

-- Import from Object Storage
BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'OBJ_STORE_CRED',
    username => 'tenancy@namespace',
    password => 'auth_token'
  );
END;
/

BEGIN
  DBMS_CLOUD.PUT_OBJECT(
    credential_name => 'OBJ_STORE_CRED',
    object_uri => 'https://objectstorage.region.oraclecloud.com/n/namespace/b/bucket/o/export.dmp',
    directory_name => 'DATA_PUMP_DIR',
    file_name => 'export.dmp'
  );
END;
/

-- Create credential
BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'OBJ_STORE_CRED',
    username => 'tenancy@namespace',
    password => 'auth_token'
  );
END;
/

-- Create external table
BEGIN
  DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
    table_name => 'SALES_DATA_EXT',
    credential_name => 'OBJ_STORE_CRED',
    file_uri_list => 'https://objectstorage.region.oraclecloud.com/n/namespace/b/bucket/o/sales*.csv',
    format => JSON_OBJECT(
      'type' VALUE 'csv',
      'skipheaders' VALUE '1',
      'delimiter' VALUE ',',
      'ignoremissingcolumns' VALUE 'true'
    ),
    column_list => 'order_id NUMBER,
                    order_date DATE,
                    customer_id NUMBER,
                    amount NUMBER'
  );
END;
/

-- Load data from external table
CREATE TABLE sales_data AS
SELECT * FROM sales_data_ext;

-- Top SQL by execution time
SELECT
  sql_id,
  sql_text,
  elapsed_time/1000000 as elapsed_seconds,
  executions,
  cpu_time/1000000 as cpu_seconds
FROM v$sql
ORDER BY elapsed_time DESC
FETCH FIRST 10 ROWS ONLY;

-- Session statistics
SELECT
  username,
  status,
  machine,
  program,
  sql_id,
  event,
  wait_time
FROM v$session
WHERE username IS NOT NULL;

-- Check OCPU usage
SELECT
  metric_name,
  value,
  begin_time,
  end_time
FROM v$metric
WHERE metric_name IN ('CPU Usage Per Sec', 'CPU Usage Per Txn')
ORDER BY begin_time DESC;

-- Storage usage
SELECT
  SUM(bytes)/1024/1024/1024 as total_gb,
  tablespace_name
FROM user_segments
GROUP BY tablespace_name;

-- Update access control list
BEGIN
  DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACL(
    host => '10.0.1.0/24',
    lower_port => 443,
    upper_port => 443,
    ace => xs$ace_type(
      privilege_list => xs$name_list('http'),
      principal_name => 'ADMIN',
      principal_type => xs_acl.ptype_db
    )
  );
END;
/

-- Enable Database Vault
EXEC DBMS_MACADM.ENABLE_DV;

-- Create realm
BEGIN
  DVSYS.DBMS_MACADM.CREATE_REALM(
    realm_name => 'HR_DATA_REALM',
    description => 'Protect HR tables',
    enabled => DBMS_MACUTL.G_YES,
    audit_options => DBMS_MACUTL.G_REALM_AUDIT_FAIL
  );
END;
/