In this blog, we have tried to explain the process to extend the 5GB data limitation of Oracle VBCS business objects. In order to understand the process, it is first important to understand business objects and why there is a requirement to change the Tenant database. So, let’s begin.
What is Business Object?
A business object is a resource, similar to a database table. Like a database table, a business object provides the structure for data. Business objects are stored in a database. The apps in your visual application and other clients access the business objects via their REST endpoints. When a business object is created, all possible endpoint types are autogenerated.
Oracle Visual Builder comes with a built-in database that is used to store data for your business objects (and your app’s metadata too). That DB, however, is limited to 5GB in size. This can be overcome by switching to your own database, you can change the Visual Builder embedded database to use your own Oracle cloud database (ATP/DBaaS).
This simple change can be done in your VB instance settings.
Why Change Tenant DB ?
- Oracle Visual Builder (VBCS)automatically manages the schema and tables/views in ATP.
- Autonomous Transaction Process ATP with VBCS provides higher storage capacity than BO.
- REST APIs needed to perform CRUD operations against the table/view are autogenerated.
Important Note: Take a backup of all your applications in your current DB, post switch – applications present in the previous tenant’s DB will be lost.
Prerequisites :
If you decide to use JDBC to connect to your DBaaS instance, you must include the
privileges required to enable the ADMIN user to create a tenant schema. The following
SQL shows the grants that are needed:
CREATE USER [adminuser] IDENTIFIED BY [password];
GRANT CONNECT, RESOURCE, DBA TO [adminuser];
GRANT SELECT ON SYS.DBA_PROFILES TO [adminuser] WITH GRANT OPTION;
GRANT SELECT ON SYS.DBA_USERS TO [adminuser] WITH GRANT OPTION;
GRANT SELECT ON SYS.DBA_DATA_FILES TO [adminuser] WITH GRANT OPTION;
GRANT SELECT ON SYS.DBA_SEGMENTS TO [adminuser] WITH GRANT OPTION;
If you decide to use ATP, you’ll need to include the wallet.zip file in the wizard in
addition to the connection info. You might want to create a new ATP ADMIN user with
the correct admin privileges. The following SQL statement shows how to create a
second ATP ADMIN user in SQL*Plus or SQL Developer.
DROP USER [adminuser] CASCADE;
CREATE USER [adminuser] IDENTIFIED BY [password];
GRANT CREATE USER, ALTER USER, DROP USER, CREATE PROFILE TO
[adminuser] WITH ADMIN OPTION;
GRANT CONNECT TO [adminuser] WITH ADMIN OPTION;
GRANT RESOURCE TO [adminuser] WITH ADMIN OPTION;
GRANT CREATE SEQUENCE, CREATE OPERATOR, CREATE SESSION,ALTER SESSION,
CREATE PROCEDURE, CREATE VIEW, CREATE JOB,CREATE DIMENSION,CREATE
INDEXTYPE,CREATE TYPE,CREATE TRIGGER,CREATE TABLE,CREATE PROFILE TO
[adminuser] WITH ADMIN OPTION;
GRANT UNLIMITED TABLESPACE TO [adminuser] WITH ADMIN OPTION;
GRANT SELECT ON SYS.DBA_PROFILES TO [adminuser] WITH GRANT OPTION;
GRANT SELECT ON SYS.DBA_USERS TO [adminuser] WITH GRANT OPTION;
GRANT SELECT ON SYS.DBA_DATA_FILES TO [adminuser] WITH GRANT OPTION;
GRANT SELECT ON SYS.DBA_SEGMENTS TO [adminuser] WITH GRANT OPTION;
Let’s Start
Here I am switching from a Visual builder embedded database to Oracle ATP (Autonomous Transaction Processing) database.
Step 1: Log in to your Visual Builder instance as Admin.
Step 2: Navigate to Settings and then to Tenant Database. On the Tenant Database tab, Click on Use Different Database.
Step 3: There are two types of connection available here, one is the Oracle Autonomous Transaction Processing Cloud Wallet and the Oracle Database Connection with JDBC.
In this process, I have used Oracle Autonomous Transaction Processing Cloud Wallet.
When switching to Oracle ATP, Cloud Wallet, and its password, DBA Username, and its password is required.
If Applications on the current DB is not exported then Oracle provides the option to export all the application present in the DB.
Select the required applications to be exported, once we click on Finish, all the applications present on this DB will be lost.
After clicking on Finish, Visual builder reloads the page with your Oracle ATP DB. This completes the process of switching from Visual Builder embedded DB to your own Database.
Recommended for you: How To Upload A File In VBCS Using File Picker
Attach a new schema to your tenant’s current database
When you connect an Oracle database instance with your Visual Builder instance, application developers can use schemas predefined in the tenant database to create business objects based on existing tables and views for an application. But for developers to access these schemas, you’ll first need to make them available to applications.
To make a tenant database’s existing schema available to applications:
- Open the instance’s Tenant Settings page.
- Click + New Schema in the Available Schemas panel.
The schema that exists in the tenant database and has been added to the list of available schemas and will become available for selection in an application’s Settings editor (under Schema Selection in the Business Objects tab). Instead of Default Managed Schema, select the schema which you added earlier.
Now, when creating a Business Object, a list of Tables/Views are available to select from the schema attached to Business Objects.
When you click create, VB creates all the REST APIs needed to perform CRUD operations against the table/view.
When two objects have a relationship defined in the database (foreign keys), the relationship will also be created between the business objects that represent those DB objects.
This is how you can switch embedded DB with Autonomous Transaction Process Database in Oracle Visual Builder Cloud Service VBCS.
We hope this document will help you to extend the 5GB limitation in Oracle VBCS effortlessly. Let us know if you have any questions in the comments or get in touch with us at Conneqtion Group.