Introduction

Oracle is a powerful and robust database system commonly used in many enterprise applications. If you are using Oracle with your TracerPlus Connect project, you should know that it is important to specify a schema when you are logging in to the Oracle database. While not necessary, this will help cut down the time needed to load your Oracle tables and views. If you are using the Oracle system account, this is essential to avoid a very long wait time. This document will explain what schemas are, and why you should use them.

Oracle Schemas

Oracle, at its heart, is a relational database. It uses a superset of SQL – PLSQL – as the command language for all its database related operations; as such, it does some things differently than a standard SQL database.

A SQL schema is normally defined to be the “blueprint” of the database: the schema contains all the tables, views, stored procedures, and other database objects that make up the database. There is one schema for the entire database. An Oracle schema takes this idea and extends it further: every user account (including the Oracle system account) has its own schema. This “user schema” defines what tables, views, and other database objects that the “owning user” has access to. The schema usually has the same name as the user account name.

It is important to note that a user can be granted permission to access schemas different than their own. The Oracle system user, by default, has access to all the schemas of the Oracle database. This also includes the internal database objects that are used by Oracle itself, and not meant for a user to access (although, they have the ability to do so via the system account). Depending on the configuration, the number of database objects that the system account has access to can reach or even exceed 25,000 items; attempting to load all 25,000 will take a long time and hamper the efficiency of your workflow.

Best Practices

If you are using the Oracle system account, make sure to specify the schema for the tables and views you want to access. The schema name is usually in all uppercase. If you are attempting to access a user-specific schema with the system account, it will be the user account name, in all caps.

Using a schema is not restricted to the Oracle system account. You can specify a schema for a normal user account since standard users can access multiple schemas as well. If you are logging in as a normal user and you only have access to one schema, it is not necessary to specify the schema, as the database will only return the tables and views that you have access to under your own schema. However, it is still good practice to specify: it adds an additional layer of organization so you know exactly what objects you are accessing for a specific TracerPlus Connect process, and it can’t hurt.