Getting Started with the Oracle Berkeley DB SQL APIs

Legal Notice

Copyright © 2002 - 2019 Oracle and/or its affiliates. All rights reserved.

This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is prohibited.

The information contained herein is subject to change without notice and is not warranted to be error-free. If you find any errors, please report them to us in writing.

Berkeley DB, and Sleepycat are trademarks or registered trademarks of Oracle. All rights to these marks are reserved. No third-party use is permitted without the express prior written consent of Oracle.

Other names may be trademarks of their respective owners.

If this is software or related documentation that is delivered to the U.S. Government or anyone licensing it on behalf of the U.S. Government, the following notice is applicable:

U.S. GOVERNMENT END USERS: Oracle programs, including any operating system, integrated software, any programs installed on the hardware, and/or documentation, delivered to U.S. Government end users are "commercial computer software" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, use, duplication, disclosure, modification, and adaptation of the programs, including any operating system, integrated software, any programs installed on the hardware, and/or documentation, shall be subject to license terms and license restrictions applicable to the programs. No other rights are granted to the U.S. Government.

This software or hardware is developed for general use in a variety of information management applications. It is not developed or intended for use in any inherently dangerous applications, including applications that may create a risk of personal injury. If you use this software or hardware in dangerous applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure its safe use. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software or hardware in dangerous applications.

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Intel and Intel Xeon are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks are used under license and are trademarks or registered trademarks of SPARC International, Inc. AMD, Opteron, the AMD logo, and the AMD Opteron logo are trademarks or registered trademarks of Advanced Micro Devices. UNIX is a registered trademark of The Open Group.

This software or hardware and documentation may provide access to or information on content, products, and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to third-party content, products, and services. Oracle Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of third-party content, products, or services.

19-Feb-2019


Table of Contents

Preface
Conventions Used in this Book
For More Information
Contact Us
1. Berkeley DB SQL: The Absolute Basics
BDB SQL Is Nearly Identical to SQLite
Getting and Installing BDB SQL
On Windows Systems
On Unix
The BDB SQL ADO.NET Interface
Accessing BDB SQL Databases
The Journal Directory
User Authentication
BDB User Authentication
BDB SQL Key-store Based User Authentication
Unsupported PRAGMAs
Changed PRAGMAs
PRAGMA auto_vacuum
PRAGMA cache_size
PRAGMA incremental_vacuum
PRAGMA journal_size_limit
Added PRAGMAs
PRAGMA bdbsql_error_file
PRAGMA bdbsql_lock_tablesize
PRAGMA bdbsql_shared_resources
PRAGMA bdbsql_single_process
PRAGMA bdbsql_system_memory
PRAGMA bdbsql_upgrade
PRAGMA bdbsql_vacuum_fillpercent
PRAGMA bdbsql_vacuum_pages
PRAGMA large_record_opt
PRAGMA multiversion
PRAGMA snapshot_isolation
PRAGMA statistics
PRAGMA statistics_file
PRAGMA trickle
PRAGMA txn_bulk
Replication PRAGMAs
PRAGMA bdbsql_userauth_add
PRAGMA bdbsql_user_login
PRAGMA bdbsql_user_edit
PRAGMA bdbsql_user_delete
Miscellaneous Differences
Berkeley DB Concepts
Encryption
Berkeley DB encryption
SQLite Encryption Extension
Using Sequences
create_sequence
nextval
currval
drop_sequence
Differences for Users of other SQL Engines
2. Locking Notes
Internal Database Usage
Lock Handling
SQLite Lock Usage
Lock Usage with the BDB SQL Interface
3. Berkeley DB Features
Using Bulk Loading
Using Multiversion Concurrency Control
Selecting the Page Size
Controlling the Number of Accumulated Log Files
4. Using DB_CONFIG to configure the Berkeley DB SQL interface
Introduction to Environments
The DB_CONFIG File
Creating the DB_CONFIG File Before Creating the Database
Re-creating the Environment
Configuring the In-Memory Cache
5. Using Replication with the SQL API
Replication Overview
Replication Masters
Elections
Durability Guarantees
Permanent Message Handling
Two-Site Replication Groups
Replication PRAGMAs
PRAGMA replication
PRAGMA replication_ack_policy
PRAGMA replication_ack_timeout
PRAGMA replication_get_master
PRAGMA replication_initial_master
PRAGMA replication_local_site
PRAGMA replication_num_sites
PRAGMA replication_perm_failed
PRAGMA replication_priority
PRAGMA replication_remote_site
PRAGMA replication_remove_site
PRAGMA replication_site_status
PRAGMA replication_verbose_output
PRAGMA replication_verbose_file
Displaying Replication Statistics
Replication Usage Examples
Example 1: Distributed Read at 3 Sites
Example 2: 2-Site Failover
6. Administrating Berkeley DB SQL Databases
Backing Up Berkeley DB SQL Databases
Backing Up Replicated Berkeley DB SQL Databases
Syncing with Oracle Databases
Syncing on Unix Platforms
Syncing on Windows Platforms
Data Migration
Migration Using the Shells
Catastrophic Recovery
Database Statistics
Verify Database Structure
A. Using the BFILE Extension
Supported Platforms and Languages
BFILE SQL Objects and Functions
BFILE_CREATE_DIRECTORY
BFILE_REPLACE_DIRECTORY
BFILE_DROP_DIRECTORY
BFILE_NAME
BFILE_FULLPATH
BFILE_OPEN
BFILE_READ
BFILE_CLOSE
BFILE_SIZE
BFILE C/C++ Objects and Functions
sqlite3_column_bfile
sqlite3_bfile_open
sqlite3_bfile_close
sqlite3_bfile_is_open
sqlite3_bfile_read
sqlite3_bfile_file_exists
sqlite3_bfile_size
sqlite3_bfile_final