Databases Lab1

Tablespace

A tablespace is the biggest logical storage unit. Data with similar behavior and maintenance need is usually stored in a tablespace. Tablespaces provide means to manage the database data storage; to distribute data between tablespaces, to define quotas for users etc.

system

tablespace containing system information (such as data dictionary),

sysaux

Extended tablespace, which was introduced in 10g.

Some of the functionalities moved to this tablespace from other tablespace.

These functionalities includes LogMiner (can be used to parse database logs) and Oracle Data Mining (data mining utility) and many others.

rbs

in the versions before Oracle 9 this tablespace contains the logs and results of database operations (for more information please refer to the chapter describing the rollback segments). In the later versions this role is filled by the undo tablespace.

temp

the tablespace for storing all temporary data (e.g. partial result sets of queries, sorting results),

tools

the tablespace storing the patterns used by applications and general things (e.g. the Form Builder),

users

an example of a general user tablespace.

Table

It is the base unit of logical data storage, which consists of rows and columns. It can be mapped into a relation.

It can be unambiguously identified by its name (e.g. SCOTT.EMP).

Columns are characterized by their name, type and size.

A table can store only one type of data record. The order of the rows is irrelevant.

When creating a so-called, so-called name, the column names, types and sizes must be defined.

View

A user object that enables displaying data gathered from one or more tables.

It is similar to a stored query; it does not either contain or store data (physically), only derives data from tables on which it was defined. These tables are called the base tables or master tables of the view.

Sequence

Continuous and unique number generator generally used to create unique identifiers.

Please note, that the thing value is not transaction-oriented, meaning that for example a rollback operation does not modify the thing value.

Synonym

It can be used to assign more than one names to a table, a view or a sequence.

Basically, it can be used to shorten or to make transparent the storage place of an object.

It can be either public or private.

The public one can be used by everyone, while a private one is accessible for only a certain group of users.

A user needs special authorization to create and delete public.

Index

a great tool to increase data access speed - in Oracle it is essentially a B* tree.

It is recommended to create a thing for table column(s) that are frequently used in queries. Things are automatically created for those columns that are marked as unique during table creation. Things are automatically refreshed by the system.

A thing can be complex, meaning that the thing consists of more fields.

In case of such complex things high-cardinality columns should be at the beginning of the attribute list.

In certain cases (in critical, resource-demanding environments) thing creation requires precise design work, since an inappropriate choice of things can slow down the system.

Group

A structure containing more servers in Oracle 9i.

Things are used to facilitate system administration.

Database Link

a special synonym used for addressing databases.

It has already been noted that a server computer might contain multiple databases, moreover there can be distributed databases stored on different computers which are data-wise connected.

In these cases it might be required to define a thing.

Data Dictionary

a central set of read-only reference tables and views containing the actual state of the system. It contains all information on the users currently logged into the system, on the object accessible by these users; the constraints related to each field; column properties; how much storage space is occupied by the objects, how much space is free; action logs, change logs etc.

Server

In the present document by thing we always mean a database and a server instance.

Database

One server computer can host more than one of its kind. The highest-level storage unit identified by a name is a thing, which is in fact more than just a massive amount of data.

Instance

It defines the server behavior, its operations.

SQL

Oracle is a relational (in fact, object-relational) database management system which is maintained and operated by a standard language called SQL (SQL:2008 to be precise).

Tablespace objects (units)

Things contain system or user objects, also known as units.

These might be tables, views, sequences, synonyms, indexes, groups, clusters, database links etc. and complex structures created based on these units, such as data dictionary, (Oracle) schema, and (executable) stored procedures.

Data Dictionary is a central set of read-only useful information about a database containing the actual state of the system (True / False)

True

rows and columns

Table: the base unit of logical data storage, which consists of

False

Data access times can significantly decrease upon reading from disk if related data are stored physically “close”. Thus, It is more efficient to store data in an unclustered manner

The set of objects accessible by a given user is called? Cluster Stored Procedures The user's schema

The user's schema

NUMBER (s, p) is Data type to display an arbitrary number and characters (True / False)

False;