Not everything stored in a database and schema is data. Oracle also manages source modules and supporting structures (such as sequences) that are used to populate new unique and primary key values when inserting data into the database. These objects belong to a schema and are stored in the Oracle Catalog. These, as well as view properties of the objects, can all be easily managed through SQL Developer, as shown in Figure 1. Take a look at the following for a brief description of these logical structures:
- Views Views give you the capability of subsetting a table and combining multiple tables through a single named object. You can think of them as a stored query. With the exception of a special type of view called a materialized view, which is used for data warehousing, data is not stored in views. Views are simply a new way of defining access to the underlying tables, which can be used for security, performance, and ease-of-use.
- Synonyms Synonyms are used to create a new name or alias for another database object such as a table, view, or another synonym, and sources such as a procedure, package, function, java class, and so on. They can be used to simplify access. As with views, data is not stored in a synonym.
- Sequences Sequences are used to generate new unique numbers that can be used by applications when inserting data into tables. Selecting the NEXTVAL from the sequence provides a unique identifier. Also available with 12c is the identity data type that will automatically increment for an ID column.
- Source programs These can be stored in the catalog and written either in Oracle’s proprietary PL/SQL or in Java. PL/SQL source types include business logic that can be written as packages, procedures, and functions. Triggers can also be used to implement business logic, but are often used to implement data integrity because they are not executed directly by a user or source program, but rather are automatically executed when an action is performed on the database. Java sources and Java classes are also implemented directly in Oracle. This server-side support of application logic improves performance because the logic resides with the data, and the source code and data is backed up together.
- User types These can be created by you to support object-oriented development. Array types, object types, and table types can all be created by you. Also, the Oracle XML Schema Processor supports XML processing by adding data types to XML documents that can be used to ensure the integrity of data in XML documents.
So now that you have seen all of the schema objects, it’s time to tie these together to your storage architecture.
The physical schema objects are stored as segments in the database. Each segment can be stored in only a single tablespace, with a tablespace being made up of one or more data files. If a tablespace is running out of space, you can expand the data files it is made up of, or you can also add a new data file to the tablespace. A data file can only store data for a single tablespace.
A single tablespace can store data for multiple segments and, in fact, for several segment types. Segments from multiple schemas can also exist in the same tablespace. So, for example, table_a from schema1 and index_b from schema2 can both be implemented in the same tablespace. Oh, and by the way, a tablespace can store data for a single database only.
The logical structures such as views and source code are stored in the Oracle Catalog but are part of a schema. So, this means that the Oracle-supplied SH schema can contain all of the objects that it needs to run the entire application under its own schema name. This provides strong security and management benefits.