dim_date_link

SKP_DAY

Date

Month

Week

MONTH_AXIS

WEEK_AXIS

ORDER_MONTH

ORDER_WEEK

FYEAR

FMONTH

FWEEK

FDATE

CMFLAT

DATATYPE

DATA_TYPE_ORDER

DATATYPE_FIX

ORA-00936: missing expression

Date

SELECT * FROM (SELECT skp_day, date, month, ... FROM DM_FIN.dim_date) ...

date

date

ENGINE=JDBC

sql
-- 1. Create the Catalog
CREATE EXTERNAL CATALOG oracle_catalog
PROPERTIES (
    "type" = "jdbc",
    "user" = "your_user",
    "password" = "your_password",
    "jdbc_uri" = "jdbc:oracle:thin:@//host:port/service",
    "driver_url" = "<https://repo1.maven.org/maven2/com/oracle/database/jdbc/ojdbc8/19.3.0.0/ojdbc8-19.3.0.0.jar>",
    "driver_class" = "oracle.jdbc.driver.OracleDriver"
);

-- 2. Query directly
SELECT * FROM oracle_catalog.DM_FIN.dim_date;

ENGINE=JDBC

sql
    CREATE VIEW DM_FIN.v_dim_date AS
    SELECT SKP_DAY, "Date" AS CALENDAR_DATE, Month, ...
    FROM DM_FIN.dim_date;

v_dim_date

CALENDAR_DATE

#67690

"date"

ENGINE=JDBC

DATE

ORDER

CHECK

ENGINE=JDBC

sql
-- 1. Create the Catalog
CREATE EXTERNAL CATALOG oracle_catalog
PROPERTIES (
    "type" = "jdbc",
    "user" = "your_user",
    "password" = "your_password",
    "jdbc_uri" = "jdbc:oracle:thin:@//host:port/service",
    "driver_url" = "<https://repo1.maven.org/maven2/com/oracle/database/jdbc/ojdbc8/19.3.0.0/ojdbc8-19.3.0.0.jar>",
    "driver_class" = "oracle.jdbc.driver.OracleDriver"
);

-- 2. Query directly
SELECT * FROM oracle_catalog.DM_FIN.dim_date;

Unknown database 'dm_fin'

SELECT * FROM oracle_catalog.DM_FIN.dim_date

dm_fin

DM_FIN

sql
SELECT * FROM oracle_catalog."DM_FIN"."DIM_DATE";

sql
-- See the list of schemas (databases)
SHOW DATABASES FROM oracle_catalog;

-- See the list of tables in that schema
SHOW TABLES FROM oracle_catalog."DM_FIN";

sql
SET CATALOG oracle_catalog;
USE "DM_FIN";
SELECT * FROM "DIM_DATE" LIMIT 10;

SHOW DATABASES

DM_FIN

USE

catalog.database

SET CATALOG DM_FIN_DTM;

sql
SET CATALOG DM_FIN_DTM;
USE "DM_FIN";

SET CATALOG

USE

"DM_FIN"

sql
SELECT * FROM "dim_date" LIMIT 10;

dim_date

"DIM_DATE"

The correct syntax is: USE 'CATALOG catalog_name'

USE

"DM_FIN"

SET CATALOG

USE

sql
SELECT * FROM DM_FIN_DTM."DM_FIN"."DIM_DATE" LIMIT 10;

DM_FIN_DTM

CREATE EXTERNAL CATALOG

USE

USE

sql
-- Step 1: Switch to the catalog
SET CATALOG DM_FIN_DTM;

-- Step 2: Verify the exact name of the schema as seen by StarRocks
SHOW DATABASES; 

-- Step 3: Use the exact name from the list above (wrapped in quotes)
USE "DM_FIN";

-- Step 4: Query the table (usually uppercase in Oracle)
SELECT * FROM "DIM_DATE" LIMIT 10;

USE DM_FIN_DTM."DM_FIN";

USE

USE "DM_FIN";

DM_FIN

SET CATALOG

DM_FIN

SHOW CATALOGS;

DM_FIN_DTM

SHOW DATABASES FROM DM_FIN_DTM;

"DM_FIN"

DM_FIN_DTM."DM_FIN"."DIM_DATE"

SHOW

USE

SELECT

Unexpected input '.'

SHOW TABLES

FROM

catalog.database

SELECT

USE

SHOW

sql
-- Pattern: catalog_name."SCHEMA_NAME"."TABLE_NAME"
SELECT * FROM DM_FIN_DTM."DM_FIN"."DIM_DATE" LIMIT 10;

SHOW TABLES

sql
SET CATALOG DM_FIN_DTM; -- OK
-- Verify you are in the correct catalog
SELECT current_catalog(); 

-- Check the exact casing of the schema name
SHOW DATABASES; 

-- Use the schema name exactly as it appears in the list above
SHOW TABLES FROM "DM_FIN";

USE

USE "DM_FIN"

USE "DM_FIN";

SET CATALOG

USE catalog.database

sql
    USE DM_FIN_DTM."DM_FIN";

information_schema

SHOW

sql
SELECT TABLE_NAME 
FROM information_schema.tables 
WHERE TABLE_CATALOG = 'DM_FIN_DTM' 
  AND TABLE_SCHEMA = 'DM_FIN';

UPPERCASE

lowercase

"DM_FIN"

SET CATALOG

DM_FIN_DTM."DM_FIN"."DIM_DATE"

SHOW TABLES FROM catalog.db

SET CATALOG

ENGINE=JDBC

sql
    CREATE VIEW DM_FIN.v_dim_date AS
    SELECT SKP_DAY, "Date" AS CALENDAR_DATE, Month, ...
    FROM DM_FIN.dim_date;

ENGINE=JDBC

sql
-- In Oracle
CREATE OR REPLACE VIEW DM_FIN.V_DIM_DATE AS
SELECT 
    SKP_DAY, 
    "Date" AS CALENDAR_DATE, -- Renaming the reserved word "Date"
    "Month" AS CALENDAR_MONTH
FROM DM_FIN.dim_date;

sql
CREATE EXTERNAL RESOURCE "oracle_resource"
PROPERTIES (
    "type" = "jdbc",
    "user" = "your_username",
    "password" = "your_password",
    "jdbc_uri" = "jdbc:oracle:thin:@//your_host:1521/your_service_name",
    "driver_url" = "<https://repo1.maven.org/maven2/com/oracle/database/jdbc/ojdbc8/19.3.0.0/ojdbc8-19.3.0.0.jar>",
    "driver_class" = "oracle.jdbc.driver.OracleDriver"
);

driver_url

lib/jdbc_drivers

sql
CREATE EXTERNAL TABLE dim_date_jdbc (
    skp_day BIGINT,
    calendar_date DATE,    -- Maps to "Date" alias from view
    calendar_month INT     -- Maps to Month
) ENGINE=jdbc
PROPERTIES (
    "resource" = "oracle_resource",
    "table" = "DM_FIN.V_DIM_DATE" -- Use the Oracle View name (usually UPPERCASE)
);

SHOW

Unexpected input '.'

SHOW TABLES

USE

catalog.database

SET CATALOG DM_FIN_DTM;

USE "DM_FIN";

date

dbt run

1064 (HY000): Tablet lost replicas

DELETE

BeReplicaSize:0, quorum:1

sql
SHOW BACKENDS;

Alive

false

DiskUsed

1719093

sql
SHOW TABLET 1719093;

DetailCmd

State

IsBad

IsBad

true

sql
SHOW PROC '/statistic';

UnhealthyTabletNum

sql
    ADMIN REPAIR TABLE dm_fin.dpd_endmonth_card_hist;

"replication_num" = "1"

SHOW CREATE TABLE dm_fin.dpd_endmonth_card_hist;

sql
        ALTER TABLE dm_fin.dpd_endmonth_card_hist SET ("replication_num" = "3");

dbt

DELETE

INSERT

DELETE

dbt

sales_code_hist

MONTH

SALES_CODE

CLASS

NO

HR_CODE

CHANNEL

SALES_NAME

CONTRACT_TYPE

CONTRACT_DATE

SALES_STATUS

TEAM_NAME

TEAM_LEADER_NAME

TEAM_LEADER_CODE

SUPERVISOR_NAME

SUPERVISOR_CODE

UH_NAME

UH_CODE

RSM_NAME

RSM_CODE

HEAD_NAME

HEAD_CODE

REGION

SUB_REGION

PROVINCE

SUB_PROVINCE

RESIGNATION_DATE

MODIFY_DATE

MONTH

MONTH

SALES_CODE

MONTH

CREATE TABLE

ORDER BY

DUPLICATE KEY

ORDER BY

DUPLICATE KEY

ORDER BY

ORDER BY

CREATE TABLE

DUPLICATE KEY(...)

ORDER BY

DUPLICATE KEY

ORDER BY

DUPLICATE KEY

DISTRIBUTED BY HASH

MONTH

sales_code_hist

SALES_CODE

sql
CREATE TABLE `sales_code_hist` (
  `MONTH` varchar(100) NOT NULL COMMENT "",
  `SALES_CODE` varchar(100) NULL COMMENT "",
  `CLASS` varchar(100) NULL COMMENT "",
  -- ... other columns ...
  `MODIFY_DATE` date NULL COMMENT ""
) ENGINE=OLAP
-- Combined the order into the Duplicate Key definition
DUPLICATE KEY(`MONTH`, `SALES_CODE`) 
-- Added SALES_CODE to distribution to prevent data skew
DISTRIBUTED BY HASH(`MONTH`, `SALES_CODE`) BUCKETS 4 
PROPERTIES (
  "compression" = "LZ4",
  "fast_schema_evolution" = "true",
  "replicated_storage" = "true",
  -- WARNING: replication_num=1 means no high availability. 
  -- If one node fails, you will get the "Tablet lost replicas" error again.
  "replication_num" = "1" 
);

Tablet lost replicas

"replication_num" = "1"

3

WHERE

MONTH

DUPLICATE KEY

DISTRIBUTED BY HASH(...) BUCKETS AUTO

SALES_CODE have null data