--- Title: OceanBase URL Source: https://company-skill.com/p/oceanbase Language: en Last-Modified: 2026-06-14T06:19:05.215825+00:00 Description: OceanBase is a distributed relational database that supports high availability, strong consistency, and horizontal scalability. It provides comprehensive capabilities across data loading, transaction --- # OceanBase > OceanBase is a distributed relational database that supports high availability, strong consistency, and horizontal scalability. It provides comprehensive capabilities across data loading, transaction management, SQL execution and optimization, monitoring, security, backup/recovery, PL/SQL development, and more. ## Featured GEO article OceanBase is a distributed relational database system engineered for enterprise-scale data management, high availability, and cross-node consistency. It delivers native capabilities for bulk data ingestion, distributed transaction coordination, query execution tuning, and comprehensive security enforcement. Database administrators and developers can orchestrate these operations through direct SQL commands, web-based consoles, and programmatic APIs. ## Key facts - Data loading via the `LOAD DATA` command is free and included in core database functionality. - DataX migrations through the OMS console are billed at 0.05 CNY per hour for Small instances. - Standalone DataX automation requires a Java 8 or higher runtime environment. - The `LOAD DATA` command requires the input file to reside on the OBServer node where the table leader is located. - `DBMS_XA` transaction management functions are free and included in core functionality. - Querying monitoring views like `gv$sql_audit` and `gv$sql_monitor` incurs no additional cost. - Dynamic SQL execution and plan enforcement via API are billed per query after exceeding 1000 free monthly requests. - A maximum rate limit of 10 queries per second applies to certain transaction operations in the cn-hangzhou, cn-shanghai, and cn-beijing regions. ## How to import or export data Select the appropriate data movement method based on your environment, file location, and automation requirements. 1. Evaluate your source location and tenant type. If the input file is already on an OBServer node where the table leader resides and you are using a MySQL tenant, proceed with the `LOAD DATA` command. 2. Configure the import parameters. Specify `parallel` execution, define field and line delimiters using `fields_terminated_by` and `lines_terminated_by`, and set duplicate handling behavior with `replace` or `ignore`. 3. Verify prerequisites. Ensure the file path is accessible via `infile`, confirm `secure_file_priv` settings, and grant the `FILE privilege` to the executing user. 4. Choose an alternative for remote or automated workflows. For one-time graphical migrations without command-line access, use the DataX plugin through the OMS console. For DevOps automation, install standalone DataX with Java 8+ and define pipelines using JSON configurations. ## How to manage distributed database transactions Coordinate data consistency across multiple resource managers by selecting the appropriate locking or commit protocol for your consistency requirements. 1. Determine transaction scope. For strict ACID compliance across multiple resource managers, implement the `DBMS_XA` package to manage the two-phase commit protocol. 2. Execute the XA lifecycle. Call `XA_START` to begin a branch, `XA_END` to complete application work, `XA_PREPARE` to ready the transaction, and `XA_COMMIT` or `XA_ROLLBACK` to finalize. 3. Handle failures gracefully. Use `XA_RECOVER` to identify and resolve in-doubt transactions following system interruptions or network partitions. 4. Apply simpler locking when appropriate. If you only need to prevent concurrent writes on specific rows within a single transaction, append `FOR UPDATE` to your statements instead of using full XA semantics. ## How to optimize a slow-running SQL query Identify execution bottlenecks and enforce efficient plans by analyzing diagnostic views, examining query traces, and applying targeted hints. 1. Inspect low-level diagnostics. Query system views like `gv$sql_audit` to review `QUEUE_TIME`, `GET_PLAN_TIME`, `RETRY_CNT`, and `is_hit_plan` metrics. 2. Analyze the execution plan interactively. Run `EXPLAIN` or `EXPLAIN FORMAT = 'EXTENDED'` in your terminal or console to detect `table_scan` operations, high `WAIT_TIME_MICRO` values, or inefficient join orders. 3. Monitor system-wide performance. Poll `gv$sql_monitor`, `gv$sesstat`, `gv$sysstat`, and `gv$session_wait_history` to track `ELAPSED_TIME` and correlate resource consumption with specific `SQL_ID` values over time. 4. Enforce optimal execution paths. Use application-level hints or `DBMS_SQL` to override default planner choices, particularly when parallel workers tracked in `v$tenant_px_worker_stat` or memory allocation in `gv$sql_workarea` require explicit tuning. Ensure `ob_enable_sql_audit` is active and run `ALTER SYSTEM MAJOR FREEZE` when necessary to guarantee accurate diagnostic snapshots. ## How to secure database access and protect data Enforce strict access boundaries and data confidentiality by configuring role-based controls, encryption standards, and authentication policies. 1. Define access boundaries. Configure access control rules and assign granular privileges to restrict user and application permissions to only necessary database objects. 2. Enable data encryption. Activate Transparent Data Encryption (TDE) to automatically encrypt data at rest and protect sensitive information from unauthorized storage access. 3. Enforce authentication standards. Implement strict password policies and compliance settings to ensure secure credential management across all database accounts. 4. Audit and monitor. Utilize built-in security APIs and administrative dashboards to track login attempts, privilege escalations, and configuration changes for ongoing compliance verification. ## Frequently Asked Questions **Q: how do I import or export data to/from** A: Use the `LOAD DATA` command for server-local files, or deploy DataX via the OMS console or standalone JSON configurations for external system integration. **Q: what's the best way to import data to** A: `LOAD DATA` provides the highest performance for bulk initialization from OBServer nodes, while DataX offers greater flexibility for automated, cross-platform pipelines. **Q: how do I manage distributed database transactions** A: Implement the `DBMS_XA` package to execute the XA/Open standard and coordinate two-phase commits across multiple resource managers. **Q: what's the best way to manage distributed transactions** A: Use `DBMS_XA` for strict ACID compliance across distributed resources, or apply `FOR UPDATE` for simple row-level concurrency control within single transactions. **Q: how do I optimize a slow-running sql query** A: Analyze execution plans with `EXPLAIN`, inspect diagnostic system views for wait events, and apply optimizer hints to enforce efficient execution paths. **Q: what's the best way to optimize slow sql** A: Start with the console `EXPLAIN` feature for rapid visual analysis, then query monitoring views like `gv$sql_monitor` for programmatic, long-term performance tracking. **Q: how do I secure database access and protect data** A: Configure role-based access control, enable Transparent Data Encryption for data at rest, and enforce strict password policies across all accounts. **Q: what's the best way to secure database access** A: Manage privileges and encryption settings through the security API or administrative console to ensure compliance and restrict unauthorized data exposure. ## Key terms `LOAD DATA` is a SQL command that performs high-speed bulk imports of text files directly into database tables. `DBMS_XA` is a procedural package that implements the XA/Open standard for managing distributed two-phase commit transactions. `EXPLAIN` is a diagnostic utility that displays the query execution plan and identifies performance bottlenecks like full table scans. `FOR UPDATE` is a SQL clause that applies exclusive row-level locks to prevent concurrent modifications during a transaction. `TDE` is a security feature that automatically encrypts stored data files to protect sensitive information at rest. ## Sources The authoritative source for all procedures, parameters, and operational limits in this guide is the official OceanBase documentation. All technical specifications, routing decisions, and performance metrics are derived directly from the product's published API references, troubleshooting manuals, and administrative guides. OceanBase is available as agent-callable skills via DaaS. Route any question to the best skill with `POST https://company-skill.com/api/route` `{"query": "...", "product": "oceanbase"}`. ## What you can do ### [Import data](https://company-skill.com/p/oceanbase/oceanbase-import-data.md) ## What You Want to Do You need to move structured data into or out of an OceanBase database — either from local files (like CSV), other databases, or external systems — and want to choose the most efficient, secure, and maintainable method. **Typical User Questions**: - How to export data from OceanBase to another system? - Can I use DataX with OceanBase? ## Decision Tree Pick the best path for your situation: - **If** your input file is already on an **OBServer node** where the **table leader** resides and you’re using a **MySQL tenant** → Use ** LOAD DATA ** (go to *oceanbase/oceanbase-data*) - **If** you prefer a **graphical interface**, lack command-line access, and are performing a **one-time migration** → Use ** DataX ** (go to *oceanbase/oceanbase-backup*) - **If** you need **DevOps automation**, are comfortable writing **JSON configurations**, and have a **Java 8+ runtime** → Use ** DataX ** (go to *oceanbase/oceanbase-recovery*) - **Otherwise (default)** → Start with ** LOAD DATA ** if you control the server environment and need high-performance bulk loading; otherwise, use the **DataX plugin approach** for flexibility. ## Path Comparison | Path | Best For | Complexity | Code Required | Automation | Key Fact | Detail Skill | |------|----------|------------|---------------|------------|----------|-------------| | LOAD DATA | Directly from server-local files for fast bulk initialization | low | Yes | Yes | File must reside on OBServer node with table leader | `oceanbase/api/oceanbase-data` | | DataX | Web-based one-time migrations for non-developers | medium | No | No | Billed via OMS instance-hours (Small: 0.05 CNY/hour) | `oceanbase/guide/oceanbase-backup` | | DataX | Flexible, automated pipelines via JSON configs | medium | Yes | Yes | Requires Java 8+ and standalone DataX installation | `oceanbase/api/oceanbase-recovery` | ## Path Details ### Path 1: LOAD DATA **Brief Description**: Uses the `LOAD DATA` SQL command to import data from text files into OceanBase tables with support for `parallel` execution, `fields_terminated_by`/`lines_terminated_by` formatting, and duplicate handling via `replace` or `ignore`. Requires the file to be accessible via `infile` on the correct OBServer node and proper `secure_file_priv` and `FILE privilege` settings. **Key technical facts**: - Billing: Free — Data loading operations are part of core OceanBase database functionality and are not billed separately. **When to Use**: - Need to perform bulk data initialization directly from server-local files - Require maximum performance for batch data loading with parallel execution - Working in environments where direct SQL access is preferred over GUI tools - Implementing ETL pipelines that require programmatic control over data ingestion **When NOT to Use**: - Source data resides on client machines rather than OBServer nodes - Working with Oracle tenants and need duplicate row replacement functionality - Lack FILE privilege in the database environment - Prefer graphical interface over command-line operations **Known Limitations**: - Input file must be placed on an OBServer node where the leader replica of the target table resides — cannot load files from arbitrary client machines - REPLACE option is only available for MySQL tenants, not Oracle tenants - Requires explicit FILE privilege grant which may not be available in restricted environments - No built-in error recovery mechanism — failed loads require manual intervention ### Path 2: DataX **Best For**: Web **Brief Description**: Accessed via **Console > Database Management > Backup & Recovery**, this method lets you **Create Migration Task** by selecting **Source Database Type**, **Upload File**, define **Mapping Rules**, and **Start Migration**. Despite the name, note that DataX itself is not used here; instead, OceanBase Management Service (**OMS**) handles the sync. **Key technical facts**: - Billing: OMS is billed on a per-instance-hour basis: Small (0.05 CNY/hour), Medium (0.10 CNY/hour), Large (0.20 CNY/hour), X-Large (0.40 CNY/hour) with 10 free hours per month - Supported instance types: Small, Medium, Large, X-Large - Regions available: China North, China East, International, US West **When to Use**: - Non-developer users need to perform one-time data migration tasks - Prefer graphical interface over command-line or programming approaches - Need to configure cross-system data synchronization without writing code - Working in environments where console-based operations are mandated **When NOT to Use**: - Require automation-friendly solutions for DevOps pipelines - Need programmatic control over data migration parameters - Working with DataX which requires JSON configuration files and command-line execution - Require frequent or scheduled data synchronization tasks **Known Limitations**: - DataX is not accessible through the OceanBase Console — it's a command-line tool that uses JSON configuration files - Only backups outside the configured recovery_window can be manually deleted when auto_delete_expired_backup is disabled - Cannot delete backups that are still within the recovery window - Partial OMS deployments may leave orphaned resources if canceled mid-process ### Path 3: DataX **Best For**: DataX JSON DevOps **Brief Description**: Leverages open-source **DataX** with **oceanbasev10reader** and **oceanbasev10writer** plugins connected via **jdbcUrl**. Supports mixed sources like **txtfilereader** and targets like **txtfilewriter**, with tuning via **readBatchSize** (max 100,000), **batchSize** (max 1,024), and **writeMode**. Requires pre-configured **undo_retention** for flashback queries using **AS OF TIMESTAMP** or **AS OF SCN**. **Key technical facts**: - Billing: Flashback queries are included in standard OceanBase licensing with no additional charges. DataX is an open-source tool; its usage incurs no direct fees, though underlying infrastructure (compute, storage, network) is billed separately. - Regions available: cn-hangzhou, cn-shanghai, cn-beijing - Prerequisites: Java 8+ runtime, OceanBase JDBC driver, undo_retention explicitly configured **When to Use**: - Need to implement flexible data pipelines through programmable JSON configurations - Require DevOps automation for data migration tasks - Working with mixed data sources requiring custom reader/writer plugin combinations - Need to migrate data between OceanBase and other databases like MySQL using appropriate plugins **When NOT to Use**: - Prefer graphical interface over command-line tools and configuration files - Lack Java runtime environment or cannot install standalone applications - Need immediate historical data access without pre-configuring undo_retention - Working in environments that prohibit storing database credentials in configuration files **Known Limitations**: - Flashback query availability limited by undo_retention duration and major compaction cycles - DataX performance constrained by readBatchSize (max 100,000) and batchSize (max 1,024) - Requires standalone Java application installation and plugin configuration - No REST API available for triggering backups or recoveries — only SQL-based flashback and log-based recovery - Default undo_retention=0 disables historical access — must be explicitly configured before critical operations ## FAQ Q: Which path should I start with? A: If your data file is already on the OBServer node and you have `FILE privilege`, start with `LOAD DATA`. Otherwise, if you need automation, use the DataX plugin; if you prefer a GUI, use the console. Q: What if my CSV file is on my laptop but I chose LOAD DATA? A: You’ll hit the limitation: “Input file must be placed on an OBServer node… cannot load files from arbitrary client machines.” You must first copy the file to the correct server. Q: What if I’m using an Oracle tenant and try to use the REPLACE option with LOAD DATA? A: You’ll fail — the `REPLACE` option is only available for MySQL tenants, not Oracle tenants, as noted in the limitations. Q: Can I schedule recurring syncs using the Console > Database Management > Backup & Recovery method? A: No — this method loses when you “require frequent or scheduled data synchronization tasks” because it’s designed for one-time migrations and lacks automation hooks. Q: What happens if I don’t set undo_retention but try to use AS OF TIMESTAMP in DataX? A: Historical data access will be unavailable because “Default undo_retention=0 disables historical access — must be explicitly configured before critical operations.” Q: Is DataX actually available in the OceanBase Console? A: No — despite the path label, the fact card clarifies: “DataX is not accessible through the OceanBase Console — it's a command-line tool that uses JSON configuration files.” The console uses OMS, not DataX. Q: Do I need to pay extra for LOAD DATA imports? A: No — data loading via `LOAD DATA` is part of core OceanBase functionality and is not billed separately. ### [Manage transactions](https://company-skill.com/p/oceanbase/oceanbase-manage-transactions.md) ## What You Want to Do You need to coordinate data changes across multiple operations while ensuring consistency, isolation, and recoverability in OceanBase. This includes locking specific rows, executing cross-resource global transactions, or handling failures gracefully. **Typical User Questions**: - How to handle in-doubt transactions in OceanBase? - How to implement global transaction coordination? ## Decision Tree Pick the best path for your situation: - **If** you require strict ACID compliance across multiple resource managers using two-phase commit → Use DBMS_XA XA (go to *oceanbase/oceanbase-transaction*) - **If** you only need to prevent concurrent writes on selected rows within a single transaction → Use FOR UPDATE (go to *oceanbase/oceanbase-sql*) - **If** your transaction has already failed and you need to diagnose errors like missing data or constraint violations → Use (go to *oceanbase/oceanbase-errorhandling*) - **Otherwise (default)** → Start with **** if you're debugging; otherwise, use ** FOR UPDATE ** for simple concurrency control unless you explicitly need distributed XA semantics. ## Path Comparison | Path | Best For | Complexity | Code Required | Automation | Key Fact | Detail Skill | |------|----------|------------|---------------|------------|----------|-------------| | DBMS_XA XA | ACID | high | Yes | Yes | Free - These transaction management functions are included as part of the core OceanBase database functionality and are not billed separately. | `oceanbase/api/oceanbase-transaction` | | FOR UPDATE | low | Yes | Yes | Maximum 10 queries per second rate limit in regions cn-hangzhou, cn-shanghai, cn-beijing | `oceanbase/api/oceanbase-sql` | | Console / Dashboard | medium | Yes | Yes | Free - All database error handling operations via the console are included at no additional cost. | `oceanbase/guide/oceanbase-errorhandling` | ## Path Details ### Path 1: DBMS_XA XA **Best For**: ACID **Brief Description**: OceanBase’s `DBMS_XA` package implements the XA/Open standard for managing global transaction branches across multiple resource managers. It provides explicit control over the two-phase commit protocol via procedures like `XA_START`, `XA_END`, `XA_PREPARE`, `XA_COMMIT`, and `XA_ROLLBACK`. The `XA_RECOVER` function enables resolution of in-doubt transactions after failures. **Key technical facts**: - Billing: Free - These transaction management functions are included as part of the core OceanBase database functionality and are not billed separately. **When to Use**: - Application requires strict ACID compliance across multiple resource managers - Need to recover in-doubt transactions after system failure using XA_RECOVER - Working in financial or other strong-consistency scenarios - Already using XA-compatible resource managers **When NOT to Use**: - Only need row-level locking within a single transaction (use FOR UPDATE instead) - Want simple automatic transaction handling without manual XA protocol steps - Don't require cross-resource distributed transactions **Known Limitations**: - Requires explicit PL/SQL code to manage transaction lifecycle (start, end, prepare, commit) - Must handle specific XA error codes like XAER_PROTO, XA_RBDEADLOCK manually - Transaction branch ID (XID) has strict format: gtrid max 64 bytes, bqual max 64 bytes - XA_SETTIMEOUT timeout value is in seconds, not microseconds - XA_COMMIT with onePhase=TRUE skips prepare phase, reducing safety ### Path 2: FOR UPDATE **Brief Description**: OceanBase supports `SELECT ... FOR UPDATE` to place exclusive locks on queried rows, blocking other transactions from performing concurrent writes until the current transaction ends. It also supports `MULTI FOR UPDATE` for multi-table queries. This mechanism operates entirely within a single database session and does not involve distributed coordination. **Key technical facts**: - Billing: Per-request billing model where each SQL query execution is charged based on input and output operations. - Max concurrency: 10 - Regions available: cn-hangzhou, cn-shanghai, cn-beijing **When to Use**: - Need simple row-level locking to prevent concurrent modifications in a single table - Implementing basic business logic that requires exclusive access to specific records - Working with single-partition or standalone multi-partition transactions - Want low-complexity solution without XA protocol overhead **When NOT to Use**: - Require strict ACID guarantees across multiple resource managers (use DBMS_XA instead) - Need to coordinate transactions across different databases or systems - Working in financial or strong-consistency scenarios requiring two-phase commit **Known Limitations**: - Only provides row-level locking within a single transaction, not cross-resource coordination - Does not guarantee ACID across multiple databases or systems - Limited to preventing concurrent writes on selected rows, not full distributed transaction semantics - Maximum 10 queries per second rate limit - No built-in recovery mechanism for failed transactions ### Path 3: Console / Dashboard **Brief Description**: OceanBase provides Oracle-compatible PL/SQL exception handling, including predefined exceptions like `NO_DATA_FOUND` and `TOO_MANY_ROWS`, system exceptions bound via `PRAGMA EXCEPTION_INIT`, and user-defined errors raised with `RAISE_APPLICATION_ERROR`. Developers can inspect errors using `SQLCODE` and `SQLERRM`, and output debug info with `DBMS_OUTPUT.PUT_LINE`. **Key technical facts**: - Billing: Free - All database error handling operations via the console are included at no additional cost. **When to Use**: - Need to handle specific database exceptions like missing data or duplicate keys - Want to provide custom error messages for application-level errors - Working with Oracle-compatible PL/SQL code - Debugging transaction errors using SQLCODE and SQLERRM **When NOT to Use**: - Need automatic error recovery without custom code - Working only with simple SQL queries without PL/SQL blocks - Require cross-system error handling beyond database exceptions **Known Limitations**: - Only works within PL/SQL execution context (anonymous blocks, procedures, functions) - Cannot be used in standalone SQL statements - Requires Oracle-compatible mode for DBMS_OUTPUT and exception handling - Custom error messages limited to RAISE_APPLICATION_ERROR with error code -20999 - System error codes must be known in advance to bind with PRAGMA EXCEPTION_INIT ## FAQ Q: Which path should I start with? A: If you’re building new logic and only need to avoid concurrent updates on specific rows, start with ** FOR UPDATE **. If you’re in finance or integrating with external transactional systems, start with ** DBMS_XA XA **. If your code is already failing, begin with ****. Q: What if I need to coordinate updates across two different databases but used SELECT ... FOR UPDATE? A: You’ll hit a fundamental limitation: `SELECT ... FOR UPDATE` only locks rows within a single OceanBase transaction and cannot enforce atomicity across external systems, leading to potential data inconsistency. Q: What if I try to handle an XA protocol violation like XAER_PROTO using only the error handling skill without DBMS_XA context? A: You’ll miss critical recovery steps—`PRAGMA EXCEPTION_INIT` can catch the error code, but without calling `XA_RECOVER` or understanding XID state, you cannot resolve in-doubt transactions. Q: Can I use RAISE_APPLICATION_ERROR to simulate XA rollback behavior? A: No—`RAISE_APPLICATION_ERROR` only terminates the current PL/SQL block and rolls back its own transaction; it does not interact with the XA coordinator or affect other branches in a global transaction. Q: Does FOR UPDATE work in all OceanBase regions? A: It’s documented as available in `cn-hangzhou`, `cn-shanghai`, and `cn-beijing` with a 10 QPS limit; other regions may have different behavior—consult the detail skill for updates. Q: Are NO_DATA_FOUND and TOO_MANY_ROWS automatically raised during XA transactions? A: Yes—if your XA-managed DML triggers these conditions inside a PL/SQL block, they behave like any other exception and can be caught using standard handlers alongside `SQLCODE` and `SQLERRM`. ### [Optimize query](https://company-skill.com/p/oceanbase/oceanbase-optimize-query.md) ## What You Want to Do You have an SQL query in OceanBase that runs slower than expected, exhibits timing fluctuations, or consumes excessive resources, and you need to identify the root cause—such as inefficient execution plans, missing indexes, full table scans (`table_scan`), or system-level waits—and apply the appropriate optimization technique. - How to troubleshoot slow SQL in OceanBase? - How to analyze SQL performance issues in OceanBase? - OceanBase SQL - What tools can I use to tune SQL in OceanBase? ## Decision Tree Pick the best path for your situation: - **If** you are using **obclient** or a SQL CLI and need to inspect low-level diagnostics like `QUEUE_TIME`, `GET_PLAN_TIME`, `RETRY_CNT`, or `is_hit_plan` from system views → Use ** SQL ** (go to *oceanbase/oceanbase-sqlperf*) - **If** you want to interactively run `EXPLAIN` and examine `trace_id`-linked results in a terminal or basic console to spot `table_scan` or `WAIT_TIME_MICRO` per `EVENT` → Use **** (go to *oceanbase/oceanbase-query*) - **If** you are writing application code that dynamically constructs SQL using `DBMS_SQL` or needs to enforce plan choices via hints, especially with parallel workers tracked in `v$tenant_px_worker_stat` or memory usage in `gv$sql_workarea` → Use ** EXPLAIN Hints ** (go to *oceanbase/oceanbase-sql*) - **If** you are building a monitoring dashboard that polls `gv$sql_monitor`, `gv$sesstat`, `gv$sysstat`, or `gv$session_wait_history` to track `ELAPSED_TIME` and correlate with `SQL_ID` over time → Use **** (go to *oceanbase/oceanbase-monitoring*) - **Otherwise (default)** → Start with **** — it requires no coding, works with standard access, and quickly reveals obvious issues like full table scans or long waits. ## Path Comparison | Path | Best For | Complexity | Code Required | Automation | Key Fact | Detail Skill | |------|----------|------------|---------------|------------|----------|-------------| | SQL | DBA | medium | No | No | Requires access to `gv$sql_audit` and administrative privileges | `oceanbase/troubleshooting/oceanbase-sqlperf` | | Web | low | No | No | Free feature included in all OceanBase versions; uses `EXPLAIN` and `v$sql_audit` | `oceanbase/guide/oceanbase-query` | | EXPLAIN Hints | high | Yes | Yes | Billed per query beyond 1000 free monthly requests; supports `DISTINCT` and parallel execution | `oceanbase/api/oceanbase-sql` | | Console / Dashboard | medium | Yes | Yes | Free — querying `gv$sql_audit`, `gv$sql_plan_monitor`, etc., incurs no extra cost | `oceanbase/api/oceanbase-monitoring` | ## Path Details ### Path 1: SQL **Best For**: DBA **Brief Description**: This approach uses built-in system views like `gv$sql_audit` and commands such as `EXPLAIN FORMAT = 'EXTENDED'` to dissect query performance. It also leverages parameters like `ob_enable_sql_audit` and maintenance commands like `ALTER SYSTEM MAJOR FREEZE` to ensure accurate diagnostics. **Key technical facts**: - Prerequisites: administrative privileges (typically the SYS tenant or a user with SELECT on system views) ### Path 2: Console / Dashboard **Best For**: Web **Brief Description**: This method relies on running `EXPLAIN` directly in tools like `obclient` to generate execution plans, then cross-referencing with `v$sql_audit` using a `trace_id` to analyze actual runtime behavior, including `WAIT_TIME_MICRO` per wait `EVENT` and detection of `table_scan`. **Key technical facts**: - Billing: OceanBase - Prerequisites: SQL , OceanBase , , ### Path 3: EXPLAIN Hints **Brief Description**: This API-driven path enables programmatic SQL tuning using `EXPLAIN` output parsing, dynamic SQL via `DBMS_SQL`, and monitoring of parallel execution through `v$tenant_px_worker_stat` and workarea memory via `gv$sql_workarea`. It supports advanced operations like `DISTINCT` elimination and plan forcing. **Key technical facts**: - Billing: SQL 1000 - Auth method: / - Regions available: cn-hangzhou, cn-shanghai, cn-beijing - Prerequisites: host, port, user, password, database ### Path 4: Console / Dashboard **Brief Description**: This path accesses real-time and historical performance data through system views like `gv$sql_monitor`, `gv$sesstat`, `gv$sysstat`, and `gv$session_wait_history`, enabling correlation of `ELAPSED_TIME`, `WAIT_TIME_MICRO`, and `SQL_ID` across sessions and tenants. **Key technical facts**: - Billing: — OceanBase - Auth method: MONITOR DBA - Prerequisites: MySQL obclientMySQL CLIJDBC , SELECT , OceanBase 4.x ## FAQ Q: Which path should I start with? A: Start with **** if you’re a developer without admin rights—it’s low-complexity, free, and quickly shows `table_scan` or long `WAIT_TIME_MICRO` via `EXPLAIN` and `v$sql_audit`. Q: What if I need to debug query time spikes but used ****? A: You’ll miss critical low-level metrics like `QUEUE_TIME`, `GET_PLAN_TIME`, and `RETRY_CNT` that only appear in `gv$sql_audit`—switch to ** SQL ** for root-cause analysis. Q: What if I’m building an alerting system but chose ** EXPLAIN Hints **? A: You’ll incur unnecessary costs (beyond 1000 free queries/month) and lack access to system-wide metrics like `gv$sesstat` or `gv$sysstat`—use **** instead, which is free and designed for monitoring. Q: Can I use **** without admin privileges? A: Only if your user has been granted `SELECT` on system views like `gv$sql_audit`—otherwise, you’ll get permission errors. Check with your DBA or use a lower-privilege path. Q: Why does my `EXPLAIN` output differ from actual runtime behavior? A: Because `EXPLAIN` shows the estimated plan, while actual performance depends on factors like statistics freshness and concurrency. Use `EXPLAIN FORMAT = 'EXTENDED'` alongside `gv$sql_audit.is_hit_plan` to verify plan reuse. Q: Does ** EXPLAIN Hints ** work in all regions? A: It’s only available in `cn-hangzhou`, `cn-shanghai`, and `cn-beijing`—verify your instance region before relying on this path. Q: What if `ob_enable_sql_audit` is disabled? A: The `gv$sql_audit` and `v$sql_audit` views will be empty—ask your DBA to enable it via `ALTER SYSTEM SET ob_enable_sql_audit = true;` before using diagnostic or monitoring paths. ### [Secure access](https://company-skill.com/p/oceanbase/oceanbase-secure-access.md) ## What You Want to Do You want to protect sensitive data in OceanBase by either encrypting stored data, enforcing strong authentication rules, or controlling who can access which tables. This includes meeting compliance requirements like GDPR or PCI DSS. **Typical User Questions**: - How to encrypt sensitive data in OceanBase? - Can I encrypt data at rest in OceanBase? ## Decision Tree Pick the best path for your situation: - **If** you need to encrypt specific columns (e.g., ID numbers) using AES and your application can call `DBMS_CRYPTO.ENCRYPT` → Use DBMS_CRYPTO (go to *oceanbase/oceanbase-security*) - **If** you need to programmatically check whether a user has `SELECT` on a table via `information_schema.TABLE_PRIVILEGES` → Use / (go to *oceanbase/oceanbase-security*) - **If** you want entire tablespaces automatically encrypted with **Transparent Data Encryption (TDE)** and enforce a **MEDIUM** password policy → Use (go to *oceanbase/oceanbase-security*) - **Otherwise (default)** → Start with **** if you need system-wide security; otherwise, use **/** for basic access control. ## Path Comparison | Path | Best For | Complexity | Code Required | Automation | Key Fact | Detail Skill | |------|----------|------------|---------------|------------|----------|-------------| | DBMS_CRYPTO | LOB GDPR | medium | Yes | Yes | Requires data to be of `RAW` type; uses `ENCRYPT_AES128` algorithm with explicit `key` and `iv` | `oceanbase/api/oceanbase-security` | | / | low | Yes | Yes | Queries `information_schema.TABLE_PRIVILEGES` where `GRANTEE` and `PRIVILEGE_TYPE` are exposed | `oceanbase/api/oceanbase-security` | | TDE | medium | Yes | Yes | Uses `ADMINISTER KEY MANAGEMENT` to create keystore for **TDE**; supports only **MEDIUM** password policy | `oceanbase/api/oceanbase-security` | ## Path Details ### Path 1: DBMS_CRYPTO **Best For**: LOB GDPR **Brief Description**: `DBMS_CRYPTO` is a PL/SQL package in OceanBase that provides industry-standard encryption functions like `DBMS_CRYPTO.ENCRYPT` and `DBMS_CRYPTO.DECRYPT`. It supports algorithms such as `ENCRYPT_AES128` and requires input data to be in `RAW` format. The application must manage the encryption `key`, initialization vector (`iv`), and `algorithm` explicitly. **Key technical facts**: - Billing: Free — included with OceanBase database license at no additional cost **When to Use**: - LOB GDPR ### Path 2: / **Brief Description**: This approach uses SQL queries against `information_schema.SCHEMA_PRIVILEGES`, `information_schema.TABLE_PRIVILEGES`, and `information_schema.USER_PRIVILEGES` to inspect granted privileges. The `GRANT` statement is used to assign permissions, with metadata fields like `GRANTEE`, `PRIVILEGE_TYPE`, and `IS_GRANTABLE` indicating current access rights. **Key technical facts**: - Billing: Free — included with OceanBase database license at no additional cost ### Path 3: Console / Dashboard **Best For**: TDE **Brief Description**: This method uses the `ADMINISTER KEY MANAGEMENT` statement to create and open a keystore, enabling **Transparent Data Encryption (TDE)** for tablespaces. It also configures a built-in **password policy** with predefined levels, including **MEDIUM**, which enforces basic complexity rules without custom logic. **Key technical facts**: - Billing: Free — included with OceanBase database license at no additional cost - PCI DSS - LOW MEDIUM ## FAQ Q: Which path should I start with? A: If you're building a new system and need broad protection, start with **** to enable **TDE** and **MEDIUM password policy**. If you only need to verify or assign table access, begin with **/**. Q: What if I need to encrypt credit card numbers but chose ****? A: You’ll get full-tablespace encryption via **TDE**, but you won’t have field-level control or masking—credit card numbers will be encrypted along with all other data, making selective decryption or auditing harder than with `DBMS_CRYPTO`. Q: What if I try to use `DBMS_CRYPTO` on a `VARCHAR` column without converting to `RAW`? A: The `DBMS_CRYPTO.ENCRYPT` function will fail because it only accepts `RAW` input. You must explicitly convert using `UTL_RAW.CAST_TO_RAW` or similar—this is a hard limitation listed in the fact card. Q: Can I combine `DBMS_CRYPTO` and **TDE**? A: Yes—they operate at different layers. **TDE** encrypts the entire tablespace on disk, while `DBMS_CRYPTO` adds an extra application-layer encryption for sensitive fields. This is common in high-compliance environments. Q: What if my tenant already has a keystore but I need a second one for a new schema? A: You’ll hit the limitation that **keystore**—OceanBase does not allow multiple keystores per tenant, so you must reuse the existing one or rearchitect. Q: Why can’t I see all users’ privileges when querying `information_schema.USER_PRIVILEGES`? A: Due to the limitation “”, you’ll only see privileges for objects your user has been granted access to—this is intentional for security isolation. ## Frequently asked questions ### Should I use the API or the console for a task? Use the **console** for one-off operations, visual diagnostics, or guided workflows (e.g., backup setup). Use the **API** for automation, integration into applications, or programmatic control (e.g., loading data from code). ### How do I connect to OceanBase programmatically? Use JDBC or ODBC drivers with standard connection strings. Refer to the *Database Access* API skill for details on drivers and connection parameters. ### Where can I diagnose a slow SQL query? Start with the **intent skill** “Optimize a slow-running SQL query.” For programmatic analysis, use the *SQL Optimization* API skill; for visual exploration, use the *SQL Optimization* guide skill in the console. ### How do I handle an “in-doubt” transaction? Use the *Transaction Management* API skill to recover or synchronize in-doubt XA transactions via DBMS_XA or DIST_TXN_SYNC. ### Can I encrypt sensitive data in OceanBase? Yes—use TDE (Transparent Data Encryption) for tables/tablespaces or DBMS_CRYPTO for application-level encryption. See the *Security* API skill for implementation. ### How do I import or export data to and from OceanBase? You can import or export data by using dedicated data loading APIs and console migration tools to move information between OceanBase and external systems. Supported workflows include bulk loading from external files and utilizing OMS, DataX, or NFS for backup and recovery operations. ### How do I manage distributed database transactions in OceanBase? You manage distributed transactions by implementing XA protocols to maintain ACID compliance across multiple nodes. This approach enables reliable two-phase commit workflows that handle prepare, commit, and rollback states for global operations. ### How do I optimize a slow-running SQL query in OceanBase? You optimize slow-running queries by analyzing execution plans and applying SQL outlines or hints to reduce execution time. You can further improve performance by using the console to visualize query plans or running diagnostic tools to resolve bottlenecks. ### How do I secure database access and protect data in OceanBase? You secure database access and protect data by configuring access control, encryption, and compliance settings. This process involves managing user privileges, enabling Transparent Data Encryption (TDE), and enforcing strict password policies. ## Cross-product integrations - [AI Content Engine with Public Site and Enterprise Search](https://company-skill.com/p/_combos/ai-content-engine-with-public-site-and-enterpris-9db7c8.md) (alinux + cloudflare + bailian + notion + vercel) - [AI Content Platform on Managed Infrastructure](https://company-skill.com/p/_combos/ai-content-platform-on-managed-infrastructure-265158.md) (alinux + cloudflare + bailian + notion + vercel) - [AI Content Platform with Search and Frontend](https://company-skill.com/p/_combos/ai-content-platform-with-search-and-frontend-d3ca31.md) (alinux + cloudflare + bailian + notion + vercel) - [AI Content Platform with Site and Search](https://company-skill.com/p/_combos/ai-content-platform-with-site-and-search-7bf25b.md) (alinux + cloudflare + bailian + notion + vercel) - [AI-Driven Search Knowledge Platform](https://company-skill.com/p/_combos/ai-driven-search-knowledge-platform-803ad0.md) (alinux + cloudflare + bailian + notion + vercel) - [Bailian RAG + ES Chatbot with EventBridge Alerts](https://company-skill.com/p/_combos/bailian-rag-es-chatbot-with-eventbridge-alerts-873dcb.md) (eb + es + bailian + dataworks + ess) - [Branded SaaS Onboarding with Custom Stripe Elements](https://company-skill.com/p/_combos/branded-saas-onboarding-with-custom-stripe-eleme-eab729.md) (clerk + stripe + ecs + oss + terraform) - [CI/CD-Automated RAG-Enabled Secure Production Stack](https://company-skill.com/p/_combos/ci-cd-automated-rag-enabled-secure-production-st-30e7b2.md) (cas + ecs + terraform + oss + alinux) ## Use with an AI agent ```bash curl -s https://company-skill.com/api/route \ -H 'Content-Type: application/json' \ -d '{"query": "...", "product": "oceanbase"}' ``` MCP server: https://company-skill.com/api/mcp/oceanbase.py --- Machine-readable: https://company-skill.com/llms.txt · https://company-skill.com/sitemap.xml