Switch to Your Own Oracle DB Instance
The database provisioned with your Visual Builder instance is used to store data for your business objects and your app's metadata, but this database has a 5GB limit and you can't access the data in the objects using regular SQL.
If the 5GB limit is insufficient for your tenant schema, you can configure your instance to use an Oracle DB instance that has more space instead of the default database. If you choose to switch to an Oracle DB instance, the database must be publicly accessible. You can connect to an Oracle DBaaS or Autonomous Transaction Processing (ATP) database instance. Using an ATP database will give you more space and direct SQL access to the objects VB creates. You can also use a Free Forever Oracle ATP, which provides 20GB of storage for free.
To use a different Oracle DB instance, you use a wizard in the Tenant Settings to create a connection to the database instance and export the applications stored in the tenant's current database.
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;Note:
If you get an error Failed to verify the target database in the Change Tenant Database dialog when switching the database, it might be because the database is not reachable (Visual Builder cannot reach databases in private subnets), or because you don't have the required privileges.
If you see the error, confirm that the ADMIN user (adminuser) has the required privileges. You might also need to assign the SYSOPER and SYSDBA roles to the ADMIN user:
GRANT SYSOPER, SYSDBA TO [adminuser];You can run the following query to confirm the ADMIN user has the necessary privileges:
select * from v$pwfile_users;In the wizard you need to select and export all the applications in your instance that you want to keep. After confirming that your instance is using the new database instance, you must import the exported applications into Visual Builder to save them in the new database instance.
Note:
If you have live applications already on the instance:
- Before switching to a new database, make sure to backup the data in their business objects using the export options in the Visual Builder data manager. You'll then be able to import that data back into the new apps you'll create from the application archives you export in the wizard.
- Lock the live applications before changing the settings of your instance's database to prevent users from using them during the migration process. You can unlock the applications when the migration process is finished. You lock and unlock live applications in the Application Options menu on the Visual Builder Home page. See Manage an Application in Developing Applications with Oracle Visual Builder in Oracle Integration 3.
To switch to a different Oracle DB instance:
- In the upper-left corner of the Visual Builder title bar, click Navigation Menu
. - Click Settings in the navigation menu to open Tenant Settings.
- Click Use Different Database in the Tenant Database panel to open the Change Tenant Database wizard.
In the Change Tenant Database wizard you supply the details for the connection to your Oracle DB instance.
- Select all the applications that you want to export. Click Finish.
You must select and export all the applications that you want to keep. Any applications that are not exported will be lost.
When you click Finish, the applications that you selected are downloaded to your local file system. Exported application archives include the details about the application's user roles, and they will be available when you re-import your app into the new database.
After switching the database, the Tenant Database pane displays the connection information for your tenant's database. In the following image you can see that the instance is now using an Autonomous Transaction Processing (ATP) database instance.
Note:
If you decide to revert back to using the embedded database, you can click 
in the Tenant Database pane. You'll be prompted to confirm that you want to switch to using the instance's embedded database instead of the current one.
When you revert to using the embedded database, the visual applications in your current database are not transferred automatically. You need to export the apps you want to keep before switching the database, and then import them into the embedded database.
Visual Builder automatically manages the schemas and tables it uses for apps and business objects in your new DB, so you don't need to do anything further.
If you would like to access the business objects using SQL, you'll find that VB creates users/schemas with names that start with VB_ followed by randomly generated strings. By examining the data dictionary you'll be able to find the users that represent specific apps. Note that you'll see separate schemas for dev, stage, and published instances of an app. The schemas for the dev and test instances will be re-created with different names with every new version of the app that you create. If you want to prevent the schema name for a published app from changing, when you publish new versions of the app you should choose the option to not replace the data.
Note:
Instead of having Visual Builder create and manage schemas, you can make a schema that already exists in your database available to applications, so developers can create business objects based on existing DB tables and views. In this case, only one schema is used for the app's dev, staged, and published instances. See Make Schemas in an Oracle DB Instance Available to Applications.